Hi There,
I am having a weird problem with aspose, I am trying to export to excel using the Export to Excel function of ASPOSE passing a data table, and it is crashing with an error message when it is exporting up to 60,000 rows ,in the try catch
This is the code and the Error message is this:
Unable to export to Excel: System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown. at System.Collections.ArrayList.set_Capacity(Int32 value) at System.Collections.ArrayList.EnsureCapacity(Int32 min) at System.Collections.ArrayList.Add(Object value) at Aspose.Cells.Cells.ImportDataTable(DataTable dataTable, Boolean isFieldNameShown, Int32 firstRow, Int32 firstColumn, Int32 rowNumber, Int32 columnNumber, Boolean insertRows, String dateFormatString, Boolean convertStringToNumber) at Aspose.Cells.Cells.ImportDataTable(DataTable dataTable, Boolean isFieldNameShown, Int32 firstRow, Int32 firstColumn, Int32 rowNumber, Int32 columnNumber, Boolean insertRows, String dateFormatString) at Aspose.Cells.Cells.ImportDataTable(DataTable dataTable, Boolean isFieldNameShown, Int32 firstRow, Int32 firstColumn, Int32 rowNumber, Int32 columnNumber, Boolean insertRows) at Aspose.Cells.Cells.ImportDataTable(DataTable dataTable, Boolean isFieldNameShown, Int32 firstRow, Byte firstColumn) at surf.web.reports.utils.ExcelUtils.ImportTable(Worksheet excelSheet, DataTable dt) in C:\Cvs.Homes\surf\Surf.Web.UI\surf.web.reports\utils\ExcelUtils.cs:line 63 at surf.web.reports.utils.ExcelUtils.ExportToExcel(DataSet ds, String fileName, ReportInfo info, HttpResponse response) in C:\Cvs.Homes\surf\Surf.Web.UI\surf.web.reports\utils\ExcelUtils.cs:line 40 at surf.web.reports.MortgageDetailReport.btnExport_Click(Object sender, EventArgs e) in C:\Cvs.Homes\surf\Surf.Web.UI\surf.web.reports\PortfolioReports\MortgageDetailReport.aspx.cs:line 303
public class ExcelUtils
{
public static void ExportToExcel(DataSet ds, string fileName, ReportInfo info, HttpResponse response)
{
Workbook excelWB = new Workbook();
int tableCount = ds.Tables.Count;
int sheetCount = excelWB.Worksheets.Count;
if (sheetCount < tableCount)
{
int diff = tableCount - sheetCount;
for (int i = 0; i < diff; i++)
{
excelWB.Worksheets.Add();
}
}
for (int i = 0; i < tableCount; i++)
{
Worksheet excelSheet = excelWB.Worksheets[i];
DataTable dt = ds.Tables[i];
if (dt.Rows.Count <= 65000)
ImportTable(excelSheet, dt);
else
{
DataTable[] splittedTables = ReportUtils.SplitTable(dt, 60000);
for (int j = 0; j < splittedTables.Length; j++)
{
if (j == 0)
ImportTable(excelSheet, splittedTables[j]);
else
{
int id = excelWB.Worksheets.Add();
ImportTable(excelWB.Worksheets[id], splittedTables[j]);
}
}
}
}
AddQueryParameters(excelWB, info);
string excelFileName = fileName + ".xls";
excelWB.Save(excelFileName, SaveType.OpenInExcel, FileFormatType.Excel2000, response);
}
private static void ImportTable(Worksheet excelSheet, DataTable dt)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (dt.Columns[j].DataType.Equals(Type.GetType("System.DateTime")))
{
excelSheet.Cells.Columns[(byte) j].Style.Custom = "mm/dd/yyyy";
}
}
excelSheet.Cells.ImportDataTable(dt, true, 0, 0);
excelSheet.AutoFitColumns();
excelSheet.Name = dt.TableName;
}
private static void AddQueryParameters(Workbook wb, ReportInfo info)
{
int sheetId = wb.Worksheets.Add();
Worksheet sheet = wb.Worksheets[sheetId];
int col = 0;
foreach (string key in info.Parameters.Keys)
{
sheet.Cells[0, col].PutValue(key);
if (info.Parameters[key] == null)
{
sheet.Cells[1, col].PutValue("Empty");
}
else if (info.Parameters[key].GetType() == typeof (string[]))
{
string[] array = info.Parameters[key] as string[];
if (array != null)
for (int j = 0; j < array.Length; j++)
sheet.Cells[1 + j, col].PutValue(array[j]);
}
else if (info.Parameters[key].GetType() == typeof (List<string>))
{
List<string> values = info.Parameters[key] as List<string>;
if (values != null)
{
if (values.Count == 0 )
sheet.Cells[1, col].PutValue("Empty");
for (int j = 0; j < values.Count; j++)
sheet.Cells[1 + j, col].PutValue(values[j]);
}
}
else
sheet.Cells[1, col].PutValue(Convert.ToString(info.Parameters[key]));
col++;
}
sheet.AutoFitColumns();
sheet.Name = "Filter Parameters";
}
}
}
Hope you guys can help me find the problem
This message was posted using Email2Forum by ShL77.