Monday, August 10, 2009

Read Excel file in Asp.Net

using Microsoft.Office.Interop.Excel;

private Excel.Application ExcelObj = null;

// Create Object with File path
Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open((Server.MapPath("..//Data") + "\\"
+ fu.FileName), 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, true, true);

// get the collection of sheets in the workbook
Microsoft.Office.Interop.Excel.Sheets sheets = theWorkbook.Worksheets;

// get the first and only worksheet from the collection of worksheets
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);

// Loop through total row count
for (int i = 0; i < worksheet.Rows.Count; i++)
{
// Get value from ranges.
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A" + i.ToString(), "B" + i.ToString());

// In Array, You will get the cell value
System.Array myvalues = (System.Array)range.Cells.Value2;

// By Row, Column
string Value1 = myvalues.GetValue(1, 1) != null ? myvalues.GetValue(1, 1).ToString() : string.Empty;
string Value2 = myvalues.GetValue(1, 2) != null ? myvalues.GetValue(1, 2).ToString() : string.Empty;

}

Read Excel File with out Excel Object
=====================================

string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Server.MapPath("") + ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection con = new OleDbConnection(strConn);
con.Open();
if (con.State == ConnectionState.Open)
{

OleDbDataAdapter adp = new OleDbDataAdapter("Select * From [test$A1:D65536]", con);
DataSet dsXLS = new DataSet();
adp.Fill(dsXLS);
}
con.Close()

2 comments:

Anonymous said...

Hi,

nice article Milind. But both Interop and OLEDB are very slow way to read Excel files, especially if you are having large number of data. You may try using 3rd party components such as GemBox .NET Excel component

Muhammad Azeem said...

This is a nice article..
Its easy to understand ..
And this article is using to learn something about it..

c#, dot.net, php tutorial, Ms sql server

Thanks a lot..!
ri80