private static CellStyles CreateCellStyles()
{
var css = new CellStyles();
// cell style 0
var cs = new CellStyle();
cs.Name = StringValue.FromString("Normal");
cs.FormatId = 0;
cs.BuiltinId = 0;
css.AppendChild(cs);
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
return(css);
}
public SpreadsheetStylesManager(WorkbookPart workbookPart)
{
_workbookPart = workbookPart;
_styles = new Dictionary <string, int>();
_fonts = new Dictionary <FontKey, int>();
_fills = new Dictionary <System.Drawing.Color, int>();
var stylesPart = _workbookPart.AddNewPart <WorkbookStylesPart>();
_stylesheet = new Stylesheet();
stylesPart.Stylesheet = _stylesheet;
_stylesheet.Fills = new Fills();
_stylesheet.Fills.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.None
}
}); // required, reserved by Excel
_stylesheet.Fills.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.Gray125
}
}); // required, reserved by Excel
_stylesheet.Fills.Count = 2;
stylesPart.Stylesheet.Fonts = new Fonts();
stylesPart.Stylesheet.Fonts.Count = 2;
stylesPart.Stylesheet.Fonts.AppendChild(new Font());
Font hyperLinkFont = new Font();
Underline underline1 = new Underline();
FontSize fontSize2 = new FontSize()
{
Val = 11D
};
Color color2 = new Color()
{
Theme = (UInt32Value)10U
};
FontName fontName2 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering()
{
Val = 2
};
FontCharSet fontCharSet2 = new FontCharSet()
{
Val = 238
};
FontScheme fontScheme2 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
hyperLinkFont.Append(underline1);
hyperLinkFont.Append(fontSize2);
hyperLinkFont.Append(color2);
hyperLinkFont.Append(fontName2);
hyperLinkFont.Append(fontFamilyNumbering2);
hyperLinkFont.Append(fontCharSet2);
hyperLinkFont.Append(fontScheme2);
stylesPart.Stylesheet.Fonts.AppendChild(hyperLinkFont);
stylesPart.Stylesheet.Borders = new Borders();
stylesPart.Stylesheet.Borders.Count = 1;
stylesPart.Stylesheet.Borders.AppendChild(new Border());
stylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats();
stylesPart.Stylesheet.CellStyleFormats.Count = 2;
stylesPart.Stylesheet.CellStyleFormats.AppendChild(new CellFormat());
stylesPart.Stylesheet.CellFormats = new CellFormats();
// empty one for index 0, seems to be required
stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat());
CellFormat hyperLinkFormt = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, ApplyNumberFormat = false, ApplyFill = false, ApplyBorder = false, ApplyAlignment = false, ApplyProtection = false
};
stylesPart.Stylesheet.CellFormats.AppendChild(hyperLinkFormt);
stylesPart.Stylesheet.CellFormats.Count = 2;
_hyperlinkStyles[new SpreadsheetStyle().GetIdentifier()] = 1;
Borders borders = new Borders()
{
Count = (UInt32Value)1U
};
Border border = new Border();
LeftBorder leftBorder = new LeftBorder();
RightBorder rightBorder = new RightBorder();
TopBorder topBorder = new TopBorder();
BottomBorder bottomBorder = new BottomBorder();
DiagonalBorder diagonalBorder = new DiagonalBorder();
border.AppendChild(leftBorder);
border.AppendChild(rightBorder);
border.AppendChild(topBorder);
border.AppendChild(bottomBorder);
border.AppendChild(diagonalBorder);
borders.AppendChild(border);
_stylesheet.Borders = borders;
CellStyles cellStyles = new CellStyles()
{
Count = (UInt32Value)1U
};
CellStyle cellStyle = new CellStyle()
{
Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U
};
cellStyles.AppendChild(cellStyle);
_stylesheet.CellStyles = cellStyles;
var dateTimeFormatInfo = DateTimeFormatInfo.CurrentInfo;
var dateTimeFormat = "d.mm.yyyy hh:mm:ss";
if (dateTimeFormatInfo != null)
{
dateTimeFormat = dateTimeFormatInfo.ShortDatePattern + " " + dateTimeFormatInfo.LongTimePattern;
dateTimeFormat = dateTimeFormat.Replace("/", "\\/");
dateTimeFormat = dateTimeFormat.Replace("tt", "AM/PM");
}
NumberingFormats numberingFormats = new NumberingFormats()
{
Count = (UInt32Value)1U
};
NumberingFormat numberingFormat1 = new NumberingFormat()
{
NumberFormatId = (UInt32Value)164U, FormatCode = dateTimeFormat
};
numberingFormats.Append(numberingFormat1);
_stylesheet.NumberingFormats = numberingFormats;
}
public static Stylesheet CreateStylesheet()
{
Stylesheet stylesheet1 = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
var fonts1 = AddFonts();
var fills1 = AddFills();
var borders1 = AddBorders();
CellStyleFormats cellStyleFormats1 = new CellStyleFormats()
{
Count = 1U
};
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U
};
cellStyleFormats1.AppendChild(cellFormat1);
CellFormats cellFormats1 = new CellFormats()
{
Count = 4U
};
// Black text on White background
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U, FormatId = 0U
};
// White text on Orange background
CellFormat cellFormat3 = new CellFormat()
{
NumberFormatId = 0U, FontId = 1U, FillId = 2U, BorderId = 0U, FormatId = 0U, ApplyFill = true
};
// White text on Blue background
CellFormat cellFormat4 = new CellFormat()
{
NumberFormatId = 0U, FontId = 1U, FillId = 3U, BorderId = 0U, FormatId = 0U, ApplyFill = true
};
// Black text on Yellow background
CellFormat cellFormat5 = new CellFormat()
{
NumberFormatId = 0U, FontId = 0U, FillId = 4U, BorderId = 0U, FormatId = 0U, ApplyFill = true
};
cellFormats1.AppendChild(cellFormat2);
cellFormats1.AppendChild(cellFormat3);
cellFormats1.AppendChild(cellFormat4);
cellFormats1.AppendChild(cellFormat5);
CellStyles cellStyles1 = new CellStyles()
{
Count = 1U
};
CellStyle cellStyle1 = new CellStyle()
{
Name = "Normal", FormatId = 0U, BuiltinId = 0U
};
cellStyles1.AppendChild(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats()
{
Count = 0U
};
TableStyles tableStyles1 = new TableStyles()
{
Count = 0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9"
};
StylesheetExtensionList stylesheetExtensionList = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles slicerStyles = new DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles()
{
DefaultSlicerStyle = "SlicerStyleLight1"
};
stylesheetExtension.AppendChild(slicerStyles);
stylesheetExtensionList.AppendChild(stylesheetExtension);
stylesheet1.AppendChild(fonts1);
stylesheet1.AppendChild(fills1);
stylesheet1.AppendChild(borders1);
stylesheet1.AppendChild(cellStyleFormats1);
stylesheet1.AppendChild(cellFormats1);
stylesheet1.AppendChild(cellStyles1);
stylesheet1.AppendChild(differentialFormats1);
stylesheet1.AppendChild(tableStyles1);
stylesheet1.AppendChild(stylesheetExtensionList);
return(stylesheet1);
}
