1

I am trying to insert a Shared Formula into an existing Excel sheet. every time I open the finished Excel, it shows me an error and fixes the Excel. I have the code from OpenXML Productivity Tools. All other formulas work without problems ...

var cellFormula141 = new CellFormula
{
    FormulaType = CellFormulaValues.Shared,
    Reference = $"J{rowIndex}",
    SharedIndex = 0U,
    Text = $"CONCATENATE(MONTH(I{rowIndex}),\"/\",YEAR(I{rowIndex}))"
};

var cellValue215 = new CellValue
{
    Text = ""
};

cell372.Append(cellFormula141);
cell372.Append(cellValue215);

Every time I open the excel it is fixed and the formula is gone.

br0ken.pipe
  • 850
  • 3
  • 17
  • 32

1 Answers1

0

pipe,

I’m also still struggling with open xml but maybe this will help. I’m not sure if you differentiate correctly between source cell and shared cell.

Compare the Properties of

DocumentFormat.OpenXml.Spreadsheet.Cell

source cell:

CellFormula.Text == "A1+B1"
CellFormula.FormulaType.Value == "Shared"
CellFormula.Reference == "A2:A9"
CellRefrence == "A2"

shared cell:

CellFormula.Text == ""
CellFormula.FormulaType.Value == "Shared"
CellFormula.Reference == null
CellRefrence == "A3"

You could also build a xlsx-File with this kind of formula and compare differences between these cells.

Also: it is always important to append cells from right to left to your DocumentFormat.OpenXml.Spreadsheet.Row. Otherwise your xml file needs to be repaired.

Rock
  • 21
  • 3