ClosedXML Excel Printing settings

Sometimes you need to create Excel files with some desired data in a project, and the most important thing is to set the design while printing from the Excel file. In my recent project, I was working on the reports module of the project, where I had to print the data from Excel in some desired format based on the client's requirements.

In that project, I used the ClosedXML library to create the Excel files. It is a .NET MIT licensed library for reading, manipulating, and writing Excel 2007+ (.xlsx, .xlsm) files. So, I have listed out some necessary ClosedXML library page setup settings that will be helpful for someone who is looking for the same. I am using version 0.102.1 of the ClosedXML library.

Define the ClosedXML Workbook and worksheet objects.

var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Contacts");

Here are all the basic settings available in the ClosedXML library.

Page Orientation: Page orientation is the way in which a rectangular page is oriented for normal viewing.

ws.PageSetup.PageOrientation = XLPageOrientation.Landscape;
ws.PageSetup.PageOrientation = XLPageOrientation.Portrait;

Apart from these common page Orientation, there are a total of 3 options available in this enum (XLPageOrientation).

Page Scaling: Scaling is used to enlarge or shrink and resize pages. There are 2 options in Excel that you can use to scale the page.

1. Adjust to %:-  

   ws.PageSetup.AdjustTo(80);

   Here 80 is the % value.

2. Fit to:- 

   ws.PageSetup.FitToPages(2, 2); 

   Alternatively, you can use the following too

   ws.PageSetup.PagesTall = x // and/or ws.PageSetup.PagesWide = x

Paper Size:  Excel is designed to work with a variety of paper sizes, including standard sizes like letter and legal, as well as custom sizes.

ws.PageSetup.PaperSize = XLPaperSize.LegalPaper;

This XLPaperSizee num has a total of 68 different Paper size values.

DPI: You can set Vertical & Horizontal DPI values by using the following code:

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

Margins: You can set different margins for different sides.

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.Footer = 0.15;
ws.PageSetup.Margins.Header = 0.30;
ws.PageSetup.CenterHorizontally = true;
ws.PageSetup.CenterVertically = true;

Header & Footer: - Header & Footer basically have 3 sections Left, Center, Right.

ws.PageSetup.Header.Left.AddText("LEFT TEXT");
ws.PageSetup.Header.Center.AddText("CENTER TEXT");
ws.PageSetup.Header.Right.AddText("RIGHT TEXT");

Below example is to set the font color and style of the font.

ws.PageSetup.Header.Right.AddText("RIGHT", XLHFOccurrence.FirstPage).SetBold();
ws.PageSetup.Header.Right.AddText("TEXT", XLHFOccurrence.FirstPage).SetFontColor(XLColor.Red);

You can also use Rich text to set the style of text like color and font and many more operations.

var richText = ws.PageSetup.Header.Right.AddText("Some Text", XLHFOccurrence.FirstPage);
richText.FontColor = XLColor.Blue;
richText.Underline = XLFontUnderlineValues.Double;

You can also use multiple methods at the same time as shown below:

ws.PageSetup.Header.Right.AddText("Page",XLHFOccurrence.FirstPage).SetBold().SetItalic().SetFontName("Impact");

  

 

In case the font defined in the code is not installed on your system, then system will show excel's default font.

Below is an example of footer text:

ws.PageSetup.Footer.Center.AddText(XLHFPredefinedText.PageNumber, XLHFOccurrence.AllPages);
ws.PageSetup.Footer.Center.AddText(" / ", XLHFOccurrence.AllPages);
ws.PageSetup.Footer.Center.AddText(XLHFPredefinedText.NumberOfPages, XLHFOccurrence.AllPages);

Page Order: This setting is used to set the print areas order i.e., from left to right or up to down.

ws.PageSetup.PageOrder = XLPageOrderValues.DownThenOver;
ws.PageSetup.PageOrder = XLPageOrderValues.OverThenDown;

Page break: Page breaks are dividers that break a worksheet into separate pages for printing. You can add Horizontal and Vertical page break with using following code.

ws.PageSetup.AddHorizontalPageBreak(x);//x is row number
ws.PageSetup.AddVerticalPageBreak(x);//x is column number

Print Area: Print areas are basically one or more ranges of cells that you designate to print when you don't want to print the entire worksheet. You can add print areas by using following code:

ws.PageSetup.PrintAreas.Add("A1:B2");
ws.PageSetup.PrintAreas.Add("D3:D5");