Kevin Kevin - 1 year ago 73
ASP.NET (C#) Question

C# Failing To Import All of the Cells from an Excel Spreadsheet

I am using some legacy code to return an Excel worksheet as a Dataset. However, when I iterate over the resulting data set it seems that not all of the cells are there. The Excel sheet that is being read has some merged cells and I am wondering if that is the problem. Here is the code:

private DataSet Get_Spreadsheet_Data(string strFileName, string strSheetName)
DataSet ds = new DataSet();
string strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strFileName + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(strConnectionString);
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + strSheetName + "$]", objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
catch (Exception Ex)
//litOutput.Text = "<span style=\"color:red;\">Exception Occurred pulling data from the spreadsheet.</span><br>Details: " + Ex.Message;
return ds;

Is this code malfunctioning? Any advice is appreciated.

Answer Source
string strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strFileName + ";" + "Extended Properties=Excel 8.0;";

needed to read:

string strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strFileName + ";" + "Extended Properties="Excel 8.0;HDR=NO;IMEX=1;";

and that did the trick!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download