/// <summary>
/// generate border for the row
/// </summary>
/// <returns></returns>
protected Border generaLastRowBorder()
{
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.Dotted | BorderStyleValues.Hair
};
Color color3 = new Color()
{
Indexed = (UInt32Value)64U
};
topBorder2.Append(color3);
BottomBorder bottomBorder2 = new BottomBorder()
{
Style = BorderStyleValues.Thin
};
Color color4 = new Color()
{
Indexed = (UInt32Value)64U
};
bottomBorder2.Append(color4);
DiagonalBorder diagonalBorder2 = new DiagonalBorder();
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
return(border2);
}
private static Border GenerateBorder()
{
Border border2 = new Border();
LeftBorder leftBorder2 = new LeftBorder()
{
Style = BorderStyleValues.Thin
};
Color color1 = new Color()
{
Indexed = (UInt32Value)64U
};
leftBorder2.Append(color1);
RightBorder rightBorder2 = new RightBorder()
{
Style = BorderStyleValues.Thin
};
Color color2 = new Color()
{
Indexed = (UInt32Value)64U
};
rightBorder2.Append(color2);
TopBorder topBorder2 = new TopBorder()
{
Style = BorderStyleValues.Thin
};
Color color3 = new Color()
{
Indexed = (UInt32Value)64U
};
topBorder2.Append(color3);
BottomBorder bottomBorder2 = new BottomBorder()
{
Style = BorderStyleValues.Thin
};
Color color4 = new Color()
{
Indexed = (UInt32Value)64U
};
bottomBorder2.Append(color4);
DiagonalBorder diagonalBorder2 = new DiagonalBorder();
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
return(border2);
}
private static Border Border1()
{
// ReSharper disable PossiblyMistakenUseOfParamsMethod
var border1 = new Border();
var leftBorder1 = new LeftBorder {
Style = BorderStyleValues.Thin
};
var color1 = new Color {
Indexed = 64U
};
leftBorder1.Append(color1);
var rightBorder1 = new RightBorder {
Style = BorderStyleValues.Thin
};
color1 = new Color {
Indexed = 64U
};
rightBorder1.Append(color1);
var topBorder1 = new TopBorder {
Style = BorderStyleValues.Thin
};
color1 = new Color {
Indexed = 64U
};
topBorder1.Append(color1);
var bottomBorder1 = new BottomBorder {
Style = BorderStyleValues.Thin
};
color1 = new Color {
Indexed = 64U
};
bottomBorder1.Append(color1);
var diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
return(border1);
// ReSharper restore PossiblyMistakenUseOfParamsMethod
}
/// <summary>
/// Генерация разных полных границ
/// </summary>
/// <returns></returns>
/// <param name="left">Левая граница</param>
/// <param name="right">Правая граница</param>
/// <param name="top">Верхняя граница</param>
/// <param name="bottom">Нижняя граница</param>
public Border GenerateStandardFullBorderSetting(BorderStyleValues left, BorderStyleValues right, BorderStyleValues top, BorderStyleValues bottom)
{
Border border = new Border();
LeftBorder leftBorder = new LeftBorder()
{
Style = left
};
Color color = new Color()
{
Indexed = (UInt32Value)64U
};
leftBorder.Append(color);
RightBorder rightBorder = new RightBorder()
{
Style = right
};
Color color1 = new Color()
{
Indexed = (UInt32Value)64U
};
rightBorder.Append(color1);
TopBorder topBorder = new TopBorder()
{
Style = top
};
Color color2 = new Color()
{
Indexed = (UInt32Value)64U
};
topBorder.Append(color2);
BottomBorder bottomBorder = new BottomBorder()
{
Style = bottom
};
Color color3 = new Color()
{
Indexed = (UInt32Value)64U
};
bottomBorder.Append(color3);
border.Append(leftBorder);
border.Append(rightBorder);
border.Append(topBorder);
border.Append(bottomBorder);
return(border);
}
protected Border GetBorder(BorderStyleValues borderStyle = BorderStyleValues.None, double indexed = 64)
{
Border border = new Border();
LeftBorder leftBorder = new LeftBorder()
{
Style = borderStyle
};
RightBorder rightBorder = new RightBorder()
{
Style = borderStyle
};
TopBorder topBorder = new TopBorder()
{
Style = borderStyle
};
BottomBorder bottomBorder = new BottomBorder()
{
Style = borderStyle
};
DiagonalBorder diagonalBorder = new DiagonalBorder();
if (borderStyle != BorderStyleValues.None)
{
//Color color = new Color() { Indexed = (UInt32Value)indexed };
leftBorder.Append(new Color()
{
Indexed = (UInt32Value)indexed
});
rightBorder.Append(new Color()
{
Indexed = (UInt32Value)indexed
});
topBorder.Append(new Color()
{
Indexed = (UInt32Value)indexed
});
bottomBorder.Append(new Color()
{
Indexed = (UInt32Value)indexed
});
}
border.Append(leftBorder);
border.Append(rightBorder);
border.Append(topBorder);
border.Append(bottomBorder);
border.Append(diagonalBorder);
return(border);
}
private static BottomBorder GenerateBottomBorder(BorderStyleValues thickness)
{
BottomBorder bottomBorder = new BottomBorder()
{
Style = thickness
};
Color color = new Color()
{
Indexed = 64U
};
bottomBorder.Append(color);
return(bottomBorder);
}
/// <summary>
/// Нижнее подчеркивание
/// </summary>
/// <returns></returns>
public Border GenerateBottomBorder()
{
Border border = new Border();
BottomBorder bottomBorder = new BottomBorder()
{
Style = BorderStyleValues.Thin
};
Color color3 = new Color()
{
Indexed = (UInt32Value)64U
};
bottomBorder.Append(color3);
border.Append(bottomBorder);
return(border);
}
public static Border CreateBorder(
Borders borders,
BorderProperties left = null,
BorderProperties right = null,
BorderProperties top = null,
BorderProperties bottom = null,
BorderProperties diagonal = null)
{
var border = new Border();
var leftBorder = new LeftBorder();
if (left != null)
{
if (left.BorderStyle.HasValue)
{
leftBorder.Style = left.BorderStyle.Value;
}
if (left.Color != null)
{
leftBorder.Append(new Color { Indexed = left.Color });
}
}
border.Append(leftBorder);
var rightBorder = new RightBorder();
if (right != null)
{
if (right.BorderStyle.HasValue)
{
rightBorder.Style = right.BorderStyle.Value;
}
if (right.Color != null)
{
rightBorder.Append(new Color { Indexed = right.Color });
}
}
border.Append(rightBorder);
var topBorder = new TopBorder();
if (top != null)
{
if (top.BorderStyle.HasValue)
{
topBorder.Style = top.BorderStyle.Value;
}
if (top.Color != null)
{
topBorder.Append(new Color { Indexed = top.Color });
}
}
border.Append(topBorder);
var bottomBorder = new BottomBorder();
if (bottom != null)
{
if (bottom.BorderStyle.HasValue)
{
bottomBorder.Style = bottom.BorderStyle.Value;
}
if (bottom.Color != null)
{
bottomBorder.Append(new Color { Indexed = bottom.Color });
}
}
border.Append(bottomBorder);
var diagonalBorder = new DiagonalBorder();
if (diagonal != null)
{
if (diagonal.BorderStyle.HasValue)
{
diagonalBorder.Style = diagonal.BorderStyle.Value;
}
if (diagonal.Color != null)
{
diagonalBorder.Append(new Color { Indexed = diagonal.Color });
}
}
border.Append(diagonalBorder);
borders.Append(border);
return border;
}
private static UInt32Value CreateBorder(Stylesheet templateSheetobj, BorderStyleValues borderStyle)
{
Border border = new Border();
LeftBorder leftBorder = new LeftBorder();
RightBorder rightBorder = new RightBorder();
BottomBorder bottomBorder = new BottomBorder();
TopBorder topBorder = new TopBorder();
if (borderStyle == BorderStyleValues.Thin)
{
leftBorder.Style = BorderStyleValues.Thin;
rightBorder.Style = BorderStyleValues.Thin;
topBorder.Style = BorderStyleValues.Thin;
bottomBorder.Style = BorderStyleValues.Thin;
leftBorder.Append(new Color()
{
Indexed = (UInt32Value)64U
});
rightBorder.Append(new Color()
{
Indexed = (UInt32Value)64U
});
topBorder.Append(new Color()
{
Indexed = (UInt32Value)64U
});
bottomBorder.Append(new Color()
{
Indexed = (UInt32Value)64U
});
}
else if (borderStyle == BorderStyleValues.Dotted)
{
leftBorder.Style = BorderStyleValues.Dotted;
rightBorder.Style = BorderStyleValues.Dotted;
topBorder.Style = BorderStyleValues.Dotted;
bottomBorder.Style = BorderStyleValues.Dotted;
leftBorder.Append(new Color()
{
Theme = 0, Tint = -0.499984740745262
});
rightBorder.Append(new Color()
{
Theme = 0, Tint = -0.499984740745262
});
topBorder.Append(new Color()
{
Theme = 0, Tint = -0.499984740745262
});
bottomBorder.Append(new Color()
{
Theme = 0, Tint = -0.499984740745262
});
}
border.Append(leftBorder);
border.Append(rightBorder);
border.Append(topBorder);
border.Append(bottomBorder);
templateSheetobj.Borders.Append(border);
UInt32Value result = templateSheetobj.Borders.Count;
templateSheetobj.Borders.Count++;
return(result);
}
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 = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme1 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
Font font2 = new Font();
Bold bold1 = new Bold();
FontSize fontSize2 = new FontSize()
{
Val = 11D
};
Color color2 = new Color()
{
Theme = (UInt32Value)1U
};
FontName fontName2 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme2 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font2.Append(bold1);
font2.Append(fontSize2);
font2.Append(color2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontScheme2);
fonts1.Append(font1);
fonts1.Append(font2);
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;
}
private void pushBordersToStylesheet(Stylesheet ss)
{
var borders = ss.Borders;
var xn = _doc.SelectSingleNode("stylesheet/borders");
var nlst = xn.SelectNodes("border");
Console.WriteLine(nlst.Count);
foreach (XmlNode f in nlst)
{
var border_id = f.Attributes.GetNamedItem("id").Value;
var border = new Border();
var left = f.SelectSingleNode("left");
var left_style = left.Attributes.GetNamedItem("style").Value;
var left_color = left.Attributes.GetNamedItem("color").Value;
var lb = new LeftBorder()
{
Style = BorderStyleValues.Thin
};
Color lc = new Color()
{
Rgb = left_color
};
lb.Append(lc);
border.Append(lb);
var right = f.SelectSingleNode("right");
var right_style = right.Attributes.GetNamedItem("style").Value;
var right_color = right.Attributes.GetNamedItem("color").Value;
var rb = new RightBorder()
{
Style = BorderStyleValues.Thin
};
Color rc = new Color()
{
Rgb = right_color
};
rb.Append(rc);
border.Append(rb);
var top = f.SelectSingleNode("top");
var top_style = top.Attributes.GetNamedItem("style").Value;
var top_color = top.Attributes.GetNamedItem("color").Value;
var tb = new TopBorder()
{
Style = BorderStyleValues.Thin
};
Color tc = new Color()
{
Rgb = top_color
};
tb.Append(tc);
border.Append(tb);
var bottom = f.SelectSingleNode("bottom");
var bottom_style = bottom.Attributes.GetNamedItem("style").Value;
var bottom_color = bottom.Attributes.GetNamedItem("color").Value;
var bb = new BottomBorder()
{
Style = BorderStyleValues.Thin
};
Color bc = new Color()
{
Rgb = bottom_color
};
bb.Append(bc);
border.Append(bb);
borders.Append(border);
var border_idx = borders.Elements <Border>().Count() - 1;
border_map.Add(border_id, border_idx);
}
}
// 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;
}
private void LCreateStyle(string numberFormatCode,
double fontSize, string fontName, System.Drawing.Color fontColor
, bool fontBold, bool fontItalic, PatternValues fillPattern, System.Drawing.Color fillForeGroundColor, System.Drawing.Color borderLeftColor
, BorderStyleValues borderLeftStyleValue, System.Drawing.Color borderBottomColor, BorderStyleValues borderBottomStyleValue, System.Drawing.Color borderRightColor
, BorderStyleValues borderRightStyleValue, System.Drawing.Color borderTopColor, BorderStyleValues borderTopStyleValue, HorizontalAlignmentValues alignmentHorizontal, VerticalAlignmentValues alignmentVertical)
{
if (numberFormatCode != null)
{
NumberingFormat numberingFormat = new NumberingFormat();
numberingFormat.FormatCode = numberFormatCode;
_oNumberingFormat = numberingFormat;
}
//font
Font font = new Font();
FontSize _fontSize = new FontSize()
{
Val = DoubleValue.FromDouble(fontSize)
};
font.Append(_fontSize);
if (fontName != null)
{
FontName _fontName = new FontName()
{
Val = fontName
};
font.Append(_fontName);
}
if (fontColor != null)
{
Color _fontColor = new Color()
{
Rgb = new HexBinaryValue()
{
Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(fontColor.R, fontColor.G, fontColor.B)).Replace("#", "")
}
};
font.Append(_fontColor);
}
if (fontBold)
{
Bold _fontBold = new Bold();
font.Append(_fontBold);
}
if (fontItalic)
{
Italic _fontItalic = new Italic();
font.Append(_fontItalic);
}
_oFont = font;
//fill
Fill _fontFill = null;
if (fillForeGroundColor == null)
{
_fontFill = new Fill(new PatternFill()
{
PatternType = fillPattern
});
}
else
{
_fontFill = new Fill(new PatternFill(
new ForegroundColor()
{
Rgb = new HexBinaryValue()
{
Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(fillForeGroundColor.R, fillForeGroundColor.G, fillForeGroundColor.B)).Replace("#", "")
}
}
)
{
PatternType = PatternValues.Solid
});
}
_oFill = _fontFill;
//border
Border _border = new Border();
LeftBorder leftBorder = new LeftBorder()
{
Style = borderLeftStyleValue
};
Color colorLeftBorder = null;
if (borderLeftColor != null)
{
colorLeftBorder = new Color()
{
Rgb = new HexBinaryValue {
Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(borderLeftColor.R, borderLeftColor.G, borderLeftColor.B)).Replace("#", "")
}
};
}
else
{
colorLeftBorder = new Color();
}
leftBorder.Append(colorLeftBorder);
_border.Append(leftBorder);
RightBorder rightBorder = new RightBorder()
{
Style = borderRightStyleValue
};
Color colorRightBorder = null;
if (borderRightColor != null)
{
colorRightBorder = new Color()
{
Rgb = new HexBinaryValue {
Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(borderRightColor.R, borderRightColor.G, borderRightColor.B)).Replace("#", "")
}
};
}
else
{
colorRightBorder = new Color();
}
rightBorder.Append(colorRightBorder);
_border.Append(rightBorder);
TopBorder topBorder = new TopBorder()
{
Style = borderTopStyleValue
};
Color colorTopBorder = null;
if (borderTopColor != null)
{
colorTopBorder = new Color()
{
Rgb = new HexBinaryValue {
Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(borderTopColor.R, borderTopColor.G, borderTopColor.B)).Replace("#", "")
}
};
}
else
{
colorTopBorder = new Color();
}
topBorder.Append(colorTopBorder);
_border.Append(topBorder);
BottomBorder bottomBorder = new BottomBorder()
{
Style = borderBottomStyleValue
};
Color colorBottomBorder = null;
if (borderBottomColor != null)
{
colorBottomBorder = new Color()
{
Rgb = new HexBinaryValue {
Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(borderBottomColor.R, borderBottomColor.G, borderBottomColor.B)).Replace("#", "")
}
};
}
else
{
colorBottomBorder = new Color();
}
bottomBorder.Append(colorBottomBorder);
_border.Append(bottomBorder);
_oBorder = _border;
Alignment _alignment = new Alignment();
_alignment.Horizontal = alignmentHorizontal;
_alignment.Vertical = alignmentVertical;
_oAlignment = _alignment;
_oAlignment.WrapText = wrapText;
_oAlignment.TextRotation.Value = textRotate;
}
// 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;
}
static Borders GenerateBorders()
{
var borders = 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 color1 = new Color()
{
Indexed = (UInt32Value)64U
};
leftBorder2.Append(color1);
var rightBorder2 = new RightBorder()
{
Style = BorderStyleValues.Thin
};
var color2 = new Color()
{
Indexed = (UInt32Value)64U
};
rightBorder2.Append(color2);
var topBorder2 = new TopBorder()
{
Style = BorderStyleValues.Thin
};
var color3 = new Color()
{
Indexed = (UInt32Value)64U
};
topBorder2.Append(color3);
var bottomBorder2 = new BottomBorder()
{
Style = BorderStyleValues.Thin
};
var color4 = new Color()
{
Indexed = (UInt32Value)64U
};
bottomBorder2.Append(color4);
var diagonalBorder2 = new DiagonalBorder();
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
borders.Append(border1);
borders.Append(border2);
return(borders);
}
// 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);
}
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.
// (borders needed for user to resize tables). Other style content skipped.
private static void GenerateWorkbookStylesContent(WorkbookStylesPart workbookStylesPart1)
{
Stylesheet stylesheet1 = new Stylesheet();
// if your data includes a table, this renders the blue borders to make it easier to see.
DifferentialFormats differentialFormats1 = new DifferentialFormats()
{
Count = (UInt32Value)2U
};
DifferentialFormat differentialFormat1 = new DifferentialFormat();
NumberingFormat numberingFormat1 = new NumberingFormat()
{
NumberFormatId = (UInt32Value)1U, FormatCode = "0"
};
differentialFormat1.Append(numberingFormat1);
DifferentialFormat differentialFormat2 = new DifferentialFormat();
Border border3 = new Border()
{
DiagonalUp = false, DiagonalDown = false
};
LeftBorder leftBorder3 = new LeftBorder()
{
Style = BorderStyleValues.Thin
};
Color color5 = new Color()
{
Indexed = (UInt32Value)12U
};
leftBorder3.Append(color5);
RightBorder rightBorder3 = new RightBorder()
{
Style = BorderStyleValues.Thin
};
Color color6 = new Color()
{
Indexed = (UInt32Value)12U
};
rightBorder3.Append(color6);
TopBorder topBorder3 = new TopBorder()
{
Style = BorderStyleValues.Thin
};
Color color7 = new Color()
{
Indexed = (UInt32Value)12U
};
topBorder3.Append(color7);
BottomBorder bottomBorder3 = new BottomBorder()
{
Style = BorderStyleValues.Thin
};
Color color8 = new Color()
{
Indexed = (UInt32Value)12U
};
bottomBorder3.Append(color8);
border3.Append(leftBorder3);
border3.Append(rightBorder3);
border3.Append(topBorder3);
border3.Append(bottomBorder3);
differentialFormat2.Append(border3);
differentialFormats1.Append(differentialFormat1);
differentialFormats1.Append(differentialFormat2);
stylesheet1.Append(differentialFormats1); // border used for table
workbookStylesPart1.Stylesheet = stylesheet1;
}
public static Border GetDefaultBorder2()
{
Border border1 = new Border()
{
Outline = true
};
LeftBorder leftBorder1 = new LeftBorder()
{
Style = BorderStyleValues.Thin
};
leftBorder1.Append(new Color()
{
Rgb = new HexBinaryValue()
{
Value = System.Drawing.Color.Red.ToHexString()
}
});
RightBorder rightBorder1 = new RightBorder()
{
Style = BorderStyleValues.Thin
};
rightBorder1.Append(new Color()
{
Rgb = new HexBinaryValue()
{
Value = System.Drawing.Color.Red.ToHexString()
}
});
TopBorder topBorder1 = new TopBorder()
{
Style = BorderStyleValues.Thin
};
topBorder1.Append(new Color()
{
Rgb = new HexBinaryValue()
{
Value = System.Drawing.Color.Red.ToHexString()
}
});
BottomBorder bottomBorder1 = new BottomBorder()
{
Style = BorderStyleValues.Thin
};
bottomBorder1.Append(new Color()
{
Rgb = new HexBinaryValue()
{
Value = System.Drawing.Color.Red.ToHexString()
}
});
DiagonalBorder diagonalBorder1 = new DiagonalBorder()
{
Style = BorderStyleValues.Thin
};
diagonalBorder1.Append(new Color()
{
Rgb = new HexBinaryValue()
{
Value = System.Drawing.Color.Red.ToHexString()
}
});
//Sort order is very very very important
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
return(border1);
}
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;
}