Skip to main content

Page Setup and Printing

Everything under Excel's Page Layout tab lives on IXLWorksheet.PageSetup. Each worksheet has its own settings — a landscape data sheet and a portrait cover page in the same workbook is perfectly normal.

using XLibur.Excel;

var ws = workbook.Worksheet("Report");

ws.PageSetup.PageOrientation = XLPageOrientation.Landscape;
ws.PageSetup.PaperSize = XLPaperSize.A4Paper;
ws.PageSetup.FitToPages(1, 0);   // one page wide, any number tall

Orientation and paper​

ws.PageSetup.PageOrientation = XLPageOrientation.Portrait;    // or Landscape, Default
ws.PageSetup.PaperSize = XLPaperSize.A4Paper;
ws.PageSetup.PaperSize = XLPaperSize.LetterPaper;
ws.PageSetup.PaperSize = XLPaperSize.LegalPaper;

ws.PageSetup.FirstPageNumber = 1;
ws.PageSetup.HorizontalDpi = 600;
ws.PageSetup.VerticalDpi = 600;

Fluent forms are available throughout:

ws.PageSetup
    .SetPageOrientation(XLPageOrientation.Landscape)
    .SetPaperSize(XLPaperSize.A4Paper)
    .SetCenterHorizontally();

Scaling​

Two mutually exclusive modes — the later call wins.

Fit to a number of pages. Pass 0 for an axis you want Excel to work out:

ws.PageSetup.FitToPages(1, 0);   // one page wide, as many pages tall as needed
ws.PageSetup.FitToPages(1, 1);   // squeeze onto a single page
ws.PageSetup.FitToPages(2, 2);

// Equivalent, set separately
ws.PageSetup.PagesWide = 1;
ws.PageSetup.PagesTall = 0;

Scale to a percentage:

ws.PageSetup.AdjustTo(80);    // print at 80% of normal size
note

AdjustTo overrides PagesWide/PagesTall, and FitToPages overrides AdjustTo. Pick one.

Without a print area, Excel prints the whole used range. Add one or more areas to restrict it:

ws.PageSetup.PrintAreas.Add("A1:F40");

// Several disjoint areas — each prints on its own page
ws.PageSetup.PrintAreas.Add("A1:B2");
ws.PageSetup.PrintAreas.Add("D3:D5");

ws.PageSetup.PrintAreas.Clear();

Repeating title rows and columns​

The rows or columns that reprint at the top or left of every page — the equivalent of Excel's Print Titles:

ws.PageSetup.SetRowsToRepeatAtTop(1, 1);        // repeat row 1
ws.PageSetup.SetRowsToRepeatAtTop("1:2");       // repeat rows 1 and 2
ws.PageSetup.SetColumnsToRepeatAtLeft(1, 1);    // repeat column A
ws.PageSetup.SetColumnsToRepeatAtLeft("A:B");

Margins​

Margins are in inches:

ws.PageSetup.Margins.Top = 1;
ws.PageSetup.Margins.Bottom = 1.25;
ws.PageSetup.Margins.Left = 0.5;
ws.PageSetup.Margins.Right = 0.75;
ws.PageSetup.Margins.Header = 0.30;
ws.PageSetup.Margins.Footer = 0.15;

ws.PageSetup.CenterHorizontally = true;
ws.PageSetup.CenterVertically = false;

Headers and footers​

Each of Header and Footer has a Left, Center, and Right section. AddText appends a run, and returns rich text you can format:

ws.PageSetup.Header.Left.AddText("Quarterly Report");
ws.PageSetup.Header.Right.AddText("Confidential").SetBold().SetFontColor(XLColor.Red);

ws.PageSetup.Footer.Left.AddText("Generated by XLibur");

Predefined fields​

XLHFPredefinedText inserts the codes Excel substitutes at print time:

var footer = ws.PageSetup.Footer;

footer.Center.AddText(XLHFPredefinedText.PageNumber);
footer.Center.AddText(" of ");
footer.Center.AddText(XLHFPredefinedText.NumberOfPages);

