在OpenXML(excel)中创建自定义列宽
本文关键字:创建 自定义 OpenXML excel | 更新日期: 2023-09-27 18:27:22
我是OpenXML(v.2.5)的新手,我可以创建行和单元格,但我需要能够设置列宽,但由于某些原因,我无法正确设置。
没有此代码:
Columns cols = new Columns();
Column c1 = new Column()
{
CustomWidth = true,
Width = 20
};
cols.Append(c1);
wspart.Worksheet.Append(cols);
该程序运行并生成一个excel文件罚款。
下面的代码符合并运行,但给我留下了一个损坏的excel文档。当我尝试添加列时,我做错了什么?
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
}
上面选择的答案并没有解决我的问题,但我终于解决了。对我来说,问题是当我调用行:Columns columns1=worksheet1.GetFirstChild<Columns>();
时,工作表中当前没有Columns
子级,因此返回的对象为null,并且当我尝试将列附加到Columns
对象时,出现了运行时错误。
问题是Excel非常挑剔。实际sheet.xml文件中的columns元素必须位于sheetdata元素之前。试图将我的自定义列附加到工作表导致文件损坏,因为它将columns元素放在sheetdata元素之后。因为我知道它必须在sheetdata元素之前,所以我必须将它插入工作表的开头,而不是将它附加到工作表中。这是对我有效的代码:
// 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>();
希望这能帮助到别人!!
我认为您遇到的问题是创建一个NEW columns元素并将其附加到现有的工作表内容中。我认为您需要将新列附加到现有的columns元素中。
我创建了一个工作簿,保存它,在一个空列中添加内容,然后用新名称保存工作簿并关闭它。
使用Open XML SDK 2.5 Productivity Tool的"比较"功能,我选择了包含差异的工作表部分,选中它,然后单击"查看包代码"。从原始文件中生成具有新列的更改文件的代码向我显示:
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);
请注意,您似乎还需要指定新列的列范围。
我遇到了与.GetFirstChild<Columns>
为空相同的问题。创建Columns
对象并在索引0处插入(与其他答案一样)使Excel抱怨该文件无效。相反,它似乎希望列定义刚好在SheetData
部分之前:
if (needToInsertColumns)
{
var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
worksheetPart.Worksheet.InsertBefore(columnsList, sheetData);
}