I am trying to convert a very large tab delimited file into an Excel spreadsheet. I have created some code that will import the tab delimited file into a DataTable. I am then trying to import that into a worksheet. The file has about 350,000 records and about 100 columns. I am gettng the following error:
"The CLR has been unable to transition from COM context 0x5092118 to COM context 0x5092288 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations."
If I tell the application to continue I get an out of memory exception. "Exception of type 'System.OutOfMemoryException' was thrown."
Is there any way to convert very large files in spreadsheets without having everything reside in memory?
Here is the code:
Workbook workbook = new Workbook();
workbook.Worksheets.Add();
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Name = "Testing";
worksheet.Cells.ImportDataTable(importDataTable, true, "A1");
workbook.Save(outputFilePath, FileFormatType.Excel2003);
Hi,
Where do you find this error, do you use multi-threading or asynchronous processing for your scenario? It seems that the error is not regarding Aspose.Cells component.
Well, Aspose.Cells for .NET can import big datatables and can manipulate / save huge excel files. I tested you scenario a bit and Aspose.Cells works great, I import a datatable and fill about 400,000 records in a worksheet without any problem. The file generated by this operation is about 10mb in size and it takes very few seconds to complete the operation. Following is the sample code and attached is the output file.
Sample code:
Workbook excel = new Workbook();
DataTable dt = new DataTable("Products");
DataRow dr = null;
for (int c = 0; c < 100; c++)
{
dt.Columns.Add("Col" + c.ToString(), typeof(string));
}
for (int x = 0; x < 4000; x++)
{
dr = dt.NewRow();
for (int y = 0; y < 100; y++)
{
dr[y] = x.ToString() + "," + y.ToString();
}
dt.Rows.Add(dr);
}
Worksheet sheet = excel.Worksheets[0];
sheet.Cells.ImportDataTable(dt, true, "A1");
excel.Save("f:\\test\\outputmyfile.xls");
And we also recommend you to try our latest version v4.7.1: http://www.aspose.com/community/files/51/file-format-components/aspose.cells-for-.net-and-java/entry174461.aspx
If you still could not evaluate the issue, please create a simple console application to reproduce the issue, we will check it soon.
Thank you.
Thanks so much for the quick response. I am not doing any multithreading or async processing. You are only creating 4000 records in your code. Not 400,000. I took your code and put it in my application exactly and just increased the 4000 to 400,000 and I get the out of memory error. So the question I have is "Is there any way to convert very large files into spreadsheets without having everything reside in memory?" Is there a file to file conversion possible?
I would be very interested in what happens on your end when you increase to 400,000 records.
Thanks,
Brad
Hi Brad,
Sorry for my mistake, I thought you are talking about filled cells. Yes, indeed I was filling 4000 rows (4000 rows * 100 cols = 400,000 cells). Well, if you fill/import data upto 350, 000 records with certain columns into a worksheet, you do need to have a lot of memory to process this big operation and you might end up with out of memory exception due to insufficient memory. And, if you complete this process some how, it would also be a time consuming job, the performance would be degraded considerably. Moreover, the resultant excel file would be a huge one. So, we think, as an alternative option, you can split your data to multiple worksheets instead of filling data to a single big sheet. Moreover, you can also try to split your data to create multiple excel files if possible. That would certainly be more efficient and optimize your performance in a great deal. Also, in this day, the out of memory issue won't occur.
For your query i.e.., "Is there any way to convert very large files into spreadsheets without having everything reside in memory?........."
Well, there is no way but to have it resided in the memory
Thank you.