Incorrectly translates A:A into A$1:A$65536

Aspose.Cells Product Family
1

Hello,

The following program creates a sheet with 100,000 rows. Cell B1 is supposed to contain the formula =SUM(A:A). It appears that the Aspose library is translating this into =SUM(A$1:A$65536), which is obviously incorrect. I am using aspose.cells for .NET version 4.9.1.0

//aspose version is 4.9.1.0

using Aspose.Cells;

namespace ConsoleApplication9 {
public class Program {
public static void Main(string[] args) {
var workbook = new Workbook();
var sheet=workbook.Worksheets.Add("Test");
sheet.Cells[0, 0].PutValue(11);
sheet.Cells[99999, 0].PutValue(22);

sheet.Cells[0, 1].Formula="=SUM(A:A)";
workbook.Save(@"c:\temp\test.xlsx", FileFormatType.Excel2007Xlsx);
}
}
}

2

Hi,

Please add a line of code to your code (as mentioned below), it will work fine. You need to explicitly set the file format type of the workbook to Excel 2007 Xlsx format soon after instantiating the workbook object. For your information, when you create/instantiate a new workbook, it will be created in Excel 2003 Xls file format type (by default), so all the formulas are evaluated and parsed in Excel 2007 Xls format only which obviously has maximum of 65536 rows in a worksheet.

//aspose version is 4.9.1.0

using Aspose.Cells;

namespace ConsoleApplication9 {
public class Program {
public static void Main(string[] args) {
var workbook = new Workbook();
workbook.FileFormat = FileFormatType.Excel2007Xlsx;

var sheet=workbook.Worksheets.Add("Test");
sheet.Cells[0, 0].PutValue(11);
sheet.Cells[99999, 0].PutValue(22);

sheet.Cells[0, 1].Formula="=SUM(A:A)";
workbook.Save(@"c:\temp\test.xlsx", FileFormatType.Excel2007Xlsx);
}
}
}


Thanks for your understating!
3

Hi,

Or you may try the following code:

var workbook = new Workbook(FileFormatType.Excel2007Xlsx); 
var sheet=workbook.Worksheets.Add(“Test”);
      sheet.Cells[0, 0].PutValue(11);
      sheet.Cells[99999, 0].PutValue(22);

      sheet.Cells[0, 1].Formula="=SUM(A:A)";
      workbook.Save(@“c:\temp\test.xlsx”, FileFormatType.Excel2007Xlsx);

Thank you.


4

That works. Thank you for your help!

5

Amjad, I don't see any documentation on the FileFormat property. Can I set it after I've loaded data and formulas into a workbook but BEFORE I've need to use any XLSX-specific features? For example, let's say I create the workbook and fill out a few small sheets, then discover that I need more than 65K rows on another sheet. Can I just change the FileFormat at that time?

A related question: if I set FileFormat to Excel2007Xlsx when I create the workbook but then discover that I didn't need any Excel 2007 features, can I save it as the older XLS format in the Save method? Do I incur any penalties for doing it this way?

Thanks,
--Howard

6

Hi Howard,

Yes, you may do it, you can use Workbook.FileFormat attribute to set/change your desired file format type later, e.g

Workbook workbook = new Workbook(FileFormatType.Excel2007Xlsx);
//Some code.
workbook.FileFormat = FileFormatType.Excel97To2003;


Thank you.