2

I' m trying to export xlsx file with these codes:

OleDbDataAdapter adapter = new OleDbDataAdapter(select, accessConnection);
adapter.Update(dataTable);

The connection string is

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\AA\Desktop\work10.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

I' m trying to export 200000 rows to xlsx file but getting spreadsheet is full error. When I try to open the excel file, I' m getting file extension is not valid error.

After changing .xlsx extension to .xls, file is opening but row count is not enough for me.

Microsoft Access Database Engine 2010 version is installed to the computer.

How can I fix it?

Anil Kocabiyik
  • 1,178
  • 6
  • 17
  • 47

1 Answers1

1

It seems that the OLEDB Excel driver is limited to 65536 rows. Thus, you cannot use your current code to export 200000 rows. Sorry, it just won't work, and there's no way you can make it work.

If you need to write more than 65536 rows, you need to use another Excel export mechanism, for example, a third-party C# Excel library, or consider using another file format such as CSV.

Community
  • 1
  • 1
Heinzi
  • 167,459
  • 57
  • 363
  • 519
  • I know .xls is limited so I'm trying to export .xlsx file. My ConnectionString seems suitable for .xlsx files – Anil Kocabiyik Jan 25 '16 at 16:12
  • @user1451549: Apparently, it's not only a limitation of the xls file format but also a limitation of the OLEDB driver (xls and xlsx). Your connection string looks fine. – Heinzi Jan 25 '16 at 16:14
  • Thank you for your response. I'll ask another question when I try to export less than 65536 rows, export finishes successfully. After that I' m trying to open exported xlsx file, I get 'Excel can not open the test.xlsx because file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file' error. How can I solve it? – Anil Kocabiyik Jan 26 '16 at 10:05
  • I asked it here http://stackoverflow.com/questions/35012472/export-excel-file-with-xlsx-extension – Anil Kocabiyik Jan 26 '16 at 11:11