Creating custom column widths in OpenXML (excel)

Question

I am new to OpenXML (v. 2.5), and I can create rows and cells, but I need to be able to set the column width and I can not do that correctly for some reason.

Without this code:

        Columns cols = new Columns();

        Column c1 = new Column()
        {
            CustomWidth = true,
            Width = 20
        };

        cols.Append(c1);
        wspart.Worksheet.Append(cols);

The program runs and generates an excel file fine.

The code below complies and runs, but leaves me with a corrupt excel document. What am I doing wrong when I try to add columns?

    public static void createExcel() //TODO change to private
    {
        //create the spreadsheet document with openxml See https://msdn.microsoft.com/en-us/library/office/ff478153.aspx
        SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Create(@"C:\Users\Reid\Documents\BLA\test.xlsx", SpreadsheetDocumentType.Workbook); //TODO change path

        //add a workbook part
        WorkbookPart wbpart = spreadsheetDoc.AddWorkbookPart();
        wbpart.Workbook = new Workbook();

        //add a worksheet part
        WorksheetPart wspart = wbpart.AddNewPart<WorksheetPart>();
        Worksheet ws = new Worksheet(new SheetData());
        wspart.Worksheet = ws;

        //create a new sheets array
        Sheets sheets = spreadsheetDoc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

        //create a new sheet
        Sheet sheet = new Sheet()
        {
            Id = spreadsheetDoc.WorkbookPart.GetIdOfPart(wspart),
            SheetId = 1,
            Name = "mySheet" //TODO change name
        };

        //add the sheet to the workbook sheet aray
        sheets.Append(sheet);

        SheetData shData = wspart.Worksheet.GetFirstChild<SheetData>();

        //////////////////////////////////row and col widths//////////////////////
        Columns cols = new Columns();

        Column c1 = new Column()
        {
            CustomWidth = true,
            Width = 20
        };

        cols.Append(c1);
        wspart.Worksheet.Append(cols);

        //create the first row
        Row r1 = new Row
        {
            RowIndex = 1,
            CustomHeight = true,
            Height = 71.25 //change height based on info
        };
        shData.Append(r1);
  ////////////////////////cell data/////////////////////////////////

        // In the new row, find the column location to insert a cell in A1.
        Cell refCell = null;
        foreach (Cell cell in r1.Elements<Cell>())
        {
            if (string.Compare(cell.CellReference.Value, "A1", true) > 0)
            {
                refCell = cell;
                break;
            }
        }
        // Add the cell to the cell table at A1.
        Cell newCell = new Cell() {
            CellReference = "A1",
        };
        r1.InsertBefore(newCell, refCell);

        // Set the cell value to be a numeric value of 100.
        newCell.CellValue = new CellValue("100");


        //TODO add in standard things (text that is always the same, headers, logos, etc.)

        //TODO add in dynamic text

        //TODO create and add in barcodes

        //Save and close the document
        wbpart.Workbook.Save();
        spreadsheetDoc.Close();

        //TODO send document to database
    }

Answer 1

The selected answer above didn't fix my issue, but I finally figured it out. The issue for me was when I called the line: Columns columns1=worksheet1.GetFirstChild<Columns>(); there was currently no Columns children in the worksheet so the object returned was null and I got a runtime error when I tried appending a column to the Columns object.

The issue is that Excel is extremely picky. The columns element in the actual sheet.xml file has to be before the sheetdata element. Trying to append my custom columns to the worksheet resulted in a corrupted file due to it placing the columns element after the sheetdata element. Since I knew it had to be before the sheetdata element I had to insert it into the beginning of the worksheet and not append it to the worksheet. Here's the code that worked for me:

// Save the stylesheet formats
stylesPart.Stylesheet.Save();

// Create custom widths for columns
Columns lstColumns = worksheetPart.Worksheet.GetFirstChild<Columns>();
Boolean needToInsertColumns = false;
if (lstColumns == null)
{
    lstColumns = new Columns();
    needToInsertColumns = true;
}
// Min = 1, Max = 1 ==> Apply this to column 1 (A)
// Min = 2, Max = 2 ==> Apply this to column 2 (B)
// Width = 25 ==> Set the width to 25
// CustomWidth = true ==> Tell Excel to use the custom width
lstColumns.Append(new Column() { Min = 1, Max = 1, Width = 25, CustomWidth = true });
lstColumns.Append(new Column() { Min = 2, Max = 2, Width = 9, CustomWidth = true });
lstColumns.Append(new Column() { Min = 3, Max = 3, Width = 9, CustomWidth = true });
lstColumns.Append(new Column() { Min = 4, Max = 4, Width = 9, CustomWidth = true });
lstColumns.Append(new Column() { Min = 5, Max = 5, Width = 13, CustomWidth = true });
lstColumns.Append(new Column() { Min = 6, Max = 6, Width = 17, CustomWidth = true });
lstColumns.Append(new Column() { Min = 7, Max = 7, Width = 12, CustomWidth = true });
// Only insert the columns if we had to create a new columns element
if (needToInsertColumns)
    worksheetPart.Worksheet.InsertAt(lstColumns, 0);

// Get the sheetData cells
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

Hope this helps someone!!

Answer 2

I think the problem you're running into is creating and appending a NEW columns element to the existing worksheet content. I believe you need to append the new column to an existing columns element.

I created a workbook, saved it, added content in an empty column, then saved the workbook under a new name and closed it.

Using the Open XML SDK 2.5 Productivity Tool's "Compare" feature I selected the worksheet part containing the difference, selected it, then clicked "View Package Code". The code that generates the changed file with the new column from the original file shows me:

Columns columns1=worksheet1.GetFirstChild<Columns>();
//other code here
Column column1 = new Column(){ Min = (UInt32Value)5U, Max = (UInt32Value)5U, Width = 16D, CustomWidth = true };
columns1.Append(column1);

Note that it appears you're also expected to specify the column range of the new column.

Answer 3

I had the same problem that .GetFirstChild<Columns> was null. Creating the Columns object and inserting at index 0 (like the other answer) made Excel complain that the file was invalid. Instead, it seems to want the columns definitions just before the SheetData section:

if (needToInsertColumns)
{
    var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
    worksheetPart.Worksheet.InsertBefore(columnsList, sheetData);
}