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