C# 具有单元格边框的 OpenXML SDK

OpenXML SDK having borders for cell

提问by Nate Pet

I have the following code that adds a cell with values and data-type for that cell in OpenXML SDK:

我有以下代码在 OpenXML SDK 中为该单元格添加一个具有值和数据类型的单元格：

Cell cell = InsertCellInWorksheet(column, row, worksheetPart);              
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);

For this cell, how do I add a border on each side? I also like to add a background color on the cell as well.

对于此单元格，如何在每一侧添加边框？我也喜欢在单元格上添加背景颜色。

I have the following but not sure how to add the border to the cell:

我有以下内容但不确定如何将边框添加到单元格：

Borders borders1 = new Borders() { Count = (UInt32Value)1U };

Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();

border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);

borders1.Append(border1);

Thanks in advance

提前致谢

采纳答案by amurra

I recommend installing the Open XML 2.0 productivity tool. Then create a blank Excel document that contains the border and color you desire. Open that file in the productivity tool and then click reflect code. It will then give you the C# code that is required to produce that border and background color. The code is a bit lengthy for posting, but if you follow those steps you should be able to use it.

我建议安装Open XML 2.0 生产力工具。然后创建一个空白的 Excel 文档，其中包含您想要的边框和颜色。在生产力工具中打开该文件，然后单击反映代码。然后它将为您提供生成该边框和背景颜色所需的 C# 代码。发布的代码有点长，但是如果您按照这些步骤操作，您应该可以使用它。

**Edit**

**编辑**

The border and fill properties are stored in a separate part called the WookbookStylesPart. This part is where you will insert the type of border, fill, font, etc that you want applied to a cell within a workbook. These properties are stored in an array type structure where you access the style you inserted via an index. Since you can have multiple styles applied to a cell, a CellFormatobject is where all the indices for the various styles are stored. Once you have a CellFormatfor a cell, its index needs to be referenced on the actual cell via the StlyeIndexproperty. That is how the cell knows how to apply the various styles on itself.

边框和填充属性存储在名为WookbookStylesPart. 在此部分中，您将插入要应用于工作簿中单元格的边框、填充、字体等类型。这些属性存储在数组类型结构中，您可以在其中访问通过索引插入的样式。由于您可以将多种样式应用于一个单元格，因此CellFormat对象是存储各种样式的所有索引的地方。一旦你有一个CellFormat单元格，它的索引需要通过StlyeIndex属性在实际单元格上引用。这就是单元格知道如何将各种样式应用于自身的方式。

Here is the code to create the border:

这是创建边框的代码：

public Border GenerateBorder()
{ 
    Border border2 = new Border();

    LeftBorder leftBorder2 = new LeftBorder(){ Style = BorderStyleValues.Thin };
    Color color1 = new Color(){ Indexed = (UInt32Value)64U };

    leftBorder2.Append(color1);

    RightBorder rightBorder2 = new RightBorder(){ Style = BorderStyleValues.Thin };
    Color color2 = new Color(){ Indexed = (UInt32Value)64U };

    rightBorder2.Append(color2);

    TopBorder topBorder2 = new TopBorder(){ Style = BorderStyleValues.Thin };
    Color color3 = new Color(){ Indexed = (UInt32Value)64U };

    topBorder2.Append(color3);

    BottomBorder bottomBorder2 = new BottomBorder(){ Style = BorderStyleValues.Thin };
    Color color4 = new Color(){ Indexed = (UInt32Value)64U };

    bottomBorder2.Append(color4);
    DiagonalBorder diagonalBorder2 = new DiagonalBorder();

    border2.Append(leftBorder2);
    border2.Append(rightBorder2);
    border2.Append(topBorder2);
    border2.Append(bottomBorder2);
    border2.Append(diagonalBorder2);

    return borders2;
}

Here is the code to add a fill:

这是添加填充的代码：

