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 Borders CreateBorders() { var borders = new Borders(); // boarder index 0 borders.AppendChild(new Border { LeftBorder = new LeftBorder(), RightBorder = new RightBorder(), TopBorder = new TopBorder(), BottomBorder = new BottomBorder(), DiagonalBorder = new DiagonalBorder() }); // boarder Index 1 borders.AppendChild(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() }); // boarder Index 2 borders.AppendChild(new Border { LeftBorder = new LeftBorder(), RightBorder = new RightBorder(), TopBorder = new TopBorder { Style = BorderStyleValues.Thin }, BottomBorder = new BottomBorder { Style = BorderStyleValues.Thin }, DiagonalBorder = new DiagonalBorder() }); borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count); return(borders); }
private static Borders CreateBorders() { var borders = new Borders(); // boarder index 0 var border = new Border(); border.LeftBorder = new LeftBorder(); border.RightBorder = new RightBorder(); border.TopBorder = new TopBorder(); border.BottomBorder = new BottomBorder(); border.DiagonalBorder = new DiagonalBorder(); borders.AppendChild(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.AppendChild(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.AppendChild(border); borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count); return(borders); }
private static Borders AddBorders() { 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.AppendChild(leftBorder1); border1.AppendChild(rightBorder1); border1.AppendChild(topBorder1); border1.AppendChild(bottomBorder1); border1.AppendChild(diagonalBorder1); borders1.AppendChild(border1); return(borders1); }
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)); }
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 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); }
private void AppendWithIndexSave(Borders parent, Border child, ExcelSheetBorderIndex excelSheetIndex) { parent.AppendChild(child); _indexes.Add(excelSheetIndex, (uint)_indexes.Count); }
// 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); }