Import Excel Data Into an ASP.NET GridView using Microsoft.Office.Interop.Excel

Tweet

In my previous article, we saw how to import Excel Data into an ASP.NET GridView using OLEDB. In this next article, we will see how to import Excel Data Into an ASP.NET GridView using Microsoft.Office.Interop.Excel. This untested code was written by Vince Xu -MSFT and I thought of sharing it with you.

C#

using Microsoft.Office.Interop.Excel;
using System.Text;
using System.Reflection;

public partial class ImportDD : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
    DataSet ds = GetExcel("c:\\aa.xls");
    GridView1.DataSource = ds;
    GridView1.DataBind();
}

public DataSet GetExcel(string fileName)
{
Application oXL;
Workbook oWB;
Worksheet oSheet;
Range oRng;
try
{
    //  creat a Application object
    oXL = new ApplicationClass();
    //   get   WorkBook  object
    oWB = oXL.Workbooks.Open(fileName, Missing.Value, Missing.Value,
            Missing.Value, Missing.Value, Missing.Value,
            Missing.Value, Missing.Value, Missing.Value,
            Missing.Value, Missing.Value, Missing.Value, Missing.Value,
            Missing.Value, Missing.Value);

    //   get   WorkSheet object
    oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets[1];
    System.Data.DataTable dt = new System.Data.DataTable("dtExcel");
    DataSet ds = new DataSet();
    ds.Tables.Add(dt);
    DataRow dr;

    StringBuilder sb = new StringBuilder();
    int jValue = oSheet.UsedRange.Cells.Columns.Count;
    int iValue = oSheet.UsedRange.Cells.Rows.Count;
    //  get data columns
    for (int j = 1; j <= jValue; j++)
    {
        dt.Columns.Add("column" + j, System.Type.GetType("System.String"));
    }

    //  get data in cell
    for (int i = 1; i <= iValue; i++)
    {
        dr = ds.Tables["dtExcel"].NewRow();
        for (int j = 1; j <= jValue; j++)
        {
            oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i, j];
            string strValue = oRng.Text.ToString();
            dr["column" + j] = strValue;
        }
        ds.Tables["dtExcel"].Rows.Add(dr);
    }
    return ds;
}
catch (Exception ex)
{
    return null;
}
finally
{
    Dispose();
}
}

}

VB.NET

Imports Microsoft.Office.Interop.Excel
Imports System.Text
Imports System.Reflection

Partial Public Class ImportDD
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        Dim ds As DataSet = GetExcel("c:\aa.xls")
        GridView1.DataSource = ds
        GridView1.DataBind()
    End Sub

    Public Function GetExcel(ByVal fileName As String) As DataSet
        Dim oXL As Application
        Dim oWB As Workbook
        Dim oSheet As Worksheet
        Dim oRng As Range
        Try
            '  creat a Application object
            oXL = New ApplicationClass()
            '   get   WorkBook  object
oWB = oXL.Workbooks.Open(fileName, Missing.Value, Missing.Value,_
Missing.Value, Missing.Value,Missing.Value, Missing.Value, Missing.Value, _
Missing.Value, Missing.Value,Missing.Value, Missing.Value, Missing.Value, _
Missing.Value, Missing.Value)

            '   get   WorkSheet object
            oSheet = CType(oWB.Sheets(1),  _
            Microsoft.Office.Interop.Excel.Worksheet)
            Dim dt As New System.Data.DataTable("dtExcel")
            Dim ds As New DataSet()
            ds.Tables.Add(dt)
            Dim dr As DataRow

            Dim sb As New StringBuilder()
            Dim jValue As Integer = oSheet.UsedRange.Cells.Columns.Count
            Dim iValue As Integer = oSheet.UsedRange.Cells.Rows.Count
            '  get data columns
            For j As Integer = 1 To jValue
                dt.Columns.Add("column" & j, _
                               System.Type.GetType("System.String"))
            Next j

            '  get data in cell
            For i As Integer = 1 To iValue
                dr = ds.Tables("dtExcel").NewRow()
                For j As Integer = 1 To jValue
                    oRng = CType(oSheet.Cells(i, j),  _
                    Microsoft.Office.Interop.Excel.Range)
                    Dim strValue As String = oRng.Text.ToString()
                    dr("column" & j) = strValue
                Next j
                ds.Tables("dtExcel").Rows.Add(dr)
            Next i
            Return ds
        Catch ex As Exception
            Return Nothing
        Finally
            Dispose()
        End Try
    End Function

End Class

Tweet

About The Author

Suprotim Agarwal, Developer Technologies MVP (Microsoft Most Valuable Professional) is the founder and contributor for DevCurry, DotNetCurry and SQLServerCurry. He is the Chief Editor of a Developer Magazine called DNC Magazine. He has also authored two Books - 51 Recipes using jQuery with ASP.NET Controls. and The Absolutely Awesome jQuery CookBook.

Follow him on twitter @suprotimagarwal.