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

在OpenXML(excel)中创建自定义列宽

上面选择的答案并没有解决我的问题,但我终于解决了。对我来说,问题是当我调用行: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);
}