static Stylesheet CreateStylesheet()
        {
            Stylesheet ss = new Stylesheet();

            #region fts (fonts)
            Fonts    fts = new Fonts();
            var      ft  = new DocumentFormat.OpenXml.Spreadsheet.Font();
            FontName ftn = new FontName();
            ftn.Val = StringValue.FromString("Arial");
            FontSize ftsz = new FontSize();
            ftsz.Val    = DoubleValue.FromDouble(11);
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);

            ft          = new DocumentFormat.OpenXml.Spreadsheet.Font();
            ftn         = new FontName();
            ftn.Val     = StringValue.FromString("Verdana");
            ftsz        = new FontSize();
            ftsz.Val    = DoubleValue.FromDouble(18);
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);

            ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
            ft.Append(new Bold());
            ftn         = new FontName();
            ftn.Val     = StringValue.FromString("Arial");
            ftsz        = new FontSize();
            ftsz.Val    = DoubleValue.FromDouble(11);
            ft.FontName = ftn;
            ft.FontSize = ftsz;
            fts.Append(ft);

            fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
            #endregion

            #region fills
            Fills       fills = new Fills();
            Fill        fill;
            PatternFill patternFill;
            fill                    = new Fill();
            patternFill             = new PatternFill();
            patternFill.PatternType = PatternValues.None;
            fill.PatternFill        = patternFill;
            fills.Append(fill);

            fill                    = new Fill();
            patternFill             = new PatternFill();
            patternFill.PatternType = PatternValues.Gray125;
            fill.PatternFill        = patternFill;
            fills.Append(fill);

            fill                            = new Fill();
            patternFill                     = new PatternFill();
            patternFill.PatternType         = PatternValues.Solid;
            patternFill.ForegroundColor     = new ForegroundColor();
            patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString("00efffd9");
            patternFill.BackgroundColor     = new BackgroundColor();
            patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
            fill.PatternFill                = patternFill;
            fills.Append(fill);

            fill                            = new Fill();
            patternFill                     = new PatternFill();
            patternFill.PatternType         = PatternValues.Solid;
            patternFill.ForegroundColor     = new ForegroundColor();
            patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString("00f8e8d6");
            patternFill.BackgroundColor     = new BackgroundColor();
            patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
            fill.PatternFill                = patternFill;
            fills.Append(fill);

            fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
            #endregion

            #region borders
            Borders borders = new Borders();
            Border  border  = new Border();
            border.LeftBorder     = new LeftBorder();
            border.RightBorder    = new RightBorder();
            border.TopBorder      = new TopBorder();
            border.BottomBorder   = new BottomBorder();
            border.DiagonalBorder = new DiagonalBorder();
            borders.Append(border);

            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.Append(border);
            borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
            #endregion

            #region csfs (CellStyleFormats)
            CellStyleFormats csfs = new CellStyleFormats();
            CellFormat       cf   = new CellFormat();
            cf.NumberFormatId = 0;
            cf.FontId         = 0;
            cf.FillId         = 0;
            cf.BorderId       = 0;
            csfs.Append(cf);
            csfs.Count = UInt32Value.FromUInt32((uint)csfs.ChildElements.Count);
            #endregion


            uint        iExcelIndex = 164;
            var         nfs         = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormats();
            CellFormats cfs         = new CellFormats();

            cf = new CellFormat();
            cf.NumberFormatId = 0;
            cf.FontId         = 0;
            cf.FillId         = 0;
            cf.BorderId       = 0;
            cf.FormatId       = 0;
            cfs.Append(cf);

            var nfDateTime = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
            nfDateTime.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
            nfDateTime.FormatCode     = StringValue.FromString("dd/mm/yyyy hh:mm:ss");
            nfs.Append(nfDateTime);

            var nfDate = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
            nfDate.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
            nfDate.FormatCode     = StringValue.FromString("dd/mm/yyyy");
            nfs.Append(nfDate);

            var nf4decimal = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
            nf4decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
            nf4decimal.FormatCode     = StringValue.FromString("#,##0.0000");
            nfs.Append(nf4decimal);

            // #,##0.00 is also Excel style index 4
            var nf2decimal = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
            nf2decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
            nf2decimal.FormatCode     = StringValue.FromString("#,##0.00");
            nfs.Append(nf2decimal);

            // @ is also Excel style index 49
            var nfForcedText = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
            nfForcedText.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
            nfForcedText.FormatCode     = StringValue.FromString("@");
            nfs.Append(nfForcedText);

            // #,##0.00 is also Excel style index 4
            var nf0decimal = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
            nf0decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
            nf0decimal.FormatCode     = StringValue.FromString("#,##0");
            nfs.Append(nf0decimal);

            // index 1
            cf = new CellFormat();
            cf.NumberFormatId    = nfDate.NumberFormatId;
            cf.FontId            = 0; //Arial 11
            cf.FillId            = 0;
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 2
            cf = new CellFormat();
            cf.NumberFormatId    = nf4decimal.NumberFormatId;
            cf.FontId            = 0; //Arial 11
            cf.FillId            = 0;
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 3
            cf = new CellFormat();
            cf.NumberFormatId    = nf0decimal.NumberFormatId;
            cf.FontId            = 0; //Arial 11
            cf.FillId            = 0;
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 4
            cf = new CellFormat();
            cf.NumberFormatId    = nfForcedText.NumberFormatId;
            cf.FontId            = 0; //Arial 11
            cf.FillId            = 0; //NO Fill
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 5
            cf = new CellFormat();
            cf.NumberFormatId    = nfForcedText.NumberFormatId;
            cf.FontId            = 1; //Verdana 18
            cf.FillId            = 0; //NO Fill
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 6
            // column text
            cf = new CellFormat();
            cf.NumberFormatId    = nfForcedText.NumberFormatId;
            cf.FontId            = 2; //Arial 11, Bold
            cf.FillId            = 0; //NO Fill
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 7
            cf = new CellFormat();
            cf.NumberFormatId    = nfForcedText.NumberFormatId;
            cf.FontId            = 0; //Arial 11
            cf.FillId            = 3; //Light Orange
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 8
            // column text
            cf = new CellFormat();
            cf.NumberFormatId    = nfForcedText.NumberFormatId;
            cf.FontId            = 2; //Arial 11, Bold
            cf.FillId            = 2; //Light Green
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cfs.Append(cf);

            // index 9
            cf = new CellFormat();
            cf.NumberFormatId    = nfForcedText.NumberFormatId;
            cf.FontId            = 0; //Arial 11
            cf.FillId            = 0; //NO Fill
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cf.Append(new Alignment()
            {
                WrapText = true
            });
            cfs.Append(cf);

            // index 10
            cf = new CellFormat();
            cf.NumberFormatId    = nfForcedText.NumberFormatId;
            cf.FontId            = 0; //Arial 11
            cf.FillId            = 3; //Light Orange
            cf.BorderId          = 0;
            cf.FormatId          = 0;
            cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
            cf.Append(new Alignment()
            {
                WrapText = true
            });
            cfs.Append(cf);

            nfs.Count = UInt32Value.FromUInt32((uint)nfs.ChildElements.Count);
            cfs.Count = UInt32Value.FromUInt32((uint)cfs.ChildElements.Count);

            ss.Append(nfs);
            ss.Append(fts);
            ss.Append(fills);
            ss.Append(borders);
            ss.Append(csfs);
            ss.Append(cfs);

            CellStyles css = new CellStyles();
            CellStyle  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);
            ss.Append(css);

            DifferentialFormats dfs = new DifferentialFormats();
            dfs.Count = 0;
            ss.Append(dfs);

            TableStyles tss = new TableStyles();
            tss.Count             = 0;
            tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
            tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
            ss.Append(tss);

            return(ss);
        }
Пример #2
0
        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);
        }