Esempio n. 1
0
        public void Write(Fill fill)
        {
            if (fill == null)
            {
                throw new ArgumentNullException(nameof(fill));
            }

            var f = new OpenXml.Fill();

            if (fill.PatternFill != null)
            {
                f.PatternFill = new OpenXml.PatternFill {
                    PatternType = MapFillPattern(fill.PatternFill.PatternType)
                };

                if (fill.PatternFill.ForegroundColor != null)
                {
                    f.PatternFill.Append(fill.PatternFill.ForegroundColor.MapToForegroundColor());
                }

                if (fill.PatternFill.BackgroundColor != null)
                {
                    f.PatternFill.Append(fill.PatternFill.BackgroundColor.MapToBackgroundColor());
                }
            }

            stylesheet.Fills.Append(f);
        }
Esempio n. 2
0
        static Fills GenerateFills()
        {
            var fills = new Fills {
                Count = 2U
            };

            var fill1        = new Fill();
            var patternFill1 = new PatternFill {
                PatternType = PatternValues.None
            };

            fill1.Append(patternFill1);

            var fill2        = new Fill();
            var patternFill2 = new PatternFill()
            {
                PatternType = PatternValues.Gray125
            };

            fill2.Append(patternFill2);

            fills.Append(fill1);
            fills.Append(fill2);

            return(fills);
        }
        private bool FillsAreEqual(Fill f, IXLFill xlFill)
        {
            var nF = new XLFill();
            if (f.PatternFill != null)
            {
                if (f.PatternFill.PatternType != null)
                    nF.PatternType = f.PatternFill.PatternType.Value.ToClosedXml();

                var fColor = GetColor(f.PatternFill.ForegroundColor);
                if (fColor.HasValue)
                    nF.PatternColor = fColor;

                var bColor = GetColor(f.PatternFill.BackgroundColor);
                if (bColor.HasValue)
                    nF.PatternBackgroundColor = bColor;
            }
            return nF.Equals(xlFill);
        }
        private static Fill GetNewFill(FillInfo fillInfo, Boolean ignoreMod = true)
        {
            var fill = new Fill();

            var patternFill = new PatternFill();
            if (fillInfo.Fill.PatternTypeModified || ignoreMod)
                patternFill.PatternType = fillInfo.Fill.PatternType.ToOpenXml();

            if (fillInfo.Fill.PatternColorModified || ignoreMod)
            {
                var foregroundColor = new ForegroundColor();
                if (fillInfo.Fill.PatternColor.ColorType == XLColorType.Color)
                    foregroundColor.Rgb = fillInfo.Fill.PatternColor.Color.ToHex();
                else if (fillInfo.Fill.PatternColor.ColorType == XLColorType.Indexed)
                    foregroundColor.Indexed = (UInt32)fillInfo.Fill.PatternColor.Indexed;
                else
                {
                    foregroundColor.Theme = (UInt32)fillInfo.Fill.PatternColor.ThemeColor;
                    if (fillInfo.Fill.PatternColor.ThemeTint != 1)
                        foregroundColor.Tint = fillInfo.Fill.PatternColor.ThemeTint;
                }
                patternFill.AppendChild(foregroundColor);
            }

            if (fillInfo.Fill.PatternBackgroundColorModified || ignoreMod)
            {
                var backgroundColor = new BackgroundColor();
                if (fillInfo.Fill.PatternBackgroundColor.ColorType == XLColorType.Color)
                    backgroundColor.Rgb = fillInfo.Fill.PatternBackgroundColor.Color.ToHex();
                else if (fillInfo.Fill.PatternBackgroundColor.ColorType == XLColorType.Indexed)
                    backgroundColor.Indexed = (UInt32)fillInfo.Fill.PatternBackgroundColor.Indexed;
                else
                {
                    backgroundColor.Theme = (UInt32)fillInfo.Fill.PatternBackgroundColor.ThemeColor;
                    if (fillInfo.Fill.PatternBackgroundColor.ThemeTint != 1)
                        backgroundColor.Tint = fillInfo.Fill.PatternBackgroundColor.ThemeTint;
                }
                patternFill.AppendChild(backgroundColor);
            }

            fill.AppendChild(patternFill);

            return fill;
        }
        private static void ResolveFillWithPattern(Fills fills, PatternValues patternValues)
        {
            if (fills.Elements<Fill>().Any(f =>
                f.PatternFill.PatternType == patternValues
                && f.PatternFill.ForegroundColor == null
                && f.PatternFill.BackgroundColor == null
                )) return;

            var fill1 = new Fill();
            var patternFill1 = new PatternFill {PatternType = patternValues};
            fill1.AppendChild(patternFill1);
            fills.AppendChild(fill1);
        }
