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

            var b = new OpenXml.Border
            {
                LeftBorder     = new OpenXml.LeftBorder(),
                RightBorder    = new OpenXml.RightBorder(),
                TopBorder      = new OpenXml.TopBorder(),
                BottomBorder   = new OpenXml.BottomBorder(),
                DiagonalBorder = new OpenXml.DiagonalBorder()
            };

            if (border.Left != null)
            {
                SetBorderProperties(b.LeftBorder, border.Left);
            }

            if (border.Right != null)
            {
                SetBorderProperties(b.RightBorder, border.Right);
            }

            if (border.Top != null)
            {
                SetBorderProperties(b.TopBorder, border.Top);
            }

            if (border.Bottom != null)
            {
                SetBorderProperties(b.BottomBorder, border.Bottom);
            }

            if (border.Diagonal != null)
            {
                SetBorderProperties(b.DiagonalBorder, border.Diagonal);

                b.DiagonalUp   = border.Diagonal.Diagonal == BorderDiagonal.Up || border.Diagonal.Diagonal == BorderDiagonal.Both;
                b.DiagonalDown = border.Diagonal.Diagonal == BorderDiagonal.Down || border.Diagonal.Diagonal == BorderDiagonal.Both;
            }

            stylesheet.Borders.Append(b);
        }
Esempio n. 2
0
        static Borders GenerateBorders()
        {
            var borders = new Borders {
                Count = 1U
            };

            var border1         = new Border();
            var leftBorder1     = new LeftBorder();
            var rightBorder1    = new RightBorder();
            var topBorder1      = new TopBorder();
            var bottomBorder1   = new BottomBorder();
            var diagonalBorder1 = new DiagonalBorder();

            border1.Append(leftBorder1);
            border1.Append(rightBorder1);
            border1.Append(topBorder1);
            border1.Append(bottomBorder1);
            border1.Append(diagonalBorder1);

            borders.Append(border1);

            return(borders);
        }
Esempio n. 3
0
        private static Borders AddBorders()
        {
            Borders borders1 = new Borders()
            {
                Count = 1U
            };

            Border         border1         = new Border();
            LeftBorder     leftBorder1     = new LeftBorder();
            RightBorder    rightBorder1    = new RightBorder();
            TopBorder      topBorder1      = new TopBorder();
            BottomBorder   bottomBorder1   = new BottomBorder();
            DiagonalBorder diagonalBorder1 = new DiagonalBorder();

            border1.AppendChild(leftBorder1);
            border1.AppendChild(rightBorder1);
            border1.AppendChild(topBorder1);
            border1.AppendChild(bottomBorder1);
            border1.AppendChild(diagonalBorder1);

            borders1.AppendChild(border1);
            return(borders1);
        }
Esempio n. 4
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. 5
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);
        }