/// <summary>
/// Create an stylesheet to use in excel files
/// </summary>
/// <returns></returns>
private static Stylesheet VocStyleSheet()
{
Stylesheet styleSheet = new Stylesheet();
Fonts fonts = new Fonts();
// 0 - normal fonts
DocumentFormat.OpenXml.Spreadsheet.Font myFont = new DocumentFormat.OpenXml.Spreadsheet.Font()
{
FontSize = new FontSize() { Val = 11 },
Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
FontName = new FontName() { Val = "Calibri" }
};
fonts.Append(myFont);
//1 - font bold
myFont = new DocumentFormat.OpenXml.Spreadsheet.Font()
{
Bold = new Bold(),
FontSize = new FontSize() { Val = 11 },
Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
FontName = new FontName() { Val = "Calibri" }
};
fonts.Append(myFont);
//2 - font title
myFont = new DocumentFormat.OpenXml.Spreadsheet.Font()
{
Bold = new Bold(),
FontSize = new FontSize() { Val = 20 },
Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = "FFFFFF" } },
FontName = new FontName() { Val = "Verdana" }
};
fonts.Append(myFont);
//3 - font bold
myFont = new DocumentFormat.OpenXml.Spreadsheet.Font()
{
Bold = new Bold(),
FontSize = new FontSize() { Val = 16 },
Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
FontName = new FontName() { Val = "Calibri" }
};
fonts.Append(myFont);
//4 - small font white
myFont = new DocumentFormat.OpenXml.Spreadsheet.Font()
{
FontSize = new FontSize() { Val = 11 },
Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = "FFFFFF" } },
FontName = new FontName() { Val = "Calibri" }
};
fonts.Append(myFont);
Fills fills = new Fills();
//default fill
Fill fill = new Fill()
{
PatternFill = new PatternFill() { PatternType = PatternValues.None }
};
fills.Append(fill);
//default fill
fill = new Fill()
{
PatternFill = new PatternFill() { PatternType = PatternValues.Gray125 }
};
fills.Append(fill);
//title fill
fill = new Fill()
{
PatternFill = new PatternFill()
{
ForegroundColor = new ForegroundColor()
{
Rgb = new HexBinaryValue() { Value = "499EB1" }
},
PatternType = PatternValues.Solid
}
};
fills.Append(fill);
Borders borders = new Borders();
//normal borders
Border border = new Border()
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
//borders applied
border = new Border()
{
LeftBorder = new LeftBorder() { Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }, Style = BorderStyleValues.Thin },
RightBorder = new RightBorder() { Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }, Style = BorderStyleValues.Thin },
TopBorder = new TopBorder() { Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }, Style = BorderStyleValues.Thin },
BottomBorder = new BottomBorder() { Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Auto = true }, Style = BorderStyleValues.Thin },
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
CellFormats cellFormats = new CellFormats();
//0- normal
CellFormat cellFormat = new CellFormat()
{
FontId = 0,
FillId = 0,
BorderId = 0,
ApplyFill = true
};
cellFormats.Append(cellFormat);
//1 - border
cellFormat = new CellFormat()
{
FontId = 0,
FillId = 0,
BorderId = 1,
Alignment = new Alignment() { WrapText = true },
ApplyFill = true
};
cellFormats.Append(cellFormat);
//2 - border and bold
cellFormat = new CellFormat()
{
FontId = 1,
FillId = 0,
BorderId = 1,
Alignment = new Alignment() { WrapText = true },
ApplyFill = true
};
cellFormats.Append(cellFormat);
//3 -title
cellFormat = new CellFormat()
{
FontId = 0,
FillId = 2,
BorderId = 0,
ApplyFill = true
};
cellFormats.Append(cellFormat);
//4 - title
cellFormat = new CellFormat()
{
FontId = 2,
FillId = 2,
BorderId = 0,
Alignment = new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Center },
ApplyFill = true
};
cellFormats.Append(cellFormat);
//5 - title
cellFormat = new CellFormat()
{
FontId = 2,
FillId = 2,
BorderId = 0,
Alignment = new Alignment() { Horizontal = HorizontalAlignmentValues.CenterContinuous, Vertical = VerticalAlignmentValues.Center },
ApplyFill = true
};
cellFormats.Append(cellFormat);
//6 - section title
cellFormat = new CellFormat()
{
FontId = 3,
FillId = 0,
BorderId = 1,
ApplyFill = true
};
cellFormats.Append(cellFormat);
//7 - section title filter values
cellFormat = new CellFormat()
{
FontId = 4,
FillId = 2,
BorderId = 0,
Alignment = new Alignment() { WrapText = false, Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Center },
ApplyFill = true
};
cellFormats.Append(cellFormat);
styleSheet.Append(fonts);
styleSheet.Append(fills);
styleSheet.Append(borders);
styleSheet.Append(cellFormats);
return styleSheet;
}
public static Font CreateFont(
Fonts fonts,
DoubleValue fontSize,
UInt32Value color,
StringValue fontName,
Int32Value fontFamilyNumbering,
Int32Value fontCharSet,
bool isBold,
bool isItalic,
bool isUnderline,
FontSchemeValues? fontScheme = null)
{
var font = new Font();
font.Append(new FontSize { Val = fontSize });
font.Append(new Color { Theme = color });
font.Append(new FontName { Val = fontName });
font.Append(new FontFamilyNumbering { Val = fontFamilyNumbering });
font.Append(new FontCharSet { Val = fontCharSet });
if (fontScheme.HasValue)
{
font.Append(new FontScheme { Val = fontScheme.Value });
}
if (isBold)
{
font.Append(new Bold());
}
if (isItalic)
{
font.Append(new Italic());
}
if (isUnderline)
{
font.Append(new Underline());
}
fonts.Append(font);
return font;
}
public CustomStylesheet()
{
Fonts fts = new Fonts();
Font ft = new Font();
FontName ftn = new FontName();
ftn.Val = StringValue.FromString("Calibri");
FontSize ftsz = new FontSize();
ftsz.Val = DoubleValue.FromDouble(11);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
ft = new Font();
ftn = new FontName();
ftn.Val = StringValue.FromString("Palatino Linotype");
ftsz = new FontSize();
ftsz.Val = DoubleValue.FromDouble(18);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
Fills fills = new Fills();
Fill fill;
PatternFill patternFill;
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.None;
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Gray125;
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Solid;
patternFill.ForegroundColor = new ForegroundColor();
patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString("00ff9728");
patternFill.BackgroundColor = new BackgroundColor();
patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
Borders borders = new Borders();
Border border = new Border();
border.LeftBorder = new LeftBorder();
border.RightBorder = new RightBorder();
border.TopBorder = new TopBorder();
border.BottomBorder = new BottomBorder();
border.DiagonalBorder = new DiagonalBorder();
borders.Append(border);
//Boarder Index 1
border = new Border();
border.LeftBorder = new LeftBorder();
border.LeftBorder.Style = BorderStyleValues.Thin;
border.RightBorder = new RightBorder();
border.RightBorder.Style = BorderStyleValues.Thin;
border.TopBorder = new TopBorder();
border.TopBorder.Style = BorderStyleValues.Thin;
border.BottomBorder = new BottomBorder();
border.BottomBorder.Style = BorderStyleValues.Thin;
border.DiagonalBorder = new DiagonalBorder();
borders.Append(border);
//Boarder Index 2
border = new Border();
border.LeftBorder = new LeftBorder();
border.RightBorder = new RightBorder();
border.TopBorder = new TopBorder();
border.TopBorder.Style = BorderStyleValues.Thin;
border.BottomBorder = new BottomBorder();
border.BottomBorder.Style = BorderStyleValues.Thin;
border.DiagonalBorder = new DiagonalBorder();
borders.Append(border);
borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
CellStyleFormats csfs = new CellStyleFormats();
CellFormat cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
csfs.Append(cf);
csfs.Count = UInt32Value.FromUInt32((uint)csfs.ChildElements.Count);
uint iExcelIndex = 164;
NumberingFormats nfs = new NumberingFormats();
CellFormats cfs = new CellFormats();
cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cfs.Append(cf);
NumberingFormat nfDateTime = new NumberingFormat();
nfDateTime.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfDateTime.FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss");
nfs.Append(nfDateTime);
NumberingFormat nf4decimal = new NumberingFormat();
nf4decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nf4decimal.FormatCode = StringValue.FromString("#,##0.0000");
nfs.Append(nf4decimal);
// #,##0.00 is also Excel style index 4
NumberingFormat nf2decimal = new NumberingFormat();
nf2decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nf2decimal.FormatCode = StringValue.FromString("#,##0.00");
nfs.Append(nf2decimal);
// @ is also Excel style index 49
NumberingFormat nfForcedText = new NumberingFormat();
nfForcedText.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfForcedText.FormatCode = StringValue.FromString("@");
nfs.Append(nfForcedText);
// index 1
// Format dd/mm/yyyy
cf = new CellFormat();
cf.NumberFormatId = 14;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 2
// Format #,##0.00
cf = new CellFormat();
cf.NumberFormatId = 4;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 3
cf = new CellFormat();
cf.NumberFormatId = nfDateTime.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 4
cf = new CellFormat();
cf.NumberFormatId = nf4decimal.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 5
cf = new CellFormat();
cf.NumberFormatId = nf2decimal.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 6
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 7
// Header text
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 1;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 8
// column text
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 1;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 9
// coloured 2 decimal text
cf = new CellFormat();
cf.NumberFormatId = nf2decimal.NumberFormatId;
cf.FontId = 0;
cf.FillId = 2;
cf.BorderId = 2;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 10
// coloured column text
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0;
cf.FillId = 2;
cf.BorderId = 2;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
nfs.Count = UInt32Value.FromUInt32((uint)nfs.ChildElements.Count);
cfs.Count = UInt32Value.FromUInt32((uint)cfs.ChildElements.Count);
this.Append(nfs);
this.Append(fts);
this.Append(fills);
this.Append(borders);
this.Append(csfs);
this.Append(cfs);
CellStyles css = new CellStyles();
CellStyle cs = new CellStyle();
cs.Name = StringValue.FromString("Normal");
cs.FormatId = 0;
cs.BuiltinId = 0;
css.Append(cs);
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
this.Append(css);
DifferentialFormats dfs = new DifferentialFormats();
dfs.Count = 0;
this.Append(dfs);
TableStyles tss = new TableStyles();
tss.Count = 0;
tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
this.Append(tss);
}
//описвывает форматы ячеек
protected void GenWorkbookStylesPart(WorkbookStylesPart workbookStylesPart)
{
Stylesheet stylesheet = new Stylesheet();
Fonts fonts1 = new Fonts();
Font font1 = new Font();
Font font2 = new Font();
FontSize fontSize2 = new FontSize() { Val = 11D };
Color color1 = new Color() { Rgb = "FF00B050" };
FontName fontName2 = new FontName() { Val = "Calibri" };
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };
FontCharSet fontCharSet1 = new FontCharSet() { Val = 204 };
font2.Append(fontSize2);
font2.Append(color1);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering1);
font2.Append(fontCharSet1);
//fonts1.Append(font2);
fonts1.Append(font1);
Fills fills1 = new Fills();
Fill fill1 = new Fill();
Fill fill2 = new Fill();
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill() { PatternType = PatternValues.Solid };
ForegroundColor foregroundColor1 = new ForegroundColor() { Rgb = "0000FF" };
patternFill3.Append(foregroundColor1);
fill3.Append(patternFill3);
fills1.Append(fill1);
fills1.Append(fill2);
fills1.Append(fill3);
Borders borders1 = new Borders();
Border border1 = new Border();
borders1.Append(border1);
//Переопределяем номера формата 164U, 166U, 167U, 168U,
NumberingFormats numberingFormats1 = new NumberingFormats() { Count = (UInt32Value)3U };
NumberingFormat numberingFormat1 = new NumberingFormat() { NumberFormatId = (UInt32Value)164U, FormatCode = "[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy" };
NumberingFormat numberingFormat2 = new NumberingFormat() { NumberFormatId = (UInt32Value)166U, FormatCode = "#,##0.000\"р.\"" };
NumberingFormat numberingFormat3 = new NumberingFormat() { NumberFormatId = (UInt32Value)167U, FormatCode = "#,##0\"р.\"" }; //"#,##0.00\"р.\"" };
NumberingFormat numberingFormat4 = new NumberingFormat() { NumberFormatId = (UInt32Value)168U, FormatCode = "#,##0.00\"р.\"" };
numberingFormats1.Append(numberingFormat1);
numberingFormats1.Append(numberingFormat2);
numberingFormats1.Append(numberingFormat3);
numberingFormats1.Append(numberingFormat4);
CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)6U };
CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U }; // Формат № 0 по умолчанию общий
CellFormat cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)164U }; // Формат № 1 для дат FormatCode = "[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy"
CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)166U }; //формат № 2 для денег по тысяч. ###,### <-- (number/ 1000)
CellFormat cellFormat5 = new CellFormat() { NumberFormatId = (UInt32Value)14U }; //формат № 3 для дат * 14.03.2015
CellFormat cellFormat6 = new CellFormat() { NumberFormatId = (UInt32Value)2U }; //формат № 4 для чисел
CellFormat cellFormat7 = new CellFormat() { NumberFormatId = (UInt32Value)1U }; //более подходящий формат № 5U для чисел (5U)
CellFormat cellFormat8 = new CellFormat() { NumberFormatId = (UInt32Value)165U };//формат №6U для денег по умолчанию
CellFormat cellFormat9 = new CellFormat() { NumberFormatId = (UInt32Value)167U };// формат №7U для денег(переопределенный) по три разряда ### ### р.
CellFormat cellFormat10 = new CellFormat() { NumberFormatId = (UInt32Value)168U };// формат №8U для денег(переопределенный) ### ###,00 р.
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);
stylesheet.Append(numberingFormats1);
stylesheet.Append(fonts1);
stylesheet.Append(fills1);
stylesheet.Append(borders1);
stylesheet.Append(cellFormats1);
workbookStylesPart.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);
}
// 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;
}
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;
}
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;
}
// 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 CreateStylesheet()
{
Stylesheet ss = new Stylesheet();
Fonts fts = new Fonts();
DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
FontName ftn = new FontName();
ftn.Val = "Calibri";
FontSize ftsz = new FontSize();
ftsz.Val = 11;
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
Fills fills = new Fills();
Fill fill;
PatternFill patternFill;
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.None;
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Gray125;
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = (uint)fills.ChildElements.Count;
Borders borders = new Borders();
Border border = new Border();
border.LeftBorder = new LeftBorder();
border.RightBorder = new RightBorder();
border.TopBorder = new TopBorder();
border.BottomBorder = new BottomBorder();
border.DiagonalBorder = new DiagonalBorder();
borders.Append(border);
borders.Count = (uint)borders.ChildElements.Count;
CellStyleFormats csfs = new CellStyleFormats();
CellFormat cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
csfs.Append(cf);
csfs.Count = (uint)csfs.ChildElements.Count;
uint iExcelIndex = 164;
NumberingFormats nfs = new NumberingFormats();
CellFormats cfs = new CellFormats();
cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cfs.Append(cf);
NumberingFormat nf;
nf = new NumberingFormat();
nf.NumberFormatId = iExcelIndex++;
nf.FormatCode = "dd/mm/yyyy hh:mm:ss";
nfs.Append(nf);
cf = new CellFormat();
cf.NumberFormatId = nf.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cfs.Append(cf);
nf = new NumberingFormat();
nf.NumberFormatId = iExcelIndex++;
nf.FormatCode = "#,##0.0000";
nfs.Append(nf);
cf = new CellFormat();
cf.NumberFormatId = nf.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cfs.Append(cf);
// #,##0.00 is also Excel style index 4
nf = new NumberingFormat();
nf.NumberFormatId = iExcelIndex++;
nf.FormatCode = "#,##0.00";
nfs.Append(nf);
cf = new CellFormat();
cf.NumberFormatId = nf.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cfs.Append(cf);
// @ is also Excel style index 49
nf = new NumberingFormat();
nf.NumberFormatId = iExcelIndex++;
nf.FormatCode = "@";
nfs.Append(nf);
cf = new CellFormat();
cf.NumberFormatId = nf.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = true;
cfs.Append(cf);
nfs.Count = (uint)nfs.ChildElements.Count;
cfs.Count = (uint)cfs.ChildElements.Count;
CellStyles css = new CellStyles();
CellStyle cs = new CellStyle();
cs.Name = "Normal";
cs.FormatId = 0;
cs.BuiltinId = 0;
css.Append(cs);
css.Count = (uint)css.ChildElements.Count;
DifferentialFormats dfs = new DifferentialFormats();
dfs.Count = 0;
TableStyles tss = new TableStyles();
tss.Count = 0;
tss.DefaultTableStyle = "TableStyleMedium9";
tss.DefaultPivotStyle = "PivotStyleLight16";
ss.Append(nfs);
ss.Append(fts);
ss.Append(fills);
ss.Append(borders);
ss.Append(csfs);
ss.Append(cfs);
ss.Append(css);
ss.Append(dfs);
return(ss);
}
// 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;
}
public static void GenerateFontTablePart1Content(FontTablePart fontTablePart1)
{
Fonts fonts1 = new Fonts()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "w14 w15 w16se w16cid"
}
};
fonts1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
fonts1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
fonts1.AddNamespaceDeclaration("w", "http://schemas.openxmlformats.org/wordprocessingml/2006/main");
fonts1.AddNamespaceDeclaration("w14", "http://schemas.microsoft.com/office/word/2010/wordml");
fonts1.AddNamespaceDeclaration("w15", "http://schemas.microsoft.com/office/word/2012/wordml");
fonts1.AddNamespaceDeclaration("w16cid", "http://schemas.microsoft.com/office/word/2016/wordml/cid");
fonts1.AddNamespaceDeclaration("w16se", "http://schemas.microsoft.com/office/word/2015/wordml/symex");
Font font1 = new Font()
{
Name = "Arial"
};
Panose1Number panose1Number1 = new Panose1Number()
{
Val = "020B0604020202020204"
};
FontCharSet fontCharSet1 = new FontCharSet()
{
Val = "BA"
};
FontFamily fontFamily1 = new FontFamily()
{
Val = FontFamilyValues.Swiss
};
Pitch pitch1 = new Pitch()
{
Val = FontPitchValues.Variable
};
FontSignature fontSignature1 = new FontSignature()
{
UnicodeSignature0 = "E0002EFF", UnicodeSignature1 = "C000785B", UnicodeSignature2 = "00000009", UnicodeSignature3 = "00000000", CodePageSignature0 = "000001FF", CodePageSignature1 = "00000000"
};
font1.Append(panose1Number1);
font1.Append(fontCharSet1);
font1.Append(fontFamily1);
font1.Append(pitch1);
font1.Append(fontSignature1);
Font font2 = new Font()
{
Name = "Times New Roman"
};
Panose1Number panose1Number2 = new Panose1Number()
{
Val = "02020603050405020304"
};
FontCharSet fontCharSet2 = new FontCharSet()
{
Val = "BA"
};
FontFamily fontFamily2 = new FontFamily()
{
Val = FontFamilyValues.Roman
};
Pitch pitch2 = new Pitch()
{
Val = FontPitchValues.Variable
};
FontSignature fontSignature2 = new FontSignature()
{
UnicodeSignature0 = "E0002EFF", UnicodeSignature1 = "C000785B", UnicodeSignature2 = "00000009", UnicodeSignature3 = "00000000", CodePageSignature0 = "000001FF", CodePageSignature1 = "00000000"
};
font2.Append(panose1Number2);
font2.Append(fontCharSet2);
font2.Append(fontFamily2);
font2.Append(pitch2);
font2.Append(fontSignature2);
Font font3 = new Font()
{
Name = "Wingdings"
};
Panose1Number panose1Number3 = new Panose1Number()
{
Val = "05000000000000000000"
};
FontCharSet fontCharSet3 = new FontCharSet()
{
Val = "02"
};
FontFamily fontFamily3 = new FontFamily()
{
Val = FontFamilyValues.Auto
};
Pitch pitch3 = new Pitch()
{
Val = FontPitchValues.Variable
};
FontSignature fontSignature3 = new FontSignature()
{
UnicodeSignature0 = "00000000", UnicodeSignature1 = "10000000", UnicodeSignature2 = "00000000", UnicodeSignature3 = "00000000", CodePageSignature0 = "80000000", CodePageSignature1 = "00000000"
};
font3.Append(panose1Number3);
font3.Append(fontCharSet3);
font3.Append(fontFamily3);
font3.Append(pitch3);
font3.Append(fontSignature3);
Font font4 = new Font()
{
Name = "Cambria"
};
Panose1Number panose1Number4 = new Panose1Number()
{
Val = "02040503050406030204"
};
FontCharSet fontCharSet4 = new FontCharSet()
{
Val = "BA"
};
FontFamily fontFamily4 = new FontFamily()
{
Val = FontFamilyValues.Roman
};
Pitch pitch4 = new Pitch()
{
Val = FontPitchValues.Variable
};
FontSignature fontSignature4 = new FontSignature()
{
UnicodeSignature0 = "E00006FF", UnicodeSignature1 = "420024FF", UnicodeSignature2 = "02000000", UnicodeSignature3 = "00000000", CodePageSignature0 = "0000019F", CodePageSignature1 = "00000000"
};
font4.Append(panose1Number4);
font4.Append(fontCharSet4);
font4.Append(fontFamily4);
font4.Append(pitch4);
font4.Append(fontSignature4);
Font font5 = new Font()
{
Name = "Calibri"
};
Panose1Number panose1Number5 = new Panose1Number()
{
Val = "020F0502020204030204"
};
FontCharSet fontCharSet5 = new FontCharSet()
{
Val = "BA"
};
FontFamily fontFamily5 = new FontFamily()
{
Val = FontFamilyValues.Swiss
};
Pitch pitch5 = new Pitch()
{
Val = FontPitchValues.Variable
};
FontSignature fontSignature5 = new FontSignature()
{
UnicodeSignature0 = "E0002AFF", UnicodeSignature1 = "C000247B", UnicodeSignature2 = "00000009", UnicodeSignature3 = "00000000", CodePageSignature0 = "000001FF", CodePageSignature1 = "00000000"
};
font5.Append(panose1Number5);
font5.Append(fontCharSet5);
font5.Append(fontFamily5);
font5.Append(pitch5);
font5.Append(fontSignature5);
fonts1.Append(font1);
fonts1.Append(font2);
fonts1.Append(font3);
fonts1.Append(font4);
fonts1.Append(font5);
fontTablePart1.Fonts = fonts1;
}
public NewExcel()
{
//创建默认字体样式1 Arial 24
var fonts = new Fonts();
var font = new DocumentFormat.OpenXml.Spreadsheet.Font();
var fontName = new FontName {
Val = StringValue.FromString("Arial")
};
var fontSize = new FontSize {
Val = DoubleValue.FromDouble(11)
};
font.FontName = fontName;
font.FontSize = fontSize;
fonts.Append(font);
fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);
//创建填充样式1
var fills = new Fills();
var fill = new Fill();
var patternFill = new PatternFill {
PatternType = PatternValues.None
};
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill {
PatternType = PatternValues.Gray0625
};
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
//创建线条样式1
var borders = new Borders();
var border = new Border {
LeftBorder = new LeftBorder(), RightBorder = new RightBorder(), TopBorder = new TopBorder(), BottomBorder = new BottomBorder(), DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
//创建单元格样式
var cellStyleFormats = new CellStyleFormats();
var cellFormat = new CellFormat
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0
};
cellStyleFormats.Append(cellFormat);
cellStyleFormats.Count = UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);
var cellFormats = new CellFormats();
cellFormat = new CellFormat
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0
};
cellFormats.Append(cellFormat);
cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);
this.Append(fonts);
this.Append(fills);
this.Append(borders);
this.Append(cellStyleFormats);
this.Append(cellFormats);
var css = new CellStyles();
var cs = new CellStyle
{
Name = StringValue.FromString("Normal"),
FormatId = 0,
BuiltinId = 0
};
css.Append(cs);
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
this.Append(css);
var dfs = new DifferentialFormats {
Count = 0
};
this.Append(dfs);
var tss = new TableStyles
{
Count = 0,
DefaultTableStyle = StringValue.FromString("TableStyleMedium9"),
DefaultPivotStyle = StringValue.FromString("PivotStyleLight16")
};
this.Append(tss);
}
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;
}
// 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 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;
}
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);
}
public static Stylesheet CreateStylesheet()
{
Stylesheet ss = new Stylesheet();
//Font 0, de base
Fonts fts = new Fonts();
Font ft = new Font();
FontName ftn = new FontName();
ftn.Val = "Calibri";
FontSize ftsz = new FontSize();
ftsz.Val = 11;
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
//Pour les couleurs de fond des cellules
Fills fills = new Fills();
Fill fill;
PatternFill patternFill;
//fill 0, de base, necessaire
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.None;
fill.PatternFill = patternFill;
fills.Append(fill);
////fill 1 de base, necessaire
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Gray125;
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = (uint)fills.ChildElements.Count;
//Border 0, de base, necessaire
Borders borders = new Borders();
Border border = new Border();
border.LeftBorder = new LeftBorder();
border.RightBorder = new RightBorder();
border.TopBorder = new TopBorder();
border.BottomBorder = new BottomBorder();
border.DiagonalBorder = new DiagonalBorder();
borders.Append(border);
borders.Count = (uint)borders.ChildElements.Count;
//164 : nbre de cellFormat implémenté dans excel, on ajoute donc les notres à la suite
NumberingFormats nfs = new NumberingFormats();
CellFormats cfs = new CellFormats();
//Cell par défault, semble ne pas compter dans l'index. Particulierement necessaire
CellStyleFormats csfs = new CellStyleFormats();
CellFormat cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
csfs.Append(cf);
csfs.Count = (uint)csfs.ChildElements.Count;
//CellFormat 0, Necessaire !
cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cfs.Append(cf);
nfs.Count = (uint)nfs.ChildElements.Count;
cfs.Count = (uint)cfs.ChildElements.Count;
ss.Append(nfs);
ss.Append(fts);
ss.Append(fills);
ss.Append(borders);
ss.Append(csfs);
ss.Append(cfs);
//A priori inutil mais a garder, peut être necessaire par la suite
CellStyles css = new CellStyles();
CellStyle cs = new CellStyle();
cs.Name = "Normal";
cs.FormatId = 0;
cs.BuiltinId = 0;
css.Append(cs);
css.Count = (uint)css.ChildElements.Count;
ss.Append(css);
DifferentialFormats dfs = new DifferentialFormats();
dfs.Count = 0;
ss.Append(dfs);
TableStyles tss = new TableStyles();
tss.Count = 0;
tss.DefaultTableStyle = "TableStyleMedium9";
tss.DefaultPivotStyle = "PivotStyleLight16";
ss.Append(tss);
return(ss);
}
public CustomStylesheet()
{
Fonts fts = new Fonts();
Font ft = new Font();
FontName ftn = new FontName();
ftn.Val = StringValue.FromString("Calibri");
FontSize ftsz = new FontSize();
ftsz.Val = DoubleValue.FromDouble(11);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
ft = new Font();
ftn = new FontName();
ftn.Val = StringValue.FromString("Palatino Linotype");
ftsz = new FontSize();
ftsz.Val = DoubleValue.FromDouble(18);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
Fills fills = new Fills();
Fill fill;
PatternFill patternFill;
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.None;
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Gray125;
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Solid;
patternFill.ForegroundColor = new ForegroundColor();
patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString("ffff00");
patternFill.BackgroundColor = new BackgroundColor();
patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
Borders borders = new Borders();
Border border = new Border();
border.LeftBorder = new LeftBorder();
border.RightBorder = new RightBorder();
border.TopBorder = new TopBorder();
border.BottomBorder = new BottomBorder();
border.DiagonalBorder = new DiagonalBorder();
borders.Append(border);
//Boarder Index 1
border = new Border();
border.LeftBorder = new LeftBorder();
border.LeftBorder.Style = BorderStyleValues.Thin;
border.RightBorder = new RightBorder();
border.RightBorder.Style = BorderStyleValues.Thin;
border.TopBorder = new TopBorder();
border.TopBorder.Style = BorderStyleValues.Thin;
border.BottomBorder = new BottomBorder();
border.BottomBorder.Style = BorderStyleValues.Thin;
border.DiagonalBorder = new DiagonalBorder();
borders.Append(border);
//Boarder Index 2
border = new Border();
border.LeftBorder = new LeftBorder();
border.RightBorder = new RightBorder();
border.TopBorder = new TopBorder();
border.TopBorder.Style = BorderStyleValues.Thin;
border.BottomBorder = new BottomBorder();
border.BottomBorder.Style = BorderStyleValues.Thin;
border.DiagonalBorder = new DiagonalBorder();
borders.Append(border);
borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
CellStyleFormats csfs = new CellStyleFormats();
CellFormat cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
csfs.Append(cf);
csfs.Count = UInt32Value.FromUInt32((uint)csfs.ChildElements.Count);
uint iExcelIndex = 164;
NumberingFormats nfs = new NumberingFormats();
CellFormats cfs = new CellFormats();
cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cfs.Append(cf);
NumberingFormat nfDateTime = new NumberingFormat();
nfDateTime.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfDateTime.FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss");
nfs.Append(nfDateTime);
NumberingFormat nf4decimal = new NumberingFormat();
nf4decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nf4decimal.FormatCode = StringValue.FromString("#,##0.0000");
nfs.Append(nf4decimal);
// #,##0.00 is also Excel style index 4
NumberingFormat nf2decimal = new NumberingFormat();
nf2decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nf2decimal.FormatCode = StringValue.FromString("#,##0.00");
nfs.Append(nf2decimal);
// @ is also Excel style index 49
NumberingFormat nfForcedText = new NumberingFormat();
nfForcedText.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfForcedText.FormatCode = StringValue.FromString("@");
nfs.Append(nfForcedText);
// index 1
// Format dd/mm/yyyy
cf = new CellFormat();
cf.NumberFormatId = 14;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 2
// Format #,##0.00
cf = new CellFormat();
cf.NumberFormatId = 4;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 3
cf = new CellFormat();
cf.NumberFormatId = nfDateTime.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 4
cf = new CellFormat();
cf.NumberFormatId = nf4decimal.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 5
cf = new CellFormat();
cf.NumberFormatId = nf2decimal.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 6
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 7
// Header text
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 1;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 8
// column text
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 1;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 9
// coloured 2 decimal text
cf = new CellFormat();
cf.NumberFormatId = nf2decimal.NumberFormatId;
cf.FontId = 0;
cf.FillId = 2;
cf.BorderId = 2;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 10
// coloured column text
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0;
cf.FillId = 2;
cf.BorderId = 2;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
nfs.Count = UInt32Value.FromUInt32((uint)nfs.ChildElements.Count);
cfs.Count = UInt32Value.FromUInt32((uint)cfs.ChildElements.Count);
this.Append(nfs);
this.Append(fts);
this.Append(fills);
this.Append(borders);
this.Append(csfs);
this.Append(cfs);
CellStyles css = new CellStyles();
CellStyle cs = new CellStyle();
cs.Name = StringValue.FromString("Normal");
cs.FormatId = 0;
cs.BuiltinId = 0;
css.Append(cs);
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
this.Append(css);
DifferentialFormats dfs = new DifferentialFormats();
dfs.Count = 0;
this.Append(dfs);
TableStyles tss = new TableStyles();
tss.Count = 0;
tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
this.Append(tss);
}
/// <summary>
/// create the default excel formats. These formats are required for the excel in order for it to render
/// correctly.
/// </summary>
/// <returns></returns>
public Stylesheet CreateDefaultStylesheet()
{
Stylesheet ss = new Stylesheet();
Fonts fts = new Fonts();
DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
FontName ftn = new FontName();
ftn.Val = "Calibri";
FontSize ftsz = new FontSize();
ftsz.Val = 11;
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
Fills fills = new Fills();
Fill fill;
PatternFill patternFill;
//default fills used by Excel, don't changes these
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.None;
fill.PatternFill = patternFill;
fills.AppendChild(fill);
fill = new Fill();
patternFill = new PatternFill();
patternFill.PatternType = PatternValues.Gray125;
fill.PatternFill = patternFill;
fills.AppendChild(fill);
fills.Count = (uint)fills.ChildElements.Count;
Borders borders = new Borders();
Border border = new Border();
border.LeftBorder = new LeftBorder();
border.RightBorder = new RightBorder();
border.TopBorder = new TopBorder();
border.BottomBorder = new BottomBorder();
border.DiagonalBorder = new DiagonalBorder();
borders.Append(border);
borders.Count = (uint)borders.ChildElements.Count;
CellStyleFormats csfs = new CellStyleFormats();
CellFormat cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
csfs.Append(cf);
csfs.Count = (uint)csfs.ChildElements.Count;
CellFormats cfs = new CellFormats();
cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cfs.Append(cf);
var nfs = new NumberingFormats();
nfs.Count = (uint)nfs.ChildElements.Count;
cfs.Count = (uint)cfs.ChildElements.Count;
ss.Append(nfs);
ss.Append(fts);
ss.Append(fills);
ss.Append(borders);
ss.Append(csfs);
ss.Append(cfs);
CellStyles css = new CellStyles(
new CellStyle()
{
Name = "Normal",
FormatId = 0,
BuiltinId = 0,
}
);
css.Count = (uint)css.ChildElements.Count;
ss.Append(css);
DifferentialFormats dfs = new DifferentialFormats();
dfs.Count = 0;
ss.Append(dfs);
TableStyles tss = new TableStyles();
tss.Count = 0;
tss.DefaultTableStyle = "TableStyleMedium9";
tss.DefaultPivotStyle = "PivotStyleLight16";
ss.Append(tss);
return ss;
}
private void CreateWorkBookStylesPart(WorkbookStylesPart workbookStylesPart)
{
Stylesheet styleSheet = new Stylesheet();
Fonts fonts = new Fonts();
fonts.Append(new DocumentFormat.OpenXml.Spreadsheet.Font()
{
FontName = new FontName()
{
Val = "Calibri"
},
FontSize = new FontSize()
{
Val = 11
},
FontFamilyNumbering = new FontFamilyNumbering()
{
Val = 2
},
});
fonts.Count = (uint)fonts.ChildElements.Count;
Fills fills = new Fills();
fills.Append(new Fill()
{
PatternFill = new PatternFill()
{
PatternType = PatternValues.None
}
});
fills.Count = (uint)fills.ChildElements.Count;
// Create "borders" node.
Borders borders = new Borders();
borders.Append(new Border()
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
});
borders.Count = (uint)borders.ChildElements.Count;
// Create "cellStyleXfs" node.
var CellStyleFormats = new CellStyleFormats();
CellStyleFormats.Append(new CellFormat()
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0
});
CellStyleFormats.Count = (uint)CellStyleFormats.ChildElements.Count;
// Create "cellXfs" node.
var CellFormats = new CellFormats();
CellFormats.Append(new CellFormat()
{
BorderId = 0,
FillId = 0,
FontId = 0,
FormatId = 0,
NumberFormatId = 0,
//ApplyNumberFormat = true
});
CellFormats.Count = (uint)CellFormats.ChildElements.Count;
// Create "cellStyles" node.
var CellStyles = new CellStyles();
CellStyles.Append(new CellStyle()
{
Name = "Normal",
FormatId = 0,
BuiltinId = 0
});
CellStyles.Count = (uint)CellStyles.ChildElements.Count;
//// Append all nodes in order.
styleSheet.Append(fonts);
styleSheet.Append(fills);
styleSheet.Append(borders);
styleSheet.Append(CellStyleFormats);
styleSheet.Append(CellFormats);
styleSheet.Append(CellStyles);
_dateStyleId = CreateCellFormat(styleSheet, null, null, UInt32Value.FromUInt32(22));
_NumbStyleId = CreateCellFormat(styleSheet, null, null, UInt32Value.FromUInt32(1));
// Set the style of workbook
workbookStylesPart.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;
}
/// <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 GetStylesheet()
{
Stylesheet stylesheet = new Stylesheet();
Fonts fonts = new Fonts();
fonts.Append(new Font()
{
FontName = new FontName() { Val = "Calibri" },
FontSize = new FontSize() { Val = 11 },
FontFamilyNumbering = new FontFamilyNumbering() { Val = 2 },
});
fonts.Count = (uint)fonts.ChildElements.Count;
Fills fills = new Fills();
fills.Append(new Fill() { PatternFill = new PatternFill() { PatternType = PatternValues.None } });
fills.Append(new Fill() { PatternFill = new PatternFill() { PatternType = PatternValues.Gray125 } });
fills.Count = (uint)fills.ChildElements.Count;
Borders borders = new Borders();
borders.Append(new Border()
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
});
borders.Count = (uint)borders.ChildElements.Count;
CellStyleFormats cellstyleformats = new CellStyleFormats();
cellstyleformats.Append(new CellFormat()
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0
});
cellstyleformats.Count = (uint)cellstyleformats.ChildElements.Count;
CellFormats cellformats = new CellFormats();
cellformats.Append(new CellFormat()
{
BorderId = 0,
FillId = 0,
FontId = 0,
NumberFormatId = 0,
FormatId = 0
});
cellformats.Append(new CellFormat()
{
BorderId = 0,
FillId = 0,
FontId = 0,
NumberFormatId = 14,
FormatId = 0,
ApplyNumberFormat = true
});
cellformats.Count = (uint)cellformats.ChildElements.Count;
CellStyles cellstyles = new CellStyles();
cellstyles.Append(new CellStyle()
{
Name = "Normal",
FormatId = 0,
BuiltinId = 0
});
cellstyles.Count = (uint)cellstyles.ChildElements.Count;
stylesheet.Append(fonts);
stylesheet.Append(fills);
stylesheet.Append(borders);
stylesheet.Append(cellstyleformats);
stylesheet.Append(cellformats);
stylesheet.Append(cellstyles);
return stylesheet;
}
private static 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
};
Font font1 = new Font();
FontSize fontSize1 = new FontSize()
{
Val = 11D
};
Color color1 = new Color()
{
Theme = (UInt32Value)1U
};
FontName fontName1 = new FontName()
{
Val = font
};
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();
if (isBold)
{
Bold bold1 = new Bold();
font2.Append(bold1);
}
FontSize fontSize2 = new FontSize()
{
Val = 11D
};
Color color2 = new Color()
{
Theme = (UInt32Value)1U
};
FontName fontName2 = new FontName()
{
Val = font
};
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme2 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font2.Append(fontSize2);
font2.Append(color2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontScheme2);
fonts1.Append(font1);
fonts1.Append(font2);
Fills fills1 = new Fills()
{
Count = (UInt32Value)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)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);
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)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)1U, FormatId = (UInt32Value)0U, ApplyBorder = true
};
CellFormat cellFormat4 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, 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
};
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;
}
public CustomStylesheet()
{
var fonts = new Fonts();
var font = new DocumentFormat.OpenXml.Spreadsheet.Font();
var fontName = new FontName { Val = StringValue.FromString("Arial") };
var fontSize = new FontSize { Val = DoubleValue.FromDouble(11) };
font.FontName = fontName;
font.FontSize = fontSize;
fonts.Append(font);
//Font Index 1
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName { Val = StringValue.FromString("Arial") };
fontSize = new FontSize { Val = DoubleValue.FromDouble(12) };
font.FontName = fontName;
font.FontSize = fontSize;
font.Bold = new Bold();
fonts.Append(font);
fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);
var fills = new Fills();
var fill = new Fill();
var patternFill = new PatternFill { PatternType = PatternValues.None };
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill { PatternType = PatternValues.Gray125 };
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 2
fill = new Fill();
patternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor()
};
patternFill.ForegroundColor =
TranslateForeground(System.Drawing.Color.LightBlue);
patternFill.BackgroundColor =
new BackgroundColor { Rgb = patternFill.ForegroundColor.Rgb };
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 3
fill = new Fill();
patternFill = new PatternFill
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor()
};
patternFill.ForegroundColor =
TranslateForeground(System.Drawing.Color.DodgerBlue);
patternFill.BackgroundColor =
new BackgroundColor { Rgb = patternFill.ForegroundColor.Rgb };
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
var borders = new Borders();
var border = new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
//All Boarder Index 1
border = new Border
{
LeftBorder = new LeftBorder { Style = BorderStyleValues.Thin },
RightBorder = new RightBorder { Style = BorderStyleValues.Thin },
TopBorder = new TopBorder { Style = BorderStyleValues.Thin },
BottomBorder = new BottomBorder { Style = BorderStyleValues.Thin },
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
//Top and Bottom Boarder Index 2
border = new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder { Style = BorderStyleValues.Thin },
BottomBorder = new BottomBorder { Style = BorderStyleValues.Thin },
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
var cellStyleFormats = new CellStyleFormats();
var cellFormat = new CellFormat
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0
};
cellStyleFormats.Append(cellFormat);
cellStyleFormats.Count =
UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);
uint iExcelIndex = 164;
var numberingFormats = new NumberingFormats();
var cellFormats = new CellFormats();
cellFormat = new CellFormat
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0
};
cellFormats.Append(cellFormat);
var nformatDateTime = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss")
};
numberingFormats.Append(nformatDateTime);
var nformat4Decimal = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("#,##0.0000")
};
numberingFormats.Append(nformat4Decimal);
var nformat2Decimal = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("#,##0.00")
};
numberingFormats.Append(nformat2Decimal);
var nformatForcedText = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex),
FormatCode = StringValue.FromString("@")
};
numberingFormats.Append(nformatForcedText);
// index 1
// Cell Standard Date format
cellFormat = new CellFormat
{
NumberFormatId = 14,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 2
// Cell Standard Number format with 2 decimal placing
cellFormat = new CellFormat
{
NumberFormatId = 4,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 3
// Cell Date time custom format
cellFormat = new CellFormat
{
NumberFormatId = nformatDateTime.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 4
// Cell 4 decimal custom format
cellFormat = new CellFormat
{
NumberFormatId = nformat4Decimal.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 5
// Cell 2 decimal custom format
cellFormat = new CellFormat
{
NumberFormatId = nformat2Decimal.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 6
// Cell forced number text custom format
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 7
// Cell text with font 12
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 1,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 8
// Cell text
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 9
// Coloured 2 decimal cell text
cellFormat = new CellFormat
{
NumberFormatId = nformat2Decimal.NumberFormatId,
FontId = 0,
FillId = 2,
BorderId = 2,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 10
// Coloured cell text
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 2,
BorderId = 2,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 11
// Coloured cell text
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 1,
FillId = 3,
BorderId = 2,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
numberingFormats.Count =
UInt32Value.FromUInt32((uint)numberingFormats.ChildElements.Count);
cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);
this.Append(numberingFormats);
this.Append(fonts);
this.Append(fills);
this.Append(borders);
this.Append(cellStyleFormats);
this.Append(cellFormats);
var css = new CellStyles();
var cs = new CellStyle
{
Name = StringValue.FromString("Normal"),
FormatId = 0,
BuiltinId = 0
};
css.Append(cs);
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
this.Append(css);
var dfs = new DifferentialFormats { Count = 0 };
this.Append(dfs);
var tss = new TableStyles
{
Count = 0,
DefaultTableStyle = StringValue.FromString("TableStyleMedium9"),
DefaultPivotStyle = StringValue.FromString("PivotStyleLight16")
};
this.Append(tss);
}
private static void CreateStyles(WorkbookPart workbookpart)
{
WorkbookStylesPart sp = workbookpart.AddNewPart <WorkbookStylesPart>();
sp.Stylesheet = new Stylesheet();
Fonts fonts = new Fonts()
{
Count = (UInt32Value)2U, KnownFonts = true
};
Font fontUsual = new Font();
fontUsual.Append(new FontSize()
{
Val = 12D
});
fontUsual.Append(new DocumentFormat.OpenXml.Office2010.Excel.Color()
{
Theme = (UInt32Value)1U
});
fontUsual.Append(new FontName()
{
Val = "Times New Roman"
});
fontUsual.Append(new FontFamilyNumbering()
{
Val = 2
});
fontUsual.Append(new FontScheme()
{
Val = FontSchemeValues.Minor
});
Font fontTitle = new Font();
fontTitle.Append(new Bold());
fontTitle.Append(new FontSize()
{
Val = 14D
});
fontTitle.Append(new DocumentFormat.OpenXml.Office2010.Excel.Color()
{
Theme = (UInt32Value)1U
});
fontTitle.Append(new FontName()
{
Val = "Times New Roman"
});
fontTitle.Append(new FontFamilyNumbering()
{
Val = 2
});
fontTitle.Append(new FontScheme()
{
Val = FontSchemeValues.Minor
});
fonts.Append(fontUsual);
fonts.Append(fontTitle);
Fills fills = new Fills()
{
Count = (UInt32Value)2U
};
Fill fill1 = new Fill();
fill1.Append(new PatternFill()
{
PatternType = PatternValues.None
});
Fill fill2 = new Fill();
fill2.Append(new PatternFill()
{
PatternType = PatternValues.Gray125
});
fills.Append(fill1);
fills.Append(fill2);
Borders borders = new Borders()
{
Count = (UInt32Value)2U
};
Border borderNoBorder = new Border();
borderNoBorder.Append(new LeftBorder());
borderNoBorder.Append(new RightBorder());
borderNoBorder.Append(new TopBorder());
borderNoBorder.Append(new BottomBorder());
borderNoBorder.Append(new DiagonalBorder());
Border borderThin = new Border();
LeftBorder leftBorder = new LeftBorder()
{
Style = BorderStyleValues.Thin
};
leftBorder.Append(new DocumentFormat.OpenXml.Office2010.Excel.Color()
{
Indexed = (UInt32Value)64U
});
RightBorder rightBorder = new RightBorder()
{
Style = BorderStyleValues.Thin
};
rightBorder.Append(new DocumentFormat.OpenXml.Office2010.Excel.Color()
{
Indexed = (UInt32Value)64U
});
TopBorder topBorder = new TopBorder()
{
Style = BorderStyleValues.Thin
};
topBorder.Append(new DocumentFormat.OpenXml.Office2010.Excel.Color()
{
Indexed = (UInt32Value)64U
});
BottomBorder bottomBorder = new BottomBorder()
{
Style = BorderStyleValues.Thin
};
bottomBorder.Append(new DocumentFormat.OpenXml.Office2010.Excel.Color()
{
Indexed = (UInt32Value)64U
});
borderThin.Append(leftBorder);
borderThin.Append(rightBorder);
borderThin.Append(topBorder);
borderThin.Append(bottomBorder);
borderThin.Append(new DiagonalBorder());
borders.Append(borderNoBorder);
borders.Append(borderThin);
CellStyleFormats cellStyleFormats = new CellStyleFormats()
{
Count = (UInt32Value)1U
};
CellFormat cellFormatStyle = new CellFormat()
{
NumberFormatId = (UInt32Value)0U,
FontId = (UInt32Value)0U,
FillId = (UInt32Value)0U,
BorderId = (UInt32Value)0U
};
cellStyleFormats.Append(cellFormatStyle);
CellFormats cellFormats = new CellFormats()
{
Count = (UInt32Value)3U
};
CellFormat cellFormatFont = new CellFormat()
{
NumberFormatId = (UInt32Value)0U,
FontId = (UInt32Value)0U,
FillId = (UInt32Value)0U,
BorderId = (UInt32Value)0U,
FormatId = (UInt32Value)0U,
ApplyFont = true
};
CellFormat cellFormatFontAndBorder = new CellFormat()
{
NumberFormatId = (UInt32Value)0U,
FontId = (UInt32Value)0U,
FillId = (UInt32Value)0U,
BorderId = (UInt32Value)1U,
FormatId = (UInt32Value)0U,
ApplyFont = true,
ApplyBorder = true
};
CellFormat cellFormatTitle = new CellFormat()
{
NumberFormatId = (UInt32Value)0U,
FontId = (UInt32Value)1U,
FillId = (UInt32Value)0U,
BorderId = (UInt32Value)0U,
FormatId = (UInt32Value)0U,
Alignment = new Alignment()
{
Vertical = VerticalAlignmentValues.Center,
WrapText = true,
Horizontal = HorizontalAlignmentValues.Center
},
ApplyFont = true
};
cellFormats.Append(cellFormatFont);
cellFormats.Append(cellFormatFontAndBorder);
cellFormats.Append(cellFormatTitle);
CellStyles cellStyles = new CellStyles()
{
Count = (UInt32Value)1U
};
cellStyles.Append(new CellStyle()
{
Name = "Normal",
FormatId = (UInt32Value)0U,
BuiltinId = (UInt32Value)0U
});
DocumentFormat.OpenXml.Office2013.Excel.DifferentialFormats
differentialFormats = new DocumentFormat.OpenXml.Office2013.Excel.DifferentialFormats()
{
Count = (UInt32Value)0U
};
TableStyles tableStyles = new TableStyles()
{
Count = (UInt32Value)0U,
DefaultTableStyle = "TableStyleMedium2",
DefaultPivotStyle = "PivotStyleLight16"
};
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");
stylesheetExtension1.Append(new SlicerStyles()
{
DefaultSlicerStyle = "SlicerStyleLight1"
});
StylesheetExtension stylesheetExtension2 = new StylesheetExtension()
{
Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}"
};
stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
stylesheetExtension2.Append(new TimelineStyles()
{
DefaultTimelineStyle = "TimeSlicerStyleLight1"
});
stylesheetExtensionList.Append(stylesheetExtension1);
stylesheetExtensionList.Append(stylesheetExtension2);
sp.Stylesheet.Append(fonts);
sp.Stylesheet.Append(fills);
sp.Stylesheet.Append(borders);
sp.Stylesheet.Append(cellStyleFormats);
sp.Stylesheet.Append(cellFormats);
sp.Stylesheet.Append(cellStyles);
sp.Stylesheet.Append(differentialFormats);
sp.Stylesheet.Append(tableStyles);
sp.Stylesheet.Append(stylesheetExtensionList);
}
// Generates content of workbookStylesPart1.
private void GenerateWorkbookStylesPart1Content(WorkbookStylesPart workbookStylesPart1)
{
Stylesheet stylesheet1 = new Stylesheet();
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)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)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true };
CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, PivotButton = true };
CellFormat cellFormat5 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyAlignment = 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 };
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
workbookStylesPart1.Stylesheet = stylesheet1;
}
// Generates content of fontTablePart1.
private void GenerateFontTablePart1Content(FontTablePart fontTablePart1)
{
Fonts fonts1 = new Fonts();
fonts1.AddNamespaceDeclaration("w", "http://schemas.openxmlformats.org/wordprocessingml/2006/main");
fonts1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
Font font1 = new Font() { Name = "Times New Roman" };
FontCharSet fontCharSet1 = new FontCharSet() { Val = "00", StrictCharacterSet = new EnumValue<StrictCharacterSet>() { InnerText = "windows-1252" } };
FontFamily fontFamily1 = new FontFamily() { Val = FontFamilyValues.Roman };
Pitch pitch1 = new Pitch() { Val = FontPitchValues.Variable };
font1.Append(fontCharSet1);
font1.Append(fontFamily1);
font1.Append(pitch1);
Font font2 = new Font() { Name = "Symbol" };
FontCharSet fontCharSet2 = new FontCharSet() { Val = "02" };
FontFamily fontFamily2 = new FontFamily() { Val = FontFamilyValues.Roman };
Pitch pitch2 = new Pitch() { Val = FontPitchValues.Variable };
font2.Append(fontCharSet2);
font2.Append(fontFamily2);
font2.Append(pitch2);
Font font3 = new Font() { Name = "Arial" };
FontCharSet fontCharSet3 = new FontCharSet() { Val = "00", StrictCharacterSet = new EnumValue<StrictCharacterSet>() { InnerText = "windows-1252" } };
FontFamily fontFamily3 = new FontFamily() { Val = FontFamilyValues.Swiss };
Pitch pitch3 = new Pitch() { Val = FontPitchValues.Variable };
font3.Append(fontCharSet3);
font3.Append(fontFamily3);
font3.Append(pitch3);
Font font4 = new Font() { Name = "Liberation Serif" };
AltName altName1 = new AltName() { Val = "Times New Roman" };
FontCharSet fontCharSet4 = new FontCharSet() { Val = "cc", StrictCharacterSet = StrictCharacterSet.chsRussian };
FontFamily fontFamily4 = new FontFamily() { Val = FontFamilyValues.Roman };
Pitch pitch4 = new Pitch() { Val = FontPitchValues.Variable };
font4.Append(altName1);
font4.Append(fontCharSet4);
font4.Append(fontFamily4);
font4.Append(pitch4);
Font font5 = new Font() { Name = "Times New Roman" };
FontCharSet fontCharSet5 = new FontCharSet() { Val = "cc", StrictCharacterSet = StrictCharacterSet.chsRussian };
FontFamily fontFamily5 = new FontFamily() { Val = FontFamilyValues.Roman };
Pitch pitch5 = new Pitch() { Val = FontPitchValues.Variable };
font5.Append(fontCharSet5);
font5.Append(fontFamily5);
font5.Append(pitch5);
Font font6 = new Font() { Name = "Liberation Sans" };
AltName altName2 = new AltName() { Val = "Arial" };
FontCharSet fontCharSet6 = new FontCharSet() { Val = "cc", StrictCharacterSet = StrictCharacterSet.chsRussian };
FontFamily fontFamily6 = new FontFamily() { Val = FontFamilyValues.Roman };
Pitch pitch6 = new Pitch() { Val = FontPitchValues.Variable };
font6.Append(altName2);
font6.Append(fontCharSet6);
font6.Append(fontFamily6);
font6.Append(pitch6);
Font font7 = new Font() { Name = "Symbol" };
FontCharSet fontCharSet7 = new FontCharSet() { Val = "cc", StrictCharacterSet = StrictCharacterSet.chsRussian };
FontFamily fontFamily7 = new FontFamily() { Val = FontFamilyValues.Roman };
Pitch pitch7 = new Pitch() { Val = FontPitchValues.Variable };
font7.Append(fontCharSet7);
font7.Append(fontFamily7);
font7.Append(pitch7);
fonts1.Append(font1);
fonts1.Append(font2);
fonts1.Append(font3);
fonts1.Append(font4);
fonts1.Append(font5);
fonts1.Append(font6);
fonts1.Append(font7);
fontTablePart1.Fonts = fonts1;
}
public static Stylesheet CreateStylesheet()
{
var ss = new Stylesheet();
var fts = new Fonts();
var ftn = new FontName { Val = "Arial" };
var ftsz = new FontSize { Val = 11 };
var ft = new DocumentFormat.OpenXml.Spreadsheet.Font { FontName = ftn, FontSize = ftsz };
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
var fills = new Fills();
var fill = new Fill();
var patternFill = new PatternFill { PatternType = PatternValues.None };
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill { PatternType = PatternValues.Gray125 };
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = (uint)fills.ChildElements.Count;
var borders = new Borders();
var border = new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
borders.Count = (uint)borders.ChildElements.Count;
var csfs = new CellStyleFormats();
var cf = new CellFormat { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0 };
csfs.Append(cf);
csfs.Count = (uint)csfs.ChildElements.Count;
// dd/mm/yyyy is also Excel style index 14
uint iExcelIndex = 164;
var nfs = new NumberingFormats();
var cfs = new CellFormats();
cf = new CellFormat { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0 };
cfs.Append(cf);
var nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "dd/mm/yyyy hh:mm:ss" };
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
iExcelIndex = 165;
nfs = new NumberingFormats();
cfs = new CellFormats();
cf = new CellFormat { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0 };
cfs.Append(cf);
nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "MMM yyyy" };
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
iExcelIndex = 170;
nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "#,##0.0000" };
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
// #,##0.00 is also Excel style index 4
iExcelIndex = 171;
nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "#,##0.00" };
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
// @ is also Excel style index 49
iExcelIndex = 172;
nf = new NumberingFormat { NumberFormatId = iExcelIndex, FormatCode = "@" };
nfs.Append(nf);
cf = new CellFormat
{
NumberFormatId = nf.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = true
};
cfs.Append(cf);
nfs.Count = (uint)nfs.ChildElements.Count;
cfs.Count = (uint)cfs.ChildElements.Count;
ss.Append(nfs);
ss.Append(fts);
ss.Append(fills);
ss.Append(borders);
ss.Append(csfs);
ss.Append(cfs);
var css = new CellStyles();
var cs = new CellStyle { Name = "Normal", FormatId = 0, BuiltinId = 0 };
css.Append(cs);
css.Count = (uint)css.ChildElements.Count;
ss.Append(css);
var dfs = new DifferentialFormats { Count = 0 };
ss.Append(dfs);
var tss = new TableStyles
{
Count = 0,
DefaultTableStyle = "TableStyleMedium9",
DefaultPivotStyle = "PivotStyleLight16"
};
ss.Append(tss);
return ss;
}
/// <summary>
/// <see cref="Stylesheet"/>の初期化処理
/// </summary>
/// <param name="part"><see cref="WorkbookStylesPart"/></param>
/// <param name="initializeNumberingFormats"><see cref="NumberingFormats"/>の定義
/// カスタム定義は <see cref="NumberingFormat.NumberFormatId"/> を 164 以降で定義する必要あり
/// 日付、外貨の金額部分などのカスタム書式を定義する場合に利用
/// 円貨は、built-in で定義されている 38 を利用すれば良い
/// 未指定 の場合は 何も登録しない
/// </param>
/// <param name="initializeFonts"><see cref="Fonts"/>の定義
/// 未指定の場合は、デフォルトフォントを登録
/// </param>
/// <param name="initializeFills"><see cref="Fills"/>の定義
/// 必ず、None, Gray125 の定義が必要
/// それ以降に 任意の <see cref="Fill"/>を登録する
/// 未指定の場合は、 None, Gray125 を登録
/// </param>
/// <param name="initializeBorders"><see cref="Borders"/>の定義
/// 必ず最初に 空の <see cref="Border"/>の指定が必要 <see cref="GetBorderAll(BDStyles)"/>を利用すると楽
/// left/right/top/bottom の組み合わせで登録する必要があるため、組み合わせが膨大になる可能性がある
/// </param>
/// <param name="initializeCellFormats"><see cref="CellFormats"/>の定義
/// 必ず最初に 空の <see cref="CellFormat"/>の指定が必要 <see cref="GetCellFormat(int, int, int, int, int, HorizontalAlignmentValues)"/>を利用すると楽
/// font, fill, border, numberingFormatId の組み合わせが異なるごとに定義が必要
/// index で指定する必要があるため、該当するセルがどのインデックスとなるかを、把握する必要がある</param>
/// <returns></returns>
public static Stylesheet InitizalizeStylesheet(this WorkbookStylesPart part,
Func <NumberingFormat[]> initializeNumberingFormats = null,
Func <Font[]> initializeFonts = null,
Func <Fill[]> initializeFills = null,
Func <Border[]> initializeBorders = null,
Func <CellFormat[]> initializeCellFormats = null
)
{
var stylesheet = part.Stylesheet;
if (stylesheet == null)
{
stylesheet = (part.Stylesheet = new Stylesheet());
}
//var minFormatId = 164u; // Excel "built-in" number-format-id was less than 164;
if (initializeNumberingFormats != null)
{
var numberingFormats = new NumberingFormats();
numberingFormats.Append(initializeNumberingFormats());
stylesheet.Append(numberingFormats);
}
var fonts = new Fonts {
KnownFonts = true
};
fonts.Append(
initializeFonts?.Invoke() ??
new[] {
GetFont(), /* 0: default font */
});
fonts.Count = (uint)fonts.ChildElements.Count;
stylesheet.Append(fonts);
var fills = new Fills {
};
fills.Append(
initializeFills?.Invoke() ??
new[] {
/* 0 */ new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.None
},
},
/* 1 */ new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.Gray125
},
},
});
fills.Count = (uint)fills.ChildElements.Count;
stylesheet.Append(fills);
var borders = new Borders {
};
borders.Append(
initializeBorders?.Invoke() ??
new[] {
/* 0 */ GetBorderAll(BDStyles.None),
});
borders.Count = (uint)borders.ChildElements.Count;
stylesheet.Append(borders);
var cellStyleFormats = new CellStyleFormats {
};
cellStyleFormats.Append(new[] {
GetCellFormat(),
GetCellFormat(22),
});
cellStyleFormats.Count = (uint)cellStyleFormats.ChildElements.Count;
stylesheet.Append(cellStyleFormats);
var cellFormats = new CellFormats {
};
cellFormats.Append(
initializeCellFormats?.Invoke() ??
new[] {
/* 0 */ GetCellFormat(),
});
cellFormats.Count = (uint)cellFormats.ChildElements.Count;
stylesheet.Append(cellFormats);
return(stylesheet);
}
