Hello,
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.
Two questions:
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?
/Fredrik
Hi,
Well, I thought you got strange requirement but anyways for your questions:
1) I think you are doing fine.
2) I think you can also use Cell.GetPrecedents() method that returns ReferredAreaCollection object, see the sample code below.
Sample code:
Workbook workbook = new Workbook();
Cells cells = workbook.Worksheets[0].Cells;
cells[“A1”].Formula = “=Sheet1!A2”;
ReferredAreaCollection areas = cells[“A1”].GetPrecedents();
for (int i = 0; i < areas.Count; i++)
{
ReferredArea area = areas[i];
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(CellsHelper.CellIndexToName(area.StartRow, area.StartColumn));
if (area.IsArea)
{
stringBuilder.Append(":");
stringBuilder.Append(CellsHelper.CellIndexToName(area.EndRow, area.EndColumn));
}
switch (i)
{
case 0:
// e.g “SheetName!CellName”);
MessageBox.Show(stringBuilder.ToString()); //This will show A2
break;
case 1:
// e.g “Sheet1!B1:B10”);
MessageBox.Show(stringBuilder.ToString());
break;
}
}
Hope, this helps,
Thank you.
That works fine (GetPrecedents).
One future possible improvement: GetPrecedents potentially returns the same cell multiple times.
If the formula is, say, "=IF(ISBLANK(C1);"";C1)", then C1 will be returned twice.
Easy to workaround of course, so it's not critical.
Hi,
We have fixed issue of duplicated cells.Please download Aspose.Cells for .NET v7.0.1