This project has moved and is read-only. For the latest updates, please go here.

Large number of rows

Jan 25, 2013 at 10:49 AM

DataSet exportData = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(getOutput);

da.Fill(exportData);

XLWorkbook xlWb = new XLWorkbook();
xlWb.Worksheets.Add(exportData);
xlWb.SaveAs(filePath.ToString());
releaseObject(xlWb);

I fill dataSet with dataadapter and then export it to excel file.
It is simple and it works fine for small files. 

But when the number of records reach for example 500.000, everything freeze. I get an Out of memory exception and i have to restart my computer.

How it should be done for large number of records?

Thanks,Simon 

Jan 25, 2013 at 2:04 PM

Compile your app to 64bit

Jan 25, 2013 at 2:35 PM

I have checked, the library I have ClosedXML.dll is 32bit dll and not 64. I have version 0.68.1.0, ClosedXML_Net3.5.
Since SQL 2008 CLR doesn't support version higher than NET 3.5, I'm limited with this 3.5 version.

I can compile my application to 64bit dll. What is the difference if ClosedXML is 32 bit? Or should I first open source project for closedXML and compile this app to 64 and then my app?

Second question:
xlWb.SaveAs(filePath.ToString());

If excel is opened, I get the following error:System.IO.IOException: The process cannot access the file 'c:\temp\test.xlsx' because it is being used by another process.

In similar situation, where I create excel from template and then save it to one location as xltm, it works, even if the same file(xltm with the same name) is already opened by many users. Where is the catch? What do you suggest?

Thank you,

Simon

Jan 25, 2013 at 4:39 PM

The library is compiled for any CPU which means it will run as x64 if your application is running as x64.