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>();
}
}
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 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();
}
}
}
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);
}
}
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 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 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 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 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>();
}
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>();
}
}
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();
}
}
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);
}
}
// 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 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 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 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 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);
}
}
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 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 Run()
{
using var stream = new FileStream($"{nameof(SheetProtection)}.xlsx", FileMode.Create, FileAccess.Write);
using var xlsxWriter = new XlsxWriter(stream);
xlsxWriter
.BeginWorksheet("Sheet 1")
.SetSheetProtection(new XlsxSheetProtection("Lorem ipsum", autoFilter: false))
.BeginRow().Write("A1").Write("B1")
.BeginRow().Write("A2").Write("B2")
.BeginRow().Write("A3").Write("B3")
.SetAutoFilter(1, 1, xlsxWriter.CurrentRowNumber - 1, 2);
}
public static void InsertionPointAfterSkipRows()
{
using (var stream = new MemoryStream())
using (var xlsxWriter = new XlsxWriter(stream))
{
xlsxWriter.BeginWorksheet("Sheet1")
.BeginRow().Write("A1").Write("B1")
.SkipRows(2);
xlsxWriter.CurrentRowNumber.Should().Be(3);
xlsxWriter.CurrentColumnNumber.Should().Be(0);
}
}
public static void ErrorStyles(XlsxDataValidation.ErrorStyle errorStyle, ExcelDataValidationWarningStyle expected)
{
using (var stream = new MemoryStream())
{
using (var xlsxWriter = new XlsxWriter(stream))
xlsxWriter.BeginWorksheet("Sheet 1").BeginRow().AddDataValidation(new XlsxDataValidation(validationType: XlsxDataValidation.ValidationType.List, errorStyle: errorStyle));
using (var package = new ExcelPackage(stream))
{
var dataValidation = (ExcelDataValidationList)package.Workbook.Worksheets[0].DataValidations[0];
dataValidation.ErrorStyle.Should().Be(expected);
}
}
}
public static void RightToLeftWorksheet(bool rightToLeft)
{
using (var stream = new MemoryStream())
{
using (var xlsxWriter = new XlsxWriter(stream))
xlsxWriter
.BeginWorksheet("Sheet 1", rightToLeft: rightToLeft)
.BeginRow().Write(@"ما هو ""لوريم إيبسوم"" ؟")
.BeginRow().Write(@"لوريم إيبسوم(Lorem Ipsum) هو ببساطة نص شكلي (بمعنى أن الغاية هي الشكل وليس المحتوى) ويُستخدم في صناعات المطابع ودور النشر.");
using (var package = new ExcelPackage(stream))
package.Workbook.Worksheets[0].View.RightToLeft.Should().Be(rightToLeft);
}
}
public static void FormulaWithoutResult()
{
using (var stream = new MemoryStream())
{
using (var xlsxWriter = new XlsxWriter(stream))
xlsxWriter.BeginWorksheet("Sheet 1").BeginRow().WriteFormula("41+1");
using (var package = new ExcelPackage(stream))
{
// We don't assert package.Workbook.FullCalcOnLoad because EPPlus always sets it to true
package.Workbook.Worksheets[0].Cells["A1"].Formula.Should().Be("41+1");
package.Workbook.Worksheets[0].Cells["A1"].Value.Should().BeNull();
}
}
}
public static void HorizontalAlignment(XlsxBorder.Style borderStyle, ExcelBorderStyle expected)
{
using (var stream = new MemoryStream())
{
using (var xlsxWriter = new XlsxWriter(stream))
xlsxWriter.BeginWorksheet("Sheet 1").BeginRow()
.Write("Test", XlsxStyle.Default.With(new XlsxBorder(top: new XlsxBorder.Line(Color.DeepPink, borderStyle))));
using (var package = new ExcelPackage(stream))
{
var border = package.Workbook.Worksheets[0].Cells["A1"].Style.Border;
border.Top.Color.Rgb.Should().Be("ff1493");
border.Top.Style.Should().Be(expected);
}
}
}
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");
}
}
