在OpenXML（excel）中创建自定义列宽 [英] Creating custom column widths in OpenXML (excel)
问题描述
我是OpenXML（v。2.5）的新手，我可以创建行和单元格，但是我需要设置列宽，因为某些原因我无法正确执行。
没有这段代码：
列cols = new Columns（） ;
列c1 = new Column（）
{
CustomWidth = true，
Width = 20
};
cols.Append（c1）;
wspart.Worksheet.Append（cols）;
程序运行并生成Excel文件。
下面的代码符合并运行，但给我一个损坏的excel文档。当我尝试添加列时，我做错了什么？
public static void createExcel（）// TODO change to private
{
//使用openxml创建电子表格文档请参阅https://msdn.microsoft.com/en-us/library/office/ff478153.aspx
SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Create（@ C：\Users\Reid\Documents\BLA\test.xlsx，SpreadsheetDocumentType.Workbook）; // TODO更改路径
//添加工作簿部分
WorkbookPart wbpart = spreadsheetDoc.AddWorkbookPart（）;
wbpart.Workbook = new Workbook（）;
//添加工作表部分
WorksheetPart wspart = wbpart.AddNewPart< WorksheetPart>（）;
工作表ws = new Worksheet（新SheetData（））;
wspart.Worksheet = ws;
//创建一个新的工作表数组
工作表= spreadsheetDoc.WorkbookPart.Workbook.AppendChild< Sheets>（new Sheets（））;
//创建新工作表
工作表=新Sheet（）
{
Id = spreadsheetDoc.WorkbookPart.GetIdOfPart（wspart），
SheetId = 1，
Name =mySheet// TODO更改名称
};
//将工作表添加到工作簿中aray
sheets.Append（sheet）;
SheetData shData = wspart.Worksheet.GetFirstChild< SheetData>（）;
//////////////////////////////行和col宽度//// ////////////////
列cols = new Columns（）;
列c1 = new Column（）
{
CustomWidth = true，
Width = 20
};
cols.Append（c1）;
wspart.Worksheet.Append（cols）;
//创建第一行
行r1 =新行
{
RowIndex = 1，
CustomHeight = true，
Height = 71.25 //根据info
}改变高度;
shData.Append（r1）;
////////////////////单元格数据//////////////////// /////////////
//在新行中，找到在A1中插入单元格的列位置。
单元格RefCell = null;
foreach（r1.Elements< Cell>（）中的单元格单元）
{
if（string.Compare（cell.CellReference.Value，A1，true）> 0）
{
refCell = cell;
break;
}
}
//将单元格添加到A1的单元格表。
Cell newCell = new Cell（）{
CellReference =A1，
};
r1.InsertBefore（newCell，refCell）;
//将单元格值设置为数值为100.
newCell.CellValue = new CellValue（100）;
// TODO添加标准的东西（文字总是相同，标题，标志等）
// TODO添加动态文本
// TODO在条形码中创建和添加
//保存并关闭文档
wbpart.Workbook.Save（）;
spreadsheetDoc.Close（）;
// TODO将文档发送到数据库
}
我认为你正在遇到的问题是创建并附加一个新的列元素到现有的工作表内容。我相信您需要将新列附加到现有的列元素。
我创建了一个工作簿，保存了它，在空列中添加了内容，然后将工作簿保存在一个新的名称并关闭它。
使用Open XML SDK 2.5生产力工具的比较功能我选择了包含差异的工作表部分，选中它，然后点击查看包代码。使用原始文件中的新列生成更改后的文件的代码显示：
列column1 = worksheet1.GetFirstChild< Columns> ;（）;
//其他代码
列column1 = new Column（）{Min =（UInt32Value）5U，Max =（UInt32Value）5U，Width = 16D，CustomWidth = true};
columns1.Append（column1）;
请注意，您似乎也希望指定新列的列范围。 p>
I am new to OpenXML (v. 2.5), and I can create rows and cells, but I need to be able to set the column width and I can not do that correctly for some reason.
Without this code:
Columns cols = new Columns();
Column c1 = new Column()
{
CustomWidth = true,
Width = 20
};
cols.Append(c1);
wspart.Worksheet.Append(cols);
The program runs and generates an excel file fine.
The code below complies and runs, but leaves me with a corrupt excel document. What am I doing wrong when I try to add columns?
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
}
I think the problem you're running into is creating and appending a NEW columns element to the existing worksheet content. I believe you need to append the new column to an existing columns element.
I created a workbook, saved it, added content in an empty column, then saved the workbook under a new name and closed it.
Using the Open XML SDK 2.5 Productivity Tool's "Compare" feature I selected the worksheet part containing the difference, selected it, then clicked "View Package Code". The code that generates the changed file with the new column from the original file shows me:
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);
Note that it appears you're also expected to specify the column range of the new column.
这篇关于在OpenXML（excel）中创建自定义列宽的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
