How to convert an Excel Function to an actual value in C# using Gembox

Question

We have an automated feed which is populated by our ERP system using C# and GemBox which generates an Excel File with pricing.

We have a formula to roundup the pricing, but we discovered that other web systems can't see the actual value as a function is 0 and only calculated when opening an Excel file. I know you can copy and Paste as Values to get the actual value of the function, but this needs to happen in code. Do we need a temporary table in the back-end to achieve this?

How can we convert the Excel Function in the code to the actual value?

https://www.gemboxsoftware.com/spreadsheet/examples/excel-cell-formula/206

*a function is 0 and only calculated when opening an Excel file*. I don't believe this is true, perhaps it is true in some cases (i.e., the file has *never* been opened in Excel). If the file has *ever* been opened, than the cell definitely should have a value which you could read from ADO. If the file has never been opened (i.e., created programmatically with functions inserted to cells) then this may be your problem. — David Zemens, Aug 13 '18 at 14:04
Yes, the file is programmatically created and never opened. Is there a way you can program it to export the actual value? — Tig7r, Aug 13 '18 at 14:08

David Zemens · Accepted Answer · 2018-08-13T14:16:10.503

If the file has ever been opened, than the cell definitely should have a value based on its last calculation, which you could read from ADO. If the file has never been opened (i.e., created programmatically with functions inserted to cells) then this may be the root cause of your problem.

I'm not familiar with GemBox, but from the documentation this appears to be supported.

GemBox.Spreadsheet supports single Excel cell calculation, Worksheet calculation, and whole file calculation:

https://www.gemboxsoftware.com/spreadsheet/examples/excel-formula-calculation/901

using System;
using GemBox.Spreadsheet;

class Sample
{
    [STAThread]
    static void Main(string[] args)
    {
        // If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        ExcelFile ef = new ExcelFile();
        ExcelWorksheet ws = ef.Worksheets.Add("Formula Calculation");

        // Some formatting.
        ExcelRow row = ws.Rows[0];
        row.Style.Font.Weight = ExcelFont.BoldWeight;

        ExcelColumn col = ws.Columns[0];
        col.SetWidth(250, LengthUnit.Pixel);
        col.Style.HorizontalAlignment = HorizontalAlignmentStyle.Left;
        col = ws.Columns[1];
        col.SetWidth(250, LengthUnit.Pixel);
        col.Style.HorizontalAlignment = HorizontalAlignmentStyle.Right;

        // Use first row for column headers.
        ws.Cells["A1"].Value = "Formula";
        ws.Cells["B1"].Value = "Calculated value";

        // Enter some Excel formulas as text in first column.
        ws.Cells["A2"].Value = "=1 + 1";
        ws.Cells["A3"].Value = "=3 * (2 - 8)";
        ws.Cells["A4"].Value = "=3 + ABS(B3)";
        ws.Cells["A5"].Value = "=B4 > 15";
        ws.Cells["A6"].Value = "=IF(B5, \"Hello world\", \"World hello\")";
        ws.Cells["A7"].Value = "=B6 & \" example\"";
        ws.Cells["A8"].Value = "=CODE(RIGHT(B7))";
        ws.Cells["A9"].Value = "=POWER(B8, 3) * 0.45%";
        ws.Cells["A10"].Value = "=SIGN(B9)";
        ws.Cells["A11"].Value = "=SUM(B2:B10)";

        // Set text from first column as second row cell's formula.
        int rowIndex = 1;
        while (ws.Cells[rowIndex, 0].ValueType != CellValueType.Null)
            ws.Cells[rowIndex, 1].Formula = ws.Cells[rowIndex++, 0].StringValue;

        // GemBox.Spreadsheet supports single Excel cell calculation, ...
        ws.Cells["B2"].Calculate();

        // ... Excel worksheet calculation,
        ws.Calculate();

        // ... and whole Excel file calculation.
        ws.Parent.Calculate();

        ef.Save("Formula Calculation.xlsx");
    }
}

I am busy looking at the same section. It seems like our code is missing WS.Calculate(); — Tig7r, Aug 13 '18 at 14:15

How to convert an Excel Function to an actual value in C# using Gembox

1 Answers1