Outofmemory exception

[Senthil Kumar]

Hi,

my query is, i have a csv file of about 750mb when i try to export all records to a series of excel file, am getting out of memory exception. i am using a 1)Streamreader 2)sheet[].text objects. i am looping thru the records and creating worksheets dynamically. if i use shhet[].value it is working fine but slow. when i use TEXT it is throwing a OUT oF MEMORY exception after creating certain worksheets. can anyone suggest me a good solutions for handling Millions of records...

[Geetha M]

Hi,

Thank you for your interest in Syncfusion products.

Could you please send me the code snippet you are using so that I could recreate and find the cause of the problem?

Regards,
Geetha

[Administrator]

Hi,

Thanks for your reply.

Now i explain u in detail,

The basic idea of this winApp is to convert a huge size CSV file say, 750 mb(millions of records with 85 columns) to a series of Excel Files approx. 150 mb each.

----------------------------------------------
Errors while running the app. are
1)OutOfMemory
2)Cannot create a compound file
----------------------------------------------
*-i have tried out with/out ImigrantRange and Text/Value property of sheet.

*-Errors are throwing only when i am dealing with TEXT Property of a sheet.

----------------------------------------------
Above errors are occuring only in the Appendexcelfile block. Pls understand the context from the below code


//initialization for excel engine,application
excelEngine= new ExcelEngine();
application= excelEngine.Excel;
workbook=application.Workbooks.create(1);
sheet=workbook.Worksheets[0];
//migrantRange=sheet.MigranrRange;

//reads a line from the .csv file thru streamreader
while(onerecord=strmrdr.ReadLine()!=null)
{
cellcollctn= onerecord.split(",");
columns=cellcollctn.length;
colindex=1;
rows++;


while(columns>0)
{

if (colindex<257)
{
cellvalue=cellcollctn[colindex-1];

//1)
sheet[rows,colindex].Text=cellvalue;

//Text property is always eating memory but value doesn't

//2,sheet[rows,colindex].Value=cellvalue;
//3,sheet.Range[rows,colindex].Text=cellvalue;
//4,sheet.Range[rows,colindex].Value=cellvalue;

//I tried these below codes too
/* ImigrantRange
**ImigrantRange is fast when it is associated with a worksheet.
migrantRange.ResetRows(rows,colindex);
migrantRange.Text=cellvalue;
migrantRange.Value=cellvalue;
*/

colindex++;
columns--;


}
}
totalrecords++;

if (totalrecords==65536)
{
// code for creating multiple excel files
// function to create a new excel file or append the current worksheet to an existing excel file based on some condition

savesheet();

//after the process
//making the excel engine, application, worksheet to NULL,if used Imigrantrange,then migrant object to null and closing the workbook

//Again reinitializes the excel engine, application, worksheet and workbook
// continuing the splitup process till EOF
}

}


function savesheet()
{

//based on some condition it will create a new excel file or append the current worksheet

saveexcelfile();
else
appenexcelfile();
}

function saveexcelfile()
{
//it saves the workbook to a specified path
workbook.saveas(somepath);
}



function appendexcelfile()
{
//opens an excel file
//errors are getting when executing the below code
wbook= en.Excel.workbooks.open(somefile);

//errors are getting when executing the below code
// adds the worksheet
wbook.addcopy(sheet);

//saving the workbook agin with the same name
wbook.saveas(samefile);

wbook.close

//making the excel engine, application, worksheet to NULL
}


pls suggest me a solution.if possible pls send me a sample code. waiting for your reply.


>Hi,

Thank you for your interest in Syncfusion products.

Could you please send me the code snippet you are using so that I could recreate and find the cause of the problem?

Regards,
Geetha

[Geetha M]

Hi,

Thank you for the details.

I am working on creating a test sample and I will get back to you in two days.

Regards,
Geetha

[Senthil Kumar]

Hi,

Pls check the wbook.addcopy(sheet) object.
it is throwing the out of memory exception.

In the exception stacktrace, i found out that

(the below namespace is not accurate)

syncfusion.xslo.parser.intptr.dataprovider.ensurecpacity()
or syncfusion.xslo.parser.intptr.dataprovider.capacity
was raising the exception.

pls check ASAP.


>Hi,

Thank you for the details.

I am working on creating a test sample and I will get back to you in two days.

Regards,
Geetha

[Geetha M]

Hi,

Thank you for the update.

I am working on this and will update you by tomorrow.

Regards,
Geetha

[Senthil Kumar]

Hi Geetha,

i m waiting for your reply. pls make it ASAP.
FYI:
Exception is throwing when i execute this statement
workbook.worksheets.Addcopy(sheet).



>Hi,

Thank you for the update.

I am working on this and will update you by tomorrow.

Regards,
Geetha

[Geetha M]

Hi,

Thank you for your patience.

