예제 #1
0
파일: Large.cs 프로젝트: salvois/LargeXlsx
        public static void Run()
        {
            var stopwatch = Stopwatch.StartNew();

            using (var stream = new FileStream($"{nameof(Large)}.xlsx", FileMode.Create, FileAccess.Write))
                using (var xlsxWriter = new XlsxWriter(stream, CompressionLevel.Level3))
                {
                    var whiteFont   = new XlsxFont("Calibri", 11, Color.White, bold: true);
                    var blueFill    = new XlsxFill(Color.FromArgb(0, 0x45, 0x86));
                    var headerStyle = new XlsxStyle(whiteFont, blueFill, XlsxBorder.None, XlsxNumberFormat.General, XlsxAlignment.Default);
                    var numberStyle = XlsxStyle.Default.With(XlsxNumberFormat.ThousandTwoDecimal);

                    xlsxWriter.BeginWorksheet("Sheet1", 1, 1);
                    xlsxWriter.BeginRow();
                    for (var j = 0; j < ColumnCount; j++)
                    {
                        xlsxWriter.Write($"Column {j}", headerStyle);
                    }
                    for (var i = 0; i < RowCount; i++)
                    {
                        xlsxWriter.BeginRow().Write($"Row {i}");
                        for (var j = 1; j < ColumnCount; j++)
                        {
                            xlsxWriter.Write(i * 1000 + j, numberStyle);
                        }
                    }
                }
            stopwatch.Stop();
            Console.WriteLine($"{nameof(Large)} completed {RowCount} rows and {ColumnCount} columns in {stopwatch.ElapsedMilliseconds} ms.");
        }
예제 #2
0
        public static void Run()
        {
            using (var stream = new FileStream($"{nameof(MultipleSheet)}.xlsx", FileMode.Create, FileAccess.Write))
                using (var xlsxWriter = new XlsxWriter(stream))
                {
                    var whiteFont      = new XlsxFont("Segoe UI", 9, Color.White, bold: true);
                    var blueFill       = new XlsxFill(Color.FromArgb(0, 0x45, 0x86));
                    var yellowFill     = new XlsxFill(Color.FromArgb(0xff, 0xff, 0x88));
                    var headerStyle    = new XlsxStyle(whiteFont, blueFill, XlsxBorder.None, XlsxNumberFormat.General, XlsxAlignment.Default);
                    var highlightStyle = XlsxStyle.Default.With(yellowFill);
                    var dateStyle      = XlsxStyle.Default.With(XlsxNumberFormat.ShortDateTime);

                    xlsxWriter
                    .BeginWorksheet("Sheet&'<1>\"", splitColumn: 1, splitRow: 2, columns: new [] { XlsxColumn.Unformatted(count: 2), XlsxColumn.Formatted(width: 20) })
                    .SetDefaultStyle(headerStyle)
                    .BeginRow().Write("Col<1>").Write("Col2").Write("Col&3")
                    .BeginRow().Write().Write("Sub2").Write("Sub3")
                    .SetDefaultStyle(XlsxStyle.Default)
                    .BeginRow().Write("Row3").Write(42).WriteFormula("B3*10", highlightStyle)
                    .BeginRow().Write("Row4").SkipColumns(1).Write(new DateTime(2020, 5, 6, 18, 27, 0), dateStyle)
                    .SkipRows(2)
                    .BeginRow().Write("Row7", XlsxStyle.Default.With(XlsxBorder.Around(new XlsxBorder.Line(Color.DeepPink, XlsxBorder.Style.Dashed))), columnSpan: 2).Write(3.14159265359)
                    .SetAutoFilter(1, 1, xlsxWriter.CurrentRowNumber, 3)
                    .BeginWorksheet("Sheet2", splitColumn: 1, splitRow: 1)
                    .BeginRow().Write("Lorem ipsum dolor sit amet,")
                    .BeginRow().Write("consectetur adipiscing elit,")
                    .BeginRow().Write("sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.")
                    .SetAutoFilter(1, 1, xlsxWriter.CurrentRowNumber, 1);
                }
        }
예제 #3
0
        public uint GetFill(IXlsxFill fillLayer)
        {
            XlsxFill layer = fillLayer as XlsxFill;

            if (layer == null)
            {
                throw new Exception("Unexpected Fill Type");
            }

            return(GetFill(layer.PatternType, layer.BackgroundColorArgb));
        }
예제 #4
0
        private static IList <IXlsxSheet> SetSheets()
        {
            IList <IXlsxSheet> sheets = new List <IXlsxSheet>();
            XlsxSheet          sheet  = new XlsxSheet();

            sheet.SheetName = "Just to test";

            IXlsxFill fill1 = new XlsxFill {
                PatternType = PatternType.DarkTrellis
            };

            IXlsxFill fill2 = new XlsxFill {
                PatternType         = PatternType.LightTrellis,
                BackgroundColorArgb = "FF0000FF"
            };

            IXlsxBorder border1 = new XlsxBorder {
                TopBorder   = BorderType.Dashed,
                RightBorder = BorderType.Double
            };

            IXlsxBorder border2 = new XlsxBorder {
                DiagonalBorder = BorderType.MediumDashDot
            };

            XlsxCell[] header = new XlsxCell[] {
                new XlsxCell("Name")
                {
                    Fill = fill1, Border = border1
                },
                new XlsxCell("Zahl")
                {
                    Fill = fill1, Border = border1
                },
                new XlsxCell("Datum")
                {
                    Fill = fill1, Border = border1
                }
            };
            XlsxCell[] row1 = new XlsxCell[] {
                new XlsxCell("Sanjay", new XlsxFont {
                    FontType      = FontType.Calibri,
                    Bold          = true,
                    Italic        = true,
                    Size          = 13,
                    UnderlineType = UnderlineType.DoubleLine,
                    ColorArgb     = "FF00FF00"
                })
                {
                    Fill = fill1
                },
                new XlsxCell(5.2765)
                {
                    Fill = fill2
                },
                new XlsxCell(new DateTime(2018, 1, 1, 12, 12, 12))
                {
                    Border = border2
                }
            };

            IXlsxCell[][] cells = new XlsxCell[][] {
                header,
                row1
            };

            sheet.Cells = cells;
            sheets.Add(sheet);
            return(sheets);
        }
