I recommend installing the Open XML 2.5 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.
**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 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.
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”:
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 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++;
}
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);
}