0

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.

excel formula is wrapped in an extra set of curly braces

error message when I attempt to delete the cell or edit its contents

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.

5
  • You may consider using the OpenXml Productivity Tool as described in this post. The post is for .docx, but the process will be very similar for .xlsx. Commented Aug 22 at 19:18
  • If you're using one of the newer versions of OpenXml the Productivity Tool may not work. If so, use Excel to create the file as you want it. Then create a copy of the file and change the extension from .xlsx to .zip and then view the contents. If you need to modify the .xlsx file, make another copy and modify the file as desired using Excel. Repeat the process previously deseibed to view the XML. Compare the two files and note the differences. Commented Aug 22 at 19:26
  • thank you for the suggestion @Itallmakescents. I did try comparing a before and after before posting but did not see anything unfortunately. Commented Aug 22 at 19:33
  • Instead of working with low-level OpenXML, try one of the many high-level libraries like EPPlus, ClosedXML, MiniExcel, Sylvan.Data.Excel. Eg in EPPlus you can load data from any collection (or DataTable or IDataReader) into a sheet with headers and styling with 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 Commented Aug 22 at 20:11
  • I will have look into your suggestion some more. Currently the reason for me not using one of the high level libraries is that I am stuck with an older version of OpenXML. My "application" is a plugin for AutoCAD which comes with OpenXML, but an older version so I cant load a newer one Commented Aug 22 at 21:28

1 Answer 1

0

I tried modifying the span of the row containing the cell.
I tried adding the spill cell the the calcChain at the end, then at the top.
I tried writing creating the Cells and Values for each cell that are part of the spill.

But what it all came down to, was this

cell.CellMetaIndex = 1;

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.