我是OpenXML的新手(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();
Worksheet ws = new Worksheet(new SheetData());
wspart.Worksheet = ws;
//create a new sheets array
Sheets sheets = spreadsheetDoc.WorkbookPart.Workbook.AppendChild(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();
//////////////////////////////////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| ())
{
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
}
|
compman2408.. 17
上面选择的答案并没有解决我的问题,但我终于明白了.我的问题是当我调用该行时:工作表中Columns columns1=worksheet1.GetFirstChild当前没有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(); 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 ();
希望这有助于某人!
上面选择的答案并没有解决我的问题,但我终于明白了.我的问题是当我调用该行时:工作表中Columns columns1=worksheet1.GetFirstChild当前没有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(); 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 ();
希望这有助于某人!
我认为您遇到的问题是在现有工作表内容中创建并附加NEW列元素.我相信您需要将新列附加到现有列元素.
我创建了一个工作簿,保存它,在空列中添加内容,然后以新名称保存工作簿并关闭它.
使用Open XML SDK 2.5 Productivity Tool的"比较"功能,我选择了包含差异的工作表部分,选中它,然后单击"查看包代码".使用原始文件中的新列生成更改文件的代码向我显示:
Columns columns1=worksheet1.GetFirstChild(); //other code here Column column1 = new Column(){ Min = (UInt32Value)5U, Max = (UInt32Value)5U, Width = 16D, CustomWidth = true }; columns1.Append(column1);
请注意,您似乎还需要指定新列的列范围.
京公网安备 11010802040832号 | 京ICP备19059560号-6 