GemBox Support Forum

Formula in ods opened with MS

Created workbook that contains SUM formula and saved in in ods format. If that ods is opened in OpenOffice - formula is calculated, but if ods is opened in MS Excel then formula cannot be displayed - Excel shows #NAME? in cell, because range values are surrounded with single quotes =SUM(‘A1’:‘A2’)

    public void SumFormula()
        var workbook = new ExcelFile();
        var worksheet = workbook.Worksheets.Add("Writing");

        worksheet.Cells["A1"].Value = 1;
        worksheet.Cells["A2"].Value = 1;
        worksheet.Cells["A3"].Formula = "=SUM(A1:A2)";
        workbook.Save(@"c:\sum.ods", new OdsSaveOptions());


Try this:

worksheet.Cells["A3"].Formula = "=SUM([.A1:.A2])";

As an FYI, I retrieved this syntax by creating the desired ODS file using OpenOffice Calc, then loading that ODS into an ExcelFile, and reading the required ExcelCell.Formula property.

I hope this helps.