footer.Right.AddText(XLHFPredefinedText.FullPath);
ws.PageSetup.Header.Center.AddText(XLHFPredefinedText.SheetName);
ws.PageSetup.Header.Left.AddText(XLHFPredefinedText.Date);

Common fields: PageNumber, NumberOfPages, Date, Time, File (file name), Path, FullPath, SheetName.

Different headers on different pages​

XLHFOccurrence scopes a run to a subset of pages:

var header = ws.PageSetup.Header;

header.Right.AddText("Cover", XLHFOccurrence.FirstPage);
header.Right.AddText(XLHFPredefinedText.PageNumber, XLHFOccurrence.OddPages);
header.Left.AddText(XLHFPredefinedText.SheetName, XLHFOccurrence.EvenPages);
header.Center.AddText("Draft", XLHFOccurrence.AllPages);

Alignment and scaling behaviour of the header/footer band:

ws.PageSetup.AlignHFWithMargins = false;    // don't align with the page margins
ws.PageSetup.ScaleHFWithDocument = false;   // keep header size when the sheet is scaled

Page breaks​

ws.PageSetup.AddHorizontalPageBreak(20);   // break after row 20
ws.PageSetup.AddVerticalPageBreak(6);      // break after column 6

Printed appearance​

ws.PageSetup.ShowGridlines = true;         // print the gridlines
ws.PageSetup.ShowRowAndColumnHeadings = true;
ws.PageSetup.BlackAndWhite = true;
ws.PageSetup.DraftQuality = false;

ws.PageSetup.PrintErrorValue = XLPrintErrorValues.Blank;
ws.PageSetup.PageOrder = XLPageOrderValues.OverThenDown;

A worked example​

A wide report set up to print cleanly: landscape, one page wide, header row repeated, page numbers in the footer.

using XLibur.Excel;

using var workbook = new XLWorkbook();
var ws = workbook.Worksheets.Add("Sales Detail");

string[] headers = ["Date", "Customer", "Region", "Product", "Units", "Unit price", "Total"];
for (var i = 0; i < headers.Length; i++)
{
    ws.Cell(1, i + 1).Value = headers[i];
}

ws.Range(1, 1, 1, headers.Length).Style
    .Font.SetBold()
    .Fill.SetBackgroundColor(XLColor.FromTheme(XLThemeColor.Background2));

// ... populate rows 2..N ...
var lastRow = 250;

// Print setup
ws.PageSetup.PageOrientation = XLPageOrientation.Landscape;
ws.PageSetup.PaperSize = XLPaperSize.A4Paper;
ws.PageSetup.FitToPages(1, 0);                       // one page wide, any height

ws.PageSetup.PrintAreas.Add($"A1:G{lastRow}");
ws.PageSetup.SetRowsToRepeatAtTop(1, 1);             // header on every page

ws.PageSetup.Margins.Top = 0.75;
ws.PageSetup.Margins.Bottom = 0.75;
ws.PageSetup.Margins.Left = 0.4;
ws.PageSetup.Margins.Right = 0.4;
ws.PageSetup.CenterHorizontally = true;

ws.PageSetup.Header.Left.AddText("Sales Detail").SetBold();
ws.PageSetup.Header.Right.AddText(XLHFPredefinedText.Date);

ws.PageSetup.Footer.Center.AddText(XLHFPredefinedText.PageNumber);
ws.PageSetup.Footer.Center.AddText(" of ");
ws.PageSetup.Footer.Center.AddText(XLHFPredefinedText.NumberOfPages);
ws.PageSetup.Footer.Right.AddText(XLHFPredefinedText.File);

ws.PageSetup.ShowGridlines = false;

ws.Columns().AdjustToContents();
workbook.SaveAs("SalesDetail.xlsx");

Where to next​

  • Worksheets — freeze panes and on-screen view settings
  • Styling — formatting that carries through to print