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.");
}
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);
}
}
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));
}
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);
}
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);
}
}
}
}