Exemple #1
0
        /// <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;
        }
        public CustomStylesheet()
        {
            NumberStyles = new Dictionary<NumberStyle, NumberingFormat>();
            CellFormatStyles = new Dictionary<CellFormatId, uint>();

            Borders = new Borders();
            BorderStyles = new Dictionary<string, uint>();

            Fonts = new Fonts();
            FontStyles = new Dictionary<string, uint>();

            RegisterBorder("None");
            RegisterFont("Default", new Font
            {
             				FontName = new FontName { Val = StringValue.FromString("Calibri") },
                FontSize = new FontSize { Val = DoubleValue.FromDouble(10) }
            });

            var fills = CreateFills();

            var numberingFormats = CreateNumberFormats();

            MyCellFormats = new DocumentFormat.OpenXml.Spreadsheet.CellFormats();
            MyCellFormats.Append(new CellFormat
            {
                NumberFormatId = 0,
                FontId = 0,
                FillId = 0,
                BorderId = 0,
                FormatId = 0
            });

            //BuildCellFormat(BorderStyle.All, NumberStyle.ForcedText);

            var cellStyleFormats = new CellStyleFormats();

            var cellFormat = new CellFormat
            {
                Alignment = new Alignment() { Horizontal = HorizontalAlignmentValues.Center },
                NumberFormatId = 0,
                FontId = 0,
                FillId = 0,
                BorderId = 0
            };
            cellStyleFormats.Append(cellFormat);
            cellStyleFormats.Count =
                 UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);

            //var cellFormats = CreateCellFormats();

            this.Append(numberingFormats);
            this.Append(Fonts);	//	OK
            this.Append(fills);
            this.Append(Borders);	//	OK
            this.Append(cellStyleFormats);
            this.Append(MyCellFormats);
            var css = new CellStyles();
            var cs = new CellStyle
            {
                Name = StringValue.FromString("Normal"),
                FormatId = 0,
                BuiltinId = 0
            };
            css.Append(cs);
            css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
            this.Append(css);
            var dfs = new DifferentialFormats { Count = 0 };
            this.Append(dfs);
            var tss = new TableStyles
            {
                Count = 0,
                DefaultTableStyle = StringValue.FromString("TableStyleMedium9"),
                DefaultPivotStyle = StringValue.FromString("PivotStyleLight16")
            };
            this.Append(tss);
        }
Exemple #3
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);
        }