Hi,
Im using Aspose.Cells to generate Excel Files with at least 6.000 rows - all containing multiple formulas.
Every thing works pretty fine except the fact that after the file got created with Aspose.Cells and gets opened for the first time in Excel the program gets forced to calculate all formulas.
As i found out this seems to be forced by Aspose.Cells explicit.
" When Aspose.Cells saves the file, it forces the formula to be re-calculated while opening in Excel."
So far so good, but is there a way to avoid this?
What i would like to do is to calculate all formulas in the sheet after i have created it and save it as calculated so that Excel doesnt recalculate it “on open” - the longer creation time for the file would not be a problem at all.
Thanks,
MK
Hi MK,
Thanks for considering Aspose.
Well, you may try to use Workbook.CalculateFormula() method and calculate the formulas at runtime. But keep an eye on its supported formulas list as a few formulas are not supported yet:
https://docs.aspose.com/display/cellsnet/Formulas
I write the following code for your need using a sample template file “formulabook.xls” (attached). Here, I calculate all the formulas on runtime and pasted the results into the related cells as values. Mind you, there will be no formula strings to be shown when you double click on the formula cells in MS Excel:
// Create a Workbook.
Workbook workbook = new Workbook();
// Open an excel file to it.
workbook.Open(@"d:\test\formulabook.xls");
// Create a Worksheet and get the first sheet.
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
for (int i = 11; i < 86; i ++)
{
string strFormula = cells[i, 2].Formula;
//Set a formula of the Cell
cells[i, 2].Formula = strFormula;
//Calculates the result of formulas
workbook.CalculateFormula(true);
cells[i, 2].PutValue(cells[i, 2].Value);
}
workbook.Save(@"d:\test\formulabook1.xls");
Thank you.