Aspose.cells formula query

Aspose.Cells Product Family
1

I got an issue with aspose.cells

i have a report which contains more than 256 columns. however, i found the formula can only count to max 256 columns

For
example, I have a formula to sum from cell F2 to cell JG2. What I did
is to set formula SUM(F2:H2) in column J and then insert new columns after column F repeatly

However, the formula can only set to SUM(F2:IT2), but the new columns are up to JG, so it should be SUM(F2:JG2)

This message was posted using Email2Forum by Tahir Manzoor.
2

Hi,


Well, in XLS file format, you can only have 256 columns in a worksheet, it is the limitation of the format set by MS Excel. However, in XLSX file format (MS Excel 2007/2010), you can have lots of columns, so if you are creating a new workbook to place your data and formulas in rows/columns, please try to use the line at the start instead:

Workbook workbook = new Workbook(FileFormatType.Xlsx);

and save to XLSX file format instead of XLS, it should fix your issue.

Thank you.
3

Hi, Amjad,


Thanks for reply.

I did save the excel into xlsx format and I have no issue with more than 256 columns.

The problem is the formula. When a new column is inserted, I suppose the formula will be changed as well. For example, it is SUM(F2:H2) originally, once a new column was added, it should change to SUM(F2:I2) and so on, and it did work in this way, until column IT2, then it stopped despite of the following newly added columns.

Regards,
4

Hi,


If you are creating Workbook from scratch, please use the line as I suggested in my previous post:

Workbook workbook = new Workbook(FileFormatType.Xlsx);

If you still find the issue, kindly give us your sample code (runnable) to reproduce the issue on our end, we will check it soon.

Thank you.
5

I think I figured out what the problem is. Although the codes have been revised, report template I used is still the .xls format. Even though I saved the output as .xlsx, it still have the limitation. Once I had changed the template into .xlsx format, formula issue was gone.

6

Hi,

Thanks for your posting and using Aspose.Cells.

You are right. There is no such issue with XLSX format. When you create a workbook object with default constructor, it creates the workbook object in XLS format which has column limitations.

Therefore, you need to specify FileFormatType.Xlsx in the workbook constructor so that Aspose.Cells could create it in XLSX format.

Let us know if you face any other issue. We will be glad to help you further.