I am having a couple of problems with the following code:
Dim dt As DataTable = sheet.Cells.ExportDataTable(0, 0, sheet.Cells.MaxRow, sheet.Cells.MaxColumn)
The DataTable is being created fine except that:
A) Any fields which contain formulas are appearing as blanks
B) The last row and in some cases the last column is being dropped off
Could you please advise what the problem could be?
Thanks for your consideration.
A) Aspose.Excel doesn’t calculate Excel formula and leaves MS Excel to do it. So if the formula is set by api, the exported cell field is blank. If the formula is set in designer file, its result could be read but cannot be consist with the cell data if source cell data changes.
For example, A1 is 1, B1 is 2, C1 is “=A1+B1”. If you don’t change A1 and B1, C1 can be exported to 3. But if you change A1 to 2, the C1 is still 3. So I leave all formula cell as blank.
B)Try to change your code to this:
Dim dt As DataTable = sheet.Cells.ExportDataTable(0, 0, sheet.Cells.MaxRow + 1, sheet.Cells.MaxColumn + 1)