One day I got a requirement of reading excel sheet (excel sheet may be either 2003 or 2007 version). To implement this I started googling and found some interesting solutions from Microsoft website and finally I developed code to read the excel sheet in the following way.
Here I created a web application and added an upload user control to a page (assume Default.aspx). Once the user uploads the excel file I will store it into the server machine and read the excel sheet from the server machine. Here is the code.
I created a class to read the excel file depending on the excel version.
ExcelReading.cs:
using System; using System.Data; using System.Configuration; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.OleDb; using System.Collections.Generic; public class ExcelReading { public ExcelReading() { // // TODO: Add constructor logic here // } //open the excel file using OLEDB OleDbConnection con; //List of Worksheet names public string[] workSheetNames = new string[] { }; private string fileName; //Path and file name of the Excel Workbook private string connectionString; public string[] ExcelRead(string fileNamePath,string extn) { this.fileName = fileNamePath; switch (extn) { //Excel 97-2003 file case ".xls": OpenExcelFile(false); break; //Excel 2007 file case ".xlsx": OpenExcelFile(true); break; default: break; } return workSheetNames; } private void OpenExcelFile(bool isOpenXMLFormat) { //while reading excel sheet Oledbdata Reader scans first 8 rows //and based on the majority data types it treats them as either integer or text. //The non matching data types are treated as NULL or empty. To solve this problem //use IMEX=1 property extender if (isOpenXMLFormat) //read a 2007 file connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +@fileName + ";Extended Properties="Excel 8.0;HDR=YES;""; else //read a 97-2003 file connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +@fileName + ";Extended Properties='Excel 8.0;IMEX=1'"; try { con = new OleDbConnection(connectionString); con.Open(); //get all the available sheets System.Data.DataTable dataSet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); //get the number of sheets in the file workSheetNames = new String[dataSet.Rows.Count]; int i = 0; foreach (DataRow row in dataSet.Rows) { //insert the sheet's name in the current element of the array //and remove the $ sign at the end workSheetNames[i] = row["TABLE_NAME"].ToString().Trim(new[] { ''','$' }); i++; } if (dataSet != null) dataSet.Dispose(); } catch(Exception e) { throw e; } finally { con.Close(); con.Dispose(); } } public System.Data.DataTable GetWorksheet(string worksheetName,string range) { OleDbConnection con = new OleDbConnection(); System.Data.DataTable excelDataTable = new System.Data.DataTable(); try { con = new System.Data.OleDb.OleDbConnection(connectionString); OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter( "select * from [" + worksheetName + "$" + range + "]", con); con.Open(); cmd.Fill(excelDataTable); } catch { } finally { con.Close(); con.Dispose(); } return excelDataTable; } }
Here is the code of the page and its code behind file from which I uploaded the excel sheet.
Default.aspx:
<asp:FileUpload ID="ctrlFileUpload" runat="server" EnableViewState="true" Width="100%" onkeypress="return false;" onkeydown="return false;" /></br> <asp:Button ID="btnUpload" runat="server" Enabled="true" Text="Upload" OnClick=" btnUpload _Click"/>
Default.aspx.cs:
protected void btnUpload_Click(object sender, EventArgs e) { string FileName = ctrlFileUpload.PostedFile.FileName; string _strUploadedFileExtn = System.IO.Path.GetExtension(ctrlFileUpload.PostedFile.FileName); //Saving the file into server machine ctrlFileUpload.PostedFile.SaveAs(Server.MapPath(".") + "\UploadedExcel\" + FileName); //Reading the file from the previously saved location. string path = @"../UploadedExcel/" + FileName; string fileNamePath = Server.MapPath(path); System.Data.DataTable dataTable = new System.Data.DataTable(); string[] workSheetNames = excelReading.ExcelRead(fileNamePath, _strUploadedFileExtn); //Read the required worksheet from the array. Here I am reading first sheet string sheetName= workSheetNames[1]; //Specify the range of the data which you want to read from excel sheet string range = "B8:B100"; dataTable = excelReading.GetWorksheet(sheetName, range); //Read the data from datatable object if (dataTable.Rows.Count > 0) { foreach (DataRow row in dataTable.Rows) { //Here write the code to read the data from datatable row. } } }
Another way of reading excel sheet:
We can also read the excel sheet by referring ‘Microsoft.Interop.Excel.dll’ but before going in that way I suggest you to go through below link because microsoft is not recommending this way.
http://support.microsoft.com/kb/257757
References for reading excel sheet:
http://support.microsoft.com/kb/306572
http://support.microsoft.com/kb/316934
http://support.microsoft.com/kb/316934
http://code.msdn.microsoft.com/NitoExcel
This post helped me to solve my problem.
Thanx
LikeLike