Simple initialization
Fill excel document with test data 100 rows x 10 columns
using (var ew = new ExcelWriter("C:\\temp\\test.xlsx")) { for (var row = 1; row <= 100; row++) { for (var col = 1; col <= 10; col++) { ew.Write($"row:{row}-col:{col}", col, row); } } }
Sheet configuration
Set custom sheet name and define columns width
var sheet = new Sheet { Name = "Monthly Report", ColumnsWidth = new List<double> { 10, 12, 8, 8, 35 } }; using (var ew = new ExcelWriter("C:\\temp\\test.xlsx", sheet)) { ew.Write("data", 1, 1); }
Using formulas
Output 20 rows and calculate average and sum values
using (var ew = new ExcelWriter("C:\\temp\\test.xlsx")) { for (var row = 1; row <= 20; row++) { ew.Write(row.ToString(), 1, row, DataType.Number); } ew.WriteFormula(FormulaType.Average, 1, 22, 1, 1, 20); ew.WriteFormula(FormulaType.Sum, 1, 23, 1, 1, 20); }
Custom formulasPro
Output 20 rows and apply custom formulas on them
using (var ew = new ExcelWriter("C:\\temp\\test.xlsx")) { for (var row = 1; row <= 20; row++) { ew.Write(row.ToString(), 1, row, 1, 0, DataType.Number); } ew.Write("A20/A10", 1, 22, 1, 0, DataType.CustomFormula); ew.Write("SUM(A1:A20)-A22", 1, 23, 1, 0, DataType.CustomFormula); }
Multiple sheets Pro
Create additional sheets with individual configuration
var sheets = new List<Sheet> { new Sheet { Name = "Summary" }, new Sheet { Name = "Details" } }; using (var ew = new ExcelWriter("C:\\temp\\test.xlsx", sheets)) { ew.Write("value for the 1st sheet", 1, 1, 1); ew.Write("value for the 2nd sheet", 1, 1, 2); }
Merging & Filtering Pro
Merge and filter cells supplying range parameters
var sheet = new Sheet { Name = "Monthly Report", MergeRanges = new List<MergeRange> { new MergeRange(2, 3, 5), new MergeRange(3, 2, 7) }, FilterRange = new FilterRange(1, 1, 5) }; var ew = new ExcelWriter("C:\\temp\\test.xlsx", sheet);
Styling fontsPro
Set custom font styles and cell alignment
var style = new Style { FontColor = Color.Black, FontName = FontName.TimesNewRoman, FontSize = FontSize.Font16, FontStyleBold = true, FontStyleItalic = true, FontStyleUnderline = true, AlignHorizontal = AlignHorizontal.Left, AlignVertical = AlignVertical.Center }; using (var ew = new ExcelWriter("C:\\temp\\test.xlsx", style)) { ew.Write("font with decoration", 1, 2, 1, 1); }
Styling bordersPro
Set custom border styles and cell fill color
var styles = new List<Style> { new Style { BorderLeft = true, BorderRight = true, BorderTop = true, BorderBottom = true, BorderColor = Color.Black, }, new Style { FillColor = Color.Grey } }; using (var ew = new ExcelWriter("C:\\temp\\test.xlsx", styles)) { ew.Write("borders", 2, 2, 1, 1); ew.Write("fill color", 3, 3, 1, 2); }
Number formatPro
Set percentage format ( 38.51% )
var style = new Style { FontSize = FontSize.Font11, NumberFormat = NumberFormat.Percentage }; using (var ew = new ExcelWriter("C:\\temp\\test.xlsx", style)) { ew.Write("0.3851", 1, 2, 1, 1, DataType.Number); }
Number formatPro
Set currency format ( $2,612.11 )
var style = new Style { FontSize = FontSize.Font11, NumberFormat = NumberFormat.Currency }; using (var ew = new ExcelWriter("C:\\temp\\test.xlsx", style)) { ew.Write("2612.11", 1, 2, 1, 1, DataType.Number); }
Custom formatPro
Set date format ( 21/Aug/2023 10:51:18 )
var style = new Style { NumberFormat = NumberFormat.Custom("dd/mmm/yyyy hh:mm:ss") }; var dateToExport = new DateTime(2023, 08, 21, 10, 51, 18); using (var ew = new ExcelWriter("C:\\temp\\test.xlsx", style)) { ew.Write($"{dateToExport.ToOADate()}", 1, 2, 1, 1, DataType.Number); }
Custom formatPro
Set shares format ( 1,357.5712 )
var style = new Style { FontSize = FontSize.Font11, NumberFormat = NumberFormat.Custom("#,##0.0000") }; using (var ew = new ExcelWriter("C:\\temp\\test.xlsx", style)) { ew.Write("1357.5712", 1, 2, 1, 1, DataType.Number); }
Freeze configurationPro
Pin rows or columns to the top when scrolling
var sheet = new Sheet { Name = "Monthly Report", FreezeType = FreezeType.Row, FreezeCount = 1 }; using (var ew = new ExcelWriter("C:\\temp\\test.xlsx", sheet)) { ew.Write("data", 1, 1); }
Rows HeightPro
Set custom rows height
var sheet = new Sheet { RowsHeight = new List<double?> { 0, null, 35 } }; using (var ew = new ExcelWriter("C:\\temp\\test.xlsx", sheet)) { ew.Write("data", 1, 1); ew.Write("data", 1, 2); ew.Write("data", 1, 3); }