Loading Excel from a program but some cell values don't show

.NET Framework Data Access and Storage

ForEverLearning

Hi,

I am trying to load an Excel file into a datatable. Most of the cells get loaded but some don't!

For example a cell would show as null but the others show good.

The input was good enough for MS-Access to load successfully showing all data. So the problem is in my code.

Here is the code:

Code Snippet

//-----------------------------------------------------------------------------------------------------------

private void button1_Click(object sender, EventArgs e)

{

string part1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";

string part2 = ";Extended Properties=Excel 8.0;";

string fn = "C:\..\A.xls"; '<-- fix if you want to test on your machine

string cnStr = part1+fn+part2;

System.Data.OleDb.OleDbConnection cn = new OleDbConnection(cnStr);

cn.Open();

DataTable dt = new DataTable("Results");

OleDbCommand cmd;

cmd = new System.Data.OleDb.OleDbCommand("Select * from [Sheet1$]", cn);

OleDbDataAdapter da = new OleDbDataAdapter(cmd);

dt.Clear();

da.Fill(dt);

MessageBox.Show(dt.Rows[0][0].ToString()); <-- Cell Shows empty!! Most of the other cells are good

dataGridView1.DataSource = dt;

If you want the sample data tell me how to send it to you - I see no file upload option on this forum!!
Top

Re: .NET Framework Data Access and Storage Loading Excel from a program but some cell values don't show

Paul P Clement IV


If you have columns with mixed data types the driver will make a best guess as to what the data type for the column should be. Try adding the IMEX argument to your connection string in order to work around this issue:

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +

"Data Source=e:\\My Documents\\Book2.xls;" +

"Extended Properties=" + (char)34 + "Excel 8.0;HDR=Yes;IMEX=1;" + (char)34;





Top

Re: .NET Framework Data Access and Storage Loading Excel from a program but some cell values don't show

gswartz

I've had the same problems but adding IMEX doesn't help. Any other ideas Thanks.


Top

Re: .NET Framework Data Access and Storage Loading Excel from a program but some cell values don't show

Paul P Clement IV


What type of data is in the column What is it that you're trying to do with the data A small code example might help.



Top

Re: .NET Framework Data Access and Storage Loading Excel from a program but some cell values don't show

gswartz

Thanks. I think I found the solution to the problem but it requires some registry changes. You can read about it at http://blog.lab49.com/ p=196 One problem I just noticed though is that when I populate the dataset with the oledbdataadapter from the excel sheet, it returns all but the last row. Anyone know a reason for this and a way around it Thanks.

BTW, here's the code.

Code Snippet

oleConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + sFilePath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"");

string sExcelDataQuery = string.Format("SELECT * FROM [{0}]", sWorksheetName);

System.Data.OleDb.OleDbDataAdapter oleCommand = new System.Data.OleDb.OleDbDataAdapter(sExcelDataQuery, oleConnection);

System.Data.DataSet ds = new System.Data.DataSet();

oleCommand.Fill(ds);




Top