private static Stylesheet CreateStylesheet()
{
Stylesheet stylesheet1 = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
Fonts fonts1 = new Fonts()
{
Count = (UInt32Value)1U, KnownFonts = true
};
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);
fonts1.Append(font1);
Fills fills1 = new Fills()
{
Count = (UInt32Value)5U
};
// FillId = 0
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill()
{
PatternType = PatternValues.None
};
fill1.Append(patternFill1);
// FillId = 1
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill()
{
PatternType = PatternValues.Gray125
};
fill2.Append(patternFill2);
// FillId = 2,RED
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor1 = new ForegroundColor()
{
Rgb = "FFFF0000"
};
BackgroundColor backgroundColor1 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill3.Append(foregroundColor1);
patternFill3.Append(backgroundColor1);
fill3.Append(patternFill3);
// FillId = 3,BLUE
Fill fill4 = new Fill();
PatternFill patternFill4 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor2 = new ForegroundColor()
{
Rgb = "FF0070C0"
};
BackgroundColor backgroundColor2 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill4.Append(foregroundColor2);
patternFill4.Append(backgroundColor2);
fill4.Append(patternFill4);
// FillId = 4,YELLO
Fill fill5 = new Fill();
PatternFill patternFill5 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor3 = new ForegroundColor()
{
Rgb = "FFFFFF00"
};
BackgroundColor backgroundColor3 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill5.Append(foregroundColor3);
patternFill5.Append(backgroundColor3);
fill5.Append(patternFill5);
fills1.Append(fill1);
fills1.Append(fill2);
fills1.Append(fill3);
fills1.Append(fill4);
fills1.Append(fill5);
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)1U
};
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U
};
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats()
{
Count = (UInt32Value)4U
};
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
CellFormat cellFormat3 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true
};
CellFormat cellFormat4 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true
};
CellFormat cellFormat5 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)4U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true
};
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
cellFormats1.Append(cellFormat5);
CellStyles cellStyles1 = new CellStyles()
{
Count = (UInt32Value)1U
};
CellStyle cellStyle1 = new CellStyle()
{
Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U
};
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats()
{
Count = (UInt32Value)0U
};
TableStyles tableStyles1 = new TableStyles()
{
Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9"
};
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
return(stylesheet1);
}
private static void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart1)
{
Stylesheet stylesheet1 = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac x16r2 xr"
}
};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
stylesheet1.AddNamespaceDeclaration("x16r2", "http://schemas.microsoft.com/office/spreadsheetml/2015/02/main");
stylesheet1.AddNamespaceDeclaration("xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision");
Fonts fonts1 = new Fonts()
{
Count = (UInt32Value)1U, KnownFonts = true
};
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);
fonts1.Append(font1);
Fills fills1 = new Fills()
{
Count = (UInt32Value)2U
};
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);
fills1.Append(fill1);
fills1.Append(fill2);
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)1U
};
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U
};
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats()
{
Count = (UInt32Value)2U
};
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
CellFormat cellFormat3 = new CellFormat()
{
NumberFormatId = (UInt32Value)14U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true
};
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
CellStyles cellStyles1 = new CellStyles()
{
Count = (UInt32Value)1U
};
CellStyle cellStyle1 = new CellStyle()
{
Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U
};
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats()
{
Count = (UInt32Value)0U
};
TableStyles tableStyles1 = new TableStyles()
{
Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16"
};
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
StylesheetExtension stylesheetExtension2 = new StylesheetExtension()
{
Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}"
};
stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
OpenXmlUnknownElement openXmlUnknownElement4 = OpenXmlUnknownElement.CreateOpenXmlUnknownElement("<x15:timelineStyles defaultTimelineStyle=\"TimeSlicerStyleLight1\" xmlns:x15=\"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main\" />");
stylesheetExtension2.Append(openXmlUnknownElement4);
stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheetExtensionList1.Append(stylesheetExtension2);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
workbookStylesPart1.Stylesheet = stylesheet1;
}
/// <summary>
/// Generates content of workbookStylesPart
/// </summary>
/// <param name="workbookStylesPart">WorkbookStylesPart Object</param>
private void CreateWorkBookStylesPart(WorkbookStylesPart workbookStylesPart)
{
// Define Style of Sheet in workbook
Stylesheet stylesheet1 = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
// Initialize an instance of fonts
Fonts fonts = new Fonts()
{
Count = (UInt32Value)2U, KnownFonts = true
};
// Initialize an instance of font,fontsize,color
Font font = new Font();
FontSize fontSize = new FontSize()
{
Val = 14D
};
Color color = new Color()
{
Theme = (UInt32Value)1U
};
FontName fontName = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme = new FontScheme()
{
Val = FontSchemeValues.Minor
};
// Add elements to font
font.Append(fontSize);
font.Append(color);
font.Append(fontName);
font.Append(fontFamilyNumbering);
font.Append(fontScheme);
fonts.Append(font);
// Define the StylesheetExtensionList Class. When the object is serialized out as xml, its qualified name is x:extLst
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
// Define the StylesheetExtension Class
StylesheetExtension stylesheetExtension1 = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles slicerStyles1 = new DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles()
{
DefaultSlicerStyle = "SlicerStyleLight1"
};
stylesheetExtension1.Append(slicerStyles1);
stylesheetExtensionList1.Append(stylesheetExtension1);
// Add elements to stylesheet
stylesheet1.Append(fonts);
stylesheet1.Append(stylesheetExtensionList1);
// Set the style of workbook
workbookStylesPart.Stylesheet = stylesheet1;
}
/*
* Write code for workbook styles by giving your own
* font size, color, font name, border properties, cell style formats etc.
*/
private void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart1)
{
Stylesheet stylesheet1 = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
Fonts fonts1 = new Fonts()
{
Count = (UInt32Value)2U,
KnownFonts = true
};
#region Fonts
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();
Bold bold1 = new Bold();
FontSize fontSize2 = new FontSize()
{
Val = 11D
};
Color color2 = new Color()
{
Theme = (UInt32Value)1U
};
FontName fontName2 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme2 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font2.Append(bold1);
font2.Append(fontSize2);
font2.Append(color2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontScheme2);
fonts1.Append(font1);
fonts1.Append(font2);
#endregion
#region Fills
Fills fills1 = new Fills()
{
Count = (UInt32Value)2U
};
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);
fills1.Append(fill1);
fills1.Append(fill2);
#endregion
#region Borders
Borders borders1 = new Borders()
{
Count = (UInt32Value)2U
};
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);
Border border2 = new Border();
LeftBorder leftBorder2 = new LeftBorder()
{
Style = BorderStyleValues.Thin
};
Color color3 = new Color()
{
Indexed = (UInt32Value)64U
};
leftBorder2.Append(color3);
RightBorder rightBorder2 = new RightBorder()
{
Style = BorderStyleValues.Thin
};
Color color4 = new Color()
{
Indexed = (UInt32Value)64U
};
rightBorder2.Append(color4);
TopBorder topBorder2 = new TopBorder()
{
Style = BorderStyleValues.Thin
};
Color color5 = new Color()
{
Indexed = (UInt32Value)64U
};
topBorder2.Append(color5);
BottomBorder bottomBorder2 = new BottomBorder()
{
Style = BorderStyleValues.Thin
};
Color color6 = new Color()
{
Indexed = (UInt32Value)64U
};
bottomBorder2.Append(color6);
DiagonalBorder diagonalBorder2 = new DiagonalBorder();
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
borders1.Append(border1);
borders1.Append(border2);
#endregion
#region CellStyleFormats
CellStyleFormats cellStyleFormats1 = new CellStyleFormats()
{
Count = (UInt32Value)1U
};
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U,
FontId = (UInt32Value)0U,
FillId = (UInt32Value)0U,
BorderId = (UInt32Value)0U
};
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats()
{
Count = (UInt32)3U
};
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U,
FontId = (UInt32Value)0U,
FillId = (UInt32Value)0U,
BorderId = (UInt32Value)0U,
FormatId = (UInt32Value)0U
};
CellFormat cellFormat3 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U,
FontId = (UInt32Value)0U,
FillId = (UInt32Value)0U,
BorderId = (UInt32Value)0U,
FormatId = (UInt32Value)0U,
ApplyBorder = true
};
CellFormat cellFormat4 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U,
FontId = (UInt32Value)0U,
FillId = (UInt32Value)0U,
BorderId = (UInt32Value)0U,
FormatId = (UInt32Value)0U,
ApplyFont = true,
ApplyBorder = true
};
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
CellStyles cellStyles1 = new CellStyles()
{
Count = (UInt32Value)1U
};
CellStyle cellStyle1 = new CellStyle()
{
Name = "Normal",
FormatId = (UInt32Value)0U,
BuiltinId = (UInt32Value)0U
};
cellStyle1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats()
{
Count = (UInt32Value)0U
};
TableStyles tableStyles1 = new TableStyles()
{
Count = (UInt32Value)0U,
DefaultTableStyle = "TableStyleMedium2",
DefaultPivotStyle = "PivotStyleLight16"
};
#endregion
#region StylesheetExtensionLists
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles()
{
DefaultSlicerStyle = "SlicerStyleLight1"
};
stylesheetExtension1.Append(slicerStyles1);
StylesheetExtension stylesheetExtension2 = new StylesheetExtension()
{
Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}"
};
stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles()
{
DefaultTimelineStyle = "TimeSlicerStyleLight1"
};
stylesheetExtension2.Append(timelineStyles1);
stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheetExtensionList1.Append(stylesheetExtension2);
#endregion
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
workbookStylesPart1.Stylesheet = stylesheet1;
}
// Generates content of workbookStyles.
private void GenerateWorkbookStylesContent(WorkbookStylesPart workbookStyles)
{
Stylesheet stylesheet1 = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
NumberingFormats numberingFormats1 = new NumberingFormats()
{
Count = (UInt32Value)1U
};
NumberingFormat numberingFormat1 = new NumberingFormat()
{
NumberFormatId = (UInt32Value)43U, FormatCode = "_(* #,##0.00_);_(* \\(#,##0.00\\);_(* \"-\"??_);_(@_)"
};
numberingFormats1.Append(numberingFormat1);
Fonts fonts1 = new Fonts()
{
Count = (UInt32Value)4U, KnownFonts = true
};
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();
Bold bold1 = new Bold();
FontSize fontSize2 = new FontSize()
{
Val = 11D
};
Color color2 = new Color()
{
Theme = (UInt32Value)1U
};
FontName fontName2 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme2 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font2.Append(bold1);
font2.Append(fontSize2);
font2.Append(color2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontScheme2);
Font font3 = new Font();
Bold bold2 = new Bold();
Italic italic1 = new Italic();
FontSize fontSize3 = new FontSize()
{
Val = 11D
};
Color color3 = new Color()
{
Rgb = "FFFF0000"
};
FontName fontName3 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering3 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme3 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font3.Append(bold2);
font3.Append(italic1);
font3.Append(fontSize3);
font3.Append(color3);
font3.Append(fontName3);
font3.Append(fontFamilyNumbering3);
font3.Append(fontScheme3);
Font font4 = new Font();
Italic italic2 = new Italic();
FontSize fontSize4 = new FontSize()
{
Val = 11D
};
Color color4 = new Color()
{
Rgb = "FFFF0000"
};
FontName fontName4 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering4 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme4 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font4.Append(italic2);
font4.Append(fontSize4);
font4.Append(color4);
font4.Append(fontName4);
font4.Append(fontFamilyNumbering4);
font4.Append(fontScheme4);
fonts1.Append(font1);
fonts1.Append(font2);
fonts1.Append(font3);
fonts1.Append(font4);
Fills fills1 = new Fills()
{
Count = (UInt32Value)4U
};
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()
{
Theme = (UInt32Value)0U, Tint = -4.9989318521683403E-2D
};
BackgroundColor backgroundColor1 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill3.Append(foregroundColor1);
patternFill3.Append(backgroundColor1);
fill3.Append(patternFill3);
Fill fill4 = new Fill();
PatternFill patternFill4 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor2 = new ForegroundColor()
{
Rgb = "FFFFFFCC"
};
BackgroundColor backgroundColor2 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill4.Append(foregroundColor2);
patternFill4.Append(backgroundColor2);
fill4.Append(patternFill4);
fills1.Append(fill1);
fills1.Append(fill2);
fills1.Append(fill3);
fills1.Append(fill4);
Borders borders1 = new Borders()
{
Count = (UInt32Value)2U
};
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);
Border border2 = new Border();
LeftBorder leftBorder2 = new LeftBorder();
RightBorder rightBorder2 = new RightBorder();
TopBorder topBorder2 = new TopBorder()
{
Style = BorderStyleValues.Thin
};
Color color5 = new Color()
{
Indexed = (UInt32Value)64U
};
topBorder2.Append(color5);
BottomBorder bottomBorder2 = new BottomBorder();
DiagonalBorder diagonalBorder2 = new DiagonalBorder();
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
borders1.Append(border1);
borders1.Append(border2);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats()
{
Count = (UInt32Value)1U
};
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U
};
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats()
{
Count = (UInt32Value)16U
};
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
CellFormat cellFormat3 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment1 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
};
cellFormat3.Append(alignment1);
CellFormat cellFormat4 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true
};
CellFormat cellFormat5 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment2 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Left
};
cellFormat5.Append(alignment2);
CellFormat cellFormat6 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment3 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
};
cellFormat6.Append(alignment3);
CellFormat cellFormat7 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment4 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
};
cellFormat7.Append(alignment4);
CellFormat cellFormat8 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment5 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Right
};
cellFormat8.Append(alignment5);
CellFormat cellFormat9 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment6 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Left
};
cellFormat9.Append(alignment6);
CellFormat cellFormat10 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment7 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
};
cellFormat10.Append(alignment7);
CellFormat cellFormat11 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)3U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment8 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
};
cellFormat11.Append(alignment8);
CellFormat cellFormat12 = new CellFormat()
{
NumberFormatId = (UInt32Value)43U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true
};
CellFormat cellFormat13 = new CellFormat()
{
NumberFormatId = (UInt32Value)9U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment9 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Left
};
cellFormat13.Append(alignment9);
CellFormat cellFormat14 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment10 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
};
cellFormat14.Append(alignment10);
CellFormat cellFormat15 = new CellFormat()
{
NumberFormatId = (UInt32Value)37U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true
};
Alignment alignment11 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
};
cellFormat15.Append(alignment11);
CellFormat cellFormat16 = new CellFormat()
{
NumberFormatId = (UInt32Value)43U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true
};
CellFormat cellFormat17 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true
};
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
cellFormats1.Append(cellFormat5);
cellFormats1.Append(cellFormat6);
cellFormats1.Append(cellFormat7);
cellFormats1.Append(cellFormat8);
cellFormats1.Append(cellFormat9);
cellFormats1.Append(cellFormat10);
cellFormats1.Append(cellFormat11);
cellFormats1.Append(cellFormat12);
cellFormats1.Append(cellFormat13);
cellFormats1.Append(cellFormat14);
cellFormats1.Append(cellFormat15);
cellFormats1.Append(cellFormat16);
cellFormats1.Append(cellFormat17);
CellStyles cellStyles1 = new CellStyles()
{
Count = (UInt32Value)1U
};
CellStyle cellStyle1 = new CellStyle()
{
Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U
};
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats()
{
Count = (UInt32Value)0U
};
TableStyles tableStyles1 = new TableStyles()
{
Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16"
};
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles slicerStyles1 = new DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles()
{
DefaultSlicerStyle = "SlicerStyleLight1"
};
stylesheetExtension1.Append(slicerStyles1);
//StylesheetExtension stylesheetExtension2 = new StylesheetExtension(){ Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}" };
//stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
//X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles(){ DefaultTimelineStyle = "TimeSlicerStyleLight1" };
//stylesheetExtension2.Append(timelineStyles1);
stylesheetExtensionList1.Append(stylesheetExtension1);
//stylesheetExtensionList1.Append(stylesheetExtension2);
stylesheet1.Append(numberingFormats1);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
workbookStyles.Stylesheet = stylesheet1;
}
/// <summary>
/// Adds schema values into the style sheet
/// </summary>
/// <param name="styleSheet">Style sheet to add to</param>
private static void AddSchemaNamespaces(Stylesheet styleSheet)
{
styleSheet.AddNamespaceDeclaration(Mc, @"http://schemas.openxmlformats.org/markup-compatibility/2006");
styleSheet.AddNamespaceDeclaration(X14ac, @"http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
styleSheet.AddNamespaceDeclaration(X16r2, @"http://schemas.microsoft.com/office/spreadsheetml/2015/02/main");
}
public static Stylesheet CreateStylesheet()
{
Stylesheet stylesheet1 = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
var fonts1 = AddFonts();
var fills1 = AddFills();
var borders1 = AddBorders();
CellStyleFormats cellStyleFormats1 = new CellStyleFormats()
{
Count = 1U
};
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U
};
cellStyleFormats1.AppendChild(cellFormat1);
CellFormats cellFormats1 = new CellFormats()
{
Count = 4U
};
// Black text on White background
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U, FormatId = 0U
};
// White text on Orange background
CellFormat cellFormat3 = new CellFormat()
{
NumberFormatId = 0U, FontId = 1U, FillId = 2U, BorderId = 0U, FormatId = 0U, ApplyFill = true
};
// White text on Blue background
CellFormat cellFormat4 = new CellFormat()
{
NumberFormatId = 0U, FontId = 1U, FillId = 3U, BorderId = 0U, FormatId = 0U, ApplyFill = true
};
// Black text on Yellow background
CellFormat cellFormat5 = new CellFormat()
{
NumberFormatId = 0U, FontId = 0U, FillId = 4U, BorderId = 0U, FormatId = 0U, ApplyFill = true
};
cellFormats1.AppendChild(cellFormat2);
cellFormats1.AppendChild(cellFormat3);
cellFormats1.AppendChild(cellFormat4);
cellFormats1.AppendChild(cellFormat5);
CellStyles cellStyles1 = new CellStyles()
{
Count = 1U
};
CellStyle cellStyle1 = new CellStyle()
{
Name = "Normal", FormatId = 0U, BuiltinId = 0U
};
cellStyles1.AppendChild(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats()
{
Count = 0U
};
TableStyles tableStyles1 = new TableStyles()
{
Count = 0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9"
};
StylesheetExtensionList stylesheetExtensionList = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles slicerStyles = new DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles()
{
DefaultSlicerStyle = "SlicerStyleLight1"
};
stylesheetExtension.AppendChild(slicerStyles);
stylesheetExtensionList.AppendChild(stylesheetExtension);
stylesheet1.AppendChild(fonts1);
stylesheet1.AppendChild(fills1);
stylesheet1.AppendChild(borders1);
stylesheet1.AppendChild(cellStyleFormats1);
stylesheet1.AppendChild(cellFormats1);
stylesheet1.AppendChild(cellStyles1);
stylesheet1.AppendChild(differentialFormats1);
stylesheet1.AppendChild(tableStyles1);
stylesheet1.AppendChild(stylesheetExtensionList);
return(stylesheet1);
}
// Generates content of workbookStylesPart1.
private void GenerateWorkbookStylesPart1Content(WorkbookStylesPart workbookStylesPart1)
{
Stylesheet stylesheet1 = new Stylesheet(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x14ac" } };
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
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 };
FontCharSet fontCharSet1 = new FontCharSet(){ Val = 128 };
FontScheme fontScheme1 = new FontScheme(){ Val = FontSchemeValues.Minor };
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontCharSet1);
font1.Append(fontScheme1);
Font font2 = new Font();
FontSize fontSize2 = new FontSize(){ Val = 6D };
FontName fontName2 = new FontName(){ Val = "Calibri" };
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering(){ Val = 2 };
FontCharSet fontCharSet2 = new FontCharSet(){ Val = 128 };
FontScheme fontScheme2 = new FontScheme(){ Val = FontSchemeValues.Minor };
font2.Append(fontSize2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontCharSet2);
font2.Append(fontScheme2);
fonts1.Append(font1);
fonts1.Append(font2);
Fills fills1 = new Fills(){ Count = (UInt32Value)2U };
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);
fills1.Append(fill1);
fills1.Append(fill2);
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)1U };
CellFormat cellFormat1 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
Alignment alignment1 = new Alignment(){ Vertical = VerticalAlignmentValues.Center };
cellFormat1.Append(alignment1);
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats(){ Count = (UInt32Value)1U };
CellFormat cellFormat2 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
Alignment alignment2 = new Alignment(){ Vertical = VerticalAlignmentValues.Center };
cellFormat2.Append(alignment2);
cellFormats1.Append(cellFormat2);
CellStyles cellStyles1 = new CellStyles(){ Count = (UInt32Value)1U };
CellStyle cellStyle1 = new CellStyle(){ Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats(){ Count = (UInt32Value)0U };
TableStyles tableStyles1 = new TableStyles(){ Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16" };
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension(){ Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" };
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles(){ DefaultSlicerStyle = "SlicerStyleLight1" };
stylesheetExtension1.Append(slicerStyles1);
StylesheetExtension stylesheetExtension2 = new StylesheetExtension(){ Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}" };
stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles(){ DefaultTimelineStyle = "TimeSlicerStyleLight1" };
stylesheetExtension2.Append(timelineStyles1);
stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheetExtensionList1.Append(stylesheetExtension2);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
workbookStylesPart1.Stylesheet = stylesheet1;
}
/// <summary>
/// Generates Styles for Workbook.
/// </summary>
/// <param name="workbookStylesPart">Workbook Style Part to add styles to</param>
private void GenerateWorkbookStylesPart1Content(WorkbookStylesPart workbookStylesPart)
{
Stylesheet stylesheet1 = new Stylesheet {
MCAttributes = new MarkupCompatibilityAttributes {
Ignorable = "x14ac x16r2 xr"
}
};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
stylesheet1.AddNamespaceDeclaration("x16r2", "http://schemas.microsoft.com/office/spreadsheetml/2015/02/main");
stylesheet1.AddNamespaceDeclaration("xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision");
Fonts fonts1 = new Fonts {
Count = 1U, KnownFonts = true
};
Font font1 = new Font();
FontSize fontSize1 = new FontSize {
Val = 11D
};
Color color1 = new Color {
Theme = 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);
fonts1.Append(font1);
Fills fills1 = new Fills {
Count = 2U
};
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);
fills1.Append(fill1);
fills1.Append(fill2);
Borders borders1 = new Borders {
Count = 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);
CellFormats cellFormats1 = new CellFormats {
Count = 2U
};
CellFormat cellFormat2 = new CellFormat {
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U, FormatId = 0U
};
CellFormat cellFormat3 = new CellFormat {
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U, FormatId = 0U, ApplyAlignment = true
};
Alignment alignment1 = new Alignment {
Horizontal = HorizontalAlignmentValues.Center
};
cellFormat3.Append(alignment1);
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
DifferentialFormats differentialFormats1 = new DifferentialFormats {
Count = 4U
};
DifferentialFormat differentialFormat1 = new DifferentialFormat();
Alignment alignment2 = new Alignment {
Horizontal = HorizontalAlignmentValues.Center,
Vertical = VerticalAlignmentValues.Bottom,
TextRotation = 0U,
WrapText = false,
Indent = 0U,
JustifyLastLine = false,
ShrinkToFit = false,
ReadingOrder = 0U
};
differentialFormat1.Append(alignment2);
DifferentialFormat differentialFormat2 = new DifferentialFormat();
Alignment alignment3 = new Alignment {
Horizontal = HorizontalAlignmentValues.Center,
Vertical = VerticalAlignmentValues.Bottom,
TextRotation = 0U,
WrapText = false,
Indent = 0U,
JustifyLastLine = false,
ShrinkToFit = false,
ReadingOrder = 0U
};
differentialFormat2.Append(alignment3);
DifferentialFormat differentialFormat3 = new DifferentialFormat();
Alignment alignment4 = new Alignment {
Horizontal = HorizontalAlignmentValues.Center,
Vertical = VerticalAlignmentValues.Bottom,
TextRotation = 0U,
WrapText = false,
Indent = 0U,
JustifyLastLine = false,
ShrinkToFit = false,
ReadingOrder = 0U
};
differentialFormat3.Append(alignment4);
DifferentialFormat differentialFormat4 = new DifferentialFormat();
Alignment alignment5 = new Alignment {
Horizontal = HorizontalAlignmentValues.Center,
Vertical = VerticalAlignmentValues.Bottom,
TextRotation = 0U,
WrapText = false,
Indent = 0U,
JustifyLastLine = false,
ShrinkToFit = false,
ReadingOrder = 0U
};
differentialFormat4.Append(alignment5);
differentialFormats1.Append(differentialFormat1);
differentialFormats1.Append(differentialFormat2);
differentialFormats1.Append(differentialFormat3);
differentialFormats1.Append(differentialFormat4);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(differentialFormats1);
workbookStylesPart.Stylesheet = stylesheet1;
}
private static Stylesheet CreateStylesheet()
{
// Stylesheet declarion and namespace
Stylesheet stylesheet = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
// List of fonts
Fonts fontsList = new Fonts()
{
Count = (UInt32Value)2U, KnownFonts = true
};
// FontId=0 - Regular Excel font
Font font0 = new Font();
FontSize fontSize0 = new FontSize()
{
Val = 11D
};
Color color0 = new Color()
{
Theme = (UInt32Value)1U
};
FontName fontName0 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering0 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme0 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font0.Append(fontSize0);
font0.Append(color0);
font0.Append(fontName0);
font0.Append(fontFamilyNumbering0);
font0.Append(fontScheme0);
// FontId=1 - Bold font for header
Font font1 = new Font();
Bold bold = new Bold();
font1.Append(bold);
fontsList.Append(font0);
fontsList.Append(font1);
// List of fills
Fills fillList = new Fills()
{
Count = (UInt32Value)3U
};
// FillId = 0, Normal background
Fill fill0 = new Fill();
PatternFill patternFill0 = new PatternFill()
{
PatternType = PatternValues.None
};
fill0.Append(patternFill0);
// FillId = 00, Normal background
Fill fill00 = new Fill();
PatternFill patternFill00 = new PatternFill()
{
PatternType = PatternValues.None
};
fill00.Append(patternFill00);
// FillId = 1, Light Blue for alternating cells
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill()
{
PatternType = PatternValues.None
};
//PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.Solid };
//ForegroundColor foregroundColor1 = new ForegroundColor() { Rgb = "FFDEEFF7" };
//patternFill1.Append(foregroundColor1);
fill1.Append(patternFill1);
fillList.Append(fill0);
fillList.Append(fill00);
fillList.Append(fill1);
// Borders style
Borders bordersList = 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);
bordersList.Append(border1);
// List of cell styles formats
CellStyleFormats cellStyleFormatsList = new CellStyleFormats()
{
Count = (UInt32Value)1U
};
CellFormat cellFormat = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U
};
cellStyleFormatsList.Append(cellFormat);
// Cells formats
CellFormats cellFormatsList = new CellFormats()
{
Count = (UInt32Value)5U
};
// StyleIndex = 0 - Regular font
CellFormat cellFormat0 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// StyleIndex = 1 - Bold font
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// StyleIndex = 2 - Date (Short Date)
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = (UInt32Value)14U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// StyleIndex = 3 - Light blue background (Text and Numbers)
CellFormat cellFormat3 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true
};
// StyleIndex = 4 - Light blue background (Date)
CellFormat cellFormat4 = new CellFormat()
{
NumberFormatId = (UInt32Value)14U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true
};
cellFormatsList.Append(cellFormat0);
cellFormatsList.Append(cellFormat1);
cellFormatsList.Append(cellFormat2);
cellFormatsList.Append(cellFormat3);
cellFormatsList.Append(cellFormat4);
// Cells styles
CellStyles cellStyleList = new CellStyles()
{
Count = (UInt32Value)1U
};
CellStyle cellStyle0 = new CellStyle()
{
Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U
};
cellStyleList.Append(cellStyle0);
DifferentialFormats differentialFormats0 = new DifferentialFormats()
{
Count = (UInt32Value)0U
};
TableStyles tableStyles0 = new TableStyles()
{
Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9"
};
StylesheetExtensionList stylesheetExtensionList = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtensionList.Append(stylesheetExtension1);
stylesheet.Append(fontsList);
stylesheet.Append(fillList);
stylesheet.Append(bordersList);
stylesheet.Append(cellStyleFormatsList);
stylesheet.Append(cellFormatsList);
stylesheet.Append(cellStyleList);
stylesheet.Append(differentialFormats0);
stylesheet.Append(tableStyles0);
stylesheet.Append(stylesheetExtensionList);
return(stylesheet);
}
private Stylesheet CreateStylesheet()
{
Stylesheet stylesheet = new Stylesheet();
stylesheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
Fonts fontsCollection = new Fonts()
{
Count = (UInt32Value)1U, KnownFonts = true
};
Font defaultFont = new Font();
FontSize defaultFontSize = new FontSize()
{
Val = 11D
};
Color defaultFontColor = new Color()
{
Theme = (UInt32Value)1U
};
FontName defaultFontName = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering defaultFontFamilyNumbering = new FontFamilyNumbering()
{
Val = 2
};
FontScheme defaultFontScheme = new FontScheme()
{
Val = FontSchemeValues.Minor
};
defaultFont.Append(defaultFontSize);
defaultFont.Append(defaultFontColor);
defaultFont.Append(defaultFontName);
defaultFont.Append(defaultFontFamilyNumbering);
defaultFont.Append(defaultFontScheme);
Font boldFont = new Font();
FontSize boldFontSize = new FontSize()
{
Val = 12D
};
Color boldFontColor = new Color()
{
Theme = (UInt32Value)1U
};
FontName boldFontName = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering boldFontFamilyNumbering = new FontFamilyNumbering()
{
Val = 2
};
FontScheme boldFontScheme = new FontScheme()
{
Val = FontSchemeValues.Minor
};
boldFont.Append(new Bold());
boldFont.Append(boldFontSize);
boldFont.Append(boldFontColor);
boldFont.Append(boldFontName);
boldFont.Append(boldFontFamilyNumbering);
boldFont.Append(boldFontScheme);
Font tableHeaderFont = new Font();
FontSize tableHeaderFontSize = new FontSize()
{
Val = 11D
};
Color tableHeaderFontColor = new Color()
{
Rgb = new HexBinaryValue("808080")
};
FontName tableHeaderFontName = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering tableHeaderFontFamilyNumbering = new FontFamilyNumbering()
{
Val = 2
};
FontScheme tableHeaderFontScheme = new FontScheme()
{
Val = FontSchemeValues.Minor
};
tableHeaderFont.Append(new Bold());
tableHeaderFont.Append(tableHeaderFontSize);
tableHeaderFont.Append(tableHeaderFontColor);
tableHeaderFont.Append(tableHeaderFontName);
tableHeaderFont.Append(tableHeaderFontFamilyNumbering);
tableHeaderFont.Append(tableHeaderFontScheme);
fontsCollection.Append(defaultFont, boldFont, tableHeaderFont);
Fills fills0 = new Fills()
{
Count = (UInt32Value)1U
};
// FillId = 0
Fill fill0 = new Fill();
PatternFill patternFill1 = new PatternFill()
{
PatternType = PatternValues.None
};
fill0.Append(patternFill1);
fills0.Append(fill0);
Borders borders0 = 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);
borders0.Append(border1);
Alignment tableHeaderAlignment = new Alignment
{
Horizontal = HorizontalAlignmentValues.Left,
Vertical = VerticalAlignmentValues.Center,
WrapText = false
};
CellFormats cellFormatsCollection = new CellFormats()
{
Count = (UInt32Value)1U
};
CellFormat cellFormatDefault = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
CellFormat cellFormatBold = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
CellFormat cellFormatTableHeader = new CellFormat {
Alignment = tableHeaderAlignment, FontId = (UInt32Value)2U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
cellFormatsCollection.Append(cellFormatDefault, cellFormatBold, cellFormatTableHeader);
stylesheet.Append(fontsCollection);
stylesheet.Append(fills0);
stylesheet.Append(borders0);
stylesheet.Append(cellFormatsCollection);
return(stylesheet);
}
// Generates content of workbookStyles.
private void GenerateWorkbookStylesContent(WorkbookStylesPart workbookStyles)
{
Stylesheet stylesheet1 = new Stylesheet(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x14ac" } };
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
NumberingFormats numberingFormats1 = new NumberingFormats(){ Count = (UInt32Value)1U };
NumberingFormat numberingFormat1 = new NumberingFormat(){ NumberFormatId = (UInt32Value)43U, FormatCode = "_(* #,##0.00_);_(* \\(#,##0.00\\);_(* \"-\"??_);_(@_)" };
numberingFormats1.Append(numberingFormat1);
Fonts fonts1 = new Fonts(){ Count = (UInt32Value)4U, KnownFonts = true };
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();
Bold bold1 = new Bold();
FontSize fontSize2 = new FontSize(){ Val = 11D };
Color color2 = new Color(){ Theme = (UInt32Value)1U };
FontName fontName2 = new FontName(){ Val = "Calibri" };
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering(){ Val = 2 };
FontScheme fontScheme2 = new FontScheme(){ Val = FontSchemeValues.Minor };
font2.Append(bold1);
font2.Append(fontSize2);
font2.Append(color2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontScheme2);
Font font3 = new Font();
Bold bold2 = new Bold();
Italic italic1 = new Italic();
FontSize fontSize3 = new FontSize(){ Val = 11D };
Color color3 = new Color(){ Rgb = "FFFF0000" };
FontName fontName3 = new FontName(){ Val = "Calibri" };
FontFamilyNumbering fontFamilyNumbering3 = new FontFamilyNumbering(){ Val = 2 };
FontScheme fontScheme3 = new FontScheme(){ Val = FontSchemeValues.Minor };
font3.Append(bold2);
font3.Append(italic1);
font3.Append(fontSize3);
font3.Append(color3);
font3.Append(fontName3);
font3.Append(fontFamilyNumbering3);
font3.Append(fontScheme3);
Font font4 = new Font();
Italic italic2 = new Italic();
FontSize fontSize4 = new FontSize(){ Val = 11D };
Color color4 = new Color(){ Rgb = "FFFF0000" };
FontName fontName4 = new FontName(){ Val = "Calibri" };
FontFamilyNumbering fontFamilyNumbering4 = new FontFamilyNumbering(){ Val = 2 };
FontScheme fontScheme4 = new FontScheme(){ Val = FontSchemeValues.Minor };
font4.Append(italic2);
font4.Append(fontSize4);
font4.Append(color4);
font4.Append(fontName4);
font4.Append(fontFamilyNumbering4);
font4.Append(fontScheme4);
fonts1.Append(font1);
fonts1.Append(font2);
fonts1.Append(font3);
fonts1.Append(font4);
Fills fills1 = new Fills(){ Count = (UInt32Value)4U };
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(){ Theme = (UInt32Value)0U, Tint = -4.9989318521683403E-2D };
BackgroundColor backgroundColor1 = new BackgroundColor(){ Indexed = (UInt32Value)64U };
patternFill3.Append(foregroundColor1);
patternFill3.Append(backgroundColor1);
fill3.Append(patternFill3);
Fill fill4 = new Fill();
PatternFill patternFill4 = new PatternFill(){ PatternType = PatternValues.Solid };
ForegroundColor foregroundColor2 = new ForegroundColor(){ Rgb = "FFFFFFCC" };
BackgroundColor backgroundColor2 = new BackgroundColor(){ Indexed = (UInt32Value)64U };
patternFill4.Append(foregroundColor2);
patternFill4.Append(backgroundColor2);
fill4.Append(patternFill4);
fills1.Append(fill1);
fills1.Append(fill2);
fills1.Append(fill3);
fills1.Append(fill4);
Borders borders1 = new Borders(){ Count = (UInt32Value)2U };
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);
Border border2 = new Border();
LeftBorder leftBorder2 = new LeftBorder();
RightBorder rightBorder2 = new RightBorder();
TopBorder topBorder2 = new TopBorder(){ Style = BorderStyleValues.Thin };
Color color5 = new Color(){ Indexed = (UInt32Value)64U };
topBorder2.Append(color5);
BottomBorder bottomBorder2 = new BottomBorder();
DiagonalBorder diagonalBorder2 = new DiagonalBorder();
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
borders1.Append(border1);
borders1.Append(border2);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats(){ Count = (UInt32Value)1U };
CellFormat cellFormat1 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats(){ Count = (UInt32Value)16U };
CellFormat cellFormat2 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
CellFormat cellFormat3 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true };
Alignment alignment1 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Center };
cellFormat3.Append(alignment1);
CellFormat cellFormat4 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
CellFormat cellFormat5 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true };
Alignment alignment2 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Left };
cellFormat5.Append(alignment2);
CellFormat cellFormat6 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true };
Alignment alignment3 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Center };
cellFormat6.Append(alignment3);
CellFormat cellFormat7 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true };
Alignment alignment4 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Center };
cellFormat7.Append(alignment4);
CellFormat cellFormat8 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true };
Alignment alignment5 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Right };
cellFormat8.Append(alignment5);
CellFormat cellFormat9 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyAlignment = true };
Alignment alignment6 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Left };
cellFormat9.Append(alignment6);
CellFormat cellFormat10 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyAlignment = true };
Alignment alignment7 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Center };
cellFormat10.Append(alignment7);
CellFormat cellFormat11 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)3U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true };
Alignment alignment8 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Center };
cellFormat11.Append(alignment8);
CellFormat cellFormat12 = new CellFormat(){ NumberFormatId = (UInt32Value)43U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true };
CellFormat cellFormat13 = new CellFormat(){ NumberFormatId = (UInt32Value)9U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFill = true, ApplyAlignment = true };
Alignment alignment9 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Left };
cellFormat13.Append(alignment9);
CellFormat cellFormat14 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyAlignment = true };
Alignment alignment10 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Center };
cellFormat14.Append(alignment10);
CellFormat cellFormat15 = new CellFormat(){ NumberFormatId = (UInt32Value)37U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };
Alignment alignment11 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Center };
cellFormat15.Append(alignment11);
CellFormat cellFormat16 = new CellFormat(){ NumberFormatId = (UInt32Value)43U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
CellFormat cellFormat17 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true };
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
cellFormats1.Append(cellFormat5);
cellFormats1.Append(cellFormat6);
cellFormats1.Append(cellFormat7);
cellFormats1.Append(cellFormat8);
cellFormats1.Append(cellFormat9);
cellFormats1.Append(cellFormat10);
cellFormats1.Append(cellFormat11);
cellFormats1.Append(cellFormat12);
cellFormats1.Append(cellFormat13);
cellFormats1.Append(cellFormat14);
cellFormats1.Append(cellFormat15);
cellFormats1.Append(cellFormat16);
cellFormats1.Append(cellFormat17);
CellStyles cellStyles1 = new CellStyles(){ Count = (UInt32Value)1U };
CellStyle cellStyle1 = new CellStyle(){ Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats(){ Count = (UInt32Value)0U };
TableStyles tableStyles1 = new TableStyles(){ Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16" };
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension(){ Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" };
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles slicerStyles1 = new DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles(){ DefaultSlicerStyle = "SlicerStyleLight1" };
stylesheetExtension1.Append(slicerStyles1);
//StylesheetExtension stylesheetExtension2 = new StylesheetExtension(){ Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}" };
//stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
//X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles(){ DefaultTimelineStyle = "TimeSlicerStyleLight1" };
//stylesheetExtension2.Append(timelineStyles1);
stylesheetExtensionList1.Append(stylesheetExtension1);
//stylesheetExtensionList1.Append(stylesheetExtension2);
stylesheet1.Append(numberingFormats1);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
workbookStyles.Stylesheet = stylesheet1;
}
private static void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart)
{
Stylesheet styleSheet = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac x16r2 xr"
}
};
//this namespace are revelead using the took OPEN XML PRODUCTIVITY TOOL -- REFLECT CODE feature
styleSheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
styleSheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
styleSheet.AddNamespaceDeclaration("x16r2", "http://schemas.microsoft.com/office/spreadsheetml/2015/02/main");
styleSheet.AddNamespaceDeclaration("xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision");
#region fonts
Fonts fontList = new Fonts {
Count = 3
};
Font f1 = new Font();
Color color1 = new Color()
{
Rgb = HexBinaryValue.FromString("FF000000")
};
f1.Append(color1);
Font f2 = new Font();
FontSize fontSize2 = new FontSize()
{
Val = 12D
};
Color color2 = new Color()
{
Rgb = HexBinaryValue.FromString("FF808080")
};
Bold bold2 = new Bold();
Italic it2 = new Italic();
FontName fontName2 = new FontName()
{
Val = "Arial Black"
};
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering()
{
Val = 2
};
f2.Append(fontSize2);
f2.Append(color2);
f2.Append(fontName2);
f2.Append(bold2);
f2.Append(it2);
f2.Append(fontFamilyNumbering2);
Font f3 = new Font();
FontSize fontSize3 = new FontSize()
{
Val = 16D
};
Color color3 = new Color()
{
Rgb = HexBinaryValue.FromString("FF0000FF")
};
Underline ud3 = new Underline();
FontName fontName3 = new FontName()
{
Val = "Times New Roman"
};
FontFamilyNumbering fontFamilyNumbering3 = new FontFamilyNumbering()
{
Val = 1
};
f3.Append(fontSize3);
f3.Append(color3);
f3.Append(ud3);
f3.Append(fontName3);
f3.Append(fontFamilyNumbering3);
fontList.Append(f1);
fontList.Append(f2);
fontList.Append(f3);
#endregion
#region Fills
Fills fillList = new Fills();
//solid red fill
var solidRed = new PatternFill()
{
PatternType = PatternValues.Solid
};
solidRed.ForegroundColor = new ForegroundColor {
Rgb = HexBinaryValue.FromString("FFFF0000")
};
solidRed.BackgroundColor = new BackgroundColor {
Indexed = 64
};
fillList.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.None
}
}); // required, reserved by Excel
fillList.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.Gray125
}
}); // required, reserved by Excel
fillList.AppendChild(new Fill {
PatternFill = solidRed
});
fillList.Count = 3;
#endregion
#region Borders
Borders bordersList = new Borders
{
Count = 1
};
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);
bordersList.Append(border1);
Border border2 = new Border();
LeftBorder leftBorder2 = new LeftBorder()
{
Style = BorderStyleValues.Thick
};
Color colorborder = new Color()
{
Indexed = (UInt32Value)64U
};
leftBorder2.Append(colorborder);
RightBorder rightBorder2 = new RightBorder()
{
Style = BorderStyleValues.Thin
};
Color color4 = new Color()
{
Indexed = (UInt32Value)64U
};
rightBorder2.Append(color4);
TopBorder topBorder2 = new TopBorder()
{
Style = BorderStyleValues.Thick
};
Color color5 = new Color()
{
Indexed = (UInt32Value)64U
};
topBorder2.Append(color5);
BottomBorder bottomBorder2 = new BottomBorder()
{
Style = BorderStyleValues.Thin
};
Color color6 = new Color()
{
Indexed = (UInt32Value)64U
};
bottomBorder2.Append(color6);
DiagonalBorder diagonalBorder2 = new DiagonalBorder();
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
bordersList.Append(border2);
#endregion
#region Cellformats
// blank cell format list
CellStyleFormats blankcellStyleFormatList = new CellStyleFormats
{
Count = 1
};
blankcellStyleFormatList.AppendChild(new CellFormat());
// cell format list
CellFormats cellStyleFormatList = new CellFormats();
// empty one for index 0, seems to be required
cellStyleFormatList.AppendChild(new CellFormat());
// cell format references style format 0, font 0, border 0, fill 2 and applies the fill
cellStyleFormatList.AppendChild(new CellFormat {
FormatId = 0, FontId = 0, BorderId = 1, FillId = 2, ApplyFill = true
}).AppendChild(new Alignment {
Horizontal = HorizontalAlignmentValues.Center
});
cellStyleFormatList.AppendChild(new CellFormat {
FormatId = 0, FontId = 1, BorderId = 1, FillId = 0, ApplyFill = true
});
cellStyleFormatList.AppendChild(new CellFormat {
FormatId = 0, FontId = 2, BorderId = 1, FillId = 0, ApplyFill = true
});
cellStyleFormatList.Count = 4;
#endregion
styleSheet.Append(fontList);
styleSheet.Append(fillList);
styleSheet.Append(bordersList);
styleSheet.Append(blankcellStyleFormatList);
styleSheet.Append(cellStyleFormatList);
workbookStylesPart.Stylesheet = styleSheet;
}
// Generates content of workbookStylesPart1.
private void GenerateWorkbookStylesPart1Content(WorkbookStylesPart workbookStylesPart1)
{
Stylesheet stylesheet1 = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
Fonts fonts1 = new Fonts()
{
Count = (UInt32Value)1U, KnownFonts = true
};
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);
fonts1.Append(font1);
Fills fills1 = new Fills()
{
Count = (UInt32Value)2U
};
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);
fills1.Append(fill1);
fills1.Append(fill2);
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)1U
};
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U
};
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats()
{
Count = (UInt32Value)1U
};
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
cellFormats1.Append(cellFormat2);
CellStyles cellStyles1 = new CellStyles()
{
Count = (UInt32Value)1U
};
CellStyle cellStyle1 = new CellStyle()
{
Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U
};
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats()
{
Count = (UInt32Value)0U
};
TableStyles tableStyles1 = new TableStyles()
{
Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", 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;
}
// Creates an Stylesheet instance and adds its children.
public Stylesheet GenerateStylesheet()
{
Stylesheet stylesheet1 = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
Fonts fonts1 = new Fonts()
{
Count = (UInt32Value)1U
};
Font font1 = new Font();
FontSize fontSize1 = new FontSize()
{
Val = 11D
};
FontName fontName1 = new FontName()
{
Val = "Calibri"
};
font1.Append(fontSize1);
font1.Append(fontName1);
fonts1.Append(font1);
Fills fills1 = new Fills()
{
Count = (UInt32Value)2U
};
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);
fills1.Append(fill1);
fills1.Append(fill2);
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);
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()
{
Style = BorderStyleValues.Thin
};
Color color5 = new Color()
{
Indexed = (UInt32Value)64U
};
diagonalBorder2.Append(color5);
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
borders1.Append(border1);
borders1.Append(border2);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats()
{
Count = (UInt32Value)1U
};
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U
};
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats()
{
Count = (UInt32Value)1U
};
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
cellFormats1.Append(cellFormat2);
CellStyles cellStyles1 = new CellStyles()
{
Count = (UInt32Value)1U
};
CellStyle cellStyle1 = new CellStyle()
{
Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U
};
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats()
{
Count = (UInt32Value)0U
};
TableStyles tableStyles1 = new TableStyles()
{
Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16"
};
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles slicerStyles1 = new DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles()
{
DefaultSlicerStyle = "SlicerStyleLight1"
};
stylesheetExtension1.Append(slicerStyles1);
stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
return(stylesheet1);
}
public static Stylesheet GenerateStylesheet()
{
Stylesheet stylesheet1 = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
Fonts fonts1 = new Fonts()
{
Count = (UInt32Value)3U, KnownFonts = true
};
Font font1 = new Font();
FontSize fontSize1 = new FontSize()
{
Val = 14D
};
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();
Bold bold2 = new Bold();
FontSize fontSize2 = new FontSize()
{
Val = 18D
};
Color color2 = new Color()
{
Theme = (UInt32Value)1U
};
FontName fontName2 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme2 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font2.Append(bold2);
font2.Append(fontSize2);
font2.Append(color2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontScheme2);
Font font3 = new Font();
Bold bold1 = new Bold();
FontSize fontSize3 = new FontSize()
{
Val = 30D
};
Color color3 = new Color()
{
Theme = (UInt32Value)1U
};
FontName fontName3 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering3 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme3 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font3.Append(bold1);
font3.Append(fontSize3);
font3.Append(color3);
font3.Append(fontName3);
font3.Append(fontFamilyNumbering3);
font3.Append(fontScheme3);
fonts1.Append(font1);
fonts1.Append(font2);
fonts1.Append(font3);
Fills fills1 = new Fills()
{
Count = (UInt32Value)2U
};
// 0 no BG color just normal Excel
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill()
{
PatternType = PatternValues.None
};
fill1.Append(patternFill1);
// 1 red or open CAT 1
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor2 = new ForegroundColor()
{
Rgb = "FFFF0000"
};
BackgroundColor backgroundColor2 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill2.Append(foregroundColor2);
patternFill2.Append(backgroundColor2);
fill2.Append(patternFill2);
// 2 silver or N/A
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor3 = new ForegroundColor()
{
Rgb = "FFCCCCCC"
};
BackgroundColor backgroundColor3 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill3.Append(foregroundColor3);
patternFill3.Append(backgroundColor3);
fill3.Append(patternFill3);
// 3 green or NaF
Fill fill4 = new Fill();
PatternFill patternFill4 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor4 = new ForegroundColor()
{
Rgb = "FF50CC83"
};
BackgroundColor backgroundColor4 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill4.Append(foregroundColor4);
patternFill4.Append(backgroundColor4);
fill4.Append(patternFill4);
// 4 black&white or Not Reviewed
Fill fill5 = new Fill();
PatternFill patternFill5 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor5 = new ForegroundColor()
{
Rgb = "FFFFFFFF"
};
BackgroundColor backgroundColor5 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill5.Append(foregroundColor5);
patternFill5.Append(backgroundColor5);
fill5.Append(patternFill5);
// 5 red or open CAT 1
Fill fill6 = new Fill();
PatternFill patternFill6 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor6 = new ForegroundColor()
{
Rgb = "FFE53935"
};
BackgroundColor backgroundColor6 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill6.Append(foregroundColor6);
patternFill6.Append(backgroundColor6);
fill6.Append(patternFill6);
// 6 orange open CAT 2
Fill fill7 = new Fill();
PatternFill patternFill7 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor7 = new ForegroundColor()
{
Rgb = "FFFFA500"
};
BackgroundColor backgroundColor7 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill7.Append(foregroundColor7);
patternFill7.Append(backgroundColor7);
fill7.Append(patternFill7);
// 7 orange open CAT 3
Fill fill8 = new Fill();
PatternFill patternFill8 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor8 = new ForegroundColor()
{
Rgb = "FFD8D80E"
};
BackgroundColor backgroundColor8 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill8.Append(foregroundColor8);
patternFill8.Append(backgroundColor8);
fill8.Append(patternFill8);
// 8 gray background for any general use
Fill fill9 = new Fill();
PatternFill patternFill9 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor9 = new ForegroundColor()
{
Rgb = "FFBBBBBB"
};
BackgroundColor backgroundColor9 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill9.Append(foregroundColor9);
patternFill9.Append(backgroundColor9);
fill9.Append(patternFill9);
fills1.Append(fill1); // these are 0 based when you include in the CellFormat statements below, so 1 off
fills1.Append(fill2);
fills1.Append(fill3);
fills1.Append(fill4);
fills1.Append(fill5);
fills1.Append(fill6);
fills1.Append(fill7);
fills1.Append(fill8);
fills1.Append(fill9);
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)1U
};
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U
};
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats()
{
Count = (UInt32Value)4U
};
// style index 0: normal font and wrapping of text for cell rows
CellFormat cellFormat2 = new CellFormat(new Alignment()
{
WrapText = true
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// style index 1: normal font with numerical format
CellFormat cellFormat3 = new CellFormat(new Alignment()
{
WrapText = true
})
{
NumberFormatId = (UInt32Value)2U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true
};
// style index 2: title font of 30 bold
CellFormat cellFormat4 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true
};
// style index 3: info row under title and header rows font bold size 18
CellFormat cellFormat5 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true
};
// fill colors based on the fillx variables above to match the 4 statuses of the checklist vulnerabilities
// style index 4: red or Open
CellFormat cellFormat6 = new CellFormat(new Alignment()
{
WrapText = true
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)5U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// style index 5: silver or Not Applicable
CellFormat cellFormat7 = new CellFormat(new Alignment()
{
WrapText = true
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// style index 6: green or Not a Finding
CellFormat cellFormat8 = new CellFormat(new Alignment()
{
WrapText = true
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// style index 7: b/w or Not Reviewed
CellFormat cellFormat9 = new CellFormat(new Alignment()
{
WrapText = true
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)4U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// CENTER The content for listings versus the excel export of a checklist above
// style index 8: red or Open centered content
CellFormat cellFormat10 = new CellFormat(new Alignment()
{
WrapText = true, Horizontal = HorizontalAlignmentValues.Center
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)5U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// style index 9: silver or Not Applicable
CellFormat cellFormat11 = new CellFormat(new Alignment()
{
WrapText = true, Horizontal = HorizontalAlignmentValues.Center
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// style index 10: green or Not a Finding
CellFormat cellFormat12 = new CellFormat(new Alignment()
{
WrapText = true, Horizontal = HorizontalAlignmentValues.Center
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// style index 11: b/w or Not Reviewed
CellFormat cellFormat13 = new CellFormat(new Alignment()
{
WrapText = true, Horizontal = HorizontalAlignmentValues.Center
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)4U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// style index 12: orange Open CAT 2
CellFormat cellFormat14 = new CellFormat(new Alignment()
{
WrapText = true
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)6U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// style index 13: yellow Open CAT 3
CellFormat cellFormat15 = new CellFormat(new Alignment()
{
WrapText = true
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)7U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// style index 14: orange Open CAT 2 centered
CellFormat cellFormat16 = new CellFormat(new Alignment()
{
WrapText = true, Horizontal = HorizontalAlignmentValues.Center
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)6U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// style index 15: yellow Open CAT 3 centered
CellFormat cellFormat17 = new CellFormat(new Alignment()
{
WrapText = true, Horizontal = HorizontalAlignmentValues.Center
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)7U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
// style index 16: yellow Open CAT 3 centered
CellFormat cellFormat18 = new CellFormat(new Alignment()
{
WrapText = true
})
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)8U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true
};
// add all these formats
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
cellFormats1.Append(cellFormat5);
cellFormats1.Append(cellFormat6);
cellFormats1.Append(cellFormat7);
cellFormats1.Append(cellFormat8);
cellFormats1.Append(cellFormat9);
cellFormats1.Append(cellFormat10);
cellFormats1.Append(cellFormat11);
cellFormats1.Append(cellFormat12);
cellFormats1.Append(cellFormat13);
cellFormats1.Append(cellFormat14);
cellFormats1.Append(cellFormat15);
cellFormats1.Append(cellFormat16);
cellFormats1.Append(cellFormat17);
cellFormats1.Append(cellFormat18);
CellStyles cellStyles1 = new CellStyles()
{
Count = (UInt32Value)1U
};
CellStyle cellStyle1 = new CellStyle()
{
Name = "Normal", FormatId = 0, BuiltinId = 0
};
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats()
{
Count = 0
};
TableStyles tableStyles1 = new TableStyles()
{
Count = 0, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16"
};
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
StylesheetExtension stylesheetExtension2 = new StylesheetExtension()
{
Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}"
};
stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheetExtensionList1.Append(stylesheetExtension2);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
return(stylesheet1);
}
// Generates content of workbookStylesPart1.
private void GenerateWorkbookStylesPart1Content(WorkbookStylesPart workbookStylesPart1)
{
Stylesheet stylesheet1 = new Stylesheet(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x14ac" } };
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
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 };
FontCharSet fontCharSet1 = new FontCharSet(){ Val = 162 };
FontScheme fontScheme2 = new FontScheme(){ Val = FontSchemeValues.Minor };
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontCharSet1);
font1.Append(fontScheme2);
Font font2 = new Font();
FontSize fontSize2 = new FontSize(){ Val = 6D };
FontName fontName2 = new FontName(){ Val = "Calibri" };
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering(){ Val = 3 };
FontCharSet fontCharSet2 = new FontCharSet(){ Val = 128 };
FontScheme fontScheme3 = new FontScheme(){ Val = FontSchemeValues.Minor };
font2.Append(fontSize2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontCharSet2);
font2.Append(fontScheme3);
fonts1.Append(font1);
fonts1.Append(font2);
Fills fills1 = new Fills(){ Count = (UInt32Value)2U };
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);
fills1.Append(fill1);
fills1.Append(fill2);
Borders borders1 = new Borders(){ Count = (UInt32Value)10U };
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);
Border border2 = new Border();
LeftBorder leftBorder2 = new LeftBorder(){ Style = BorderStyleValues.Thin };
Color color2 = new Color(){ Indexed = (UInt32Value)8U };
leftBorder2.Append(color2);
RightBorder rightBorder2 = new RightBorder();
TopBorder topBorder2 = new TopBorder(){ Style = BorderStyleValues.Thin };
Color color3 = new Color(){ Indexed = (UInt32Value)8U };
topBorder2.Append(color3);
BottomBorder bottomBorder2 = new BottomBorder();
DiagonalBorder diagonalBorder2 = new DiagonalBorder();
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
Border border3 = new Border();
LeftBorder leftBorder3 = new LeftBorder(){ Style = BorderStyleValues.Thin };
Color color4 = new Color(){ Indexed = (UInt32Value)65U };
leftBorder3.Append(color4);
RightBorder rightBorder3 = new RightBorder();
TopBorder topBorder3 = new TopBorder(){ Style = BorderStyleValues.Thin };
Color color5 = new Color(){ Indexed = (UInt32Value)8U };
topBorder3.Append(color5);
BottomBorder bottomBorder3 = new BottomBorder();
DiagonalBorder diagonalBorder3 = new DiagonalBorder();
border3.Append(leftBorder3);
border3.Append(rightBorder3);
border3.Append(topBorder3);
border3.Append(bottomBorder3);
border3.Append(diagonalBorder3);
Border border4 = new Border();
LeftBorder leftBorder4 = new LeftBorder(){ Style = BorderStyleValues.Thin };
Color color6 = new Color(){ Indexed = (UInt32Value)65U };
leftBorder4.Append(color6);
RightBorder rightBorder4 = new RightBorder(){ Style = BorderStyleValues.Thin };
Color color7 = new Color(){ Indexed = (UInt32Value)8U };
rightBorder4.Append(color7);
TopBorder topBorder4 = new TopBorder(){ Style = BorderStyleValues.Thin };
Color color8 = new Color(){ Indexed = (UInt32Value)8U };
topBorder4.Append(color8);
BottomBorder bottomBorder4 = new BottomBorder();
DiagonalBorder diagonalBorder4 = new DiagonalBorder();
border4.Append(leftBorder4);
border4.Append(rightBorder4);
border4.Append(topBorder4);
border4.Append(bottomBorder4);
border4.Append(diagonalBorder4);
Border border5 = new Border();
LeftBorder leftBorder5 = new LeftBorder(){ Style = BorderStyleValues.Thin };
Color color9 = new Color(){ Indexed = (UInt32Value)8U };
leftBorder5.Append(color9);
RightBorder rightBorder5 = new RightBorder();
TopBorder topBorder5 = new TopBorder(){ Style = BorderStyleValues.Thin };
Color color10 = new Color(){ Indexed = (UInt32Value)65U };
topBorder5.Append(color10);
BottomBorder bottomBorder5 = new BottomBorder();
DiagonalBorder diagonalBorder5 = new DiagonalBorder();
border5.Append(leftBorder5);
border5.Append(rightBorder5);
border5.Append(topBorder5);
border5.Append(bottomBorder5);
border5.Append(diagonalBorder5);
Border border6 = new Border();
LeftBorder leftBorder6 = new LeftBorder(){ Style = BorderStyleValues.Thin };
Color color11 = new Color(){ Indexed = (UInt32Value)65U };
leftBorder6.Append(color11);
RightBorder rightBorder6 = new RightBorder();
TopBorder topBorder6 = new TopBorder(){ Style = BorderStyleValues.Thin };
Color color12 = new Color(){ Indexed = (UInt32Value)65U };
topBorder6.Append(color12);
BottomBorder bottomBorder6 = new BottomBorder();
DiagonalBorder diagonalBorder6 = new DiagonalBorder();
border6.Append(leftBorder6);
border6.Append(rightBorder6);
border6.Append(topBorder6);
border6.Append(bottomBorder6);
border6.Append(diagonalBorder6);
Border border7 = new Border();
LeftBorder leftBorder7 = new LeftBorder(){ Style = BorderStyleValues.Thin };
Color color13 = new Color(){ Indexed = (UInt32Value)65U };
leftBorder7.Append(color13);
RightBorder rightBorder7 = new RightBorder(){ Style = BorderStyleValues.Thin };
Color color14 = new Color(){ Indexed = (UInt32Value)8U };
rightBorder7.Append(color14);
TopBorder topBorder7 = new TopBorder(){ Style = BorderStyleValues.Thin };
Color color15 = new Color(){ Indexed = (UInt32Value)65U };
topBorder7.Append(color15);
BottomBorder bottomBorder7 = new BottomBorder();
DiagonalBorder diagonalBorder7 = new DiagonalBorder();
border7.Append(leftBorder7);
border7.Append(rightBorder7);
border7.Append(topBorder7);
border7.Append(bottomBorder7);
border7.Append(diagonalBorder7);
Border border8 = new Border();
LeftBorder leftBorder8 = new LeftBorder(){ Style = BorderStyleValues.Thin };
Color color16 = new Color(){ Indexed = (UInt32Value)8U };
leftBorder8.Append(color16);
RightBorder rightBorder8 = new RightBorder();
TopBorder topBorder8 = new TopBorder(){ Style = BorderStyleValues.Thin };
Color color17 = new Color(){ Indexed = (UInt32Value)65U };
topBorder8.Append(color17);
BottomBorder bottomBorder8 = new BottomBorder(){ Style = BorderStyleValues.Thin };
Color color18 = new Color(){ Indexed = (UInt32Value)8U };
bottomBorder8.Append(color18);
DiagonalBorder diagonalBorder8 = new DiagonalBorder();
border8.Append(leftBorder8);
border8.Append(rightBorder8);
border8.Append(topBorder8);
border8.Append(bottomBorder8);
border8.Append(diagonalBorder8);
Border border9 = new Border();
LeftBorder leftBorder9 = new LeftBorder(){ Style = BorderStyleValues.Thin };
Color color19 = new Color(){ Indexed = (UInt32Value)65U };
leftBorder9.Append(color19);
RightBorder rightBorder9 = new RightBorder();
TopBorder topBorder9 = new TopBorder(){ Style = BorderStyleValues.Thin };
Color color20 = new Color(){ Indexed = (UInt32Value)65U };
topBorder9.Append(color20);
BottomBorder bottomBorder9 = new BottomBorder(){ Style = BorderStyleValues.Thin };
Color color21 = new Color(){ Indexed = (UInt32Value)8U };
bottomBorder9.Append(color21);
DiagonalBorder diagonalBorder9 = new DiagonalBorder();
border9.Append(leftBorder9);
border9.Append(rightBorder9);
border9.Append(topBorder9);
border9.Append(bottomBorder9);
border9.Append(diagonalBorder9);
Border border10 = new Border();
LeftBorder leftBorder10 = new LeftBorder(){ Style = BorderStyleValues.Thin };
Color color22 = new Color(){ Indexed = (UInt32Value)65U };
leftBorder10.Append(color22);
RightBorder rightBorder10 = new RightBorder(){ Style = BorderStyleValues.Thin };
Color color23 = new Color(){ Indexed = (UInt32Value)8U };
rightBorder10.Append(color23);
TopBorder topBorder10 = new TopBorder(){ Style = BorderStyleValues.Thin };
Color color24 = new Color(){ Indexed = (UInt32Value)65U };
topBorder10.Append(color24);
BottomBorder bottomBorder10 = new BottomBorder(){ Style = BorderStyleValues.Thin };
Color color25 = new Color(){ Indexed = (UInt32Value)8U };
bottomBorder10.Append(color25);
DiagonalBorder diagonalBorder10 = new DiagonalBorder();
border10.Append(leftBorder10);
border10.Append(rightBorder10);
border10.Append(topBorder10);
border10.Append(bottomBorder10);
border10.Append(diagonalBorder10);
borders1.Append(border1);
borders1.Append(border2);
borders1.Append(border3);
borders1.Append(border4);
borders1.Append(border5);
borders1.Append(border6);
borders1.Append(border7);
borders1.Append(border8);
borders1.Append(border9);
borders1.Append(border10);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats(){ Count = (UInt32Value)1U };
CellFormat cellFormat1 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats(){ Count = (UInt32Value)11U };
CellFormat cellFormat2 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
CellFormat cellFormat3 = new CellFormat(){ NumberFormatId = (UInt32Value)14U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true };
CellFormat cellFormat4 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyBorder = true };
CellFormat cellFormat5 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)2U, FormatId = (UInt32Value)0U, ApplyBorder = true };
CellFormat cellFormat6 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)3U, FormatId = (UInt32Value)0U, ApplyBorder = true };
CellFormat cellFormat7 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)4U, FormatId = (UInt32Value)0U, ApplyBorder = true };
CellFormat cellFormat8 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)5U, FormatId = (UInt32Value)0U, ApplyBorder = true };
CellFormat cellFormat9 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)6U, FormatId = (UInt32Value)0U, ApplyBorder = true };
CellFormat cellFormat10 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)7U, FormatId = (UInt32Value)0U, ApplyBorder = true };
CellFormat cellFormat11 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)8U, FormatId = (UInt32Value)0U, ApplyBorder = true };
CellFormat cellFormat12 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)9U, FormatId = (UInt32Value)0U, ApplyBorder = true };
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
cellFormats1.Append(cellFormat5);
cellFormats1.Append(cellFormat6);
cellFormats1.Append(cellFormat7);
cellFormats1.Append(cellFormat8);
cellFormats1.Append(cellFormat9);
cellFormats1.Append(cellFormat10);
cellFormats1.Append(cellFormat11);
cellFormats1.Append(cellFormat12);
CellStyles cellStyles1 = new CellStyles(){ Count = (UInt32Value)1U };
CellStyle cellStyle1 = new CellStyle(){ Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats(){ Count = (UInt32Value)1U };
DifferentialFormat differentialFormat1 = new DifferentialFormat();
NumberingFormat numberingFormat1 = new NumberingFormat(){ NumberFormatId = (UInt32Value)164U, FormatCode = "dd/mm/yyyy" };
differentialFormat1.Append(numberingFormat1);
differentialFormats1.Append(differentialFormat1);
TableStyles tableStyles1 = new TableStyles(){ Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16" };
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension(){ Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" };
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles(){ DefaultSlicerStyle = "SlicerStyleLight1" };
stylesheetExtension1.Append(slicerStyles1);
StylesheetExtension stylesheetExtension2 = new StylesheetExtension(){ Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}" };
stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles(){ DefaultTimelineStyle = "TimeSlicerStyleLight1" };
stylesheetExtension2.Append(timelineStyles1);
stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheetExtensionList1.Append(stylesheetExtension2);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
workbookStylesPart1.Stylesheet = stylesheet1;
}
public WorkbookStylesPart Do(WorkbookPart workbookPart, params XlsxCellStyle[] styles)
{
var fonts = styles.Select(x => new Font(
Map(x.FontStyle),
new FontSize {
Val = x.FontSize
},
new Color {
Rgb = x.FontCollorRgb
}, //{Theme = 1U}
new FontName {
Val = x.FontFamily
},
new FontFamilyNumbering {
Val = 2
},
new FontCharSet {
Val = 204
},
new FontScheme {
Val = FontSchemeValues.Minor
}
)).ToArray();
var fills = new[] {
new Fill(new PatternFill {
PatternType = PatternValues.None
}),
new Fill(new PatternFill {
PatternType = PatternValues.None
})
}.Concat(styles.Select(x => x.Fill == null
? new Fill(new PatternFill {
PatternType = PatternValues.None
})
: new Fill(new PatternFill(
new ForegroundColor {
Rgb = x.Fill.FillForegroundColorArgb
}, //{Theme = 1U}
new BackgroundColor {
Rgb = x.Fill.FillBackgroundColorArgb
} //{Theme = 1U}
)
{
PatternType = PatternValues.Solid
})
)).ToArray();
var borders = new OpenXmlElement[] {
new Border(
new LeftBorder(),
new RightBorder(),
new TopBorder(),
new BottomBorder(),
new DiagonalBorder()
),
new Border(
new LeftBorder(new Color {
Theme = 1U
})
{
Style = BorderStyleValues.Thin
},
new RightBorder(new Color {
Theme = 1U
})
{
Style = BorderStyleValues.Thin
},
new TopBorder(new Color {
Theme = 1U
})
{
Style = BorderStyleValues.Thin
},
new BottomBorder(new Color {
Theme = 1U
})
{
Style = BorderStyleValues.Thin
},
new DiagonalBorder(new Color {
Theme = 1U
})
{
Style = BorderStyleValues.Thin
}
),
new Border(
new LeftBorder(new Color {
Theme = 1U
})
{
Style = BorderStyleValues.Thick
},
new RightBorder(new Color {
Theme = 1U
})
{
Style = BorderStyleValues.Thin
},
new TopBorder(new Color {
Theme = 1U
})
{
Style = BorderStyleValues.Thin
},
new BottomBorder(new Color {
Theme = 1U
})
{
Style = BorderStyleValues.Thin
},
new DiagonalBorder(new Color {
Theme = 1U
})
{
Style = BorderStyleValues.Thin
}
)
}; // Color color4 = new Color(){ Indexed = (UInt32Value)64U };
var cellStyleFormats = new OpenXmlElement[] {
new CellFormat()
{
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U
}
};
var cellFormats = styles.Select((x, index) => new CellFormat(
new Alignment {
Horizontal = x.HorizontalAlignment,
Vertical = x.VerticalAlignment,
WrapText = x.WrapText
})
{
NumberFormatId = 0U,
FontId = (uint)index,
FillId = (uint)index + 2,
BorderId = 0U,
FormatId = 0U,
ApplyFont = true,
ApplyAlignment = true
}
).ToArray();
var cellStyles = new OpenXmlElement[] {
new CellStyle {
Name = "ќбычный", FormatId = 0U, BuiltinId = 0U
}
};
var differentialFormats = new OpenXmlElement[] { };
var tableStyles = new OpenXmlElement[] { };
var slicerStyleExtension = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
slicerStyleExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
slicerStyleExtension.AppendChild(new DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles {
DefaultSlicerStyle = "SlicerStyleLight1"
});
//var timelineStyleExtension = new StylesheetExtension() { Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}" };
//timelineStyleExtension.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
//timelineStyleExtension.AppendChild(new X15.TimelineStyles() { DefaultTimelineStyle = "TimeSlicerStyleLight1" });
var stylesheet = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
stylesheet.Append(
new Fonts(fonts)
{
Count = (uint)fonts.Count(), KnownFonts = true
},
new Fills(fills)
{
Count = (uint)fills.Count()
},
new Borders(borders)
{
Count = (uint)borders.Count()
},
new CellStyleFormats(cellStyleFormats)
{
Count = (uint)cellStyleFormats.Count()
},
new CellFormats(cellFormats)
{
Count = (uint)cellFormats.Count()
},
new CellStyles(cellStyles)
{
Count = (uint)cellStyles.Count()
},
new DifferentialFormats(differentialFormats)
{
Count = (uint)differentialFormats.Count()
},
new TableStyles(tableStyles)
{
Count = (uint)tableStyles.Count(),
DefaultTableStyle = "TableStyleMedium2",
DefaultPivotStyle = "PivotStyleMedium9"
},
new StylesheetExtensionList(
slicerStyleExtension //,
//timelineStyleExtension
)
);
var workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>();
workbookStylesPart.Stylesheet = stylesheet;
return(workbookStylesPart);
}
// Generates content of workbookStylesPart1.
private void GenerateWorkbookStylesPart1Content(WorkbookStylesPart workbookStylesPart1)
{
Stylesheet stylesheet1 = new Stylesheet(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x14ac" } };
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
NumberingFormats numberingFormats1 = new NumberingFormats(){ Count = (UInt32Value)1U };
NumberingFormat numberingFormat11 = new NumberingFormat(){ NumberFormatId = (UInt32Value)164U, FormatCode = "\"¥\"#,##0" };
numberingFormats1.Append(numberingFormat11);
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 = 6D };
FontName fontName2 = new FontName(){ Val = "Calibri" };
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering(){ Val = 3 };
FontCharSet fontCharSet1 = new FontCharSet(){ Val = 128 };
FontScheme fontScheme2 = new FontScheme(){ Val = FontSchemeValues.Minor };
font2.Append(fontSize2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontCharSet1);
font2.Append(fontScheme2);
fonts1.Append(font1);
fonts1.Append(font2);
Fills fills1 = new Fills(){ Count = (UInt32Value)2U };
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);
fills1.Append(fill1);
fills1.Append(fill2);
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)1U };
CellFormat cellFormat1 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats(){ Count = (UInt32Value)8U };
CellFormat cellFormat2 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
CellFormat cellFormat3 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyAlignment = true };
Alignment alignment1 = new Alignment(){ Vertical = VerticalAlignmentValues.Center };
cellFormat3.Append(alignment1);
CellFormat cellFormat4 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyAlignment = true };
Alignment alignment2 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center };
cellFormat4.Append(alignment2);
CellFormat cellFormat5 = new CellFormat(){ NumberFormatId = (UInt32Value)14U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyAlignment = true };
Alignment alignment3 = new Alignment(){ Vertical = VerticalAlignmentValues.Center };
cellFormat5.Append(alignment3);
CellFormat cellFormat6 = new CellFormat(){ NumberFormatId = (UInt32Value)164U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyAlignment = true };
Alignment alignment4 = new Alignment(){ Vertical = VerticalAlignmentValues.Center };
cellFormat6.Append(alignment4);
CellFormat cellFormat7 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, PivotButton = true };
CellFormat cellFormat8 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyAlignment = true };
Alignment alignment5 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Left };
cellFormat8.Append(alignment5);
CellFormat cellFormat9 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true };
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
cellFormats1.Append(cellFormat5);
cellFormats1.Append(cellFormat6);
cellFormats1.Append(cellFormat7);
cellFormats1.Append(cellFormat8);
cellFormats1.Append(cellFormat9);
CellStyles cellStyles1 = new CellStyles(){ Count = (UInt32Value)1U };
CellStyle cellStyle1 = new CellStyle(){ Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats(){ Count = (UInt32Value)14U };
DifferentialFormat differentialFormat1 = new DifferentialFormat();
Alignment alignment6 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center, TextRotation = (UInt32Value)0U, WrapText = false, Indent = (UInt32Value)0U, JustifyLastLine = false, ShrinkToFit = false, ReadingOrder = (UInt32Value)0U };
differentialFormat1.Append(alignment6);
DifferentialFormat differentialFormat2 = new DifferentialFormat();
NumberingFormat numberingFormat12 = new NumberingFormat(){ NumberFormatId = (UInt32Value)164U, FormatCode = "\"¥\"#,##0" };
differentialFormat2.Append(numberingFormat12);
DifferentialFormat differentialFormat3 = new DifferentialFormat();
Alignment alignment7 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center, TextRotation = (UInt32Value)0U, WrapText = false, Indent = (UInt32Value)0U, JustifyLastLine = false, ShrinkToFit = false, ReadingOrder = (UInt32Value)0U };
differentialFormat3.Append(alignment7);
DifferentialFormat differentialFormat4 = new DifferentialFormat();
NumberingFormat numberingFormat13 = new NumberingFormat(){ NumberFormatId = (UInt32Value)0U, FormatCode = "General" };
differentialFormat4.Append(numberingFormat13);
DifferentialFormat differentialFormat5 = new DifferentialFormat();
NumberingFormat numberingFormat14 = new NumberingFormat(){ NumberFormatId = (UInt32Value)165U, FormatCode = "yyyy/mm/dd" };
differentialFormat5.Append(numberingFormat14);
DifferentialFormat differentialFormat6 = new DifferentialFormat();
NumberingFormat numberingFormat15 = new NumberingFormat(){ NumberFormatId = (UInt32Value)0U, FormatCode = "General" };
differentialFormat6.Append(numberingFormat15);
DifferentialFormat differentialFormat7 = new DifferentialFormat();
NumberingFormat numberingFormat16 = new NumberingFormat(){ NumberFormatId = (UInt32Value)0U, FormatCode = "General" };
differentialFormat7.Append(numberingFormat16);
DifferentialFormat differentialFormat8 = new DifferentialFormat();
NumberingFormat numberingFormat17 = new NumberingFormat(){ NumberFormatId = (UInt32Value)0U, FormatCode = "General" };
differentialFormat8.Append(numberingFormat17);
DifferentialFormat differentialFormat9 = new DifferentialFormat();
NumberingFormat numberingFormat18 = new NumberingFormat(){ NumberFormatId = (UInt32Value)165U, FormatCode = "yyyy/mm/dd" };
differentialFormat9.Append(numberingFormat18);
DifferentialFormat differentialFormat10 = new DifferentialFormat();
Alignment alignment8 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center, TextRotation = (UInt32Value)0U, WrapText = false, Indent = (UInt32Value)0U, JustifyLastLine = false, ShrinkToFit = false, ReadingOrder = (UInt32Value)0U };
differentialFormat10.Append(alignment8);
DifferentialFormat differentialFormat11 = new DifferentialFormat();
NumberingFormat numberingFormat19 = new NumberingFormat(){ NumberFormatId = (UInt32Value)164U, FormatCode = "\"¥\"#,##0" };
differentialFormat11.Append(numberingFormat19);
DifferentialFormat differentialFormat12 = new DifferentialFormat();
Alignment alignment9 = new Alignment(){ Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center, TextRotation = (UInt32Value)0U, WrapText = false, Indent = (UInt32Value)0U, JustifyLastLine = false, ShrinkToFit = false, ReadingOrder = (UInt32Value)0U };
differentialFormat12.Append(alignment9);
DifferentialFormat differentialFormat13 = new DifferentialFormat();
NumberingFormat numberingFormat20 = new NumberingFormat(){ NumberFormatId = (UInt32Value)165U, FormatCode = "yyyy/mm/dd" };
differentialFormat13.Append(numberingFormat20);
DifferentialFormat differentialFormat14 = new DifferentialFormat();
NumberingFormat numberingFormat21 = new NumberingFormat(){ NumberFormatId = (UInt32Value)165U, FormatCode = "yyyy/mm/dd" };
differentialFormat14.Append(numberingFormat21);
differentialFormats1.Append(differentialFormat1);
differentialFormats1.Append(differentialFormat2);
differentialFormats1.Append(differentialFormat3);
differentialFormats1.Append(differentialFormat4);
differentialFormats1.Append(differentialFormat5);
differentialFormats1.Append(differentialFormat6);
differentialFormats1.Append(differentialFormat7);
differentialFormats1.Append(differentialFormat8);
differentialFormats1.Append(differentialFormat9);
differentialFormats1.Append(differentialFormat10);
differentialFormats1.Append(differentialFormat11);
differentialFormats1.Append(differentialFormat12);
differentialFormats1.Append(differentialFormat13);
differentialFormats1.Append(differentialFormat14);
TableStyles tableStyles1 = new TableStyles(){ Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16" };
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension(){ Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" };
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles(){ DefaultSlicerStyle = "SlicerStyleLight1" };
stylesheetExtension1.Append(slicerStyles1);
StylesheetExtension stylesheetExtension2 = new StylesheetExtension(){ Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}" };
stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles(){ DefaultTimelineStyle = "TimeSlicerStyleLight1" };
stylesheetExtension2.Append(timelineStyles1);
stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheetExtensionList1.Append(stylesheetExtension2);
stylesheet1.Append(numberingFormats1);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
workbookStylesPart1.Stylesheet = stylesheet1;
}
/// <summary>
/// Создает стилевую составляющую документа
/// </summary>
/// <param name="workbookStylesPart">Стилевая часть книги</param>
private void GenerateStyles(WorkbookStylesPart workbookStylesPart)
{
Stylesheet stylesheet = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
#region Fonts
Fonts fonts = new Fonts()
{
Count = 1U,
KnownFonts = true
};
Font font = new Font();
FontSize fontSize = new FontSize()
{
Val = 11D
};
Color color1 = new Color()
{
Theme = 1U
};
FontName fontName = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering = new FontFamilyNumbering()
{
Val = 2
};
FontCharSet fontCharSet = new FontCharSet()
{
Val = 204
};
FontScheme fontScheme = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font.Append(fontSize);
font.Append(color1);
font.Append(fontName);
font.Append(fontFamilyNumbering);
font.Append(fontCharSet);
font.Append(fontScheme);
fonts.Append(font);
#endregion
#region Fills
Fills fills = new Fills()
{
Count = 2U
};
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);
fills.Append(fill1);
fills.Append(fill2);
#endregion
#region Borders
Borders borders = new Borders()
{
Count = 1U
};
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder);
border1.Append(diagonalBorder1);
borders.Append(border1);
#endregion
#region Cell Styles And Formats
CellStyleFormats cellStyleFormats = new CellStyleFormats()
{
Count = 1U
};
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = 0U,
FontId = 0U,
FillId = 0U,
BorderId = 0U
};
cellStyleFormats.Append(cellFormat1);
CellFormats cellFormats = new CellFormats()
{
Count = 1U
};
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = 0U,
FontId = 0U,
FillId = 0U,
BorderId = 0U,
FormatId = 0U
};
cellFormats.Append(cellFormat2);
CellStyles cellStyles = new CellStyles()
{
Count = 1U
};
CellStyle cellStyle = new CellStyle()
{
Name = "Обычный",
FormatId = 0U,
BuiltinId = 0U
};
cellStyles.Append(cellStyle);
#endregion
DifferentialFormats differentialFormats = new DifferentialFormats()
{
Count = 0U
};
TableStyles tableStyles = new TableStyles()
{
Count = 0U,
DefaultTableStyle = "TableStyleMedium2",
DefaultPivotStyle = "PivotStyleLight16"
};
#region Stylesheet Extensions
StylesheetExtensionList stylesheetExtensionList = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension1.AddNamespaceDeclaration("x14",
"http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.SlicerStyles slicerStyles = new X14.SlicerStyles()
{
DefaultSlicerStyle = "SlicerStyleLight1"
};
stylesheetExtension1.Append(slicerStyles);
StylesheetExtension stylesheetExtension2 = new StylesheetExtension()
{
Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}"
};
stylesheetExtension2.AddNamespaceDeclaration("x15",
"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
OpenXmlUnknownElement unknownElement =
OpenXmlUnknownElement.CreateOpenXmlUnknownElement(
"<x15:timelineStyles defaultTimelineStyle=\"TimeSlicerStyleLight1\" xmlns:x15=\"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main\" />");
stylesheetExtension2.Append(unknownElement);
stylesheetExtensionList.Append(stylesheetExtension1);
stylesheetExtensionList.Append(stylesheetExtension2);
#endregion
stylesheet.Append(fonts);
stylesheet.Append(fills);
stylesheet.Append(borders);
stylesheet.Append(cellStyleFormats);
stylesheet.Append(cellFormats);
stylesheet.Append(cellStyles);
stylesheet.Append(differentialFormats);
stylesheet.Append(tableStyles);
stylesheet.Append(stylesheetExtensionList);
workbookStylesPart.Stylesheet = stylesheet;
}
// Generates content of workbookStylesPart1.
private void GenerateWorkbookStylesPart1Content(WorkbookStylesPart workbookStylesPart1)
{
Stylesheet stylesheet1 = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
Fonts fonts1 = new Fonts() { Count = (UInt32Value)5U, KnownFonts = true };
Font font1 = new Font();
FontSize fontSize3 = new FontSize() { Val = 11D };
Color color3 = new Color() { Theme = (UInt32Value)1U };
FontName fontName1 = new FontName() { Val = "Calibri" };
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };
FontScheme fontScheme2 = new FontScheme() { Val = FontSchemeValues.Minor };
font1.Append(fontSize3);
font1.Append(color3);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme2);
Font font2 = new Font();
Bold bold2 = new Bold();
Underline underline1 = new Underline();
FontSize fontSize4 = new FontSize() { Val = 11D };
Color color4 = new Color() { Rgb = "FFFF0000" };
FontName fontName2 = new FontName() { Val = "Calibri" };
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering() { Val = 2 };
FontScheme fontScheme3 = new FontScheme() { Val = FontSchemeValues.Minor };
font2.Append(bold2);
font2.Append(underline1);
font2.Append(fontSize4);
font2.Append(color4);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontScheme3);
Font font3 = new Font();
Underline underline2 = new Underline();
FontSize fontSize5 = new FontSize() { Val = 11D };
Color color5 = new Color() { Theme = (UInt32Value)10U };
FontName fontName3 = new FontName() { Val = "Calibri" };
FontFamilyNumbering fontFamilyNumbering3 = new FontFamilyNumbering() { Val = 2 };
FontScheme fontScheme4 = new FontScheme() { Val = FontSchemeValues.Minor };
font3.Append(underline2);
font3.Append(fontSize5);
font3.Append(color5);
font3.Append(fontName3);
font3.Append(fontFamilyNumbering3);
font3.Append(fontScheme4);
Font font4 = new Font();
FontSize fontSize6 = new FontSize() { Val = 9D };
Color color6 = new Color() { Indexed = (UInt32Value)81U };
FontName fontName4 = new FontName() { Val = "Tahoma" };
FontCharSet fontCharSet1 = new FontCharSet() { Val = 1 };
font4.Append(fontSize6);
font4.Append(color6);
font4.Append(fontName4);
font4.Append(fontCharSet1);
Font font5 = new Font();
Bold bold3 = new Bold();
FontSize fontSize7 = new FontSize() { Val = 9D };
Color color7 = new Color() { Indexed = (UInt32Value)81U };
FontName fontName5 = new FontName() { Val = "Tahoma" };
FontCharSet fontCharSet2 = new FontCharSet() { Val = 1 };
font5.Append(bold3);
font5.Append(fontSize7);
font5.Append(color7);
font5.Append(fontName5);
font5.Append(fontCharSet2);
fonts1.Append(font1);
fonts1.Append(font2);
fonts1.Append(font3);
fonts1.Append(font4);
fonts1.Append(font5);
Fills fills1 = new Fills() { Count = (UInt32Value)2U };
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);
fills1.Append(fill1);
fills1.Append(fill2);
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)2U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, ApplyNumberFormat = false, ApplyFill = false, ApplyBorder = false, ApplyAlignment = false, ApplyProtection = false };
cellStyleFormats1.Append(cellFormat1);
cellStyleFormats1.Append(cellFormat2);
CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)4U };
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)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true };
CellFormat cellFormat5 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyAlignment = true };
Alignment alignment1 = new Alignment() { WrapText = true };
cellFormat5.Append(alignment1);
CellFormat cellFormat6 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U };
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
cellFormats1.Append(cellFormat5);
cellFormats1.Append(cellFormat6);
CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)2U };
CellStyle cellStyle1 = new CellStyle() { Name = "Hyperlink", FormatId = (UInt32Value)1U, BuiltinId = (UInt32Value)8U };
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 = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9" };
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension() { Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" };
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles() { DefaultSlicerStyle = "SlicerStyleLight1" };
stylesheetExtension1.Append(slicerStyles1);
StylesheetExtension stylesheetExtension2 = new StylesheetExtension() { Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}" };
stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles() { DefaultTimelineStyle = "TimeSlicerStyleLight1" };
stylesheetExtension2.Append(timelineStyles1);
stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheetExtensionList1.Append(stylesheetExtension2);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
workbookStylesPart1.Stylesheet = stylesheet1;
}
/// <summary>
/// 样式
/// </summary>
/// <returns></returns>
private void GenerateWorkbookStylesPart(WorkbookStylesPart workbookStylesPart)
{
Stylesheet styleSheet = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac x16r2 xr"
}
};
styleSheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
styleSheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
styleSheet.AddNamespaceDeclaration("x16r2", "http://schemas.microsoft.com/office/spreadsheetml/2015/02/main");
styleSheet.AddNamespaceDeclaration("xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision");
// 字体
Fonts fonts = new Fonts(
// 0 默认
new Font(),
// 1 表头
new Font(
new Bold()
));
// 填充
Fills fills = new Fills(
new Fill(new PatternFill()
{
PatternType = PatternValues.None
}), // 0 默认
new Fill(new PatternFill()
{
PatternType = PatternValues.Gray125
}) // 0 默认
);
// 边框
Borders borders = new Borders(
// 0 默认，无边框
new Border(
new LeftBorder(),
new RightBorder(),
new TopBorder(),
new BottomBorder(),
new DiagonalBorder()),
new Border( // 1 四周边框
new LeftBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
},
new RightBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
},
new TopBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
},
new BottomBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
},
new DiagonalBorder())
);
// 表格总体样式格式
CellStyleFormats cellStyleFormats = new CellStyleFormats(
new CellFormat()
{
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0
}
);
// 表格格式
CellFormats cellFormats = new CellFormats(
new CellFormat()
{
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0
}, // 默认
new CellFormat()
{
NumberFormatId = 0, FontId = 1, FillId = 0, BorderId = 1, FormatId = 0, ApplyFont = true, ApplyBorder = true
}, // 表头
new CellFormat()
{
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 1, FormatId = 0, ApplyBorder = true
}, // 文本内容
new CellFormat()
{
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 1, FormatId = 0, ApplyNumberFormat = true, ApplyBorder = true
} // 数字内容
);
// 表格样式
CellStyles cellStyles = new CellStyles(
new CellStyle()
{
FormatId = 0, BuiltinId = 0
}
);
StylesheetExtensionList stylesheetExtensionList = new StylesheetExtensionList();
StylesheetExtension stylesheetExtensionX15 = new StylesheetExtension()
{
Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}"
};
stylesheetExtensionX15.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
OpenXmlUnknownElement openXmlUnknownElementX15 = OpenXmlUnknownElement.CreateOpenXmlUnknownElement("<x15:timelineStyles defaultTimelineStyle=\"TimeSlicerStyleLight1\" xmlns:x15=\"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main\" />");
stylesheetExtensionX15.Append(openXmlUnknownElementX15);
stylesheetExtensionList.Append(stylesheetExtensionX15);
styleSheet.Append(fonts);
styleSheet.Append(fills);
styleSheet.Append(borders);
styleSheet.Append(cellStyleFormats);
styleSheet.Append(cellFormats);
styleSheet.Append(cellStyles);
styleSheet.Append(stylesheetExtensionList);
workbookStylesPart.Stylesheet = styleSheet;
}
// Generates content of workbookStylesPart1.
private void GenerateWorkbookStylesPart1Content(WorkbookStylesPart workbookStylesPart1)
{
Stylesheet stylesheet1 = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
Fonts fonts1 = new Fonts() { Count = (UInt32Value)1U, KnownFonts = true };
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);
fonts1.Append(font1);
Fills fills1 = new Fills() { Count = (UInt32Value)2U };
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);
fills1.Append(fill1);
fills1.Append(fill2);
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)1U };
CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)1U };
CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
cellFormats1.Append(cellFormat2);
CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U };
CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)0U };
TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", 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;
}
private static Stylesheet GenerateStyleSheet()
{
Stylesheet stylesheet = new Stylesheet();
stylesheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
stylesheet.AddNamespaceDeclaration("x16r2", "http://schemas.microsoft.com/office/spreadsheetml/2015/02/main");
stylesheet.AddNamespaceDeclaration("xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision");
var fonts = new Fonts()
{
Count = 2U
};
var fills = new Fills()
{
Count = 5U
};
var borders = new Borders()
{
Count = 1
};
var cellFormats = new CellFormats()
{
Count = 4U
};
// Create Default Row Font : Verdana Black 12
Font rowFont = new Font();
rowFont.Append(new FontSize()
{
Val = 12D
});
rowFont.Append(new Color()
{
Rgb = "FF000000"
});
rowFont.Append(new FontName()
{
Val = "Verdana"
});
// Create Header Font : Calibri White 16 Bold
Font headerFont = new Font();
headerFont.Append(new Bold());
headerFont.Append(new FontSize()
{
Val = 16D
});
headerFont.Append(new Color()
{
Rgb = "FFFFFFFF"
});
headerFont.Append(new FontName()
{
Val = "Calibri"
});
fonts.Append(rowFont);
fonts.Append(headerFont);
// Create Header Fill : Dark Grey
PatternFill headerPatternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor {
Rgb = "FF4D4D4D"
},
BackgroundColor = new BackgroundColor {
Indexed = 64
}
};
// Create Row Odd Fill : Light Grey
PatternFill oddRowPatternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor {
Rgb = "FFEAEAEA"
},
BackgroundColor = new BackgroundColor {
Indexed = 64
}
};
// Create Row Even Fill : White
PatternFill evenRowPatternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor {
Rgb = "FFFFFFFF"
},
BackgroundColor = new BackgroundColor {
Indexed = 64
}
};
fills.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.None
}
}); // required
fills.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.Gray125
}
}); // required
fills.AppendChild(new Fill {
PatternFill = headerPatternFill
});
fills.AppendChild(new Fill {
PatternFill = oddRowPatternFill
});
fills.AppendChild(new Fill {
PatternFill = evenRowPatternFill
});
// Create default border
Border border1 = new Border();
border1.Append(new LeftBorder());
border1.Append(new RightBorder());
border1.Append(new TopBorder());
border1.Append(new BottomBorder());
border1.Append(new DiagonalBorder());
borders.Append(border1);
cellFormats.AppendChild(new CellFormat());
cellFormats.AppendChild(new CellFormat {
FontId = 1, FillId = 2, BorderId = 0, ApplyFill = true
}); // 1.header cell format
cellFormats.AppendChild(new CellFormat {
FontId = 0, FillId = 3, BorderId = 0, ApplyFill = true
}); // 2.odd row cell format
cellFormats.AppendChild(new CellFormat {
FontId = 0, FillId = 4, BorderId = 0, ApplyFill = true
}); // 3.even row cell format
stylesheet.Append(fonts);
stylesheet.Append(fills);
stylesheet.Append(borders);
stylesheet.Append(cellFormats);
return(stylesheet);
}
// Creates an Stylesheet instance and adds its children.
public static Stylesheet GenerateStylesheet()
{
var stylesheet1 = new Stylesheet {MCAttributes = new MarkupCompatibilityAttributes {Ignorable = "x14ac"}};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
var fonts1 = new Fonts {Count = 7U, KnownFonts = true};
var font1 = new Font();
var fontSize1 = new FontSize {Val = 11D};
var color1 = new Color {Theme = 1U};
var fontName1 = new FontName {Val = "Calibri"};
var fontFamilyNumbering1 = new FontFamilyNumbering {Val = 2};
var fontCharSet1 = new FontCharSet {Val = 204};
var fontScheme1 = new FontScheme {Val = FontSchemeValues.Minor};
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontCharSet1);
font1.Append(fontScheme1);
var font2 = new Font();
var bold1 = new Bold();
var fontSize2 = new FontSize {Val = 11D};
var color2 = new Color {Theme = 1U};
var fontName2 = new FontName {Val = "Calibri"};
var fontFamilyNumbering2 = new FontFamilyNumbering {Val = 2};
var fontCharSet2 = new FontCharSet {Val = 204};
var fontScheme2 = new FontScheme {Val = FontSchemeValues.Minor};
font2.Append(bold1);
font2.Append(fontSize2);
font2.Append(color2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontCharSet2);
font2.Append(fontScheme2);
var font3 = new Font();
var bold2 = new Bold();
var italic1 = new Italic();
var fontSize3 = new FontSize {Val = 11D};
var color3 = new Color {Theme = 1U};
var fontName3 = new FontName {Val = "Calibri"};
var fontFamilyNumbering3 = new FontFamilyNumbering {Val = 2};
var fontCharSet3 = new FontCharSet {Val = 204};
var fontScheme3 = new FontScheme {Val = FontSchemeValues.Minor};
font3.Append(bold2);
font3.Append(italic1);
font3.Append(fontSize3);
font3.Append(color3);
font3.Append(fontName3);
font3.Append(fontFamilyNumbering3);
font3.Append(fontCharSet3);
font3.Append(fontScheme3);
var font4 = new Font();
var bold3 = new Bold();
var fontSize4 = new FontSize {Val = 18D};
var color4 = new Color {Theme = 1U};
var fontName4 = new FontName {Val = "Calibri"};
var fontFamilyNumbering4 = new FontFamilyNumbering {Val = 2};
var fontCharSet4 = new FontCharSet {Val = 204};
var fontScheme4 = new FontScheme {Val = FontSchemeValues.Minor};
font4.Append(bold3);
font4.Append(fontSize4);
font4.Append(color4);
font4.Append(fontName4);
font4.Append(fontFamilyNumbering4);
font4.Append(fontCharSet4);
font4.Append(fontScheme4);
var font5 = new Font();
var bold4 = new Bold();
var fontSize5 = new FontSize {Val = 11D};
var fontName5 = new FontName {Val = "Calibri"};
var fontFamilyNumbering5 = new FontFamilyNumbering {Val = 2};
var fontCharSet5 = new FontCharSet {Val = 204};
var fontScheme5 = new FontScheme {Val = FontSchemeValues.Minor};
font5.Append(bold4);
font5.Append(fontSize5);
font5.Append(fontName5);
font5.Append(fontFamilyNumbering5);
font5.Append(fontCharSet5);
font5.Append(fontScheme5);
var font6 = new Font();
var fontSize6 = new FontSize {Val = 11D};
var fontName6 = new FontName {Val = "Calibri"};
var fontFamilyNumbering6 = new FontFamilyNumbering {Val = 2};
var fontCharSet6 = new FontCharSet {Val = 204};
var fontScheme6 = new FontScheme {Val = FontSchemeValues.Minor};
font6.Append(fontSize6);
font6.Append(fontName6);
font6.Append(fontFamilyNumbering6);
font6.Append(fontCharSet6);
font6.Append(fontScheme6);
var font7 = new Font();
var bold5 = new Bold();
var fontSize7 = new FontSize {Val = 12D};
var color5 = new Color {Theme = 1U};
var fontName7 = new FontName {Val = "Calibri"};
var fontFamilyNumbering7 = new FontFamilyNumbering {Val = 2};
var fontCharSet7 = new FontCharSet {Val = 204};
var fontScheme7 = new FontScheme {Val = FontSchemeValues.Minor};
font7.Append(bold5);
font7.Append(fontSize7);
font7.Append(color5);
font7.Append(fontName7);
font7.Append(fontFamilyNumbering7);
font7.Append(fontCharSet7);
font7.Append(fontScheme7);
fonts1.Append(font1);
fonts1.Append(font2);
fonts1.Append(font3);
fonts1.Append(font4);
fonts1.Append(font5);
fonts1.Append(font6);
fonts1.Append(font7);
var fills1 = new Fills {Count = 2U};
var fill1 = new Fill();
var patternFill1 = new PatternFill {PatternType = PatternValues.None};
fill1.Append(patternFill1);
var fill2 = new Fill();
var patternFill2 = new PatternFill {PatternType = PatternValues.Gray125};
fill2.Append(patternFill2);
fills1.Append(fill1);
fills1.Append(fill2);
var borders1 = new Borders {Count = 2U};
var border1 = new Border();
var leftBorder1 = new LeftBorder();
var rightBorder1 = new RightBorder();
var topBorder1 = new TopBorder();
var bottomBorder1 = new BottomBorder();
var diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
var border2 = new Border();
var leftBorder2 = new LeftBorder {Style = BorderStyleValues.Thin};
var color6 = new Color {Indexed = 64U};
leftBorder2.Append(color6);
var rightBorder2 = new RightBorder {Style = BorderStyleValues.Thin};
var color7 = new Color {Indexed = 64U};
rightBorder2.Append(color7);
var topBorder2 = new TopBorder {Style = BorderStyleValues.Thin};
var color8 = new Color {Indexed = 64U};
topBorder2.Append(color8);
var bottomBorder2 = new BottomBorder {Style = BorderStyleValues.Thin};
var color9 = new Color {Indexed = 64U};
bottomBorder2.Append(color9);
var diagonalBorder2 = new DiagonalBorder();
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
borders1.Append(border1);
borders1.Append(border2);
var cellStyleFormats1 = new CellStyleFormats {Count = 4U};
var cellFormat1 = new CellFormat {NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U};
var cellFormat2 = new CellFormat
{
NumberFormatId = 0U,
FontId = 5U,
FillId = 0U,
BorderId = 1U,
ApplyNumberFormat = false,
ApplyAlignment = false,
ApplyProtection = false
};
var cellFormat3 = new CellFormat
{
NumberFormatId = 0U,
FontId = 4U,
FillId = 0U,
BorderId = 1U,
ApplyNumberFormat = false,
ApplyAlignment = false,
ApplyProtection = false
};
var cellFormat4 = new CellFormat {NumberFormatId = 0U, FontId = 6U, FillId = 0U, BorderId = 1U};
cellStyleFormats1.Append(cellFormat1);
cellStyleFormats1.Append(cellFormat2);
cellStyleFormats1.Append(cellFormat3);
cellStyleFormats1.Append(cellFormat4);
var cellFormats1 = new CellFormats {Count = 5U};
var cellFormat5 = new CellFormat
{
NumberFormatId = 0U,
FontId = 0U,
FillId = 0U,
BorderId = 0U,
FormatId = 0U
};
var cellFormat6 = new CellFormat
{
NumberFormatId = 0U,
FontId = 1U,
FillId = 0U,
BorderId = 0U,
FormatId = 0U,
ApplyFont = true
};
var cellFormat7 = new CellFormat
{
NumberFormatId = 0U,
FontId = 2U,
FillId = 0U,
BorderId = 0U,
FormatId = 0U,
ApplyFont = true,
ApplyBorder = true
};
var cellFormat8 = new CellFormat
{
NumberFormatId = 0U,
FontId = 0U,
FillId = 0U,
BorderId = 0U,
FormatId = 0U,
ApplyAlignment = true
};
var cellFormat9 = new CellFormat
{
NumberFormatId = 0U,
FontId = 3U,
FillId = 0U,
BorderId = 0U,
FormatId = 0U,
ApplyFont = true,
ApplyAlignment = true
};
var alignment1 = new Alignment {Horizontal = HorizontalAlignmentValues.Center};
cellFormat9.Append(alignment1);
cellFormats1.Append(cellFormat5);
cellFormats1.Append(cellFormat6);
cellFormats1.Append(cellFormat7);
cellFormats1.Append(cellFormat8);
cellFormats1.Append(cellFormat9);
var cellStyles1 = new CellStyles {Count = 4U};
var cellStyle1 = new CellStyle {Name = "Custom", FormatId = 3U};
var cellStyle2 = new CellStyle {Name = "Нейтральный", FormatId = 2U, BuiltinId = 28U, CustomBuiltin = true};
var cellStyle3 = new CellStyle {Name = "Обычный", FormatId = 0U, BuiltinId = 0U};
var cellStyle4 = new CellStyle {Name = "Плохой", FormatId = 1U, BuiltinId = 27U, CustomBuiltin = true};
cellStyles1.Append(cellStyle1);
cellStyles1.Append(cellStyle2);
cellStyles1.Append(cellStyle3);
cellStyles1.Append(cellStyle4);
var differentialFormats1 = new DifferentialFormats {Count = 0U};
var tableStyles1 = new TableStyles
{
Count = 0U,
DefaultTableStyle = "TableStyleMedium2",
DefaultPivotStyle = "PivotStyleLight16"
};
var stylesheetExtensionList1 = new StylesheetExtensionList();
var stylesheetExtension1 = new StylesheetExtension {Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"};
stylesheetExtension1.AddNamespaceDeclaration("x14",
"http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
var slicerStyles1 = new X14.SlicerStyles {DefaultSlicerStyle = "SlicerStyleLight1"};
stylesheetExtension1.Append(slicerStyles1);
stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
return stylesheet1;
}
public static void AddStylesToWorkbookPart(WorkbookPart part)
{
var workbookStylesPart = part.AddNewPart <WorkbookStylesPart>("WorkbookStyles");
var stylesheet1 = new Stylesheet {
MCAttributes = new MarkupCompatibilityAttributes {
Ignorable = "x14ac"
}
};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
var fonts1 = new Fonts {
Count = 1U, KnownFonts = true
};
var font1 = new Font();
var fontSize1 = new FontSize {
Val = 11D
};
var color1 = new Color {
Theme = 1U
};
var fontName1 = new FontName {
Val = "Calibri"
};
var fontFamilyNumbering1 = new FontFamilyNumbering {
Val = 2
};
var fontScheme1 = new FontScheme {
Val = FontSchemeValues.Minor
};
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
fonts1.Append(font1);
var fills1 = new Fills {
Count = 2U
};
var fill1 = new Fill();
var patternFill1 = new PatternFill {
PatternType = PatternValues.None
};
fill1.Append(patternFill1);
var fill2 = new Fill();
var patternFill2 = new PatternFill {
PatternType = PatternValues.Gray125
};
fill2.Append(patternFill2);
fills1.Append(fill1);
fills1.Append(fill2);
var borders1 = new Borders {
Count = 1U
};
var border1 = new Border();
var leftBorder1 = new LeftBorder();
var rightBorder1 = new RightBorder();
var topBorder1 = new TopBorder();
var bottomBorder1 = new BottomBorder();
var diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
borders1.Append(border1);
var cellStyleFormats1 = new CellStyleFormats {
Count = 1U
};
var cellStyleFormat = new CellFormat {
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U
};
cellStyleFormats1.Append(cellStyleFormat);
var cellFormats = new CellFormats {
Count = 2U
};
// index 0 (default) => unlocked
var cellFormat0 = new CellFormat {
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U, FormatId = 0U, ApplyProtection = true
}.AppendItem(new Protection {
Locked = false
});
// index 1 => locked
var cellFormat1 = new CellFormat {
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U, FormatId = 0U
};
cellFormats.Append(cellFormat0);
cellFormats.Append(cellFormat1);
var cellStyles1 = new CellStyles {
Count = 1U
};
var cellStyle1 = new CellStyle {
Name = "Normal", FormatId = 0U, BuiltinId = 0U
};
cellStyles1.Append(cellStyle1);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats);
stylesheet1.Append(cellStyles1);
workbookStylesPart.Stylesheet = stylesheet1;
}
/// <summary>
/// Creates a basic stylesheet
/// Can be overriden for custom cell styles
/// </summary>
/// <returns></returns>
private Stylesheet AddStylesheet()
{
Stylesheet stylesheet = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
// create fonts
Fonts fonts = new Fonts()
{
Count = (UInt32Value)1U, KnownFonts = true
};
int fontId;
var simpleFont = GetFont(new BsGridExcelStyle(), out fontId);
var boldFont = GetFont(new BsGridExcelStyle()
{
Font = new BsGridExcelFont()
{
Bold = true
}
}, out fontId);
fonts.Append(simpleFont);
fonts.Append(boldFont);
// create fills
Fills fills = new Fills()
{
Count = (UInt32Value)1U
};
int fillId;
var noneFill = GetFill(new BsGridExcelStyle()
{
FillPattern = PatternValues.None
}, out fillId); //needed, reserved by excel
var greyFill = GetFill(new BsGridExcelStyle()
{
FillPattern = PatternValues.Gray125
}, out fillId); //needed, reserved by excel
fills.Append(noneFill);
fills.Append(greyFill);
// create borders
Borders borders = new Borders()
{
Count = (UInt32Value)1U
};
var border = ExcelHelpers.CreateBorder();
borders.Append(border);
// create cell style formats
CellStyleFormats cellStyleFormats = new CellStyleFormats()
{
Count = (UInt32Value)1U
};
CellFormat cellFormat = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U
};
cellStyleFormats.Append(cellFormat);
CellFormats cellFormats = new CellFormats()
{
Count = (UInt32Value)2U
};
int formatId;
CellFormat headerCellFormat = GetCellFormat(new BsGridExcelStyle
{
FontId = 1,
FillId = 0
}, out formatId);
CellFormat dataCellFormat = GetCellFormat(new BsGridExcelStyle
{
FontId = 0,
FillId = 0
}, out formatId);
cellFormats.Append(headerCellFormat);
cellFormats.Append(dataCellFormat);
CellStyles cellStyles = new CellStyles()
{
Count = (UInt32Value)1U
};
CellStyle cellStyle = new CellStyle()
{
Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U
};
cellStyles.Append(cellStyle);
// add to stylesheet
stylesheet.Append(fonts);
stylesheet.Append(fills);
stylesheet.Append(borders);
stylesheet.Append(cellStyleFormats);
stylesheet.Append(cellFormats);
stylesheet.Append(cellStyles);
return(stylesheet);
}
private void CreateWorkbookStylesPart(WorkbookStylesPart part)
{
Stylesheet stylesheet = new Stylesheet {
MCAttributes = new MarkupCompatibilityAttributes {
Ignorable = "x14ac"
}
};
stylesheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
Fonts fonts = new Fonts {
Count = 2, KnownFonts = true
};
Font font = new Font();
font.Append(new FontSize {
Val = 11
});
font.Append(new Color {
Theme = 1
});
font.Append(new FontName {
Val = "Calibri"
});
font.Append(new FontFamilyNumbering {
Val = 2
});
font.Append(new FontScheme {
Val = FontSchemeValues.Minor
});
fonts.Append(font);
Font fontHeader = new Font();
fontHeader.Append(new Bold());
fontHeader.Append(new FontSize {
Val = 11
});
fontHeader.Append(new Color {
Theme = 1
});
fontHeader.Append(new FontName {
Val = "Calibri"
});
fontHeader.Append(new FontFamilyNumbering {
Val = 2
});
fontHeader.Append(new FontScheme {
Val = FontSchemeValues.Minor
});
fonts.Append(fontHeader);
Fills fills = new Fills {
Count = 1
};
Fill fill1 = new Fill(new PatternFill {
PatternType = PatternValues.None
});
fills.Append(fill1);
Borders borders = new Borders {
Count = 1
};
Border border = new Border();
border.Append(new LeftBorder());
border.Append(new RightBorder());
border.Append(new TopBorder());
border.Append(new BottomBorder());
border.Append(new DiagonalBorder());
borders.Append(border);
// Numbers
NumberingFormats numberingFormats = new NumberingFormats {
Count = 0
};
for (int index = 0; index < Table.Columns.Count; index++)
{
var tableColumn = Table.Columns[index];
if (tableColumn.Format != null)
{
numberingFormats.Count++;
numberingFormats.Append(new NumberingFormat {
NumberFormatId = 164 + (uint)index, FormatCode = tableColumn.Format
});
}
}
CellStyleFormats cellStyleFormats = new CellStyleFormats {
Count = 2
};
cellStyleFormats.Append(new CellFormat {
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0
});
CellFormats cellFormats = new CellFormats {
Count = 3
};
// Default
cellFormats.Append(new CellFormat {
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0
});
// Date
cellFormats.Append(new CellFormat {
NumberFormatId = 14, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0, ApplyNumberFormat = true
});
// Header
cellFormats.Append(new CellFormat {
NumberFormatId = 0, FontId = 1, FillId = 0, BorderId = 0, FormatId = 0, ApplyFont = true
});
// Columns
for (int index = 0; index < Table.Columns.Count; index++)
{
var tableColumn = Table.Columns[index];
_styleIndex[tableColumn] = cellFormats.Count;
if (tableColumn.Format != null)
{
cellFormats.Append(new CellFormat {
NumberFormatId = 164 + (uint)index, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0, ApplyNumberFormat = true
});
}
else
{
cellFormats.Append(new CellFormat {
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0
});
}
cellFormats.Count++;
}
CellStyles cellStyles = new CellStyles {
Count = 1
};
CellStyle cellStyle1 = new CellStyle {
Name = "Normal", FormatId = 0, BuiltinId = 0
};
cellStyles.Append(cellStyle1);
DifferentialFormats differentialFormats = new DifferentialFormats {
Count = 0
};
TableStyles tableStyles = new TableStyles {
Count = 0, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16"
};
StylesheetExtensionList stylesheetExtensionList = new StylesheetExtensionList();
stylesheet.Append(numberingFormats);
stylesheet.Append(fonts);
stylesheet.Append(fills);
stylesheet.Append(borders);
stylesheet.Append(cellStyleFormats);
stylesheet.Append(cellFormats);
stylesheet.Append(cellStyles);
stylesheet.Append(differentialFormats);
stylesheet.Append(tableStyles);
stylesheet.Append(stylesheetExtensionList);
part.Stylesheet = stylesheet;
}
// Creates an Stylesheet instance and adds its children.
public Stylesheet GenerateStylesheet()
{
Stylesheet stylesheet1 = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
Fonts fonts1 = new Fonts() { Count = (UInt32Value)1U };
Font font1 = new Font();
FontSize fontSize1 = new FontSize() { Val = 11D };
FontName fontName1 = new FontName() { Val = "Calibri" };
font1.Append(fontSize1);
font1.Append(fontName1);
fonts1.Append(font1);
Fills fills1 = new Fills() { Count = (UInt32Value)2U };
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);
fills1.Append(fill1);
fills1.Append(fill2);
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);
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() { Style = BorderStyleValues.Thin };
Color color5 = new Color() { Indexed = (UInt32Value)64U };
diagonalBorder2.Append(color5);
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
borders1.Append(border1);
borders1.Append(border2);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)1U };
CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)1U };
CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
cellFormats1.Append(cellFormat2);
CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U };
CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)0U };
TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16" };
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension() { Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" };
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles slicerStyles1 = new DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles() { DefaultSlicerStyle = "SlicerStyleLight1" };
stylesheetExtension1.Append(slicerStyles1);
stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
return stylesheet1;
}
public static Stylesheet CreateStylesheet(WorkbookStylesPart workbookStylesPart)
{
var stylesheet = new Stylesheet { MCAttributes = new MarkupCompatibilityAttributes { Ignorable = "x14ac" } };
stylesheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
workbookStylesPart.Stylesheet = stylesheet;
return stylesheet;
}
private void CreateStylesheet(WorkbookPart workbookPart)
{
var workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>("rId101");
var stylesheet = new Stylesheet {
MCAttributes = new MarkupCompatibilityAttributes {
Ignorable = "x14ac"
}
};
stylesheet.AddNamespaceDeclaration("mc", MC_SCHEMA);
stylesheet.AddNamespaceDeclaration("x14ac", X14_AC_SCHEMA);
var numberingFormats = new NumberingFormats(new NumberingFormat
{
NumberFormatId = 44U,
FormatCode = FORMAT_CODE
})
{
Count = 1U
};
var fonts = new Fonts {
Count = 3U, KnownFonts = true
};
var font1 = new Font(new FontSize {
Val = 11D
}, new Color {
Theme = 1U
}, new FontName {
Val = "Calibri"
},
new FontFamilyNumbering {
Val = 2
}, new FontScheme {
Val = FontSchemeValues.Minor
});
var font2 = new Font(new FontSize {
Val = 11D
}, new Color {
Theme = 1U
}, new FontName {
Val = "Calibri"
},
new FontFamilyNumbering {
Val = 2
}, new FontScheme {
Val = FontSchemeValues.Minor
});
var font3 = new Font(new Bold(), new FontSize {
Val = 11D
}, new Color {
Theme = 0U
},
new FontName {
Val = "Calibri"
}, new FontFamilyNumbering {
Val = 2
},
new FontScheme {
Val = FontSchemeValues.Minor
});
fonts.Append(font1, font2, font3);
var fills = new Fills {
Count = 3U
};
var fill1 = new Fill(new PatternFill {
PatternType = PatternValues.None
});
var fill2 = new Fill(new PatternFill {
PatternType = PatternValues.Gray125
});
var fill3 = new Fill(new PatternFill(
new ForegroundColor {
Rgb = "FF0070C0"
},
new BackgroundColor {
Indexed = 64U
})
{
PatternType = PatternValues.Solid
});
fills.Append(fill1, fill2, fill3);
var borders = new Borders(new Border(), new LeftBorder(), new RightBorder(), new TopBorder(),
new BottomBorder(), new DiagonalBorder())
{
Count = 1U
};
var cellStyleFormats = new CellStyleFormats {
Count = 2U
};
var cellFormat1 = new CellFormat {
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U
};
var cellFormat2 = new CellFormat
{
NumberFormatId = 44U,
FontId = 1U,
FillId = 0U,
BorderId = 0U,
ApplyFont = false,
ApplyFill = false,
ApplyBorder = false,
ApplyAlignment = false,
ApplyProtection = false
};
cellStyleFormats.Append(cellFormat1, cellFormat2);
var cellFormats = new CellFormats {
Count = 6U
};
var cellFormat3 = new CellFormat
{
NumberFormatId = 0U,
FontId = 0U,
FillId = 0U,
BorderId = 0U,
FormatId = 0U
};
var cellFormat4 = new CellFormat
{
NumberFormatId = 0U,
FontId = 0U,
FillId = 0U,
BorderId = 0U,
FormatId = 0U,
ApplyProtection = true
};
var protection1 = new Protection {
Locked = false
};
cellFormat4.AppendChild(protection1);
var cellFormat5 = new CellFormat
{
NumberFormatId = 44U,
FontId = 0U,
FillId = 0U,
BorderId = 0U,
FormatId = 1U,
ApplyFont = true,
ApplyProtection = true
};
var protection2 = new Protection {
Locked = false
};
cellFormat5.AppendChild(protection2);
var cellFormat6 = new CellFormat
{
NumberFormatId = 14U,
FontId = 0U,
FillId = 0U,
BorderId = 0U,
FormatId = 0U,
ApplyNumberFormat = true,
ApplyProtection = true
};
var protection3 = new Protection {
Locked = false
};
cellFormat6.AppendChild(protection3);
var cellFormat7 = new CellFormat
{
NumberFormatId = 0U,
FontId = 2U,
FillId = 2U,
BorderId = 0U,
FormatId = 0U,
ApplyFont = true,
ApplyFill = true,
ApplyProtection = true
};
var cellFormat8 = new CellFormat
{
NumberFormatId = 0U,
FontId = 0U,
FillId = 0U,
BorderId = 0U,
FormatId = 0U,
ApplyProtection = true
};
cellFormats.Append(cellFormat3, cellFormat4, cellFormat5, cellFormat6, cellFormat7, cellFormat8);
var cellStyles = new CellStyles {
Count = 2U
};
var cellStyle1 = new CellStyle {
Name = "Currency", FormatId = 1U, BuiltinId = 4U
};
var cellStyle2 = new CellStyle {
Name = "Normal", FormatId = 0U, BuiltinId = 0U
};
cellStyles.Append(cellStyle1, cellStyle2);
var differentialFormats = new DifferentialFormats {
Count = 0U
};
var tableStyles = new TableStyles
{
Count = 0U,
DefaultTableStyle = "TableStyleMedium2",
DefaultPivotStyle = "PivotStyleLight16"
};
var stylesheetExtensionList = new StylesheetExtensionList();
var stylesheetExtension = new StylesheetExtension {
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension.AddNamespaceDeclaration("x14",
X14_SCHEMA);
var slicerStyles = new X14.SlicerStyles {
DefaultSlicerStyle = "SlicerStyleLight1"
};
stylesheetExtension.AppendChild(slicerStyles);
stylesheetExtensionList.AppendChild(stylesheetExtension);
stylesheet.Append(numberingFormats, fonts, fills, borders, cellStyleFormats, cellFormats, cellStyles,
differentialFormats, tableStyles, stylesheetExtensionList);
workbookStylesPart.Stylesheet = stylesheet;
}