具有单元格边框的OpenXML SDK [英] OpenXML SDK having borders for cell

问题描述

我有以下的code，增加了与价值观和数据类型的细胞中的OpenXML SDK该单元格：

I have the following code that adds a cell with values and datatype for that cell in 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);

在此先感谢

推荐答案

我建议安装的的Open XML 2.0生产力工具。然后创建一个包含你的愿望边框和颜色的空白Excel文档。打开生产力工具，文件，然后单击反映code。然后，它会给你需要产生边框和背景色的C＃code。在code是用于发布一个有点冗长，但如果你遵循这些步骤，你应该能够使用它。

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.

边框和填充属性存储在一个单独的部分叫做 WookbookStylesPart 。这部分是在那里你会插入要应用到单元格的工作簿中的边框，填充，字体等类型。这些属性存储在您访问通过索引插入式的数组类型结构。既然你都可以应用细胞多个款式， CellFormat 对象是用于存储所有的风格各异的指数。一旦你有一个 CellFormat 的细胞，它的索引需要对通过 StlyeIndex 属性的实际单元格引用。这是细胞是如何知道如何运用自身的各种风格。

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 CellFormat object is where all the indices for the various styles are stored. Once you have a CellFormat for a cell, its index needs to be referenced on the actual cell via the StlyeIndex property. That is how the cell knows how to apply the various styles on itself.

下面是code创建边界：

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

下面是code以添加填充：

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

您将需要此code插入边框和填充到样式的一部分：

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++;
}

您需要先得到你想要的填充和边框添加到该cellAddress是在B2的形式单元格的引用：

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":

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

然后，一旦你得到你的手机，你需要获得 CellFormat 属于该小区，并添加一个新的 CellFormat ：

Then once you get your cell you need to get the CellFormat that belong to that cell and also to add a new 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++;
}

一旦你的 CellFormat 现在你可以改变填充和边框属性。一旦这些被改变，你需要插入新的 CellFormat 键，然后该指数 CellFormat 指向 StyleIndex 的细胞。这是细胞就会知道如何应用到自己什么风格。

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

 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);
 }

这篇关于具有单元格边框的OpenXML SDK的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！