/// <summary> /// Creates Stylesheet from collections of border styles, fontstyles, color styles etc... /// </summary> public static Stylesheet GetStyleSheet(ExcelType scheme) { var stylesheet = new Stylesheet(); var fontCollection = new FontCollection(); var fillsCollection = new FillsCollection(); var borderCollection = new BorderCollection(); var cellStyleFormats = new CellStyleFormats(); var cellStyle = new CellFormat(); cellStyle.NumberFormatId = 0; cellStyle.FontId = 0; cellStyle.FillId = 0; cellStyle.BorderId = 0; cellStyleFormats.Append(cellStyle); cellStyleFormats.Count = UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count); var numberingFormats = new CellContentFormat(); var cellFormats = new DocumentFormat.OpenXml.Spreadsheet.CellFormats(); cellFormats.Append(GetStyleSheetScheme(scheme)); cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count); stylesheet.Append(numberingFormats); stylesheet.Append(fontCollection); stylesheet.Append(fillsCollection); stylesheet.Append(borderCollection); stylesheet.Append(cellStyleFormats); stylesheet.Append(cellFormats); var css = new DocumentFormat.OpenXml.Spreadsheet.CellStyles(); var cs = new CellStyle(); cs.Name = StringValue.FromString("Normal"); cs.FormatId = 0; cs.BuiltinId = 0; css.Append(cs); css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count); stylesheet.Append(css); var dfs = new DifferentialFormats(); dfs.Count = 0; stylesheet.Append(dfs); var tss = new TableStyles(); tss.Count = 0; tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9"); tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16"); stylesheet.Append(tss); return stylesheet; }
private static MSOpenXML.Stylesheet CreateStylesheet(int pQuantidadeDecimais = 2) { MSOpenXML.Stylesheet ss = new MSOpenXML.Stylesheet(); #region Fontes MSOpenXML.Fonts fts = new MSOpenXML.Fonts(); MSOpenXML.Font ft = new MSOpenXML.Font(); MSOpenXML.FontName ftn = new MSOpenXML.FontName(); ftn.Val = "Calibri"; MSOpenXML.FontSize ftsz = new MSOpenXML.FontSize(); ftsz.Val = 11; ft.FontName = ftn; ft.FontSize = ftsz; fts.Append(ft); ft = new MSOpenXML.Font(); ft.Bold = new MSOpenXML.Bold(); ftn = new MSOpenXML.FontName(); ftn.Val = "Calibri"; ftsz = new MSOpenXML.FontSize(); ftsz.Val = 11; ft.FontName = ftn; ft.FontSize = ftsz; fts.Append(ft); fts.Count = (uint)fts.ChildElements.Count; #endregion #region Preenchimento MSOpenXML.Fills fills = new MSOpenXML.Fills(); MSOpenXML.Fill fill; MSOpenXML.PatternFill patternFill; fill = new MSOpenXML.Fill(); patternFill = new MSOpenXML.PatternFill(); patternFill.PatternType = MSOpenXML.PatternValues.None; fill.PatternFill = patternFill; fills.Append(fill); /*fill = new dos.Fill(); * patternFill = new dos.PatternFill(); * patternFill.PatternType = dos.PatternValues.Gray125; * fill.PatternFill = patternFill; * fills.Append(fill); * * fill = new dos.Fill(); * patternFill = new dos.PatternFill(); * patternFill.PatternType = dos.PatternValues.Solid; * patternFill.ForegroundColor = new dos.ForegroundColor(); * patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString("00ff9728"); * patternFill.BackgroundColor = new dos.BackgroundColor(); * patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb; * fill.PatternFill = patternFill; * fills.Append(fill); */ fills.Count = (uint)fills.ChildElements.Count; #endregion #region Bordas MSOpenXML.Borders borders = new MSOpenXML.Borders(); MSOpenXML.Border border = new MSOpenXML.Border(); border.LeftBorder = new MSOpenXML.LeftBorder(); border.RightBorder = new MSOpenXML.RightBorder(); border.TopBorder = new MSOpenXML.TopBorder(); border.BottomBorder = new MSOpenXML.BottomBorder(); border.DiagonalBorder = new MSOpenXML.DiagonalBorder(); borders.Append(border); border = new MSOpenXML.Border(); border.LeftBorder = new MSOpenXML.LeftBorder(); border.RightBorder = new MSOpenXML.RightBorder(); border.TopBorder = new MSOpenXML.TopBorder(); border.TopBorder.Style = MSOpenXML.BorderStyleValues.Thin; border.BottomBorder = new MSOpenXML.BottomBorder(); border.DiagonalBorder = new MSOpenXML.DiagonalBorder(); borders.Append(border); borders.Count = (uint)borders.ChildElements.Count; #endregion MSOpenXML.CellStyleFormats csfs = new MSOpenXML.CellStyleFormats(); MSOpenXML.CellFormat cf = new MSOpenXML.CellFormat(); cf.NumberFormatId = 0; cf.FontId = 0; cf.BorderId = 0; cf.Alignment = new MSOpenXML.Alignment() { WrapText = false }; csfs.Append(cf); csfs.Count = (uint)csfs.ChildElements.Count; uint iExcelIndex = 164; MSOpenXML.NumberingFormats nfs = new MSOpenXML.NumberingFormats(); MSOpenXML.CellFormats cfs = new MSOpenXML.CellFormats(); cf = new MSOpenXML.CellFormat(); cf.NumberFormatId = 0; cf.FontId = 0; cf.BorderId = 0; cf.FormatId = 0; cf.Alignment = new MSOpenXML.Alignment() { WrapText = false }; cfs.Append(cf); MSOpenXML.NumberingFormat nfDateTime = new MSOpenXML.NumberingFormat(); nfDateTime.NumberFormatId = iExcelIndex++; nfDateTime.FormatCode = "dd/mm/yyyy"; nfs.Append(nfDateTime); MSOpenXML.NumberingFormat nf4decimal = new MSOpenXML.NumberingFormat(); nf4decimal.NumberFormatId = iExcelIndex++; nf4decimal.FormatCode = "#,##0"; nfs.Append(nf4decimal); // #,##0.00 is also Excel style index 4 MSOpenXML.NumberingFormat nf2decimal = new MSOpenXML.NumberingFormat(); nf2decimal.NumberFormatId = iExcelIndex++; nf2decimal.FormatCode = FormatoDecimal(pQuantidadeDecimais); //"#,##0.00" nfs.Append(nf2decimal); // @ is also Excel style index 49 MSOpenXML.NumberingFormat nfForcedText = new MSOpenXML.NumberingFormat(); nfForcedText.NumberFormatId = iExcelIndex++; nfForcedText.FormatCode = "@"; nfs.Append(nfForcedText); // index 1 cf = new MSOpenXML.CellFormat(); cf.NumberFormatId = nfDateTime.NumberFormatId; cf.FontId = 0; cf.BorderId = 0; cf.FormatId = 0; cf.ApplyNumberFormat = true; cf.Alignment = new MSOpenXML.Alignment() { WrapText = false }; cfs.Append(cf); // index 2 cf = new MSOpenXML.CellFormat(); cf.NumberFormatId = nf4decimal.NumberFormatId; cf.FontId = 0; cf.BorderId = 0; cf.FormatId = 0; cf.ApplyNumberFormat = true; cf.Alignment = new MSOpenXML.Alignment() { WrapText = false }; cfs.Append(cf); // index 3 cf = new MSOpenXML.CellFormat(); cf.NumberFormatId = nf2decimal.NumberFormatId; cf.FontId = 0; cf.BorderId = 0; cf.FormatId = 0; cf.ApplyNumberFormat = true; cf.Alignment = new MSOpenXML.Alignment() { WrapText = false }; cfs.Append(cf); // index 4 cf = new MSOpenXML.CellFormat(); cf.NumberFormatId = nfForcedText.NumberFormatId; cf.FontId = 0; cf.BorderId = 0; cf.FormatId = 0; cf.ApplyNumberFormat = true; cf.Alignment = new MSOpenXML.Alignment() { WrapText = false }; cfs.Append(cf); // index 5 // Header text cf = new MSOpenXML.CellFormat(); cf.NumberFormatId = nfForcedText.NumberFormatId; cf.FontId = 1; cf.BorderId = 0; cf.FormatId = 0; cf.Alignment = new MSOpenXML.Alignment() { WrapText = false }; cf.Alignment.Horizontal = MSOpenXML.HorizontalAlignmentValues.Center; cf.ApplyNumberFormat = true; cfs.Append(cf); // index 6 // group text cf = new MSOpenXML.CellFormat(); cf.NumberFormatId = nf2decimal.NumberFormatId; cf.FontId = 1; cf.BorderId = 1; cf.FormatId = 0; cf.ApplyNumberFormat = true; cf.Alignment = new MSOpenXML.Alignment() { WrapText = false }; cfs.Append(cf); // index 7 // Total text, ColumnHeader Text cf = new MSOpenXML.CellFormat(); cf.NumberFormatId = nf2decimal.NumberFormatId; cf.FontId = 1; cf.BorderId = 0; cf.FormatId = 0; cf.ApplyNumberFormat = true; cf.Alignment = new MSOpenXML.Alignment() { WrapText = false }; cfs.Append(cf); nfs.Count = (uint)nfs.ChildElements.Count; cfs.Count = (uint)cfs.ChildElements.Count; ss.Append(nfs); ss.Append(fts); ss.Append(fills); ss.Append(borders); ss.Append(csfs); ss.Append(cfs); MSOpenXML.CellStyles css = new MSOpenXML.CellStyles(); MSOpenXML.CellStyle cs = new MSOpenXML.CellStyle(); cs.Name = "Normal"; cs.FormatId = 0; cs.BuiltinId = 0; css.Append(cs); css.Count = (uint)css.ChildElements.Count; ss.Append(css); MSOpenXML.DifferentialFormats dfs = new MSOpenXML.DifferentialFormats(); dfs.Count = 0; ss.Append(dfs); MSOpenXML.TableStyles tss = new MSOpenXML.TableStyles(); tss.Count = 0; //tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9"); //tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16"); ss.Append(tss); return(ss); }