As you may know Office 2007 or later uses “Office Open XML” as the default storage format. Working with Open XML can be annoying. Doing simple things such as setting a cell color is complicated. In this post I give the code for creating a speadsheet with the Open XML SDK 2.0.
For learning the structure of Open XML see “Open XML Explained“. The easiest way to create C# code for Open XML is to use the “Open XML SDK 2.0 Productivity Tool” included in the OpenXMLSDKTool.msi package. With this tool you open a xlsx document and view the structure of the document. It additionally has an option to generate C# code that will create a duplicate of the xlsx document.
To set the background color of a cell I created a xlsx document with the color of one cell set to red. I then opened the document in the Open XML Productivity tool and viewed the generated C# code. The code was very lengthy and I edited it down to the following:
// Creates a SpreadsheetDocument.
public void CreatePackage(string filePath)
{
using(SpreadsheetDocument package = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
{
CreateParts(package);
}
}
// Adds child parts and generates content of the specified part.
private void CreateParts(SpreadsheetDocument document)
{
WorkbookPart workbookPart1 = document.AddWorkbookPart();
GenerateWorkbookPart1Content(workbookPart1);
WorksheetPart worksheetPart3 = workbookPart1.AddNewPart<WorksheetPart>("rId1");
GenerateWorksheetPart3Content(worksheetPart3);
WorkbookStylesPart workbookStylesPart1 = workbookPart1.AddNewPart<WorkbookStylesPart>("rId2");
GenerateWorkbookStylesPart1Content(workbookStylesPart1);
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook();
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet(){ Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
sheets1.Append(sheet1);
workbook1.Append(sheets1);
workbookPart1.Workbook = workbook1;
}
// Generates content of worksheetPart3.
private void GenerateWorksheetPart3Content(WorksheetPart worksheetPart3)
{
Worksheet worksheet3 = new Worksheet();
worksheet3.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
SheetData sheetData3 = new SheetData();
Row row1 = new Row(){};
Cell cell1 = new Cell() { };// StyleIndex = (UInt32Value)1U };
CellValue cellValue1 = new CellValue();
cell1.DataType = CellValues.String;
cellValue1.Text = "A1";
cell1.StyleIndex = 1U;
cell1.Append(cellValue1);
row1.Append(cell1);
sheetData3.Append(row1);
worksheet3.Append(sheetData3);
worksheetPart3.Worksheet = worksheet3;
}
// Generates content of workbookStylesPart1.
private void GenerateWorkbookStylesPart1Content(WorkbookStylesPart workbookStylesPart1)
{
Stylesheet stylesheet1 = new Stylesheet();
Fonts fonts1 = new Fonts() { Count = (UInt32Value)2U };
Font font1 = new Font();
FontSize fontSize1 = new FontSize() { Val = 11D };
Color color1 = new Color() { Theme = (UInt32Value)1U };
FontName fontName1 = new FontName() { Val = "Calibri" };
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };
FontScheme fontScheme1 = new FontScheme() { Val = FontSchemeValues.Minor };
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
Font font2 = new Font();
FontSize fontSize2 = new FontSize() { Val = 11D };
Color color2 = new Color() { Rgb = "FF006100" };
FontName fontName2 = new FontName() { Val = "Calibri" };
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering() { Val = 2 };
FontScheme fontScheme2 = new FontScheme() { Val = FontSchemeValues.Minor };
font2.Append(fontSize2);
font2.Append(color2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontScheme2);
fonts1.Append(font1);
fonts1.Append(font2);
Fills fills1 = new Fills() { Count = (UInt32Value)3U };
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None };
fill1.Append(patternFill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };
fill2.Append(patternFill2);
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill() { PatternType = PatternValues.Solid };
ForegroundColor foregroundColor1 = new ForegroundColor() { Rgb = "FFFF0000" };
patternFill3.Append(foregroundColor1);
fill3.Append(patternFill3);
fills1.Append(fill1);
fills1.Append(fill2);
fills1.Append(fill3);
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();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
borders1.Append(border1);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)2U };
CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, ApplyNumberFormat = false, ApplyBorder = false, ApplyAlignment = false, ApplyProtection = false };
cellStyleFormats1.Append(cellFormat1);
cellStyleFormats1.Append(cellFormat2);
CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)2U };
CellFormat cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U };
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)2U };
CellStyle cellStyle1 = new CellStyle() { Name = "Good", FormatId = (UInt32Value)1U, BuiltinId = (UInt32Value)26U };
CellStyle cellStyle2 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
cellStyles1.Append(cellStyle1);
cellStyles1.Append(cellStyle2);
DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)0U };
TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium9", DefaultPivotStyle = "PivotStyleLight16" };
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
workbookStylesPart1.Stylesheet = stylesheet1;
}
