OpenXML自定义列宽不起作用

问题描述

我正在尝试更改某些excel列的宽度.我已经尝试了所有方法，但是我不断得到空的excel文档，没有此添加，我的excel文件也可以正常工作:

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);

但是，只要我像该用户建议的那样添加 ，我就必须修复我的excel文件，它将是空的.我收到此错误:已替换的部分:/xl/worksheets/sheet.xml发生XML错误的部分.加载错误.第1行，第0列..并且已修复"的文件为空.

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.

我的代码与 MSDN示例

我尝试了许多不同的方式来更改列宽，所以我认为问题出在其他地方.我似乎无法找出问题所在.

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.

完整代码

_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>();

推荐答案

元素的顺序略有不同. Columns应该放在SheetData之前，而不是之后.工作表的XML模式的相关部分是:

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"/>

要修复代码，您可以从new Worksheet行中删除SheetData并在Columns之后添加SheetData:

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());

或者您可以按原样保留new Worksheet代码，并使用Columns将它们插入SheetData之前的rel ="noreferrer"> InsertBefore 方法:

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);

要注意的另一件事-您应该将SpreadsheetDocument.Create包装在using语句中.这将清理所有资源并在更改结束时为您保存文件，即:

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...
}

这篇关于OpenXML自定义列宽不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！