Convert formula to value

Aspose.Cells Product Family
1

Hi,


A cant find information about this topic, but I think Aspose.cells has this functionality.

The use case is simple… I have a xls file full of formulas and I need a new one with the values instead of formulas. We need to hide the logic to the final user.

Is this possible in Aspose.cell?


I really appreciate any help you can provide.
2

Hi Tinsa,

Thank you for contacting Aspose support.

You can opt to create a range of cells that you wish to copy to another Workbook/Worksheet, and create a similar range on the destination before calling the Range.copyValue method. Please have a look at the below provided code snippet as well as attached spreadsheet for your kind reference.

Java


Workbook input = new Workbook(myDir + “input.xls”);
input.calculateFormula();
Worksheet sheet = input.getWorksheets().get(0);
Cells cells = sheet.getCells();
Range inRange = cells.createRange(“A1”, “D9”);

Workbook output = new Workbook ();
sheet = output.getWorksheets().get(0);
cells = sheet.getCells();
Range outRange = cells.createRange(“A1”, “D9”);
outRange.copyValue(inRange);
output.save(myDir + “output.xls”, SaveFormat.EXCEL_97_TO_2003);

Hope this helps a bit.
3

Hi,


Alternatively, you my try to use Cells.RemoveFormulas() method which will replace all the formulas with calculated values in the cells (in the worksheet), see the sample code for your reference:

string filePath = @“e:\test2\template1.xls”;
Workbook _wb = new Workbook(filePath);
_wb.Settings.CreateCalcChain = false;
Worksheet worksheet = _wb.Worksheets[“Sheet1”];
_wb.CalculateFormula();
Cells cells = worksheet.Cells;
cells.RemoveFormulas();

_wb.Save(“e:\test2\mynewoutfile1.xls”);


Thank you.
4

Fantastic!!!


This is exactly what I was looking for.

Thanks a lot!!!
5

Hi Tinsa,

Good to know that you are up and running again. Please feel free to write back in case you need our further assistance.