예제 #5
0
        public static void Simple()
        {
            using (var stream = new MemoryStream())
            {
                using (var xlsxWriter = new XlsxWriter(stream))
                {
                    var whiteFont      = new XlsxFont("Segoe UI", 9, Color.White, bold: true);
                    var blueFill       = new XlsxFill(Color.FromArgb(0, 0x45, 0x86));
                    var yellowFill     = new XlsxFill(Color.FromArgb(0xff, 0xff, 0x88));
                    var headerStyle    = new XlsxStyle(whiteFont, blueFill, XlsxBorder.None, XlsxNumberFormat.General, XlsxAlignment.Default);
                    var highlightStyle = XlsxStyle.Default.With(yellowFill);
                    var dateStyle      = XlsxStyle.Default.With(XlsxNumberFormat.ShortDateTime);

                    xlsxWriter
                    .BeginWorksheet("Sheet&'<1>\"")
                    .SetDefaultStyle(headerStyle)
                    .BeginRow().Write("Col<1>").Write("Col2").Write("Col&3")
                    .BeginRow().Write().Write("Sub2").Write("Sub3")
                    .SetDefaultStyle(XlsxStyle.Default)
                    .BeginRow().Write("Row3").Write(42).Write(-1, highlightStyle)
                    .BeginRow().Write("Row4").SkipColumns(1).Write(new DateTime(2020, 5, 6, 18, 27, 0), dateStyle)
                    .SkipRows(2)
                    .BeginRow().Write("Row7", columnSpan: 2).Write(3.14159265359);
                }

                using (var package = new ExcelPackage(stream))
                {
                    package.Workbook.Worksheets.Count.Should().Be(1);
                    var sheet = package.Workbook.Worksheets[0];
                    sheet.Name.Should().Be("Sheet&'<1>\"");

                    sheet.Cells["A1"].Value.Should().Be("Col<1>");
                    sheet.Cells["B1"].Value.Should().Be("Col2");
                    sheet.Cells["C1"].Value.Should().Be("Col&3");
                    sheet.Cells["A2"].Value.Should().BeNull();
                    sheet.Cells["B2"].Value.Should().Be("Sub2");
                    sheet.Cells["C2"].Value.Should().Be("Sub3");
                    sheet.Cells["A3"].Value.Should().Be("Row3");
                    sheet.Cells["B3"].Value.Should().Be(42);
                    sheet.Cells["C3"].Value.Should().Be(-1);
                    sheet.Cells["A4"].Value.Should().Be("Row4");
                    sheet.Cells["B4"].Value.Should().BeNull();
                    sheet.Cells["C4"].Value.Should().Be(new DateTime(2020, 5, 6, 18, 27, 0));
                    sheet.Cells["C4"].Style.Numberformat.NumFmtID.Should().Be(22);
                    sheet.Cells["A5"].Value.Should().BeNull();
                    sheet.Cells["A6"].Value.Should().BeNull();
                    sheet.Cells["A7"].Value.Should().Be("Row7");
                    sheet.Cells["B7"].Value.Should().BeNull();
                    sheet.Cells["C7"].Value.Should().Be(3.14159265359);

                    sheet.Cells["A7:B7"].Merge.Should().BeTrue();

                    foreach (var cell in new[] { "A1", "B1", "C1", "A2", "B2", "C2" })
                    {
                        sheet.Cells[cell].Style.Fill.PatternType.Should().Be(ExcelFillStyle.Solid);
                        sheet.Cells[cell].Style.Fill.BackgroundColor.Rgb.Should().Be("004586");
                        sheet.Cells[cell].Style.Font.Bold.Should().BeTrue();
                        sheet.Cells[cell].Style.Font.Color.Rgb.Should().Be("ffffff");
                        sheet.Cells[cell].Style.Font.Name.Should().Be("Segoe UI");
                        sheet.Cells[cell].Style.Font.Size.Should().Be(9);
                    }

                    sheet.Cells["C3"].Style.Fill.PatternType.Should().Be(ExcelFillStyle.Solid);
                    sheet.Cells["C3"].Style.Fill.BackgroundColor.Rgb.Should().Be("ffff88");
                    sheet.Cells["C3"].Style.Font.Bold.Should().BeFalse();
                    sheet.Cells["C3"].Style.Font.Color.Rgb.Should().Be("000000");
                    sheet.Cells["C3"].Style.Font.Name.Should().Be("Calibri");
                    sheet.Cells["C3"].Style.Font.Size.Should().Be(11);

                    foreach (var cell in new[] { "A3", "B3", "A4", "B4", "C4", "A5", "B5", "C5", "A6", "B6", "C6", "A7", "B7", "C7" })
                    {
                        sheet.Cells[cell].Style.Fill.PatternType.Should().Be(ExcelFillStyle.None);
                        sheet.Cells[cell].Style.Font.Bold.Should().BeFalse();
                        sheet.Cells[cell].Style.Font.Color.Rgb.Should().Be("000000");
                        sheet.Cells[cell].Style.Font.Name.Should().Be("Calibri");
                        sheet.Cells[cell].Style.Font.Size.Should().Be(11);
                    }
                }
            }
        }