List of all formulas in a workbook

Aspose.Cells Product Family
1

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


2

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.
3

Hi,


Furthermore, to traverse all cells in a worksheet, it is better to use Enumerator instead, it will give you good performance, see the sample code below.
e.g
Sample code:

string filePath = @“e:\test\Book1.xlsx”;

//Load a source workbook
Workbook wb = new Workbook(filePath);

int cnt = 0;
string cellname = null;
string ftext = null; //you may define some arrays or list

//Iterate all worksheets
for (int i = 0; i < wb.Worksheets.Count; i++)
{
Cells cells = wb.Worksheets[i].Cells;

//iterate all the cells.
for (IEnumerator ie = cells.GetEnumerator(); ie.MoveNext(); )
{
Cell cell = (Cell)ie.Current;

if (cell.IsFormula)
{

cellname = cell.Name;
ftext = cell.Formula;
//…
//Your code goes here.
//…
cnt++;
}//if
}//for
}//for
…


Thank you.
4

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

5

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


6

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
7

Hi,


Well, I am afraid, currently there is no such API available for accessing formulas only or deleting your desired blank cells in the sheet. We are also not sure what you actually need to perform and your actual final goal for accessing the formulas list only. We think maybe you can check whether Cells.Find method with suitable find options set that can fit your requirement to search the formulas “a formula normally starts with ‘=’ sign” firstly. Please see the following code segment for your reference, you may update or write your own codes accordingly for your requirements:
e.g
Sample code:

…
FindOptions fopts = new FindOptions();
fopts.LookInType = LookInType.OnlyFormulas;
fopts.LookAtType = LookAtType.StartWith;
fopts.CaseSensitive = true;
cell = cells.Find("=", null, fopts);
while (cell != null)
{
Console.WriteLine(cell.Name + “: " + cell.Formula); //here you can give your own logic to do with the cell and formula
cell = cells.Find(”=", cell, fopts);
}
We hope, this will help you to find and do with only cells with formulas.


Thank you.