Hi,
I am working on an application where we have to deal with exporting large amount of data in to excel sheets. Currently i am using a trial version of Aspose Cell to check the compatibility, with the record size of 400060 rows and 10 columns it is throwing a Memory Exception. What is the max size in terms of rows supported by the Aspose ?
P.S : We are a existing customer of Aspose PDF and Word , planning to get a copy of Aspose Cell license if there is a solution available for the same. .
MS Excel only allows you to have a maximum of 1048576 rows for a worksheet (you cannot save/load more data than this limitation), so you cannot cross this limit. For your information, Aspose.Cells follows MS Excel standards and specifications for MS Excel file formats and cannot go beyond it.
You may please use latest version for your testing or if you want to test Aspose.Cells without evaluation version limitations, you can also request a 30 Day Temporary License.
Thanks for the reply. I have downloaded the latest version available.
May I know the best approach to import data from an IEnumerable to excel ? Currently I’m converting it into a Data table and while importing it using cells.ImportDataTable(datatable,true,”A1”) I’m facing a memory exception . If possible provide a code snippet for the same.
@mani.parupalli,
You may please consider using Aspose Light Cells which is specially designed for this purpose i.e. to load large data into Excel by avoiding many overheads. Please give it a try and share the feedback
Using LightCells API
Otherwise, there are different options to import data into excel file using Aspose.Cells for .NET as described in the following article. You may consider one of these options if possible.
Import Data into Worksheet
Now say you want to use DataTable to import data, you may review the following article which provides the fastest way to fill the DataTable manually.
Fastest way to fill DataTable manually in C#
Regarding the exception, please provide us a complete runnable solution along with the sample data for our analysis. We will look into it and provide our feedback.
I have tried using the LightCells API, still facing the same Memory Exception when I tried to import more than 100000 rows.
Please find the code snippet below. Please help to resolve the issue.
string reportName = “Excel_” + DateTime.Now.ToString().Replace(":", “”).Replace("/", “”).Replace(" “, “_”)+”.xlsx";
DataTable dt = ConvertToDataTable(formulary); //Formulary is Ienumerable collection with 400000 rows and 30 columns
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells.ImportDataTable(dt, true,“A1”); // System.IO.Memory Exception
style.IsTextWrapped = true;
worksheet.AutoFitColumns();
MemoryStream output = new MemoryStream();
workbook.Save(output,ooxmlSaveOptions);
output.Position = 0;
byte[] data = new byte[output.Length];
output.Read(data, 0, data.Length);
output.Flush();
output.Seek(0, SeekOrigin.Begin);
Response.Buffer = true;
Response.AddHeader(“Content-Disposition”, string.Format(“attachment; filename={0}”, reportName));
return File(output.ToArray(), “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”, reportName);
public DataTable ConvertToDataTable(IList data)
{
PropertyDescriptorCollection properties =
TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
foreach (PropertyDescriptor prop in properties)
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
foreach (T item in data)
{
DataRow row = table.NewRow();
foreach (PropertyDescriptor prop in properties)
row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
table.Rows.Add(row);
}
return table;
}
I am able to create a Work book successfully by splitting the Work book into multiple sheets and setting Memory Setting as Memory Preference. But i am getting a “‘System.OutOfMemoryException’ occurred in Aspose.Cells.dll” when im trying to copy the workbook data to stream .
Below is my code snippet.
Worksheet worksheet;
int rownum = 0;
int i;
int row = 0;
int ct = 0;
for (int cnt = 0; cnt <= 20; cnt++)
{
worksheet = workbook.Worksheets[workbook.Worksheets.Add()];
worksheet.Cells.MemorySetting = MemorySetting.MemoryPreference;
row = ct;
rownum = 0;
for (i = row; i <= row + 20000; i++)
{
if (i > dt.Rows.Count)
{
break;
}
else
{
worksheet.Cells.ImportDataRow(dt.Rows[i], rownum++, 0);
ct++;
}
}
Style style = workbook.DefaultStyle;
style.IsTextWrapped = true;
worksheet.AutoFitColumns();
}
MemoryStream output = new MemoryStream();
output = workbook.SaveToStream(); // Error is poped out in this line
output.Position = 0;
byte[] data = new byte[output.Length];
output.Read(data, 0, data.Length);
output.Flush();
output.Seek(0, SeekOrigin.Begin);
Response.Clear();
Response.Buffer = true;
Response.AddHeader(“Content-Disposition”, string.Format(“attachment; filename={0}”, reportName));
return File(output, “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”, reportName);
@mani.parupalli,
I have created a sample Excel file having 20 worksheets where each worksheet has 20000 rows and 30 columns. Dummy data is filled in all the cells in these sheets. I tried following code with this sample file:
Workbook workbook = new Workbook(@"Book20Sheets.xlsx");
Worksheet worksheet;
for(int cnt = 0; cnt < 20; cnt++)
{
worksheet = workbook.Worksheets[cnt];
worksheet.Cells.MemorySetting = MemorySetting.MemoryPreference;
Aspose.Cells.Style style = workbook.DefaultStyle;
style.IsTextWrapped = true;
worksheet.AutoFitColumns();
}
MemoryStream output = new MemoryStream();
output = workbook.SaveToStream(); //NO Error is poped out in this line
output.Position = 0;
byte[] data = new byte[output.Length];
output.Read(data, 0, data.Length);
output.Flush();
output.Seek(0, SeekOrigin.Begin);
No issue is observed while executing this code and it works fine. It seems that your file is larger than mine in terms of data in the cells. Please share your sample file with us for testing. Upload the sample file to some public file sharing server like Google drive, Dropbox or https://send.firefox.com/ etc. and share the link here for download. We will analyze this file and provide our feedback.
Due to Sensitive reasons I couldn’t share the data . But may I know if there is any way to increase the size of Memory stream for Aspose.Cells.dll ? i am able to import data to worksheet but converting into stream is having the issue. aspose.PNG (9.5 KB)
Please provide any alternative solution if possible. I have attached the screenshot for the same. Let me know if any additional info is required. Looking forward for a solution.
Thanks
Aspose.Cells does not limit or control size of memory stream so we need the data to analyze it further. You can send me link for data through a private message by clicking on my name icon (circled pink A) and then pressing the Message button. I will download the file and use it to test the scenario.
By the way, could you try to change the line of code:
i.e.,
…
output = workbook.SaveToStream(); // Error is poped out in this line
with:
…
workbook.Save(output, Aspose.Cells.SaveFormat.Xlsx);
if it makes any difference.
I have received your file and working on this. I will share my feedback soon after analyzing it.
I have tested your sample file in an MVC project and observed the issue. If we use the same file in a desktop application, there is no issue and file is loaded and saved into memory stream successfully. I have googled this issue and found a lot of threads where people got this issue in ASP.NET MVC application while loading large files of any type. Hence it is not an issue of Aspose.Cells but MVC application limitation. You may devise some solution by posting this issue on some appropriate forum. Following are few of the links where this issue is discussed:
IIS Out of Memory exception (ASP.Net web application)
ASP.NET System.OutOfMemoryException
How to Troubleshoot Out of Memory Issues (System.OutOfMemoryException) in ASP.NET
ASP.NET 2.0 - System.out of memory exception
(Solved) System out of memory exception