Create Excels with C# and ClosedXML: Miscellaneous
posted in dotnet on • by Wouter Van SchandevijlMiscellaneous features
ClosedXML can do a whole lot more for you. This post covers some interesting stuff that didn’t really fit anywhere else.
// Set workbook properties like title, author, company, ...
XLWorkbookProperties props = workbook.Properties;
// Modify default settings for all new workbooks
workbook.RowHeight = 20; // & ColumnWidth
// DefaultShowGridLines, DefaultShowZeros, ...
Printing
- More advanced printing options
- sheet.PageSetup.PaperSize options
- Margins options with
sheet.PageSetup.Margins
using var workbook = new XLWorkbook();
var sheet = workbook.Worksheets.Add("Printing");
sheet.PageSetup.Header.Center.AddText("YourTitle", XLHFOccurrence.AllPages)
.SetFontSize(24)
.SetBold()
.SetUnderline();
sheet.PageSetup.Header.Right.AddText(XLHFPredefinedText.Date, XLHFOccurrence.AllPages);
sheet.PageSetup.Header.Left.AddText(XLHFPredefinedText.SheetName, XLHFOccurrence.AllPages);
sheet.PageSetup.Footer.Right.AddText("Page ", XLHFOccurrence.AllPages);
sheet.PageSetup.Footer.Right.AddText(XLHFPredefinedText.PageNumber, XLHFOccurrence.AllPages);
sheet.PageSetup.Footer.Right.AddText(" of ", XLHFOccurrence.AllPages);
sheet.PageSetup.Footer.Right.AddText(XLHFPredefinedText.NumberOfPages, XLHFOccurrence.AllPages);
sheet.PageSetup.SetRowsToRepeatAtTop(1, 2);
sheet.PageSetup.SetColumnsToRepeatAtLeft(1, 7);
sheet.SheetView.View = XLSheetViewOptions.PageLayout;
Comments
using var workbook = new XLWorkbook();
var sheet = workbook.Worksheets.Add("Comments");
var comment = sheet.Cell("D4").CreateComment();
comment.Author = "evil corp";
comment.AddSignature(); // Makes Author visible
comment.AddText("Bold title:\r\n").SetBold(); // or use .AddNewLine()
comment.AddText("Unbolded subtext").SetBold(false);
sheet.Cell("G4").CreateComment().AddText("Orientation = Vertical");
sheet.Cell("G4").GetComment().Style
.Alignment.SetOrientation(XLDrawingTextOrientation.Vertical)
.Alignment.SetAutomaticSize()
.Margins.SetAll(0.25)
.ColorsAndLines.SetFillColor(XLColor.RichCarmine)
.ColorsAndLines.SetFillTransparency(0.25);
// Control positioning of the comment:
// sheet.Cell(G4").GetComment().Position
foreach (var cell in sheet.CellsUsed(XLCellsUsedOptions.Comments, c => c.HasComment))
{
cell.GetComment().SetVisible();
}
RichText
using var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Rich Text");
var cell1 = ws.Cell(1, 1).SetValue("The show must go on...");
cell1.Style.Font.FontColor = XLColor.Blue;
cell1.CreateRichText().Substring(4, 4)
.SetFontColor(XLColor.Red)
.SetFontName("Broadway");
ws.Cell(3, 1).CreateRichText()
.AddText("Hello").SetFontColor(XLColor.Red)
.AddText(" BIG ").SetFontColor(XLColor.Blue).SetBold()
.AddText("World").SetFontColor(XLColor.Red);
Migrating from EPPlus
| EPPlus | ClosedXML |
|---|---|
| Basics | |
| new ExcelPackage() | new XLWorkbook() |
| Workbook.Worksheets.Add(“str”) | AddWorksheet(“str”) |
| With Sheet | |
| Cells[1, 1] | Cell(1, 1) |
| Cell[“A2”] | Cell(“A2”) |
| Cells[“A2:C5”] | Range(“A2:C5”) |
| Cells[“B2,D2”] | Cells(“B2,D2”) or Ranges(“B2,D2”) |
| Dimension | LastRowUsed() & LastColumnUsed() |
| Or RangeUsed() | |
| View.FreezePanes | SheetView.Freeze |
| SheetView.FreezeRows & FreezeColumns | |
| View.ShowGridLines | ShowGridLines |
| View.ShowHeaders | ShowRowColHeaders |
| With Cell(s) | |
| Address | Address.ToString() |
| Start.Column | Address.ColumnNumber |
| Formula | FormulaA1 |
| Text | GetString() & GetFormattedString() |
| Hyperlink = new Uri() | SetHyperlink(new XLHyperlink()) |
| Merge = true | Merge() / Unmerge() |
| Styling with Cell.Style | |
| Font.Color.SetColor(Color.Ivory) | Style.Font.SetFontColor(XLColor.Ivory) |
| Style.Font.FontColor = XLColor.Ivory | |
| Fill.PatternType = ExcelFillStyle.Solid | Fill.SetPatternType(XLFillPatternValues.Solid) |
| Fill.BackgroundColor.SetColor(Color.Navy) | Fill.SetBackgroundColor(XLColor.Navy) |
| Border.BorderAround() | Border.OutsideBorder |
| ShrinkToFit = true | Alignment.ShrinkToFit = true |
| WrapText = true | Alignment.WrapText = true |
| HorizontalAlignment | Alignment.Horizontal |
| Formulas | |
| Formula | FormulaA1 |
| Calculate() | RecalculateAllFormulas() |
| Misc | |
| LoadFromCollection | InsertTable / InsertData |
| Cells.AutoFitColumns() | ColumnsUsed().AdjustToContents() |
Other
Things not covered…
Tables
ClosedXML has good functionality and examples for creating tables:
Stuff that came into being during the making of this post