private Stylesheet CreateStylesheet() { var stylesheet = new Stylesheet(); // Order is important! stylesheet.AppendChild(_numberingFormatProvider.GetNumberingFormats()); stylesheet.AppendChild(_fontProvider.GetFonts()); stylesheet.AppendChild(_fillProvider.GetFills()); stylesheet.AppendChild(_borderProvider.GetBorders()); stylesheet.AppendChild(CreateCellFormats()); return(stylesheet); }
/// <summary> /// Import cell format /// </summary> /// <param name="sourceStyleSheet"></param> /// <param name="srcFormat"></param> /// <param name="targetStyleSheet"></param> /// <returns></returns> protected static uint ImportCellFormat(Stylesheet sourceStyleSheet, CellFormat srcFormat, Stylesheet targetStyleSheet) { var tgtCellFormats = targetStyleSheet.CellFormats ?? targetStyleSheet.AppendChild(new CellFormats()); var tgtCellFormat = tgtCellFormats.AppendChild(srcFormat.Clone() as CellFormat); tgtCellFormats.Count++; // Import Style if (srcFormat.FormatId != null) { var srcStyle = sourceStyleSheet.CellStyleFormats.ElementAt((int)srcFormat.FormatId.Value) as CellFormat; if (srcStyle == null) { throw new InvalidOperationException(String.Format("Style {0} was not found in source document!", srcFormat.FormatId)); } // Style is complex, so just clone it var tgtStyle = srcStyle.Clone() as CellFormat; // Add style to target if (targetStyleSheet.CellStyleFormats == null) { targetStyleSheet.CellStyleFormats = new CellStyleFormats(); } targetStyleSheet.CellStyleFormats.AppendChild(tgtStyle); tgtCellFormat.FormatId = targetStyleSheet.CellStyleFormats.Count - 1; // Import details ImportCellFormatDetails(sourceStyleSheet, tgtStyle, targetStyleSheet); } // Import details ImportCellFormatDetails(sourceStyleSheet, tgtCellFormat, targetStyleSheet); return(tgtCellFormats.Count - 1); }
/// <summary> /// Import cell format /// </summary> /// <param name="sourceStyleSheet"></param> /// <param name="srcFormat"></param> /// <param name="targetStyleSheet"></param> /// <returns></returns> protected static uint ImportCellFormat(Stylesheet sourceStyleSheet, CellFormat srcFormat, Stylesheet targetStyleSheet) { var tgtCellFormats = targetStyleSheet.CellFormats ?? targetStyleSheet.AppendChild(new CellFormats()); var tgtCellFormat = tgtCellFormats.AppendChild(srcFormat.Clone() as CellFormat); tgtCellFormats.Count ++; // Import Style if (srcFormat.FormatId != null) { var srcStyle = sourceStyleSheet.CellStyleFormats.ElementAt((int) srcFormat.FormatId.Value) as CellFormat; if (srcStyle == null) throw new InvalidOperationException(String.Format("Style {0} was not found in source document!", srcFormat.FormatId)); // Style is complex, so just clone it var tgtStyle = srcStyle.Clone() as CellFormat; // Add style to target if (targetStyleSheet.CellStyleFormats == null) targetStyleSheet.CellStyleFormats = new CellStyleFormats(); targetStyleSheet.CellStyleFormats.AppendChild(tgtStyle); tgtCellFormat.FormatId = targetStyleSheet.CellStyleFormats.Count - 1; // Import details ImportCellFormatDetails(sourceStyleSheet, tgtStyle, targetStyleSheet); } // Import details ImportCellFormatDetails(sourceStyleSheet, tgtCellFormat, targetStyleSheet); return tgtCellFormats.Count - 1; }
// 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); }
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); }