Can your products convert from Formula to Value?

Aspose.Cells Product Family
1

Can any of your products read a stream which contains an Excel document (1 or more sheets) / read it in and convert all formula to values and then save the document to a stream?

Chris

2

The answer is yes.

Could you elaborate your need? What kind of conversion are you expected? Then I can give your more detail information.

3

Basically I was asked the following by my boss.

Create a SOAP XML web service that accepts an Excel file, converts the formulas in all cells of all sheets into output values, and then returns the converted Excel file.

Which product of yours can do this? and at what cost?

How fast would it be to convert a spreadsheet with say 10 sheets and 1000 rows with 25 columns?

Just a general fast, or slow would be OK.

In Excel you can simple copy/paste as but that uses the clipboard and is not multithreaded automation friendly.

Chris

4

Hi Chris,

You can use Aspose.Cells for this purpose. For pricing issue, please check https://forum.aspose.com/c/purchase.

For conversion time, it depends on number of formulas in your file. You can post your sample file here and I will do the test for you. Generally I think it's very fast.

5

hi laurence,

i’m wondering how one should go about converting ALL formulae in a workbook to values? or specify only formulae that designate another cell location?

the brute-force way is to:
1. iterate through all worksheets, and then
2. for each worksheet, iterate through all Cells to check for individual Formula and write back the value into the cell

is there a better approach?

thanks,
nick

6

Hi Nick,

I think this is the only approach. Following is the sample code:

workbook.CalculateFormula(true);

for(int i = 0; i < workbook.Worksheets.Count; i ++)

{

Cells cells = workbook.Worksheets[ i ].Cells;

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

{

Cell cell = cells[ j ];

if(cell.IsFormula)

{

cell.PutValue(cell.Value);

}

}

}

7

And the other way around? Is it possible to convert values to formulas and keeping the high performance of Aspose.Cells? When using this piece of code:

Worksheet dataWs = this.newWorkbook.Worksheets[sheetNo];

dataWs.Cells[row, col].Formula = dataWs.Cells[row, col].StringValue;

I get a performance of around 2000 formulas per 100 seconds. Not very fast!

8

Hi,

Thanks for considering Aspose.

Yes, In Aspose.Cells, you can convert a string value to formula and a formula to value.

Regarding performance you can evaluate it.

Regards

Amjad Sahi

Aspose Nanjing Team