Limitation to number of worksheet a workbook can have

Aspose.Cells Product Family
1

Hi,

May I know is there any limitation to the number of worksheet a workbook can have?

Thanks.

2

Hi,

Well, no, there is not any worksheet limit enforced by Aspose.Cells for .NET whatsoever. You can have as many sheets in a single workbook (xls or xlsx formats) as you want and as long as your memory allows this. Now, Aspose.Cells for .NET supports both MS Excel(97 - 2003) and MS Excel 2007 (Xlsx, Xlsm,Xltm etc.) formats. If you requirement is to have/export more than 65k rows in a worksheet to excel file, you should use/create Xlsx file formats, see the document for reference:

http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/saving-files.html


Thank you.

3

Hi,

Thanks for the reply. Apart from that I realised excel generated is giving inaccurate size.

E.g, I generated an excel with 100 worksheets, it is giving the file size as 16.3MB but when I open that file and re-save it as another file name, the file size become 248KB.

Any idea whay there is such a great change in the file size?

4

Hi,

In order to increase the performance and reduce the file size, Aspose.Cells for .Net does not export cell name to .xlsx (.xlsm, .xltx, .xltm) file by default. So the file size will become smaller after you re-save it. MS Excel will open the file correctly anyways.

For your information, the property “Workbook.SaveOptions.ExpCellNameToXLSX” indicates if exporting cell names to Excel2007 .xlsx (.xlsm, .xltx, .xltm) file format. The default value is set to false. So, you may use this attribute for your need.

Furthermore, since SQL Server DTS cannot open .xlsx (.xlsm, .xltx, .xltm) file without cell names, if the output file is going to be accessed by SQL Server DTS, so consequently you have to set property “Workbook.SaveOptions.ExpCellNameToXLSX” to true.

Thank you

5

Hi,

However i export the excel to Excel 2003 .xls. Does Aspose.Cells for .Net work the same as well for Excel 2003 xls?

Thanks.

6

Hi,

Yes, sure, it works similar to MS Excel 2003. I have attached our latest fix v4.8.0.15, you may try it. If you find any issue, kindly let us know and we will check your issue soon.

Thank you.

7

Hi,

We have actually find out which line of our code causing the enormous file size. But we are wondering why would this line of code (that is the line related to adding item into arraylist) impact Aspose to generate such a huge file size. Any advise would be appreciated. The line in pink font is causing the enormous file size.

private int GetRowByGrade(int wksheet, string grade, IList<GradingSchemeBO> gradingSchemeList, int gradeRow, Workbook wb)

{

cells = wb.Worksheets[wksheet].Cells;

Range rng1;

// Add the range value into an arraylist

ArrayList myvalues = new ArrayList();

// Range of the grade

rng1 = cells.CreateRange("A" + gradeRow.ToString(), "A" + (gradingSchemeList.Count + 11).ToString());

for (int k = 0; k <= rng1.RowCount - 1; k++)

{

myvalues.Add(rng1[k, 0].StringValue);

}

for (int k = 0; k <= myvalues.Count - 1; k++)

{

string value = myvalues[k].ToString();

if (value != string.Empty)
{

if (value == grade)

{

return (gradeRow + k);

}

}

}

return 0;

}

8

Hi,

Do you use Aspose.Cells for ,NET to generate xls file or xlsx file? Could you post us the generated excel file, also post the re-saved file here. We want to check file size difference.

We also appreciate if you could create a sample console application using the latest fix(I posted i.e. v4.8.0.15), zip it and post it here. It would help us really to tracing the issue to figure it out.

Thank you.