I have run into a problem while using the Cell.R1C1Formula property. The issue appears when I try to use it to access cells starting at row 65536. If I try that, it will wrap around and the resulting formula will contain the wrong cells. This code should replicate the behaviour I am experiencing (currently using version 7.3):
Dim TempFileName As String = "C:\Temp\Test.xlsx"
Dim Workbook As New Workbook()
Workbook.Worksheets(0).Cells(0, 0).Value = 100
Workbook.Worksheets(0).Cells(10000, 0).Value = 100
Workbook.Worksheets(0).Cells(50000, 0).Value = 100
Workbook.Worksheets(0).Cells(69999, 0).Value = 100
Workbook.Worksheets(0).Cells(70000, 0).R1C1Formula = "=SUM(R[-70000]C:R[-1]C)"
Workbook.Worksheets(0).Cells(0, 1).Value = 100
Workbook.Worksheets(0).Cells(10000, 1).Value = 100
Workbook.Worksheets(0).Cells(50000, 1).Value = 100
Workbook.Worksheets(0).Cells(65534, 1).Value = 100
Workbook.Worksheets(0).Cells(65535, 1).R1C1Formula = "=SUM(R[-65535]C:R[-1]C)"
Workbook.Save(TempFileName, SaveFormat.Xlsx)
If I run this example code, then the first cell (at position A70001) will contain the formula =SOM(A1:A4464) (70000 - 65536) and the value 100 instead of 400. The second cell (at position B36636) will contain the formula =SOM(B1:B65535) and the expected value of 400.
I don't know if this is a known issue, I could not find any information on it. If so, is it fixed, or will it be fixed? As I said I am currently using version 7.3 but upgrading will take some time and I would like to know if it will be of help. I believe I can work around it using normal formulas but I'd rather use these as the numbers of rows and cell positions are all calculated in code. Thank you for any assistance you can offer.
Hi,
Well, it is not a bug rather you have to specify the FileForamtType to XLSX file format while creating new workbooks, please update your line of code i.e…,
Dim Workbook As New Workbook()
to:
Dim Workbook As New Workbook(FileFormatType.Xlsx)
it will work fine.
Let us know if you still find any issue.
Thank you.
Ah, I see. That does work.
I now do have another question. I am working with various SaveFormat values, which are not known to me at the time of writing these functions. Will I need to map the various SaveFormat values to FileFormatType values so that I can instantiate the Workbook proper, or is it safe for me to always use FileFormatType.Xlsx and call the Save function any SaveFormat I want? And if I need to map them, is this mapping available somewhere?
Thank you for your help.
Hi,
Well, Aspose.Cells by default instantiates the workbooks in XLS (MS Excel 97-2003) file format. Whenever you need to create a new workbook for advanced/new MS Excel 200/2010 file formats (e.g XLSX, XLSM, XLSB etc.), you should instantiate the workbook specifying the relative FileFormatType enum member, so everything especially setting/calculating formulas work fine and accordingly.
Thank you.
And there is no risk in using FileformatType.Xlsx and then saving with SaveFormat.Excel97To2003? So this will work? Or should I expect problems with that? In my test it cuts the rows off, of course, but this is fine.
Dim TempFileName As String = "C:\Temp\Test.xls"
Dim Workbook As New Workbook(FileFormatType.Xlsx)
Workbook.Worksheets(0).Cells(0, 0).Value = 100
Workbook.Worksheets(0).Cells(10000, 0).Value = 100
Workbook.Worksheets(0).Cells(50000, 0).Value = 100
Workbook.Worksheets(0).Cells(69999, 0).Value = 100
Workbook.Worksheets(0).Cells(70000, 0).R1C1Formula = "=SUM(R[-70000]C:R[-1]C)"
Workbook.Worksheets(0).Cells(0, 1).Value = 100
Workbook.Worksheets(0).Cells(10000, 1).Value = 100
Workbook.Worksheets(0).Cells(50000, 1).Value = 100
Workbook.Worksheets(0).Cells(65534, 1).Value = 100
Workbook.Worksheets(0).Cells(65535, 1).R1C1Formula = "=SUM(R[-65535]C:R[-1]C)"
Workbook.Save(TempFileName, SaveFormat.Excel97To2003)
Thank you
Hi,
Well, I think it will work fine for your needs (instantiating new Workbooks in XLSX file format but saving in older XLS file format --> will give you resultant file for MS Excel 97-2003 XLS which it should do.
Thank you.