Creating custom column widths in OpenXML (excel)

Question

I am new to OpenXML (version 2.5) and I can create rows and cells, but I need to set the column width, and for some reason I cannot do it right.

Without this code:

Columns cols = new Columns(); Column c1 = new Column() { CustomWidth = true, Width = 20 }; cols.Append(c1); wspart.Worksheet.Append(cols); 

The program starts and generates an Excel file.

The code below matches and works, but leaves me with a damaged 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

I think the problem you are working with is creating and adding the NEW columns element to existing worksheet content. I believe that you need to add a new column to an existing column element.

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

Using the Open Compare SDK 2.5 Productivity Tool “Compare”, I selected the part of the worksheet that contains the difference, selected it, and then clicked “View Package Code”. The code that generates the modified file with a new column from the source 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 you must also specify the column range of the new column.

Answer 2

The selected answer above did not fix my problem, but I finally figured it out. The problem was that I was calling a row: Columns columns1=worksheet1.GetFirstChild<Columns>(); there were currently no Columns on the worksheet, so the returned object was empty, and I got a runtime error when I tried to add a column to the Columns object.

The problem is that Excel is very picky. The column element in the actual sheet.xml should be before the sheetdata element. An attempt to add my custom columns to the worksheet resulted in a damaged file due to its placement of the column element after the sheetdata element. Since I knew that this should be before the sheetdata element, I had to insert it at the beginning of the worksheet and not add it to the worksheet. Here is 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 3

I tried the following, but could not get a healthy conclusion.

 public void CreateExcelDoc(string fileName) { using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = document.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(); var workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>(); workbookStylesPart.Stylesheet = new Stylesheet(); workbookStylesPart.Stylesheet.Save(); // Make the columns in the worksheet var columns = worksheetPart.Worksheet.GetFirstChild<Columns>(); bool needToInsertColumns = false; if (columns == null) { columns = new Columns(); needToInsertColumns = true; } Column column1 = new Column() { CustomWidth = true, Width = 5 }; columns.Append(column1); Column column2 = new Column() { CustomWidth = true, Width = 10 }; columns.Append(column2); Column column3 = new Column() { CustomWidth = true, Width = 20 }; columns.Append(column3); Column column4 = new Column() { CustomWidth = true, Width = 20 }; columns.Append(column4); // Insert the columns into the Worksheet if (needToInsertColumns) worksheetPart.Worksheet.InsertAt(columns, 0); SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData()); Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets()); Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Employees" }; sheets.Append(sheet); List<Employee> employees = Employees.EmployeeList; // Constructing header Row row = new Row(); row.Append( ConstructCell("Id", CellValues.String), ConstructCell("Name", CellValues.String), ConstructCell("Birth Date", CellValues.String), ConstructCell("Salary", CellValues.String)); // Insert the header row to the Sheet Data sheetData.AppendChild(row); workbookPart.Workbook.Save(); worksheetPart.Worksheet.Save(); document.Save(); } }