private Fonts BuildFonts() { var fonts = new Fonts(); fonts.AppendChild(BuildFont(11, "000000", "Calibri")); FontDefaultId = 0; fonts.AppendChild(BuildFont(11, "000000", "Calibri", true)); FontBoldId = 1; fonts.AppendChild(BuildFont(11, "000000", "Calibri", false, true)); FontItalicId = 2; return(fonts); }
public CustomStylesheet() { // blank font list var fonts = new Fonts(); fonts.AppendChild(new Font()); fonts.Count = 1; Append(fonts); // create fills var fills = new Fills(); // create a solid blue fill var solidBlue = new PatternFill() { PatternType = PatternValues.Solid }; solidBlue.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("397FDB") }; // blue fill solidBlue.BackgroundColor = new BackgroundColor { Indexed = 64 }; fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.None } }); // required, reserved by Excel fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.Gray125 } }); // required, reserved by Excel fills.AppendChild(new Fill { PatternFill = solidBlue }); fills.Count = 3; Append(fills); // blank border list var borders = new Borders(); borders.AppendChild(new Border()); borders.AppendChild(new Border() { TopBorder = new TopBorder() { Style = BorderStyleValues.Thin }, RightBorder = new RightBorder() { Style = BorderStyleValues.Thin }, BottomBorder = new BottomBorder() { Style = BorderStyleValues.Thin }, LeftBorder = new LeftBorder() { Style = BorderStyleValues.Thin } }); borders.Count = 2; Append(borders); // blank cell format list var cellStyleFormats = new CellStyleFormats(); cellStyleFormats.AppendChild(new CellFormat()); cellStyleFormats.Count = 1; Append(cellStyleFormats); // cell format list var cellFormats = new CellFormats(); // empty one for index 0, seems to be required cellFormats.AppendChild(new CellFormat()); // cell format default with border cellFormats.AppendChild(new CellFormat() { FormatId = 0, FontId = 0, BorderId = 1, FillId = 0 }).AppendChild(new Alignment() { WrapText = true }); // cell format for header (blue with border) cellFormats.AppendChild(new CellFormat { FormatId = 0, FontId = 0, BorderId = 1, FillId = 2, ApplyFill = true }).AppendChild(new Alignment { Horizontal = HorizontalAlignmentValues.Center }); cellFormats.Count = 2; Append(cellFormats); }
private static Fonts CreateFonts() { var fts = new Fonts(); // font 0 fts.AppendChild(new Font { FontName = new FontName { Val = StringValue.FromString("Arial") }, FontSize = new FontSize { Val = DoubleValue.FromDouble(11) } }); // font 1 fts.AppendChild(new Font { FontName = new FontName { Val = StringValue.FromString("Arial") }, FontSize = new FontSize { Val = DoubleValue.FromDouble(12) }, Bold = new Bold() }); // font 2 fts.AppendChild(new Font { FontName = new FontName { Val = StringValue.FromString("Arial") }, FontSize = new FontSize { Val = DoubleValue.FromDouble(18) }, Bold = new Bold() }); // font 3 fts.AppendChild(new Font { FontName = new FontName { Val = StringValue.FromString("Arial") }, FontSize = new FontSize { Val = DoubleValue.FromDouble(14) } }); // font 4 fts.AppendChild(new Font { Color = new DocumentFormat.OpenXml.Spreadsheet.Color { Rgb = HexBinaryValueFromColor(Color.MediumBlue) }, FontName = new FontName { Val = StringValue.FromString("Arial") }, FontSize = new FontSize { Val = DoubleValue.FromDouble(11) } }); fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count); return(fts); }
private static Fonts CreateFonts() { var fts = new Fonts(); // font 0 var ft = new Font(); var ftn = new FontName { Val = StringValue.FromString("Arial") }; var ftsz = new FontSize { Val = DoubleValue.FromDouble(11) }; ft.FontName = ftn; ft.FontSize = ftsz; fts.AppendChild(ft); // font 1 ft = new Font(); ftn = new FontName { Val = StringValue.FromString("Arial") }; ftsz = new FontSize { Val = DoubleValue.FromDouble(12) }; ft.FontName = ftn; ft.FontSize = ftsz; ft.Bold = new Bold(); fts.AppendChild(ft); // font 2 ft = new Font(); ftn = new FontName { Val = StringValue.FromString("Arial") }; ftsz = new FontSize { Val = DoubleValue.FromDouble(18) }; ft.FontName = ftn; ft.FontSize = ftsz; ft.Bold = new Bold(); fts.AppendChild(ft); // font 3 ft = new Font(); ftn = new FontName { Val = StringValue.FromString("Arial") }; ftsz = new FontSize { Val = DoubleValue.FromDouble(14) }; ft.FontName = ftn; ft.FontSize = ftsz; fts.AppendChild(ft); // font 4 ft = new Font(); ftn = new FontName { Val = StringValue.FromString("Arial") }; ftsz = new FontSize { Val = DoubleValue.FromDouble(11) }; var fontColor = Color.MediumBlue; ft.Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = HexBinaryValueFromColor(fontColor) }; ft.FontName = ftn; ft.FontSize = ftsz; fts.AppendChild(ft); fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count); return(fts); }
private static Stylesheet SetDefault() { //Fonts var fonts = new Fonts(); var font0 = new Font(); var fontSize0 = new FontSize { Val = 10 }; font0.AppendChild(fontSize0); var font1 = new Font(); var fontSize1 = new FontSize { Val = 12 }; var fontBold1 = new Bold(); var fontColor1 = new Color { Rgb = "FFFFFF" }; font1.AppendChild(fontSize1); font1.AppendChild(fontBold1); font1.AppendChild(fontColor1); fonts.AppendChild(font0); fonts.AppendChild(font1); // Alignments var alignmentCenter = new Alignment(); alignmentCenter.Horizontal = HorizontalAlignmentValues.Center; //Fill var fills = new Fills(); var fill0 = new Fill(); var patternFill0 = new PatternFill { PatternType = PatternValues.None }; fill0.Append(patternFill0); var fillSkip = new Fill(); // Not valid needs to skip var patternFillSkip = new PatternFill { PatternType = PatternValues.None }; fillSkip.Append(patternFillSkip); var fill1 = new Fill(); var patternFill1 = new PatternFill { PatternType = PatternValues.Solid }; var foregroundColor1 = new ForegroundColor { Rgb = "00000000" }; var backgroundColor1 = new BackgroundColor { Indexed = 64U }; patternFill1.Append(foregroundColor1); patternFill1.Append(backgroundColor1); fill1.Append(patternFill1); fills.Append(fill0); fills.Append(fillSkip); fills.Append(fill1); //Border var borders = new Borders(); var border0 = new Border(); var border1 = new Border(); borders.AppendChild(border0); borders.AppendChild(border1); var cellFormats = new CellFormats( new CellFormat { FontId = 0, FillId = 0, BorderId = 0, ApplyFill = true }, // Index 0 new CellFormat { FontId = 1, FillId = 2, BorderId = 1, ApplyFill = true, Alignment = alignmentCenter } // Index 1 ); return(new Stylesheet(fonts, fills, borders, cellFormats)); }
private void AppendWithIndexSave(Fonts parent, Font child, ExcelStylesheetFontIndex excelSheetIndex) { parent.AppendChild(child); _indexes.Add(excelSheetIndex, (uint)_indexes.Count); }
public ExcelStylesheetProvider() { var fonts = new Fonts(); //default Font fonts.AppendChild(new Font { Color = new Color() }); uint defaultFontsCount = (uint)fonts.ChildElements.Count; var fills = new Fills(); //default Fills fills.AppendChild(new Fill(new PatternFill { PatternType = PatternValues.None })); fills.AppendChild(new Fill(new PatternFill { PatternType = PatternValues.Gray125 })); uint defaultFillsCount = (uint)fills.ChildElements.Count; foreach (var color in Colors.Values) { fonts.AppendChild(new Font { Color = new Color { Rgb = color } }); fonts.AppendChild(new Font { Color = new Color { Rgb = color }, Bold = new Bold() }); fonts.AppendChild(new Font { Color = new Color { Rgb = color }, Strike = new Strike() }); fonts.AppendChild(new Font { Color = new Color { Rgb = color }, Bold = new Bold(), Strike = new Strike() }); fills.AppendChild(new Fill ( new PatternFill { ForegroundColor = new ForegroundColor { Rgb = color }, PatternType = PatternValues.Solid } )); } fonts.Count = (uint)fonts.ChildElements.Count; fills.Count = (uint)fills.ChildElements.Count; var borders = new Borders(); //default Border borders.AppendChild(new Border()); borders.Append(new Border { LeftBorder = new LeftBorder { Style = BorderStyleValues.Medium, Color = new Color { Indexed = (UInt32Value)64U } }, RightBorder = new RightBorder { Style = BorderStyleValues.Medium, Color = new Color { Indexed = (UInt32Value)64U } }, TopBorder = new TopBorder { Style = BorderStyleValues.Medium, Color = new Color { Indexed = (UInt32Value)64U } }, BottomBorder = new BottomBorder { Style = BorderStyleValues.Medium, Color = new Color { Indexed = (UInt32Value)64U } }, DiagonalBorder = new DiagonalBorder() }); borders.Count = (uint)borders.ChildElements.Count; var cellFormats = new CellFormats(); //default CellFormat cellFormats.AppendChild(new CellFormat { FontId = 0, FillId = 0, BorderId = 0 }); uint fontIndex = 0; uint csIndex = 0; foreach (Font font in fonts.ChildElements) { if (fontIndex < defaultFontsCount) { fontIndex++; continue; } uint fillIndex = 0; foreach (Fill fill in fills.ChildElements) { if (fillIndex < defaultFillsCount) { fillIndex++; continue; } foreach (var typeDetails in SupportedTypesFormats.Data) { foreach (HorizontalAlignment hor in Enum.GetValues(typeof(HorizontalAlignment))) { foreach (VerticalAlignment ver in Enum.GetValues(typeof(VerticalAlignment))) { cellFormats.AppendChild(new CellFormat { ApplyNumberFormat = true, NumberFormatId = typeDetails.Value, ApplyAlignment = true, Alignment = new Alignment { Vertical = ToVerticalAlignmentValues(ver), WrapText = true, Horizontal = ToHorizontalAlignmentValues(hor) }, ApplyBorder = true, BorderId = 1, ApplyFont = true, FontId = fontIndex, ApplyFill = true, FillId = fillIndex, FormatId = 0 }); csIndex++; _styles[GetKey(typeDetails.Key, font.Color.Rgb, fill.PatternFill.ForegroundColor.Rgb.Value, font.Bold != null, font.Strike != null, hor, ver)] = csIndex; } } } fillIndex++; } fontIndex++; } cellFormats.Count = (uint)cellFormats.ChildElements.Count; Stylesheet = new Stylesheet(fonts, fills, borders, cellFormats); }
// Stylesheet has to follow this order: // Font -> Fills/Borders -> CellFormats // If you change *any* of the order, Excel will consider the spreadsheet broken. internal Stylesheet CreateStylesheet() { var stylesheet = new Stylesheet(); var fonts = new Fonts(); fonts.AppendChild(new Font { Bold = new Bold(), FontName = new FontName { Val = "Microsoft YaHei" }, FontSize = new FontSize { Val = 12 }, FontFamilyNumbering = new FontFamilyNumbering { Val = 1 } }); fonts.AppendChild(new Font { FontName = new FontName { Val = "Microsoft YaHei Light" }, FontSize = new FontSize { Val = 12 }, FontFamilyNumbering = new FontFamilyNumbering { Val = 1 } }); fonts.KnownFonts = true; fonts.Count = (uint)fonts.ChildElements.Count; stylesheet.AppendChild(fonts); // Default everything else because Excel considers this // spreadsheet broken if it's missing *any* of these. Fill fill = new Fill() { PatternFill = new PatternFill() }; Fills fills = new Fills(); fills.AppendChild(fill); fills.Count = (uint)fills.ChildElements.Count; stylesheet.AppendChild(fills); Border border = new Border() { LeftBorder = new LeftBorder(), RightBorder = new RightBorder(), BottomBorder = new BottomBorder(), DiagonalBorder = new DiagonalBorder(), TopBorder = new TopBorder() }; Borders borders = new Borders(); borders.AppendChild(border); borders.Count = (uint)borders.ChildElements.Count; stylesheet.AppendChild(borders); // Now we can actually define the cell formats. // Screw OpenXML. var cellFormats = new CellFormats(); var titleCellFormat = new CellFormat() { FontId = 0, FillId = 0, BorderId = 0 }; var regularCellFormat = new CellFormat() { FontId = 1, FillId = 0, BorderId = 0, ApplyFont = true }; cellFormats.AppendChild(titleCellFormat); cellFormats.AppendChild(regularCellFormat); cellFormats.Count = (uint)cellFormats.ChildElements.Count; stylesheet.AppendChild(cellFormats); return(stylesheet); }