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

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


Will you give this article a +1 ? Thanks in advance


About The Author

Suprotim Agarwal
Suprotim Agarwal, ASP.NET Architecture MVP (Microsoft Most Valuable Professional) works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He is the Editor of a Developer Magazine called DNC Magazine. He has also written two EBooks 51 Recipes using jQuery with ASP.NET Controls. and The Absolutely Awesome jQuery CookBook

Follow him on twitter @suprotimagarwal

5 comments:

Anonymous said...

Excellent article, it was a tremendous help, thank you much!!

jonathan maes said...

thanks, it was very helpfull

Ruchira said...

Thanks alot!!!!!!!!!!!!

Unknown said...

Small problem here. UsedRange.Cells.Rows.Count will return the total number of rows that are used, not the maximum used row number. If your first first row is blank then the 2nd row counts as the first in the range. If you have information from row 2 through 40, your count will be 39. If you then do a read from every row 1 through 39, as in your existing article, you will miss row 40.

Hafiz Muhammad Najmul Hasnain said...

Excellent