dear support,
is there a possibility to get a list of all cell with formulas in a workbook or worksheet ? Or get a list of all formulas.
thank you,
guido
Hi,
Well, you got to loop through the cells in sheet and check if the cell is a formula cell, then you may easily put the cell formula to some arrays or list for your requirements, see the sample code below:
e.g
Sample code:
Workbook workbook = new Workbook(@“e:\test2\Book1.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
int cnt = 0;
string cellname = null;
string ftext = null; //you may define some arrays or list
for (int i = 0; i < cells.Count; i++)
{
if (cells[i].IsFormula)
{
cellname = cells[i].Name;
ftext = cells[i].Formula;
//…
//Your code goes here.
//…
cnt++;
}
}
MessageBox.Show("Total formula cells: " + cnt);
Hope, it helps a bit.
Thank you.
ok, thank you for your mail.
when i look at the cells object, it contains lots of cells that have been edited in the past but that contain no more value or formula anymore.
So the Cells objetc contains lots of useless entries, which slows down the search in big spreadsheets that have been edited a lot.
Is there a way to clean up this useless entries in the cells object, or get to the cells that are nonzero ( no data or no formula ) ?
thanks for yoru thought,
guido
furthermore to my previous concern regarding ebtries in the Cell object that are empty, there is another problem.
Some of my Excel function are spaaning multiple cells ( see attached excel ). That means that the reuslt is more than one cell, they are entered with the Ctrl-Shift-Enter keystrokes when an adjacent range is selected.
The porblem is that there is no mechanism in Aspose.Cells to see if a given formula is part of a larger formula that spans multiple cells.
Any ideas ?
thank you,
guido
sorry, please disregard my previous question - i found that the IsarrayHeader serves this purpose.
But the issue of having too many entries in the cells object remains