Add Columns to Existing Excel 2007 workbook using Open Xml

.net, c#, openxml, openxml-sdk

Solution

This first part of answer deals about how to set columns width (based on the initial sample code, I was thinking that you wanted only define the width of the columns).

First, it seems you misunderstood what are Min and Max properties of the `Column` object. They represent respectively First and Last column affected by this 'column info' record. So if you have a set of contiguous columns with the same width, you can set that width using one `Column` class. In your snippet you define 3 times the width of the same column (Index 1).

Then, you presume `Columns` collection doesn't exist yet...

- And finally, the main point is that if the `Columns` collection is appended after `SheetData`, Excel will throw error.

Final code that work for me (Open XML SDK 2.0)

using (SpreadsheetDocument document = SpreadsheetDocument.Open(outputPath, true)) {
    Sheet sheet2 = document.WorkbookPart.Workbook.Descendants<Sheet>().Single(s => s.Name == "Your sheet name");

    Worksheet workSheet2 = ((WorksheetPart)document.WorkbookPart.GetPartById(sheet2.Id)).Worksheet;

    // Check if the column collection exists
    Columns cs = workSheet2.Elements<Columns>().FirstOrDefault();

    if ((cs == null)) {
        // If Columns appended to worksheet after sheetdata Excel will throw an error.
        SheetData sd = workSheet2.Elements<SheetData>().FirstOrDefault();
        if ((sd != null)) {
            cs = workSheet2.InsertBefore(new Columns(), sd);
        } else {
            cs = new Columns();
            workSheet2.Append(cs);
        }
    }

    //create a column object to define the width of columns 1 to 3  
    Column c = new Column {
        Min = (UInt32Value)1U,
        Max = (UInt32Value)3U,
        Width = 44.33203125,
        CustomWidth = true
    };
    cs.Append(c);

}

I'm still confused on how to perform column insert. Says I have columns A, B and C, I want to insert three columns between B and C, ending up with columns A,B,C,D,E,F. How can i achieve it?

The `Columns` object in OpenXml SDK is here to store styles and width informations for the columns. Inserting a `Column` in the collection won't "insert" a column in the sheet.

"Inserting" a column like you mean is a very large and complex task with OpenXmlSDK.

From my understanding of the problem, it means you will have to find all cells and shift them by changing their reference (ex. a cell with ref "B1" would become "F1" after inserting 3 columns, etc ...). And it means you will have to change a lot of other things (reference of cell in formulas for example).

This kind of task could be easily done with Office.Interop or probably with libraries like EEPlus or ClosedXml.

Problem

I have a predefined Excel workbook with all sheets in place and I need to write content to it. I succesfully write to cells. The problem is in a particular worksheet that i need to add three columns to it. In the code bellow, first i'm grabbing the `Worksheet` and then i proceed to add columns. This code runs fine, i mean, no exception is thrown, but then I get an error when I try to open the Excel file, stating that there are some content that cannot be read and all the content of this particular worksheet is cleared. I know that the problem is with this operation because if I comment out those lines that add columns, the workbook opens just fine with all the cells values I write from code in place. This is the relevant code, for testing purpose I'm trying to add 3 columns: ``` using (SpreadsheetDocument document = SpreadsheetDocument.Open(outputPath, true)){ Sheet sheet2 = document.WorkbookPart.Workbook.Descendants<Sheet>().Single( s => s.Name == "Miscellaneous Credit" ); Worksheet workSheet2 = ( (WorksheetPart)document.WorkbookPart.GetPartById( sheet2.Id ) ).Worksheet; Columns cs = new Columns(); for ( var y = 1; y <= 3; y++ ) { Column c = new Column() { Min = (UInt32Value)1U, Max = (UInt32Value)1U, Width = 44.33203125D, CustomWidth = true }; cs.Append( c ); } workSheet2.Append( cs ); } ``` EDIT : As per Chris's explanation about columns's concept ``` using (SpreadsheetDocument document = SpreadsheetDocument.Open(outputPath, true)){ Sheet sheet2 = document.WorkbookPart.Workbook.Descendants<Sheet>().Single( s => s.Name == "Miscellaneous Credit" ); Worksheet workSheet2 = ( (WorksheetPart)document.WorkbookPart.GetPartById( sheet2.Id ) ).Worksheet; // Check if the column collection exists Columns cs = workSheet2.Elements<Columns>().FirstOrDefault(); if ( ( cs == null ) ) { // If Columns appended to worksheet after sheetdata Excel will throw an error. SheetData sd = workSheet2.Elements<SheetData>().FirstOrDefault(); if ( ( sd != null ) ) { cs = workSheet2.InsertBefore( new Columns(), sd ); } else { cs = new Columns(); workSheet2.Append( cs ); } } //create a column object to define the width of columns 1 to 3 Column c = new Column { Min = (UInt32Value)1U, Max = (UInt32Value)3U, Width = 44.33203125, CustomWidth = true }; cs.Append( c ); } ```

Original source