Hi There!
I am trying to generate an excel file using the aspose.cells. I am creating it on the fly with some custom formatting. Everything works well when i try to generate the file with say, 5000 or 7000 rows and around 150 column. But when i try to create the file for more than 10000 rows and 200 columns, it throws an error with the message: OutOfMemory exception thrown by the code.
Here is the code that i am using for formatting the worksheet:
Range objRangeData = worksheet.Cells.CreateRange(0, 0, dtReport.Rows.Count + 1, dtReport.Columns.Count);
objRangeData.Name = "DataRange";
Aspose.Cells.Style StyleDataRange = workbook.Styles[workbook.Styles.Add()];
StyleDataRange.Font.Name = "Arial";
StyleDataRange.Font.Size = 8;
StyleDataRange.Font.Color = System.Drawing.Color.Black;
StyleDataRange.HorizontalAlignment = TextAlignmentType.Left;
StyleDataRange.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
StyleDataRange.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
StyleDataRange.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
StyleDataRange.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
StyleDataRange.ForegroundColor = System.Drawing.Color.FromArgb(255, 250, 223);
StyleDataRange.Pattern = BackgroundType.Solid;
//Define a style flag struct.
StyleFlag flagDataRange = new StyleFlag();
flagDataRange.CellShading = true;
flagDataRange.FontName = true;
flagDataRange.FontSize = true;
flagDataRange.FontColor = true;
flagDataRange.HorizontalAlignment = true;
flagDataRange.Borders = true;
flagDataRange.ShrinkToFit = true;
flagDataRange.WrapText = true;
objRangeData.ApplyStyle(StyleDataRange, flagDataRange);
I have tried different methods for saving the file but all of them are causing the same exception.
The methods that i tried are:
workbook.Save(fileName);,
and
workbook.Save("Report.xls", FileFormatType.Default, SaveType.OpenInExcel, Response);
and Finally
MemoryStream MS = new MemoryStream();
MS = workbook.SaveToStream();
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment; filename=Report.xls");
Response.BinaryWrite((Byte[])MS.ToArray());
Response.End();
All of these methods are causing for the same exception. but at the same time these are working fine for smaller data set.
Can anyone help me out here.
Thanks in advance.
Mayank
Hi,
Well, I tried your scenario a bit using the following code and it works fine. I did not find any issue for generating both xls or xlsx files. The process is big and does take a little time (less than 40 seconds) but it works fine without any error. I have filled 10000 * 200 records and format a range of cells with your mentioned code. I have tested it with the attached version. Here is my sample code:
Sample code:
Workbook workbook = new Workbook();
Color color = Color.FromArgb(255, 250, 223);
workbook.ChangePalette(color, 55);
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
int rows = 10000;
int numberOfColumns = 200;
//Fill Data in 10000 * 200 matrix.
for (int i = 0; i <= rows; i++)
{
for (int j = 0; j <= numberOfColumns; j++)
{
cells[i, j].PutValue(i.ToString() + “,” + j.ToString());
}
}
//Apply to range style.
Range objRangeData = worksheet.Cells.CreateRange(0, 0,1000, 50);
objRangeData.Name = “DataRange”;
Aspose.Cells.Style StyleDataRange = workbook.Styles[workbook.Styles.Add()];
StyleDataRange.Font.Name = “Arial”;
StyleDataRange.Font.Size = 8;
StyleDataRange.Font.Color = System.Drawing.Color.Black;
StyleDataRange.HorizontalAlignment = TextAlignmentType.Left;
StyleDataRange.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
StyleDataRange.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
StyleDataRange.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
StyleDataRange.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
StyleDataRange.ForegroundColor = System.Drawing.Color.FromArgb(255, 250, 223);
StyleDataRange.Pattern = BackgroundType.Solid;
//Define a style flag struct.
StyleFlag flagDataRange = new StyleFlag();
flagDataRange.CellShading = true;
flagDataRange.FontName = true;
flagDataRange.FontSize = true;
flagDataRange.FontColor = true;
flagDataRange.HorizontalAlignment = true;
flagDataRange.Borders = true;
flagDataRange.ShrinkToFit = true;
flagDataRange.WrapText = true;
objRangeData.ApplyStyle(StyleDataRange, flagDataRange);
workbook.Save(“e:\test\ntestmem_test.xlsx”);
If you still find any issue, kindly create a sample project and post it here, we will check it soon.
Thank you.
Hi There!
Well, that time i was able to resolve that problem. I just downloaded the latest dll file and used it and the problem was resolved.
Now, this time i have got a lot more bigger data set which is more than 1,00,000 records. And with this dataset i am again stuck with the same problem... System.OutOfMemory exception.
I am trying to generate an excel file using the aspose.cells 4.9.0.0. I am creating it on the fly with some custom formatting. Everything works fine when i try to generate the file with say, 40,000 or 50,000 rows and around 150 column. But when i try to create the file for more than 75,000 rows and 150 columns, it throws an error with the message: OutOfMemory exception thrown by the code.
This time my requirement is lot more bigger. I need to export around 1,50,000 records.
Here is the sample that i trying to generate with my code.
|
261477005
|
EUR
|
470.11 |
- 689.54 |
- 1,369.68 |
182.84 |
253.13 |
|
261477451
|
EUR
|
- 504.61 |
- 822.17 |
- 989.92 |
72.28 |
350.24 |
|
261514680
|
EUR
|
- 707.69 |
- 1,116.50 |
- 1,444.30 |
64.94 |
629.35 |
|
261592111
|
EUR
|
- 1,106.67 |
- 7,197.78 |
- 13,005.61 |
146.38 |
709.30 |
|
261763950
|
EUR
|
- 617.70 |
- 812.95 |
- 1,016.12 |
65.99 |
639.52 |
|
261848860
|
EUR
|
- 508.93 |
- 1,478.74 |
- 1,638.65 |
115.00 |
1,114.49 |
Can someone help me out here.
Hi,
Thanks for providing us further details.
1) Well, the Range.ApplyStyle will always create all the Cell' s instances in the range even though if you got some cells empty in the data range. So, if you want to apply the style to the whole worksheet, you may try:
a) If the workbook only contains a single worksheet, please simply set the DefaultStyle of the Workbook.
b) Else, please use/call Cells.ApplyColumnStyle individually for your desired columns.
2) Could you post us the file which contains about 100000*150 data (records) and a sample project to reproduce the issue? We will check whether we can give a better solution for this issue.
3) By the way, where does the OutOfMemory Exception occur in your code segment, e.g on importing data or on saving file?
Thank you.
Thanks Amjad,
I'll try the methods that you suggested here and revert back to you soon.
This exception occures at the place where the code, to save the file, is written. I'm sorry, i can't share the file.
What could be the resion that I got this exception. My requirements can be enlarge in the future.
It is possible that i'll require to export around 2-3 lacs of record in an excel. What could be the best solution for this.
Hi,
Please try the new fix (attached).
We have to store some data to memory in Workbook.Save method, as this exception occurs at saving file. Please set Workbook.SaveOptions.CachedFileFolder property explicitly according to your need, then, we can store temporary data to the disk. If you want to export a very large file to Response, please save it to the disk first, then export a file to Response.
Please check the following sample code segment:
Workbook workbook = new Workbook();
//loading data.
workbook.SaveOptions.CachedFileFolder = @"F:\FileTemp\Temp\";
//.................
workbook.Save(@"F:\FileTemp\Book1.xls");
Thank you.
workbook.getSaveOptions().setCachedFileFolder(“location”);
workbook.getSaveOptions() seems to be deprecated now.
What are the alternatives in v 8.4.2 for java?
Hi Hitesh,
Please note that the SaveOptions property has been removed from the Workbook class (as per latest version 9.0.0). As an alternative, you can use the corresponding SaveOptions class to set the Cached File Folder as demonstrated below. Moreover, please check the documentation provided with your current version of the API for the available SaveOptions classes.
Java
Workbook book = new Workbook(dir + “sample.xlsx”);
SaveOptions options = new XlsSaveOptions();
options.setCachedFileFolder(dir + “temp”);
book.save(dir + “output.xls”, options);