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?
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.