Free Support Forum - aspose.com

Not able to export more than 500 records to excel file

Hi,

I’m using licensed Aspose.Cells for .net to export data to excel.

I’m able to export small amount of data to excel but when I come to export large data like 500 records it just do continuous looping.

I’m using following code to export my data:

private void ExportToExcel()
{
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(System.Configuration.ConfigurationManager.AppSettings[“AsposeLicenseKey”]);

// Variables
Workbook oExcel = new Workbook();
Worksheet oSheet;
DataTable dtOutput = new DataTable();
dtOutput.Columns.Add(“Id”);
dtOutput.Columns.Add(“Name”);

for (int i = 0; i < 500; i++)
{
DataRow dr = dtOutput.NewRow();
dr[“Id”] = i;
dr[“Name”] = “Name” + i.ToString();
dtOutput.Rows.Add(dr);
}

MemoryStream msOutput = new MemoryStream();

// Add a worksheet
oExcel.Worksheets.Add();

// Setup the sheet
oSheet = oExcel.Worksheets[0];

// Export Data
oSheet.Name = “Export GF Data”;
oSheet.Cells.ImportDataTable(dtOutput, true, 0, 0, dtOutput.Rows.Count, dtOutput.Columns.Count, true, “mm/dd/yyyy”, true);

// Free Data Table
dtOutput.Dispose();

// Loop and Autosize
for (byte nCnt = 0; nCnt <= dtOutput.Rows.Count - 1; nCnt++)
{
oSheet.AutoFitColumn(nCnt);
}

// Save to Memory
oExcel.Save(msOutput, FileFormatType.Excel2000);

// Free Resources
oExcel = null;

// Variables
byte[] aBytes;

// Write all Data
aBytes = msOutput.ToArray();

// Send as file to browser…
Response.ClearContent();
Response.ClearHeaders();
Response.AppendHeader(“Content-Disposition”, "inline;filename = " + “YourFile.xls”);
Response.ContentType = “application/msexcel”;
Response.AddHeader(“content-length”, Convert.ToString(aBytes.Length));
Response.BinaryWrite(aBytes);
Response.Flush();
Response.Close();

// Free Stream
msOutput.Close();

}

Could you please reply me with the solution ASAP.

My Aspose file version is 4.7.1.0.

thanks,
Shreyash

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

There is a slight mistake in your code which is causing this behavior. Please update the code as below:

// Free Data Table

dtOutput.Dispose();

// <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />Loop and Autosize

for (byte nCnt = 0; nCnt <= dtOutput.Columns.Count - 1; nCnt++)

{

oSheet.AutoFitColumn(nCnt);

}

Thank you & Best Regards,