public static void UnderLine()
{
using (var stream = new MemoryStream())
{
using (var xlsxWriter = new XlsxWriter(stream))
{
var singleUnderLineStyle =
XlsxStyle.Default.With(XlsxFont.Default.WithUnderline());
var doubleUnderLineStyle =
XlsxStyle.Default.With(XlsxFont.Default.WithUnderline(XlsxFont.Underline.Double));
xlsxWriter.BeginWorksheet("Sheet1")
.BeginRow().Write("Row1")
.BeginRow().Write("Row2", singleUnderLineStyle)
.BeginRow().Write("Row3", doubleUnderLineStyle);
}
using (var package = new ExcelPackage(stream))
{
var sheet = package.Workbook.Worksheets[0];
sheet.Cells["A1"].Style.Font.UnderLine.Should().Be(false);
sheet.Cells["A1"].Style.Font.UnderLineType.Should().Be(ExcelUnderLineType.None);
sheet.Cells["A2"].Style.Font.UnderLine.Should().Be(true);
sheet.Cells["A2"].Style.Font.UnderLineType.Should().Be(ExcelUnderLineType.Single);
sheet.Cells["A3"].Style.Font.UnderLine.Should().Be(true);
sheet.Cells["A3"].Style.Font.UnderLineType.Should().Be(ExcelUnderLineType.Double);
}
}
}
public static void SkipUnformatted()
{
using (var stream = new MemoryStream())
{
using (var xlsxWriter = new XlsxWriter(stream))
xlsxWriter.BeginWorksheet("Sheet 1", columns: new[]
{
XlsxColumn.Formatted(count: 2, width: 0, hidden: true),
XlsxColumn.Unformatted(count: 3),
XlsxColumn.Formatted(count: 1, width: 0, hidden: true)
});
using (var package = new ExcelPackage(stream))
{
var worksheet = package.Workbook.Worksheets[0];
worksheet.Column(1).Hidden.Should().BeTrue();
worksheet.Column(2).Hidden.Should().BeTrue();
worksheet.Column(3).Hidden.Should().BeFalse();
worksheet.Column(4).Hidden.Should().BeFalse();
worksheet.Column(5).Hidden.Should().BeFalse();
worksheet.Column(6).Hidden.Should().BeTrue();
worksheet.Column(7).Hidden.Should().BeFalse();
}
}
}
public void IdolMailReadTest(string mailName, string infoName, string xlsxName)
{
using XlsxWriter xlsx = new XlsxWriter(xlsxName, true);
using FileStream mailStream = new FileStream(mailName, FileMode.Open, FileAccess.Read);
using FileStream infoStream = new FileStream(infoName, FileMode.Open, FileAccess.Read);
IdolMail.ReadFile(mailStream, infoStream, xlsx);
}
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()
{
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 static void Defaults()
{
using (var stream = new MemoryStream())
{
using (var xlsxWriter = new XlsxWriter(stream))
xlsxWriter.BeginWorksheet("Sheet 1").BeginRow().Write("Test");
using (var package = new ExcelPackage(stream))
{
var style = package.Workbook.Worksheets[0].Cells["A1"].Style;
style.Border.Top.Color.Rgb.Should().BeNull();
style.Border.Top.Style.Should().Be(ExcelBorderStyle.None);
style.Border.Right.Color.Rgb.Should().BeNull();
style.Border.Right.Style.Should().Be(ExcelBorderStyle.None);
style.Border.Bottom.Color.Rgb.Should().BeNull();
style.Border.Bottom.Style.Should().Be(ExcelBorderStyle.None);
style.Border.Left.Color.Rgb.Should().BeNull();
style.Border.Left.Style.Should().Be(ExcelBorderStyle.None);
style.Border.Diagonal.Color.Rgb.Should().BeNull();
style.Border.Diagonal.Style.Should().Be(ExcelBorderStyle.None);
style.Border.DiagonalDown.Should().BeFalse();
style.Border.DiagonalUp.Should().BeFalse();
}
}
}
/// <summary>
/// Save this workbook to the given stream
/// </summary>
/// <param name="stream"></param>
/// <param name="compress">Use compression? (Smaller files/higher CPU Usage)</param>
/// <exception cref="InvalidOperationException">Thrown if there are no <see cref="Worksheet">sheets</see> in the workbook.</exception>
public void Save(Stream stream, bool compress = true)
{
if (stream == null)
{
throw new ArgumentNullException(nameof(stream));
}
if (!stream.CanWrite)
{
throw new InvalidOperationException("Stream to save to is not writeable.");
}
if (stream.CanSeek)
{
XlsxWriter.Save(this, stream, compress);
}
else
{
// ZipArchive needs a seekable stream. If a stream is not seekable (e.g., HttpContext.Response.OutputStream), wrap it in a MemoryStream instead.
// TODO: Can we guess the required capacity?
using (var ms = new MemoryStream())
{
XlsxWriter.Save(this, ms, compress);
ms.CopyTo(stream);
}
}
}
public static void Run()
{
using (var stream = new FileStream($"{nameof(DataValidation)}.xlsx", FileMode.Create, FileAccess.Write))
using (var xlsxWriter = new XlsxWriter(stream))
{
xlsxWriter.BeginWorksheet("Sheet 1")
.BeginRow()
.AddDataValidation(new XlsxDataValidation(validationType: XlsxDataValidation.ValidationType.List, formula1: "\"Lorem,Ipsum,Dolor\""))
.Write(XlsxStyle.Default.With(new XlsxFill(Color.OldLace)))
.SkipColumns(1)
.AddDataValidation(1, 2, new XlsxDataValidation(validationType: XlsxDataValidation.ValidationType.List, formula1: "\"Lorem,Ipsum,Dolor\""))
.Write(XlsxStyle.Default.With(new XlsxFill(Color.OldLace)), repeatCount: 2)
.BeginRow()
.AddDataValidation(XlsxDataValidation.List(new[] { "Lorem", "Ipsum", "Dolor" }))
.Write(XlsxStyle.Default.With(new XlsxFill(Color.OldLace)))
.BeginRow()
.AddDataValidation(XlsxDataValidation.List(new[] { "1", "3.141592", "Sit" },
showErrorMessage: true, showInputMessage: true, errorStyle: XlsxDataValidation.ErrorStyle.Warning))
.Write(XlsxStyle.Default.With(new XlsxFill(Color.AliceBlue)))
.BeginRow()
.AddDataValidation(XlsxDataValidation.List(new[] { "1", "\"2\"", "\"3.141592\"", "\"3,141592\"", "3,,141592", "\"Sit, Amet\"" },
showErrorMessage: true, showInputMessage: true, errorTitle: "Error title", error: "A very informative error message",
promptTitle: "Prompt title", prompt: "A very enlightening prompt"))
.Write(XlsxStyle.Default.With(new XlsxFill(Color.BlueViolet)))
.BeginRow()
.AddDataValidation(new XlsxDataValidation(validationType: XlsxDataValidation.ValidationType.List, formula1: "=Choices!A1:A3"))
.Write(XlsxStyle.Default.With(new XlsxFill(Color.PaleGreen)))
.BeginWorksheet("Choices").BeginRow().Write(3.141592).BeginRow().Write("Lorem").BeginRow().Write("Ipsum, Dolor");
}
}
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 void SongInfoReadTest(string binName, string xlsxName)
{
using XlsxWriter xlsx = new XlsxWriter(xlsxName, true);
using FileStream stream = new FileStream(binName, FileMode.Open, FileAccess.Read);
IEnumerable<Record> records = SongInfo.ReadFile(stream);
xlsx.AppendRows("songInfo", records);
}
public static void SheetProtection()
{
using var stream = new MemoryStream();
using (var xlsxWriter = new XlsxWriter(stream))
{
xlsxWriter
.BeginWorksheet("Sheet1")
.SetSheetProtection(new XlsxSheetProtection("Lorem ipsum", autoFilter: false))
.BeginRow().Write("A1");
}
using (var package = new ExcelPackage(stream))
{
package.Workbook.Worksheets.Count.Should().Be(1);
var protection = package.Workbook.Worksheets[0].Protection;
protection.IsProtected.Should().BeTrue();
protection.AllowAutoFilter.Should().BeTrue();
protection.AllowDeleteColumns.Should().BeFalse();
protection.AllowDeleteRows.Should().BeFalse();
protection.AllowEditObject.Should().BeFalse();
protection.AllowEditScenarios.Should().BeFalse();
protection.AllowFormatCells.Should().BeFalse();
protection.AllowFormatColumns.Should().BeFalse();
protection.AllowFormatRows.Should().BeFalse();
protection.AllowInsertColumns.Should().BeFalse();
protection.AllowInsertHyperlinks.Should().BeFalse();
protection.AllowInsertRows.Should().BeFalse();
protection.AllowPivotTables.Should().BeFalse();
protection.AllowSelectLockedCells.Should().BeTrue();
protection.AllowSelectUnlockedCells.Should().BeTrue();
protection.AllowSort.Should().BeFalse();
}
}
public static void SheetProtection_PasswordTooLong()
{
using var stream = new MemoryStream();
using var xlsxWriter = new XlsxWriter(stream);
Func <XlsxWriter> act = () => xlsxWriter.BeginWorksheet("Sheet1").SetSheetProtection(new XlsxSheetProtection("Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in"));
act.Should().Throw <ArgumentException>();
}
public static void SheetProtection_PasswordTooShort()
{
using var stream = new MemoryStream();
using var xlsxWriter = new XlsxWriter(stream);
Func <XlsxWriter> act = () => xlsxWriter.BeginWorksheet("Sheet1").SetSheetProtection(new XlsxSheetProtection(""));
act.Should().Throw <ArgumentException>();
}
/// <summary>
/// Save this workbook to the given stream
/// </summary>
/// <param name="stream"></param>
/// <param name="compress">Use compression? (Smaller files/higher CPU Usage)</param>
/// <exception cref="InvalidOperationException">Thrown if there are no <see cref="Worksheet">sheets</see> in the workbook.</exception>
public void Save(Stream stream, bool compress = true)
{
if (stream == null || !stream.CanWrite || !stream.CanSeek)
{
throw new InvalidOperationException("Stream to save to must be writeable and seekable.");
}
XlsxWriter.Save(this, stream, compress);
}
public void WriteRow(XlsxWriter xlsx, Row row)
{
xlsx.AppendCell(row, "A", file);
xlsx.AppendCell(row, "B", messageID);
xlsx.AppendCell(row, "C", flag1 == 1);
xlsx.AppendCell(row, "D", flag2 == 1);
xlsx.AppendCell(row, "E", name_raw);
xlsx.AppendCell(row, "F", message_raw);
}
public static void WorksheetNameTooLong()
{
using (var stream = new MemoryStream())
using (var xlsxWriter = new XlsxWriter(stream))
{
Func <XlsxWriter> act = () => xlsxWriter.BeginWorksheet("A very, very, very, long worksheet name exceeding what Excel can handle");
act.Should().Throw <ArgumentException>();
}
}
/// <summary>
/// Saves the workbook with the defined name
/// </summary>
/// <param name="filename">filename of the saved workbook</param>
/// <exception cref="Exceptions.IOException">Throws IOException in case of an error</exception>
/// <exception cref="RangeException">Throws an RangeException if the start or end address of a handled cell range was out of range</exception>
/// <exception cref="Exceptions.FormatException">Throws a FormatException if a handled date cannot be translated to (Excel internal) OADate</exception>
/// <exception cref="StyleException">Throws an StyleException if one of the styles of the workbook cannot be referenced or is null</exception>
public void SaveAs(string filename)
{
string backup = filename;
this.filename = filename;
XlsxWriter l = new XlsxWriter(this);
l.Save();
this.filename = backup;
}
public static void DuplicateWorksheetName()
{
using (var stream = new MemoryStream())
using (var xlsxWriter = new XlsxWriter(stream))
{
xlsxWriter.BeginWorksheet("Sheet1");
Func <XlsxWriter> act = () => xlsxWriter.BeginWorksheet("Sheet1");
act.Should().Throw <ArgumentException>();
}
}
/// <summary>
/// Saves the workbook with the defined name asynchronous.
/// </summary>
/// <param name="fileName">fileName of the saved workbook</param>
/// <returns>Task object (void)</returns>
/// <exception cref="Exceptions.IOException">May throw an IOException in case of an error. The asynchronous operation may hide the exception.</exception>
/// <exception cref="RangeException">May throw a RangeException if the start or end address of a handled cell range was out of range. The asynchronous operation may hide the exception.</exception>
/// <exception cref="Exceptions.FormatException">May throw a FormatException if a handled date cannot be translated to (Excel internal) OADate. The asynchronous operation may hide the exception.</exception>
/// <exception cref="StyleException">May throw a StyleException if one of the styles of the workbook cannot be referenced or is null. The asynchronous operation may hide the exception.</exception>
public async Task SaveAsAsync(string fileName)
{
string backup = fileName;
filename = fileName;
XlsxWriter l = new XlsxWriter(this);
await l.SaveAsync();
filename = backup;
}
public static void Operator(XlsxDataValidation.Operator operatorType, ExcelDataValidationOperator expected)
{
using (var stream = new MemoryStream())
{
using (var xlsxWriter = new XlsxWriter(stream))
xlsxWriter.BeginWorksheet("Sheet 1").BeginRow().AddDataValidation(new XlsxDataValidation(validationType: XlsxDataValidation.ValidationType.Decimal, operatorType: operatorType));
using (var package = new ExcelPackage(stream))
((ExcelDataValidationDecimal)package.Workbook.Worksheets[0].DataValidations[0]).Operator.Should().Be(expected);
}
}
public static void Hidden()
{
using (var stream = new MemoryStream())
{
using (var xlsxWriter = new XlsxWriter(stream))
xlsxWriter.BeginWorksheet("Sheet 1", columns: new[] { XlsxColumn.Formatted(width: 0, hidden: true) });
using (var package = new ExcelPackage(stream))
package.Workbook.Worksheets[0].Column(1).Hidden.Should().BeTrue();
}
}
// XlsxDataValidation.ValidationType.None has no corresponding representation in EPPlus
public static void ValidationType(XlsxDataValidation.ValidationType validationType, eDataValidationType expected)
{
using (var stream = new MemoryStream())
{
using (var xlsxWriter = new XlsxWriter(stream))
xlsxWriter.BeginWorksheet("Sheet 1").BeginRow().AddDataValidation(new XlsxDataValidation(validationType: validationType));
using (var package = new ExcelPackage(stream))
package.Workbook.Worksheets[0].DataValidations[0].ValidationType.Type.Should().Be(expected);
}
}
public static void Hidden()
{
using (var stream = new MemoryStream())
{
using (var xlsxWriter = new XlsxWriter(stream))
xlsxWriter.BeginWorksheet("Sheet 1").BeginRow(hidden: true).Write("Test");
using (var package = new ExcelPackage(stream))
package.Workbook.Worksheets[0].Row(1).Hidden.Should().BeTrue();
}
}
public static void Height()
{
using (var stream = new MemoryStream())
{
using (var xlsxWriter = new XlsxWriter(stream))
xlsxWriter.BeginWorksheet("Sheet 1").BeginRow(height: 36.5).Write("Test");
using (var package = new ExcelPackage(stream))
package.Workbook.Worksheets[0].Row(1).Height.Should().Be(36.5);
}
}
public static void Width()
{
using (var stream = new MemoryStream())
{
using (var xlsxWriter = new XlsxWriter(stream))
xlsxWriter.BeginWorksheet("Sheet 1", columns: new[] { XlsxColumn.Formatted(width: 20) });
using (var package = new ExcelPackage(stream))
package.Workbook.Worksheets[0].Column(1).Width.Should().Be(20);
}
}
public static void Run()
{
using (var stream = new FileStream($"{nameof(RightToLeft)}.xlsx", FileMode.Create, FileAccess.Write))
using (var xlsxWriter = new XlsxWriter(stream))
{
xlsxWriter
.BeginWorksheet("Sheet 1", rightToLeft: true)
.BeginRow().Write(@"ما هو ""لوريم إيبسوم"" ؟")
.BeginRow().Write(@"لوريم إيبسوم(Lorem Ipsum) هو ببساطة نص شكلي (بمعنى أن الغاية هي الشكل وليس المحتوى) ويُستخدم في صناعات المطابع ودور النشر.");
}
}
public static void ReadingOrder(XlsxAlignment.ReadingOrder readingOrder, ExcelReadingOrder expected)
{
using (var stream = new MemoryStream())
{
using (var xlsxWriter = new XlsxWriter(stream))
xlsxWriter.BeginWorksheet("Sheet 1").BeginRow()
.Write("Test", XlsxStyle.Default.With(new XlsxAlignment(readingOrder: readingOrder)));
using (var package = new ExcelPackage(stream))
package.Workbook.Worksheets[0].Cells["A1"].Style.ReadingOrder.Should().Be(expected);
}
}
public static void VerticalAlignment(XlsxAlignment.Vertical alignment, ExcelVerticalAlignment expected)
{
using (var stream = new MemoryStream())
{
using (var xlsxWriter = new XlsxWriter(stream))
xlsxWriter.BeginWorksheet("Sheet 1").BeginRow()
.Write("Test", XlsxStyle.Default.With(new XlsxAlignment(vertical: alignment)));
using (var package = new ExcelPackage(stream))
package.Workbook.Worksheets[0].Cells["A1"].Style.VerticalAlignment.Should().Be(expected);
}
}
public static void Indent()
{
using (var stream = new MemoryStream())
{
using (var xlsxWriter = new XlsxWriter(stream))
xlsxWriter.BeginWorksheet("Sheet 1").BeginRow()
.Write("Test", XlsxStyle.Default.With(new XlsxAlignment(indent: 2)));
using (var package = new ExcelPackage(stream))
package.Workbook.Worksheets[0].Cells["A1"].Style.Indent.Should().Be(2);
}
}
