Using OpenXML to write "spill" or array data to Excel. This is data that all lives in one cell, but spills over into other cells.
If the cell already has a spill/array in it and then I run my code everything works fine. But if the cell is empty or has a single value, once I run my program and open the .xlsx the editted cells formula is wrapped in an extra pair of curly braces {} and if I try to edit that cell at all I get an error in excel.
My code uses OpenXML to edit the XML of the .xlsx file. I started by just editing the CellFormula. But then I started editing the Row the cell is contained in, and now adding the cell to the calcChain but none of these additions have seemed to fix the issue.
public static void WriteSpillData(this WorksheetPart worksheetPart, string cellName, List<dynamic> data)
{
if(data.Count < 1) return;
var cell = worksheetPart.GetNamedCell(cellName);
if(cell is null) return;
var currentRef = cell?.CellFormula?.Reference;
if (!string.IsNullOrEmpty(currentRef))//if an existing spill existed this clears the cells of the old values
{
var range = worksheetPart.GetRange(CellReference.ParseRange(currentRef));
foreach (Cell existingCell in range)
{
if(existingCell is null ||
existingCell.CellReference.Value == cell.CellReference.Value) continue;
existingCell?.Remove();
}
}
//CellReference is just a helped class for converting between excels 1's based
//numbering convention for rows and columns and handle converting "B9" to a
//usable column and row number
CellReference reference = new CellReference(cell.CellReference.Value);
CellReference otherEnd = new CellReference(reference.Row, (reference.Column + data.Count - 1));
var refStr = $@"{reference}:{otherEnd}";
string arrayValue = string.Empty;
StringBuilder build = new StringBuilder();
if (IsNumeric(data.FirstOrDefault()))
{
build.Append("{");
build.Append(String.Join(',', data));
build.Append("}");
}
else
{
build.Append("{\"");
build.Append(String.Join("\",\"", data));
build.Append("\"}");
}
arrayValue = build.ToString();
cell.CellFormula = new CellFormula(arrayValue)
{
FormulaType = new EnumValue<CellFormulaValues>(CellFormulaValues.Array),
Reference = refStr,
AlwaysCalculateArray = true,
};
cell.CellValue?.Remove();
if (cell.Parent is Row row)
{
row.Spans = null;
row.RemoveAttribute("spans", "");
row.Spans = new ListValue<StringValue> { InnerText = $"1:{data.Count + 2}" };
}
worksheetPart.AddCellToCalcChain(cell.CellReference?.Value);
}
private static void AddCellToCalcChain(this WorksheetPart worksheet, string? cellReference)
{
if(string.IsNullOrEmpty(cellReference)) return;
// Use nullable type and null check to fix CS8600
WorkbookPart? workbookPart = worksheet.GetWorkbook();
if (workbookPart == null) return;
if (!(worksheet.GetSheetId() is uint sheetId)) return;
var calcChainPart = workbookPart.GetPartsOfType<CalculationChainPart>().FirstOrDefault();
if (calcChainPart == null)
{
calcChainPart = workbookPart.AddNewPart<CalculationChainPart>();
calcChainPart.CalculationChain = new CalculationChain();
}
// Create a new CalcCell and add it to the chain.
// You might want to check for an existing entry if necessary.
var calcCell = new CalculationCell
{
CellReference = cellReference,
Array = new BooleanValue(true),
NewLevel = new BooleanValue(true),
SheetId = new Int32Value((int)sheetId)
};
calcChainPart.CalculationChain.AppendChild(calcCell);
var childCalcCell = new CalculationCell
{
CellReference = cellReference,
SheetId = new Int32Value((int)sheetId),
InChildChain = new BooleanValue(true)
};
calcChainPart.CalculationChain.AppendChild(childCalcCell);
calcChainPart.CalculationChain.Save();
}
If I use "tar -xf [.xlsx name here]" I can see the underlying XML. And after running my code I can see the changes I've made to the cell, row, calcChain.
//////from sheet2.xml
<x:row r="9" spans="1:20">
<x:c r="A9" s="13" t="s">
<x:v>170</x:v>
</x:c>
<x:c r="B9">
<x:f t="array" aca="1" ref="B9:S9">{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18}</x:f>
</x:c>
</x:row>
/////from calcChain
<x:c r="B9" i="12" l="1" a="1" />
<x:c r="B9" i="12" s="1" />
I have taken a "clean/blank" spreadsheet, added a spill/array (={0,1,2}) to one cell, saved and unzipped using tar. I've looked at how excel seems to handle the array and tried to mimic that with no luck.


sheet.Cells["C1"].LoadFromCollection(products,true, TableStyles.Dark1);. Check the LoadFromCollection examples. MiniExcel and Sylvan.Data.Excel have fewer features but are a lot faster