I am facing difficulties in creating a test sample to meet the criteria of more than 65536 records. I will continue on this and will update you by Monday. Meanwhile could you please give me the following details?

1. Stack trace of the exception.
2. Essential Studio version currently using

Also, could you please try to save the file in Excel 2007 format which allows more than 65536 rows?

You can get a more details on this from the below documentation link:
http://help.syncfusion.com/Reporting/XlsIO/Windows%20Forms

Regards,
Geetha

[Senthil Kumar]

Hi Geetha,
Pls find the details below,

1) i m using a licensed version of Syncfusion Essential studio 6.2.0.32

2) StackTrace of the Exception,

Syn.Xlsio.parser.biff_records.intptrdataprovider.ensurecapacity(int32 size)
at
syn.xlsio.imple.sstdictionary.setrefcount(int32 index, int32 count)
at
...
...
...
syn.xlsio.imple.collec.workshtscoll.addcopyafter()

i suggest u a simple solution,

1) pls use a excel file, say excel1.xls of 700mb having around 6Lkh records with 50/60 colmns.

2) use the open method to open the excel file (excel1.xls) and try to read the first worksheet and save it to a new excel file, say excel2.xls. likewise u have to read each worksheet of excel1.xls and keepon adding the sheets to excel2.xls.

i hope this would help u.

my requirement is to export in XLS format and m reading the records from a CSV file. i m waiting for your fix. This would help me out in releasing my application soon.



>Hi,

Thank you for your patience.

I am facing difficulties in creating a test sample to meet the criteria of more than 65536 records. I will continue on this and will update you by Monday. Meanwhile could you please give me the following details?

1. Stack trace of the exception.
2. Essential Studio version currently using

Also, could you please try to save the file in Excel 2007 format which allows more than 65536 rows?

You can get a more details on this from the below documentation link:
http://help.syncfusion.com/Reporting/XlsIO/Windows%20Forms

Regards,
Geetha

[Geetha M]

Hi,

Thank you for your patience and for the details.

I regret to inform you that I was not able to reproduce the problem.

We have optimized the performance of Essentail XlsIO in our latest release. Could you please try running your application using our latest version?

Regards,
Geetha

[Amal Ibrahim]

Hi Geetha,
It looks the same issue that I had with my incident.
There was no fix for this one?
Thanks
Amal

[Geetha M]

Hi Amal,

I am afraid that I am not able to reproduce the problem. As I said earlier, we have improved our performance in our latest releases and could you please check using it? You can get the details of our latest version from the forum thread linked below:

http://www.syncfusion.com/support/forums/general/82311/

Regards,
Geetha

[Hugo]

We are having the same problem sometimes creating an excel file with many rows (more than 65536). Note that this can happen when your not really out of memory, but the memory is fragmented such that .NET framework cannot allocate enough contiguous pages in memory, so i.e. allocating a very large array of something. The solution can then be to allocate smaller blocks, which in total can contain the same amount of data.

XlsIO version: 9.102.0.20

Call stack:

Exceptie: System.OutOfMemoryException
Exceptie melding: Er is een uitzondering opgetreden van het type System.OutOfMemoryException.

Stacktrace:
   bij Syncfusion.XlsIO.Implementation.Collections.ArrayListEx.UpdateSize(Int32 iCount)
   bij Syncfusion.XlsIO.Implementation.Collections.ArrayListEx.set_Item(Int32 index, RowStorage value)
   bij Syncfusion.XlsIO.Implementation.Collections.RecordTable.GetOrCreateRow(Int32 rowIndex, Int32 height, Boolean bCreate, ExcelVersion version)
   bij Syncfusion.XlsIO.Implementation.Collections.RecordTable.set_Item(Int32 rowIndex, Int32 colIndex, Object value)
   bij Syncfusion.XlsIO.Implementation.Collections.CellRecordCollection.set_Item(Int32 iRow, Int32 iColumn, ICellPositionFormat value)
   bij Syncfusion.XlsIO.Implementation.WorksheetImpl.SetLabelSSTIndex(Int64 cellIndex, Int32 iSSTIndex)
   bij Syncfusion.XlsIO.Implementation.RangeRichTextString.EndUpdate()
   bij Syncfusion.XlsIO.Implementation.RangeImpl.set_Text(String value)
   bij ?.
________________________________________
. ?(IRange targetRange, Object cellValue)
   bij ?.??.??(Object value, Nullable`1 foreColor, Nullable`1 backColor, Object originalCell)
   bij ?.?[1].??(Object exportInfoObject)

[Sridhar]

Hi Wout de zeeuw,

Thank you for using Syncfusion products.

Currently, In XlsIO we have planned to include performance and memory leak issues in our upcoming major volume release Vol 1 2013. Could you please provide us the sample with excel file creation which causes the above reported exception which will be helpful for us to analyse furhter on this issue.

Let me know if you have any concerns.

Thanks,
Sridhar.S