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 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 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 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 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()
{
using (var stream = new FileStream($"{nameof(Alignment)}.xlsx", FileMode.Create, FileAccess.Write))
using (var xlsxWriter = new XlsxWriter(stream))
{
xlsxWriter
.BeginWorksheet("Sheet 1", columns: new[] { XlsxColumn.Formatted(width: 40) })
.BeginRow().Write("Left", XlsxStyle.Default.With(new XlsxAlignment(horizontal: XlsxAlignment.Horizontal.Left)))
.BeginRow().Write("Center", XlsxStyle.Default.With(new XlsxAlignment(horizontal: XlsxAlignment.Horizontal.Center)))
.BeginRow().Write("Right", XlsxStyle.Default.With(new XlsxAlignment(horizontal: XlsxAlignment.Horizontal.Right)))
.BeginRow(height: 30).Write("Top", XlsxStyle.Default.With(new XlsxAlignment(vertical: XlsxAlignment.Vertical.Top)))
.BeginRow(height: 30).Write("Middle", XlsxStyle.Default.With(new XlsxAlignment(vertical: XlsxAlignment.Vertical.Center)))
.BeginRow(height: 30).Write("Bottom", XlsxStyle.Default.With(new XlsxAlignment(vertical: XlsxAlignment.Vertical.Bottom)))
.BeginRow(height: 90).Write("Rotated by 45°", XlsxStyle.Default.With(new XlsxAlignment(textRotation: 45)))
.BeginRow(height: 120).Write("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.",
XlsxStyle.Default.With(new XlsxAlignment(horizontal: XlsxAlignment.Horizontal.Justify, vertical: XlsxAlignment.Vertical.Justify, wrapText: true)))
.BeginRow().Write("Lorem ipsum dolor sit amet, consectetur adipiscing elit", XlsxStyle.Default.With(new XlsxAlignment(shrinkToFit: true)));
}
}
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 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);
}
}
/// <summary>
/// Immutable class for holding PropertyInfo and XlsxColumn info.
/// </summary>
/// <param name="info">PropertyInfo</param>
/// <param name="attr">XlsxColumn</param>
public MetaInfo(PropertyInfo info, XlsxColumn attr)
{
PropertyInfo = info;
Attribute = attr;
}
