dotnet / Open-XML-SDK

Open XML SDK by Microsoft
https://www.nuget.org/packages/DocumentFormat.OpenXml/
MIT License
3.99k stars 544 forks source link

CellFormula created with Open XML SDK does not work in Google Sheets #1746

Closed RunningDeer closed 2 months ago

RunningDeer commented 2 months ago

My code creates a spreadsheet with a formula in cell B5, e.g. =SUM(B6:B9). The xlsx created opend correctly in Excel and LibreOffice. However, opening it in Google Sheets, shows that B5 contains ==SUM(B6:B9), the duplicate == causing a formula parse error.

Code

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

var fileName = "c:/tmp/test.xlsx";
using var spreadsheetDocument = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook);
var workbookPart = spreadsheetDocument.AddWorkbookPart();
workbookPart.Workbook = new();
var sheets = workbookPart.Workbook.AppendChild(new Sheets());
var worksheetPartSummary = workbookPart.AddNewPart<WorksheetPart>();
worksheetPartSummary.Worksheet = new(new SheetData());
var sheetData = worksheetPartSummary.Worksheet.Elements<SheetData>().First();
var summary = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPartSummary), SheetId = 1, Name = "Summary" };
sheets.Append(summary);
var row5 = new Row { RowIndex = 5 };
var a5 = new Cell
{
    CellReference = new("A5"),
    DataType = CellValues.String,
    CellValue = new("Total:")
};
var b5 = new Cell { CellFormula = new("=SUM(B6:B9)") };
row5.Append(a5, b5);
sheetData.Append(row5);

Expected: Opening in Google Sheets should have =SUM(B6:B9) in cell B5, as it does in opening in Excel Actual: Opening in Google Sheets has ==SUM(B6:B9) in cell B5, unlike Excel

Note

This is only an issue when creating a .xlsx file programmatically with this library. When I create a new spreadsheet in Excel, and then open it with Google Sheets, the formula displays correctly with a single =.

RunningDeer commented 2 months ago

Solution: remove the = sign entirely

var b5 = new Cell { CellFormula = new("SUM(B6:B9)") };