Esempio n. 6
0
 public void ApplySettings(DocumentFormat.OpenXml.Spreadsheet.Font font, DocumentFormat.OpenXml.Spreadsheet.Fill fill, params ExcelBorder[] borders)
 {
 }
Esempio n. 7
0
        public override byte[] Render(global::Campus.Report.Base.Report report)
        {
            byte[] buffer;
            using (var outputMemoryStream = new MemoryStream())
            {
                using (var document = SpreadsheetDocument.Create(outputMemoryStream, SpreadsheetDocumentType.Workbook, true))
                {
                    // Add a WorkbookPart to the document.
                    var workbookpart = document.AddWorkbookPart();
                    workbookpart.Workbook = new Workbook();

                    // Add a WorksheetPart to the WorkbookPart.
                    var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
                    worksheetPart.Worksheet = new Worksheet(new SheetData());

                    // Add Sheets to the Workbook.
                    var sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());

                    // Append a new worksheet and associate it with the workbook.
                    var sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
                    {
                        Id      = document.WorkbookPart.GetIdOfPart(worksheetPart),
                        SheetId = 1,
                        Name    = "Sheet1"
                    };

                    sheets.Append(sheet);

                    // Add a WorkbookStylesPart to the WorkbookPart
                    var stylesPart = document.WorkbookPart.AddNewPart <WorkbookStylesPart>();

                    var shData = worksheetPart.Worksheet.GetFirstChild <SheetData>();



                    stylesPart.Stylesheet = InitializeDefaultStyles(stylesPart.Stylesheet);

                    uint styleId = 2; //обязательно 2 потому что уже додано два стиля по умолчанию с индексами 0 и 1;

                    var styles = report.Select(o => o.Style).ToList();

                    //нельзя перенести в отдельный метод :(
                    foreach (var s in styles)
                    {
                        if (s is TableStyle)
                        {
                            var style = s as TableStyle;

                            var border = new DocumentFormat.OpenXml.Spreadsheet.Border();

                            var leftBorder   = new DocumentFormat.OpenXml.Spreadsheet.LeftBorder();
                            var rightBorder  = new DocumentFormat.OpenXml.Spreadsheet.RightBorder();
                            var topBorder    = new DocumentFormat.OpenXml.Spreadsheet.TopBorder();
                            var bottomBorder = new DocumentFormat.OpenXml.Spreadsheet.BottomBorder();

                            leftBorder.Style   = SetLineType(style.BorderLine);
                            rightBorder.Style  = SetLineType(style.BorderLine);
                            topBorder.Style    = SetLineType(style.BorderLine);
                            bottomBorder.Style = SetLineType(style.BorderLine);

                            leftBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
                                Rgb = ToHexBinaryValue(style.BorderColor)
                            };
                            rightBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
                                Rgb = ToHexBinaryValue(style.BorderColor)
                            };
                            topBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
                                Rgb = ToHexBinaryValue(style.BorderColor)
                            };
                            bottomBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
                                Rgb = ToHexBinaryValue(style.BorderColor)
                            };

                            border.LeftBorder   = leftBorder;
                            border.RightBorder  = rightBorder;
                            border.TopBorder    = topBorder;
                            border.BottomBorder = bottomBorder;

                            stylesPart.Stylesheet.Borders.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Border>(border);



                            var patternFill = new DocumentFormat.OpenXml.Spreadsheet.PatternFill
                            {
                                BackgroundColor = new DocumentFormat.OpenXml.Spreadsheet.BackgroundColor {
                                    Rgb = ToHexBinaryValue(style.Background)
                                },
                                ForegroundColor = new DocumentFormat.OpenXml.Spreadsheet.ForegroundColor {
                                    Rgb = ToHexBinaryValue(style.Foreground)
                                },
                                PatternType = SetPatternType(style.PatternType)
                            };

                            var fill = new DocumentFormat.OpenXml.Spreadsheet.Fill {
                                PatternFill = patternFill
                            };

                            stylesPart.Stylesheet.Fills.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Fill>(fill);

                            DocumentFormat.OpenXml.Spreadsheet.Font font = new DocumentFormat.OpenXml.Spreadsheet.Font()
                            {
                                FontName = new DocumentFormat.OpenXml.Spreadsheet.FontName {
                                    Val = new StringValue {
                                        Value = s.FontName
                                    }
                                },
                                FontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize {
                                    Val = s.FontSize
                                },
                                Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
                                    Rgb = ToHexBinaryValue(s.FontColor)
                                }
                            };
                            stylesPart.Stylesheet.Fonts.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Font>(font);
                        }
                        else
                        {
                            stylesPart.Stylesheet.Fills.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Fill>(new DocumentFormat.OpenXml.Spreadsheet.Fill());
                            stylesPart.Stylesheet.Borders.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Border>(new DocumentFormat.OpenXml.Spreadsheet.Border());
                            DocumentFormat.OpenXml.Spreadsheet.Font font;
                            if (s != null)
                            {
                                font = new DocumentFormat.OpenXml.Spreadsheet.Font
                                {
                                    FontName = new DocumentFormat.OpenXml.Spreadsheet.FontName {
                                        Val = new StringValue {
                                            Value = s.FontName
                                        }
                                    },
                                    FontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize {
                                        Val = s.FontSize
                                    },
                                    Color = new DocumentFormat.OpenXml.Spreadsheet.Color {
                                        Rgb = ToHexBinaryValue(s.FontColor)
                                    }
                                };


                                foreach (var item in s.TextStyle)
                                {
                                    switch (item)
                                    {
                                    case TextStyleType.Bold: font.Bold = new DocumentFormat.OpenXml.Spreadsheet.Bold {
                                            Val = true
                                    }; break;

                                    case TextStyleType.Italic: font.Italic = new DocumentFormat.OpenXml.Spreadsheet.Italic {
                                            Val = true
                                    }; break;

                                    case TextStyleType.Underline: font.Underline = new DocumentFormat.OpenXml.Spreadsheet.Underline {
                                            Val = DocumentFormat.OpenXml.Spreadsheet.UnderlineValues.Single
                                    }; break;

                                    case TextStyleType.Normal: break;
                                    }
                                }
                            }
                            else
                            {
                                font = new DocumentFormat.OpenXml.Spreadsheet.Font();
                            }

                            stylesPart.Stylesheet.Fonts.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Font>(font);
                        }


                        if (s != null)
                        {
                            var cellFormat = new DocumentFormat.OpenXml.Spreadsheet.CellFormat
                            {
                                BorderId = UInt32Value.ToUInt32(styleId),
                                FillId   = UInt32Value.ToUInt32(styleId),
                                FontId   = UInt32Value.ToUInt32(styleId)
                            };

                            if (s.Aligment != null)
                            {
                                var align = new Alignment()
                                {
                                    TextRotation = new UInt32Value(s.Aligment.Rotation),
                                    Horizontal   = AlignmentMapper.MapHorizontalAligment(s.Aligment.HorizontalAligment),
                                    Vertical     = AlignmentMapper.MapVerticalAligment(s.Aligment.VerticalAligment)
                                };

                                cellFormat.Append(align);
                            }

                            stylesPart.Stylesheet.CellFormats.AppendChild <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(cellFormat);
                        }

                        styleId = styleId + 1;
                    }

                    foreach (var x in report)
                    {
                        if (x is ComplexHeaderCell)
                        {
                            var element = x as ComplexHeaderCell;

                            if (Render(element, styles, shData, worksheetPart, workbookpart))
                            {
                                continue;
                            }
                        }

                        if (x is TextElement)
                        {
                            var element = x as TextElement;

                            if (Render(element, styles, shData, workbookpart))
                            {
                                continue;
                            }
                        }

                        if (x is TableElement)
                        {
                            var element = x as TableElement;
                            if (Render(element, styles, shData, workbookpart))
                            {
                                continue;
                            }
                        }

                        if (x is ImageElement)
                        {
                            var element = x as ImageElement;

                            if (Render(element, styles, shData, workbookpart))
                            {
                                continue;
                            }
                        }
                    }

                    //var dateLine = shData.AppendChild(new Spreadsheet.Row());

                    //dateLine.AppendChild(new Spreadsheet.Cell()
                    //{
                    //    CellValue = new Spreadsheet.CellValue(_report.TimeStamp.Date.ToString()),
                    //    DataType = Spreadsheet.CellValues.String,
                    //    StyleIndex = 0
                    //});

                    workbookpart.Workbook.Save();
                }

                buffer = new byte[outputMemoryStream.Position];
                outputMemoryStream.Position = 0;
                outputMemoryStream.Read(buffer, 0, buffer.Length);
            }
            return(buffer);
        }
Esempio n. 8
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);
        }