CODEWITHSUNDEEP
c#openxml
Litrico
Litrico

Reputation: 107

OpenXML Custom column width not working

I'm trying to change the width of some excel columns. I've tried everything but I keep getting empty excel documents, my excel file works fine without this addition:

Columns columns = new Columns();

columns.Append(new Column() { Min = 1, Max = 3, Width = 20, CustomWidth = true });
columns.Append(new Column() { Min = 4, Max = 4, Width = 30, CustomWidth = true });

wsPart.Worksheet.Append(columns);

But as soon as I add this like this user suggested, I have to repair my excel file, and it will be empty. I get this error: "Replaced Part: /xl/worksheets/sheet.xml part with XML error. Load error. Line 1, column 0. ". And the 'repaired' file is empty.

My code is basically the same as that from the MSDN example

I've tried many different ways of changing the column width, so I think the problem lies elsewhere. I can't seem to figure out what's wrong.

EDIT: Full code

_document = SpreadsheetDocument.Create(newFileName, SpreadsheetDocumentType.Workbook);

_workbookPart = _document.AddWorkbookPart();
_workbookPart.Workbook = new Workbook();

WorksheetPart worksheetPart = _workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetViews(new SheetView { WorkbookViewId = 0, ShowGridLines = new BooleanValue(false) }), new SheetData());

_document.WorkbookPart.Workbook.AppendChild(new Sheets());

Sheet sheet = new Sheet { Id = _document.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" };
_workbookPart.Workbook.Sheets.Append(sheet);


WorkbookStylesPart stylesPart = _workbookPart.AddNewPart<WorkbookStylesPart>();
stylesPart.Stylesheet = CreateStylesheet();
stylesPart.Stylesheet.Save();

Columns columns = new Columns();

columns.Append(new Column() { Min = 1, Max = 3, Width = 20, CustomWidth = true });
columns.Append(new Column() { Min = 4, Max = 4, Width = 30, CustomWidth = true });

worksheetPart.Worksheet.Append(columns);

_sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

Upvotes: 1

Views: 6790

Answers (1)

petelids
petelids

Reputation: 12815

The order of your elements is slightly out. The Columns should be placed before the SheetData rather than after. The relevant part of the XML schema for a Worksheet is:

<xsd:complexType name="CT_Worksheet">
    <xsd:sequence>
    <xsd:element name="sheetPr" type="CT_SheetPr" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="dimension" type="CT_SheetDimension" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="sheetViews" type="CT_SheetViews" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="sheetFormatPr" type="CT_SheetFormatPr" minOccurs="0" maxOccurs="1"/>
    <xsd:element name="cols" type="CT_Cols" minOccurs="0" maxOccurs="unbounded"/>
    <xsd:element name="sheetData" type="CT_SheetData" minOccurs="1" maxOccurs="1"/>

To fix your code you could either remove the SheetData from your new Worksheet line and add the SheetData after the Columns:

worksheetPart.Worksheet = new Worksheet(new SheetViews(new SheetView { WorkbookViewId = 0, ShowGridLines = new BooleanValue(false) }));

//....code omitted for brevity

Columns columns = new Columns();

columns.Append(new Column() { Min = 1, Max = 3, Width = 20, CustomWidth = true });
columns.Append(new Column() { Min = 4, Max = 4, Width = 30, CustomWidth = true });

worksheetPart.Worksheet.Append(columns);
worksheetPart.Worksheet.Append(new SheetData());

OR you could leave the new Worksheet code as-is and use the InsertBefore method when adding the Columns to insert them before the SheetData:

Columns columns = new Columns();

columns.Append(new Column() { Min = 1, Max = 3, Width = 20, CustomWidth = true });
columns.Append(new Column() { Min = 4, Max = 4, Width = 30, CustomWidth = true });

var sheetdata = worksheetPart.Worksheet.GetFirstChild<SheetData>();
worksheetPart.Worksheet.InsertBefore(columns, sheetdata);

One other thing to note - you ought to wrap the SpreadsheetDocument.Create in a using statement. This will clean up any resources and save the file for you at the end of your changes i.e.:

using (var _document = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
{
    //all your OpenXml code here...
}

Upvotes: 6

Related Questions