Importing data into Excel sheet formula is not calculated in .NET

Aspose.Cells Product Family
1

I’ve created an xlsx file using the following code:


Formula field:

sheet.Cells[“D” + rowCounter].Formula = “=100 * " + “F” + rowCounter + " / C” + rowCounter;
excel.Save(filename, Aspose.Cells.SaveFormat.Xlsx);

Then later on I’m trying to import that excel file using the following code:


string ConnectString = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” + newfilename + ";Extended Properties=“Excel 12.0;HDR=Yes;IMEX=2"”;

OleDbConnection connection = new OleDbConnection();

connection.ConnectionString = ConnectString;

connection.Open();

OleDbDataAdapter command = new OleDbDataAdapter(“SELECT * FROM [Sheet1$]”, connection);

DataSet ExcelData = new DataSet();

command.Fill(ExcelData);

DataView dv = new DataView(ExcelData.Tables[0]);


The problem is when trying to read the values from the formula field, it returns a blank:

string colval = Convert.ToDouble(dr[“FormulaColumn”]);

However, if I take the xlsx file and click Save in Excel and then try to import it, the value is no longer blank.

The problem only happens with the ASPOSE generated file directly.



2

I should mention I’m using aspose.cells.dll version 8.1.1.0

3

One more thing. Whenever I open up the file generated from ASPOSE, and close it (without doing anything to the file), it always asks me to SAVE the file, as if I had changed something. However, once I save the file in Excel it never asks me to make this sort of “nothing changed yet ask for save” scenario.

4

Hi Mike,

Thanks for your posting and using Aspose.Cells.

You need to call the Workbook.CalculateFormula() method before accessing the values of the formula field.

Please see the following sample code for your reference. If you will comment the Workbook.CalculateFormula() method, then you will get blank values but if you will not comment it, you will get calculated values of the formula.

C#


Workbook workbook = new Workbook();


Worksheet sheet = workbook.Worksheets[0];


for (int rowCounter = 1; rowCounter < 10; rowCounter++)

{

sheet.Cells[“C” + rowCounter].PutValue(rowCounter);

sheet.Cells[“F” + rowCounter].PutValue(rowCounter);

sheet.Cells[“D” + rowCounter].Formula = “=100 * " + “F” + rowCounter + " / C” + rowCounter;

}


//Call workbook.calculateformula before reading the values from formula field.

//if you will comment this line then values read will be blank

workbook.CalculateFormula();


//Access the values of D1, D2 and D3

Console.WriteLine(sheet.Cells[“D1”].StringValue);

Console.WriteLine(sheet.Cells[“D2”].StringValue);

Console.WriteLine(sheet.Cells[“D3”].StringValue);



Console Output:
100
100
100