We are all aware of the "feature" in Excel, that a cell that contains a reference to another cell will become 0 if the original cell is blank.
For instance, if the formula of A1 is "=Sheet1!A2", and the A2 cell in Sheet1 is blank, then A1 will have the value of A1 will be 0, not blank.
Also, if using ExportDataTable, the value retrieved from A1 will be 0, not null.
For different reasons we can't use the work-around "=IF(ISBLANK(Sheet1!A2),"",Sheet1!A2)" - we need to use simple references, like "=Sheet1!A2".
I am thinking if implementing this in the following way:
Instead of using ExportDataTable, I will loop over all the cells in the range I need to export. If the cell contains a value, I retrieve the value. If the cell contains a formula, I find the cell that is referenced (A2 on Sheet1 in the example above), and retrieve the value from that cell.
1. Is this the best way to do this, or are there is there some clever way I am missing here, some feature in Aspose.Cells that I have missed, for instance?
2. Using the Aspose API, how do I quickly find a cell in a workbook by formula? I.e. given the string "=Sheet1!A2", how do I get a reference to the cell A2 on Sheet1?