Unable to Export to Excel large data

Aspose.Cells Product Family
1

Hi Team,

I am unable to export the data in an .xlsx. The data size is huge(around 25000 and above). I tried 2 cells dlls. (8.5.1.0 and 8.8.2.0) . The page freezes/unresponsive(Aw Snap!).

Source: Aspose.Cells
TargetSite: Void AutoFitColumn(Int32)
Message: Invalid column index.

Here is the code snippet-

try {

Aspose.Cells.License licence = new Aspose.Cells.License();

string licenseFile = Server.MapPath("~/app_data/Aspose.Total.lic");

licence.SetLicense(licenseFile);


Workbook myWorkbook = new Workbook();

myWorkbook.Worksheets.RemoveAt(0);

Aspose.Cells.Style HeaderStyle = myWorkbook.Styles[myWorkbook.Styles.Add()];

HeaderStyle.Name = "HeaderStyle";

//WorksheetStyle myWorksheetStyle = null;

// myWorksheetStyle = myWorkbook.Styles.Add("HeaderStyle")

HeaderStyle.Font.Name = "MS Sans Serif";

HeaderStyle.Font.Size = 10;



//Styles for Work Sheets

//Header Style


Aspose.Cells.Style DefaultColumn = myWorkbook.Styles[myWorkbook.Styles.Add()];


DefaultColumn.Font.Name = "MS Sans Serif";

DefaultColumn.Font.Size = 10;


Aspose.Cells.Style dateupload = myWorkbook.Styles[myWorkbook.Styles.Add()];


dateupload.Font.Name = "MS Sans Serif";

dateupload.Font.Size = 10;

dateupload.Number = 22;


StyleFlag style3 = new StyleFlag();

style3.NumberFormat = true;



Worksheet myWorksheet = myWorkbook.Worksheets.Add("My Report");

//myWorksheet.Options.ActivePane = 2;

myWorksheet.IsSelected = true;

myWorksheet.ActiveCell = "A2";

//Adding columns to work sheet

// Index Column

myWorksheet.Cells.InsertColumn(0);

myWorksheet.Cells.ApplyColumnStyle(0, DefaultColumn, style3);

// File Name Column

myWorksheet.Cells.InsertColumn(1);

myWorksheet.Cells.ApplyColumnStyle(0, DefaultColumn, style3);

// Folder Path Column

myWorksheet.Cells.InsertColumn(2);

myWorksheet.Cells.ApplyColumnStyle(0, DefaultColumn, style3);

// Date Uploaded Column

myWorksheet.Cells.InsertColumn(3);

myWorksheet.Cells.ApplyColumnStyle(0, dateupload, style3);

// RRD Admin User Column

if (reportType == "Me")

{

myWorksheet.Cells.InsertColumn(4);

myWorksheet.Cells.ApplyColumnStyle(0, DefaultColumn, style3);

}

// File Size Column

myWorksheet.Cells.InsertColumn(5);

myWorksheet.Cells.ApplyColumnStyle(0, DefaultColumn, style3);

// Page Count Column

myWorksheet.Cells.InsertColumn(6);

myWorksheet.Cells.ApplyColumnStyle(0, DefaultColumn, style3);




// Create the Header Row

myWorksheet.Cells.InsertRow(1);

myWorksheet.Cells["A1"].PutValue("Index Number", true);

myWorksheet.Cells["A1"].SetStyle(HeaderStyle);

myWorksheet.Cells["B1"].PutValue("File Name", true);

myWorksheet.Cells["B1"].SetStyle(HeaderStyle);

myWorksheet.Cells["C1"].PutValue("Folder Path", true);

myWorksheet.Cells["C1"].SetStyle(HeaderStyle);

myWorksheet.Cells["D1"].PutValue("Date Uploaded", true);

myWorksheet.Cells["D1"].SetStyle(dateupload);

myWorksheet.Cells["E1"].PutValue("User", true);

myWorksheet.Cells["E1"].SetStyle(HeaderStyle);

myWorksheet.Cells["F1"].PutValue("File Size (MB)", true);

myWorksheet.Cells["F1"].SetStyle(HeaderStyle);

myWorksheet.Cells["G1"].PutValue("Page Count", true);

myWorksheet.Cells["G1"].SetStyle(HeaderStyle);



DataTable dt = GenerateData(); // This contains data rows more than 20000

int rownumber = 1;

if (dt != null)

{

foreach (DataRow dr in dt.Rows)

{


myWorksheet.Cells.InsertRow(rownumber);

myWorksheet.Cells[rownumber, 0].PutValue(dr["Index"].ToString(), true);

myWorksheet.Cells[rownumber, 1].PutValue(dr["File Name"].ToString(), true);

myWorksheet.Cells[rownumber, 2].PutValue(dr["Path"].ToString(), true);

myWorksheet.Cells[rownumber, 3].PutValue(dr["Date Uploaded"].ToString(), true);

myWorksheet.Cells[rownumber, 4].PutValue(dr["User"].ToString(), true);

myWorksheet.Cells[rownumber, 5].PutValue(dr["File Size (MB)"].ToString(), true);

myWorksheet.Cells[rownumber, 6].PutValue(dr["Page Count"].ToString(), true);

myWorksheet.AutoFitColumn(rownumber); // Exception comes here which is thrown


rownumber++;

}

}

myWorksheet.AutoFitColumn(rownumber);

Please assist.

2

Hi,


Thanks for providing us code segment and details.

Well, if you are importing data from some data source (e.g DataTable), we recommend you to directly use Data Importing options provided by Aspose.Cells APIs which are efficient ways to import long list of data into spreadsheet, see the document for your reference:
http://www.aspose.com/docs/display/cellsnet/Import+and+Export+Data
(Note: please see the sub-topic Import Data from DataTable for your needs)

Also, we could not evaluate your code segment properly, because we are not sure about your data source which you are importing to an Excel workbook. We suggest you to kindly first update your sample code (in your project) in accordance with the suggested document/article and import your data table directly to Excel worksheet(s). If you still find any issue, kindly do create a simple console application (runnable), zip it and provide us, we will check it soon. Moreover, please remove any interdependencies for data source and try to use dynamic dataset/data table in your code, so we could analyse your issue properly. Alternatively, you may use MS Access Database to create your underlying table to fill data and provide the database file with the project.

Thank you.

3

Hi Amjad,


Thanks for the reply.
The issue which I am facing is similar to the issue posted in the link below
AutoFitWidth not working when reading an Xml Spreadsheet file

In my case, I am unable to export the data into the excel. The data rows are huge in count(More than 20000 rows).

<span style=“color: rgb(51, 51, 51); font-family: “Helvetica Neue”, Helvetica, “Segoe UI”, Arial, freesans, sans-serif, “Apple Color Emoji”, “Segoe UI Emoji”, “Segoe UI Symbol”; font-size: 14px; line-height: 22.4px; background-color: rgb(255, 255, 255);”>Source: Aspose.Cells<br style=“box-sizing: border-box; color: rgb(51, 51, 51); font-family: “Helvetica Neue”, Helvetica, “Segoe UI”, Arial, freesans, sans-serif, “Apple Color Emoji”, “Segoe UI Emoji”, “Segoe UI Symbol”; font-size: 14px; line-height: 22.4px; background-color: rgb(255, 255, 255);”><span style=“color: rgb(51, 51, 51); font-family: “Helvetica Neue”, Helvetica, “Segoe UI”, Arial, freesans, sans-serif, “Apple Color Emoji”, “Segoe UI Emoji”, “Segoe UI Symbol”; font-size: 14px; line-height: 22.4px; background-color: rgb(255, 255, 255);”>TargetSite: Void AutoFitColumn(Int32)<br style=“box-sizing: border-box; color: rgb(51, 51, 51); font-family: “Helvetica Neue”, Helvetica, “Segoe UI”, Arial, freesans, sans-serif, “Apple Color Emoji”, “Segoe UI Emoji”, “Segoe UI Symbol”; font-size: 14px; line-height: 22.4px; background-color: rgb(255, 255, 255);”><span style=“color: rgb(51, 51, 51); font-family: “Helvetica Neue”, Helvetica, “Segoe UI”, Arial, freesans, sans-serif, “Apple Color Emoji”, “Segoe UI Emoji”, “Segoe UI Symbol”; font-size: 14px; line-height: 22.4px; background-color: rgb(255, 255, 255);”>Message: Invalid column index.
<span style=“color: rgb(51, 51, 51); font-family: “Helvetica Neue”, Helvetica, “Segoe UI”, Arial, freesans, sans-serif, “Apple Color Emoji”, “Segoe UI Emoji”, “Segoe UI Symbol”; font-size: 14px; line-height: 22.4px; background-color: rgb(255, 255, 255);”>
<span style=“color: rgb(51, 51, 51); font-family: “Helvetica Neue”, Helvetica, “Segoe UI”, Arial, freesans, sans-serif, “Apple Color Emoji”, “Segoe UI Emoji”, “Segoe UI Symbol”; font-size: 14px; line-height: 22.4px; background-color: rgb(255, 255, 255);”>Any help is helpful.

4

Hi,


Thanks for providing further details

Well, the issue mentioned in other thread has different scenario/ application as the exception occurred on loading a SpreadsheetML file. As I told you before, I am afraid, we cannot evaluate your issue properly as we are not sure about your data source and other objects (used in your code segment). We request you to kindly do create a simple console application (runnable), zip it and provide us here to reproduce the issue on our end, we will check it soon. Moreover, please remove any interdependencies for data source and try to create/use dynamic datasets/data tables in your code, so we could analyse your issue properly. Alternatively, you may try to use MS Access Database to create your underlying table to fill data and provide the database file with the project. Furthermore, you may use dummy data if you have reservations for your confidential data.

Thank you.