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
}
Tip: A quick way to make the column width fit the size of the text is to double-click the boundary between column headers. To increase the width of multiple consecutive columns, select the columns you want to change. Click the header boundary of any selected column and drag to the right, like in the picture below.
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!!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With