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()
{
var rnd = new Random();
using (var stream = new FileStream($"{nameof(ColumnFormatting)}.xlsx", FileMode.Create, FileAccess.Write))
using (var xlsxWriter = new XlsxWriter(stream))
{
var blueStyle = new XlsxStyle(XlsxFont.Default.With(Color.White), new XlsxFill(Color.FromArgb(0, 0x45, 0x86)), XlsxBorder.None, XlsxNumberFormat.General, XlsxAlignment.Default);
xlsxWriter
.BeginWorksheet("Sheet 1", columns: new[]
{
XlsxColumn.Formatted(count: 2, width: 20),
XlsxColumn.Unformatted(3),
XlsxColumn.Formatted(style: blueStyle, width: 9),
XlsxColumn.Formatted(hidden: true, width: 0)
});
for (var i = 0; i < 10; i++)
{
xlsxWriter.BeginRow();
for (var j = 0; j < 10; j++)
{
xlsxWriter.Write(rnd.Next());
}
}
}
}
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);
}
}
private XlsxColumn(int count, bool hidden, XlsxStyle style, double?width)
{
Count = count;
Hidden = hidden;
Style = style;
Width = width;
}
public static void Run()
{
using (var stream = new FileStream($"{nameof(RowFormatting)}.xlsx", FileMode.Create, FileAccess.Write))
using (var xlsxWriter = new XlsxWriter(stream))
{
var blueStyle = new XlsxStyle(XlsxFont.Default.With(Color.White), new XlsxFill(Color.FromArgb(0, 0x45, 0x86)), XlsxBorder.None, XlsxNumberFormat.General, XlsxAlignment.Default);
xlsxWriter
.BeginWorksheet("Sheet 1")
.BeginRow().Write("A1").Write("B1").Write("C1")
.BeginRow(hidden: true).Write("A2").Write("B2").Write("C2")
.BeginRow().Write("A3").Write("B3").Write("C3")
.BeginRow(height: 36.5).Write("A4").Write("B4").Write("C4")
.BeginRow(style: blueStyle).Write("A5").SkipColumns(1).Write("C5");
}
}
public static void Style()
{
var blueStyle = new XlsxStyle(XlsxFont.Default.With(Color.White), new XlsxFill(Color.FromArgb(0, 0x45, 0x86)), XlsxBorder.None, XlsxNumberFormat.General, XlsxAlignment.Default);
using (var stream = new MemoryStream())
{
using (var xlsxWriter = new XlsxWriter(stream))
xlsxWriter.BeginWorksheet("Sheet 1", columns: new[] { XlsxColumn.Formatted(width: 20, style: blueStyle) });
using (var package = new ExcelPackage(stream))
{
var style = package.Workbook.Worksheets[0].Column(1).Style;
style.Fill.PatternType.Should().Be(ExcelFillStyle.Solid);
style.Fill.BackgroundColor.Rgb.Should().Be("004586");
style.Font.Color.Rgb.Should().Be("ffffff");
}
}
}
public static void Run()
{
var rnd = new Random();
var stopwatch = Stopwatch.StartNew();
using (var stream = new FileStream($"{nameof(StyledLarge)}.xlsx", FileMode.Create, FileAccess.Write))
using (var xlsxWriter = new XlsxWriter(stream, CompressionLevel.Level3))
{
var headerStyle = new XlsxStyle(
new XlsxFont("Calibri", 11, Color.White, bold: true),
new XlsxFill(Color.FromArgb(0, 0x45, 0x86)),
XlsxBorder.None,
XlsxNumberFormat.General,
XlsxAlignment.Default);
var cellStyles = Enumerable.Repeat(0, 100)
.Select(_ => XlsxStyle.Default.With(new XlsxFill(Color.FromArgb(rnd.Next(256), rnd.Next(256), rnd.Next(256)))))
.ToList();
xlsxWriter.BeginWorksheet("Sheet1", 1, 1);
xlsxWriter.BeginRow();
for (var j = 0; j < ColumnCount; j++)
{
xlsxWriter.Write($"Column {j}", headerStyle);
}
var cellStyleIndex = 0;
for (var i = 0; i < RowCount; i++)
{
xlsxWriter.BeginRow().Write($"Row {i}");
for (var j = 1; j < 180; j++)
{
xlsxWriter.Write(i * ColumnCount + j, cellStyles[cellStyleIndex]);
cellStyleIndex = (cellStyleIndex + 1) % cellStyles.Count;
}
}
}
stopwatch.Stop();
Console.WriteLine($"{nameof(StyledLarge)} completed {RowCount} rows, {ColumnCount} columns and {ColorCount} colors in {stopwatch.ElapsedMilliseconds} ms.");
}
public static void Run()
{
using (var stream = new FileStream($"{nameof(Simple)}.xlsx", FileMode.Create, FileAccess.Write))
using (var xlsxWriter = new XlsxWriter(stream))
{
var headerStyle = new XlsxStyle(
new XlsxFont("Segoe UI", 9, Color.White, bold: true),
new XlsxFill(Color.FromArgb(0, 0x45, 0x86)),
XlsxStyle.Default.Border,
XlsxStyle.Default.NumberFormat,
XlsxAlignment.Default);
var highlightStyle = XlsxStyle.Default.With(new XlsxFill(Color.FromArgb(0xff, 0xff, 0x88)));
var dateStyle = XlsxStyle.Default.With(XlsxNumberFormat.ShortDateTime);
var borderedStyle = highlightStyle.With(XlsxBorder.Around(new XlsxBorder.Line(Color.DeepPink, XlsxBorder.Style.Dashed)));
xlsxWriter
.BeginWorksheet("Sheet 1", columns: new[] { XlsxColumn.Unformatted(count: 2), XlsxColumn.Formatted(width: 20) })
.SetDefaultStyle(headerStyle)
.BeginRow().AddMergedCell(2, 1).Write("Col1").Write("Top2").Write("Top3")
.BeginRow().Write().Write("Col2").Write("Col3")
.SetDefaultStyle(XlsxStyle.Default)
.BeginRow().Write("Row3").Write(42).WriteFormula(
$"{xlsxWriter.GetRelativeColumnName(-1)}{xlsxWriter.CurrentRowNumber}*10", highlightStyle)
.BeginRow().Write("Row4").SkipColumns(1).Write(new DateTime(2020, 5, 6, 18, 27, 0), dateStyle)
.SkipRows(2)
.BeginRow().Write("Row7", borderedStyle, columnSpan: 2).Write(3.14159265359)
.BeginRow().Write("Bold").Write().Write("Be bold", XlsxStyle.Default.With(XlsxFont.Default.WithBold()))
.BeginRow().Write("Italic").Write().Write("Be italic", XlsxStyle.Default.With(XlsxFont.Default.WithItalic()))
.BeginRow().Write("Strike").Write().Write("Be struck", XlsxStyle.Default.With(XlsxFont.Default.WithStrike()))
.BeginRow().Write("Underline").Write().Write("Single", XlsxStyle.Default.With(XlsxFont.Default.WithUnderline()))
.BeginRow().Write("Underline").Write().Write("Double", XlsxStyle.Default.With(XlsxFont.Default.WithUnderline(XlsxFont.Underline.Double)))
.BeginRow().Write("Underline").Write().Write("SingleAccounting", XlsxStyle.Default.With(XlsxFont.Default.WithUnderline(XlsxFont.Underline.SingleAccounting)))
.BeginRow().Write("Underline").Write().Write("DoubleAccounting", XlsxStyle.Default.With(XlsxFont.Default.WithUnderline(XlsxFont.Underline.DoubleAccounting)))
.SetAutoFilter(2, 1, xlsxWriter.CurrentRowNumber - 1, 3);
}
}
public static XlsxColumn Formatted(double width, int count = 1, bool hidden = false, XlsxStyle style = null)
{
return(new XlsxColumn(count, hidden, style, width));
}
/// <summary>
/// Writes a ADODataReader to a Worksheet
/// </summary>
public static int WriteToWorksheet(ADOTabular.AdomdClientWrappers.AdomdDataReader reader, XlsxWriter xlsxWriter, IStatusBarMessage statusProgress, IQueryRunner runner)
{
int iMaxCol = reader.FieldCount - 1;
int iRowCnt = 0;
ADOTabularColumn daxCol;
int colIdx = 0;
XlsxStyle[] columnStyles = new XlsxStyle[reader.FieldCount];
var headerStyle = new XlsxStyle(
new XlsxFont("Segoe UI", 9, Color.White, bold: true),
new XlsxFill(Color.FromArgb(0, 0x45, 0x86)),
XlsxStyle.Default.Border,
XlsxStyle.Default.NumberFormat,
XlsxAlignment.Default);
var wrapStyle = XlsxStyle.Default.With(new XlsxAlignment(vertical: Vertical.Top, wrapText: true));
var defaultStyle = XlsxStyle.Default;
// Write out Header Row
xlsxWriter.SetDefaultStyle(headerStyle).BeginRow();
foreach (var colName in reader.CleanColumnNames())
{
// write out the column name
xlsxWriter.Write(colName);
// cache the column formatstrings as Excel Styles
reader.Connection.Columns.TryGetValue(reader.GetName(colIdx), out daxCol);
if (daxCol != null)
{
columnStyles[colIdx] = GetStyle(daxCol);
}
else
{
columnStyles[colIdx] = defaultStyle;
}
colIdx++;
}
xlsxWriter.SetDefaultStyle(defaultStyle);
while (reader.Read())
{
// check if we have reached the limit of an xlsx file
if (iRowCnt >= 999999)
{
runner.OutputWarning("Results truncated, reached the maximum row limit for an Excel file");
break;
}
// increment row count
iRowCnt++;
// start outputting the next row
xlsxWriter.BeginRow();
for (int iCol = 0; iCol < reader.FieldCount; iCol++)
{
var fieldValue = reader[iCol];
switch (fieldValue)
{
case int i:
xlsxWriter.Write(i, columnStyles[iCol]);
break;
case double dbl:
xlsxWriter.Write(dbl, columnStyles[iCol]);
break;
case decimal dec:
xlsxWriter.Write(dec, columnStyles[iCol]);
break;
case DateTime dt:
xlsxWriter.Write(dt, columnStyles[iCol]);
break;
case string str:
if (str.Contains("\n") || str.Contains("\r"))
{
xlsxWriter.Write(str, wrapStyle);
}
else
{
xlsxWriter.Write(str);
}
break;
case bool b:
xlsxWriter.Write(b.ToString()); // Writes out TRUE/FALSE
break;
case null:
xlsxWriter.Write();
break;
case long lng:
if (lng < int.MaxValue && lng > int.MinValue)
{
xlsxWriter.Write(Convert.ToInt32(lng), columnStyles[iCol]);
}
else // TODO - should we be converting large long values to double??
{
xlsxWriter.Write(lng.ToString()); // write numbers outside the size of int as strings
}
break;
default:
xlsxWriter.Write(fieldValue.ToString());
break;
}
}
if (iRowCnt % 1000 == 0)
{
statusProgress.Update($"Written {iRowCnt:n0} rows to the file output");
}
}
return(iRowCnt);
}
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);
}
}
}
}
