OpenXML SDK with cell borders

Question

I have the following code that adds a cell with values ​​and data type for this cell in the 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 to add a border on each side? I would also like to add a background color to the cell.

I have the following, but not sure how to add a 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); 

Thank you in advance

Answer 1

I recommend installing the Open XML 2.0 productivity tool . Then create an empty Excel document that contains the border and color you need. Open this file in the performance tool, and then click the Flip Code button. Then it will provide you with the C # code needed to create this border and background color. The code is long enough to publish, but if you follow these steps, you can use it.

** Change **

The border and fill properties are stored in a separate part called the WookbookStylesPart . In this part you will enter the type of border, fill, font, etc. that you want to apply to the cell in the book. These properties are stored in an array type structure, where you access the style that you entered with the index. Since you can use multiple styles for a cell, the CellFormat object is the place where all indexes for different styles are stored. When you have a CellFormat cell, its index must be specified in the actual cell through the StlyeIndex property. This is how a cell knows how to apply different styles on itself.

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 the 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 a border and fill part of the style:

 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 first need to get a link to the cell that you want to add a fill and a border, where the address of 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)); } 

Then, as soon as you get your cell, you need to get the CellFormat belonging to that cell, and also 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++; } 

Once you have CellFormat , you can now change the fill and border properties. After the change, you need to insert a new CellFormat , and then point the index of this CellFormat to StyleIndex on the cell. This is how the cell will know which 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); } 

Answer 2

It’s much easier to just call

 ws.Cells[row, col].Style.Border.BorderAround(ExcelBorderStyle.Thin); 

Answer 3

SpreadsheetDocument is structured as a collection of WorkbookParts . One of them, WorkbookStylesPart , contains all the styles used in the document. WorkbookPart contains Worksheets . To apply a style to a cell or range of cells, you need to set the StyleIndex property to the appropriate style in WorkbookStylesPart.

This answer will help you get started: stack overflow