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

Answer 1

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");
    }
}