Create Excels with C# and ClosedXML: Miscellaneous

posted in dotnet on 1 October 2025 • by Wouter Van Schandevijl

Miscellaneous 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()