OpenXML sdk 2.5 Excel new column issue

Question

I have part of code that generates Excel document using OpenXML. The problem is to set custom column width. This question is frequently discussed herew on SO, but unfortunately nothing helped to me.

So here the code of creating file:

SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(stream
     , SpreadsheetDocumentType.Workbook);
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
     Id = spreadsheetDocument.WorkbookPart.
     GetIdOfPart(worksheetPart),
     SheetId = 1,
     Name = _sheetName
};
sheets.Append(sheet);
document = spreadsheetDocument;
wbPart = workbookpart

After creating I am trying to set custom column width to the first column

Worksheet ws = ((WorksheetPart)(wbPart.GetPartById(sheet.Id))).Worksheet;
Columns columns = new Columns();                
Column column = new Column() { Min = (UInt32Value)5U, Max = (UInt32Value)5U,
     Width = 16D, CustomWidth = true };
columns.Append(column);
ws.Append(columns);
ws.Save();

Code runs without any exceptions, but result file is broken Here is the structure opf result sheet xml

So the question is, what am I doing wrong ?

UPD. Problem solved, thanks to petelids. I just added :

SheetData sheetData = ws.GetFirstChild();
ws.InsertBefore(columns,sheetData);

And then, only specify the range here Min = (UInt32Value)5U, Max = (UInt32Value)5U

petelids · Accepted Answer

You have the order of your elements incorrect which leads to a corrupted file message. The ECMA-376 standard defines the XML for a Worksheet as

Notice that it's a sequence so the order of the items matters and cols comes before sheetData (lines 5 and 6).

In your case you need to add the Columns directly before SheetData. There's an InsertBefore method on the Worksheet which can be used in place of your call to Append.

OpenXML sdk 2.5 Excel new column issue

Answers (1)

Related Questions