public Fill GenerateFill()
{
    Fill fill = new Fill();

    PatternFill patternFill = new PatternFill(){ PatternType = PatternValues.Solid };
    ForegroundColor foregroundColor1 = new ForegroundColor(){ Rgb = "FFFFFF00" };
    BackgroundColor backgroundColor1 = new BackgroundColor(){ Indexed = (UInt32Value)64U };

    patternFill.Append(foregroundColor1);
    patternFill.Append(backgroundColor1);

    fill.Append(patternFill);

    return fill;
}

You will need this code to insert the border and fill into the style part:

您将需要此代码来插入边框并填充样式部分：

public uint InsertBorder(WorkbookPart workbookPart, Border border)
{
    Borders borders = workbookPart.WorkbookStylesPart.Stylesheet.Elements<Borders>().First();
    borders.Append(border);
    return (uint)borders.Count++;
}

public uint InsertFill(WorkbookPart workbookPart, Fill fill)
{
    Fills fills = workbookPart.WorkbookStylesPart.Stylesheet.Elements<Fills>().First();
    fills.Append(fill);
    return (uint)fills.Count++;
}

You will need to first get a reference to the cell you want to add the fill and border to where the cellAddress is in the form of "B2":

您需要首先获得对要添加填充和边框的单元格的引用，其中 cellAddress 的格式为“B2”：

public Cell GetCell(WorksheetPart workSheetPart, string cellAddress)
{
    return workSheetPart.Worksheet.Descendants<Cell>()
                                .SingleOrDefault(c => cellAddress.Equals(c.CellReference));
}

Then once you get your cell you need to get the CellFormatthat belong to that cell and also to add a new CellFormat:

然后一旦你得到你的单元格，你需要得到CellFormat属于那个单元格的并且添加一个新的CellFormat：

public CellFormat GetCellFormat(WorkbookPart workbookPart, uint styleIndex)
{
    return workbookPart.WorkbookStylesPart.Stylesheet.Elements<CellFormats>().First().Elements<CellFormat>().ElementAt((int)styleIndex);
}

public uint InsertCellFormat(WorkbookPart workbookPart, CellFormat cellFormat)
{
    CellFormats cellFormats = workbookPart.WorkbookStylesPart.Stylesheet.Elements<CellFormats>().First();
    cellFormats.Append(cellFormat);
    return (uint)cellFormats.Count++;
}

Once you have the CellFormatyou can now alter the fill and border properties. Once those are altered you need to insert the new CellFormatand then point the index of that CellFormatto the StyleIndexon the cell. This is how the cell will know what styles to apply to itself.

拥有后，CellFormat您现在可以更改填充和边框属性。一旦这些被改变，你需要插入新的CellFormat，然后的是，指数点CellFormat到StyleIndex的细胞。这就是单元格如何知道要应用到自己的样式的方式。

 public void SetBorderAndFill(WorkbookPart workbookPart, WorksheetPart workSheetPart)
 {
      Cell cell = GetCell(workSheetPart, "B2");

      CellFormat cellFormat = cell.StyleIndex != null ? GetCellFormat(workbookPart, cell.StyleIndex).CloneNode(true) as CellFormat : new CellFormat();
      cellFormat.FillId = InsertFill(workbookPart, GenerateFill());
      cellFormat.BorderId = InsertBorder(workbookPart, GenerateBorder());    

      cell.StyleIndex = InsertCellFormat(workbookPart, cellFormat);
 }

回答by saarp

The SpreadsheetDocumentis structured as collection of WorkbookParts. One of these, the WorkbookStylesPart, contains all the styles used in the document. The WorkbookPartcontains your Worksheets. In order to apply a style to a cell or range of cells, you need to set the StyleIndexproperty to the corresponding style in the WorkbookStylesPart.

该SpreadsheetDocument的结构为收集WorkbookParts。其中之一，WorkbookStylesPart，包含文档中使用的所有样式。该WorkbookPart包含您的工作表。为了将样式应用于单元格或单元格范围，您需要将StyleIndex属性设置为WorkbookStylesPart中的相应样式。

This answer should help get you started: https://stackoverflow.com/a/11118442/741326

这个答案应该可以帮助您入门：https: //stackoverflow.com/a/11118442/741326