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 stopwatch = Stopwatch.StartNew();
using (var stream = new FileStream($"{nameof(Zip64Huge)}.xlsx", FileMode.Create, FileAccess.Write))
using (var xlsxWriter = new XlsxWriter(stream, compressionLevel: CompressionLevel.BestSpeed, useZip64: true))
{
xlsxWriter.BeginWorksheet("Sheet1", 1, 1);
xlsxWriter.BeginRow();
for (var j = 0; j < ColumnCount; j++)
{
xlsxWriter.Write($"Column {j}");
}
for (var i = 0; i < RowCount; i++)
{
xlsxWriter.BeginRow().Write($"Row {i}");
for (var j = 1; j < ColumnCount; j++)
{
xlsxWriter.Write(i * 100 + j);
}
if (i % 50000 == 0)
{
Console.WriteLine($"{nameof(Zip64Huge)} wrote {i} rows in {stopwatch.ElapsedMilliseconds} ms...");
}
}
}
stopwatch.Stop();
Console.WriteLine($"{nameof(Zip64Huge)} 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()
{
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.");
}
/// <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);
}