Formula count in Excel sheet

Aspose.Cells Product Family
1

<o:shapelayout v:ext=“edit”>

<o:idmap v:ext=“edit” data=“1” />

</o:shapelayout><![endif]–>

Hi,

We are using “Aspose.Cells” in one of our project which contains Excel formula and text. We need to get list of cells which only have formula. Is there any function which returns number of cells which only contains formula?

Thanks in advance.

Best Regards,

Manish Misra

This message was posted using Email2Forum by ShL77.
2

Hi,

Please try the following sample code for your reference for your requirement.

Workbook workbook = new Workbook(@“e:\test\Book1.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;

/*
int rows = cells.MaxDataRow;

int numberOfColumns = cells.MaxDataColumn;
//To count the formulated Cells in a worksheet.
int cnt = 0;
for (int i = 0; i <= rows; i++)
{
for (int j = 0; j <= numberOfColumns; j++)
{
if (cells[i, j].IsFormula)
{
cnt++;
}
}

}
*/


//Or you may use this approach:
//To count the formulated Cells in a worksheet.
int cnt = 0;
string cellname = null;
string fstring = null;

for (int i = 0; i < cells.Count; i++)
{

if (cells[i].IsFormula)
{
cellname = cells[i].Name;
fstring = cells[i].Formula;
cnt++;
}


}


MessageBox.Show("Total formula cells: " + cnt);


Thank you.

3

One more query; Is there any function in Aspose.cells that returns name of functions as collection/ array which is used in formula.

ex. - =A1*Int(A2)+Round(A3,2) - we need collection/ array which contains Int and Round.

4

Hi,

Well, I am afraid, we don’t have such kind of api, I think you may get the formula string using Cell.Formula and obtain the function names from the Formula String.

Thank you.

5

Thanks a lot for your help.