1

I try to retrieve a whole Excel sheet. Everything seems to be alright but when I put one or more empty rows to the begining of the sheet, they won't be read by OleDbAdapter. Unfortunately I need them because the user tells me (the program) which rows have to be read.

So when the user says, row 2 and 3 have to be read and he inserts an empty row at the begining my program would miss the data because the first row was not read.

This is the way I create the connection strings, depending if it is a xls or xlsx file and if ACE is available:

XLS, ACE available

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"" + file + "\";Mode=Read Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;ImportMixedTypes=Text\""

XLS, ACE not available

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" + file + "\";Mode=Read;Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;ImportMixedTypes=Text\""

XLSX, ACE needs to be available (same for XLSM, XLSB)

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"" + file + "\";Mode=Read;Extended Properties=\"Excel 12.0 Xml;HDR=No;IMEX=1;ImportMixedTypes=Text\""

This is my select:

string select = "SELECT * FROM [" + sheet + "$]";

And this is how I retrieve the data:

OleDbDataAdapter adapter = new OleDbDataAdapter(select, connectionString);
DataTable dt = new DataTable(sheet);
adapter.Fill(dt);
int rowCount = dt.Rows.Count;
int columnCount = dt.Columns.Count;

Edit: Here someone else has the same problem but I don't see differences to my code and I do not get the empty lines.

So my question again: How do I read in empty rows at the beginning?

Community
  • 1
  • 1
iop
  • 312
  • 2
  • 4
  • 12
  • In the question you reference they weren't including the file extension in the connection string. Are you? Do you get the same behavior with all three connection strings? – nickfinity Jul 09 '14 at 16:27
  • @nickfinity Yes I do include the file extension and the behaviour is the same for all connection strings. – iop Jul 10 '14 at 06:25
  • Not sure then. I've seen odd behavior when using interop if the sheet is set to print view, but I don't know if that would be the same here or not. Sorry I'm not more help. – nickfinity Jul 10 '14 at 12:19
  • 1
    @nickfinity Ok, thanks for trying ;) ..in one of the other threads about this issue someone said this could be intended behaviour, a design decision. Thats probably the same effect that will not return all the empty rows below the data. ...probably. But seriously, can someone reproduce this? – iop Jul 11 '14 at 12:48
  • @iop Same situation here. It seems a design decision of the driver as you say :(, i didnt found any connection parameter that changes this behavior...will post a response if i found a solution – VSP Jun 19 '19 at 15:18
  • @VSP Good luck! I switched to EPPlus, it's more catchy and does what it should... – iop Jun 24 '19 at 10:10

0 Answers0