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()

No comments:

Post a Comment