Edit this page
Available for: UI for ASP.NET AJAX | UI for ASP.NET MVC | UI for WPF | UI for WinForms | UI for Silverlight

Worksheet Page Setup

There are cases, such as printing or exporting scenarios, when you need to present the Worksheet's content on a set of pages. In these cases, particularly handy comes the WorksheetPageSetup class which provides you with the needed properties for controlling how the content is split and presented into pages.

This article aims to present the WorksheetPageSetup API and demonstrate how to use it. It contains the following sections:

WorksheetPageSetup Properties

Through the Worksheet's WorksheetPageSetup property you may change the following worksheet's page setup properties:

  • PaperType: Specify paper type using the PaperTypes enumeration.

  • PageOrientation: Specify whether the page orientation should be Portrait or Landscape.

  • Margins: Specify the sizes of the page margins.

  • HeaderFooterSettings: Allows you specify a header and/or a footer for a worksheet. For more information on how to achieve this, please check the Headers and Footers topic.

  • PageOrder: Specify whether the page order should be "Down, then over" or "Over, then down".

  • CenterHorizontally: Specify whether the print content should be centered horizontally within the area between the page margins.

  • CenterVertically: Specify whether the print content should be centered vertically within the area between the page margins.

  • ScaleFactor: Specify the scale factor to print with value in the range from 50% to 400%.

  • PrintOptions: Specify print options such as whether to print gridlines or row and column headings.

  • PrintArea: Change the print area in the selected worksheet.

  • PageBreaks: Change the page breaks collection in the selected worksheet.

Figures 1 and 2 show an example of Worksheet's page setup usage. In the example, we have spreadsheet data that has bigger width than height. Previewing the print pages with the default settings we can see that it doesn't fit well as the print content is split into two pages.

Figure 1: Initial print preview of data

Rad Spread Processing Features Worksheet Page Setup 01

In order to fit the print content better, we use the Worksheet's page setup and change page orientation as well as the scale factor and some additional print settings. Example 1 shows the code that needs to be executed.

[C#] Example 1: Use WorksheetPageSetup

WorksheetPageSetup pageSetup = workbook.ActiveWorksheet.WorksheetPageSetup;

pageSetup.PaperType = PaperTypes.A4;
pageSetup.PageOrientation = PageOrientation.Landscape;
pageSetup.ScaleFactor = new Size(0.9, 0.9);
pageSetup.CenterHorizontally = true;

As a result, we managed to fit the data into a single page with size A4 as shown in Figure 2.

Figure 2: Result after page setup

Rad Spread Processing Features Worksheet Page Setup 02

Using Print Area

When printing a worksheet, by default the whole used cell range is used for printing. If you do not need to print the whole content of the worksheet, you can set a print area by specifying a list of ranges to print.

Through WorksheetPageSetup's PrintArea property you can access the print area of a worksheet and change its print ranges with the following methods:

  • SetPrintArea(): Sets the print area ranges using some given set of CellRange instances. This method clears all previously set ranges.

  • CanAddToPrintArea(): Returns a Boolean indicating whether the passed set of print ranges can be added in the existing print area. If some of the given ranges intersects with an already existing print area range, the result is false.

  • TryAddToPrintArea(): Tries to add some given set of CellRange instances to the collection of areas and returns a Boolean indicating the success of this operation.

  • Clear(): Clears the existing print area ranges.

The example shown in Figure 3 demonstrates how to use Worksheet's print area. In this example, we have a big table with data and we want to print only two specific ranges. To achieve that, the print area is set with these cell ranges in the code snippet from Example 2.

[C#] Example 2: Set PrintArea

PrintArea printArea = workbook.ActiveWorksheet.WorksheetPageSetup.PrintArea;

printArea.SetPrintArea(new CellRange[]
{
    new CellRange(0, 0, 4, 8),
    new CellRange(1, 1, 25, 3)
});

Figure 3: Resulting PrintArea preview

Rad Spread Processing Features Worksheet Page Setup 03

Using Page Breaks

When a big cell range cannot fit into a single page it gets split into multiple pages. If you need your pages to be split in some concrete places, you can specify these places by inserting a PageBreak.

Through WorksheetPageSetup's PageBreaks property you can manipulate the page breaks collection of a worksheet using the following methods:

  • TryInsertHorizontalPageBreak(): Tries to insert a horizontal page break at some specific index. Returns true when a page break is inserted.

  • TryInsertHorizontalPageBreak(): Tries to insert a horizontal page break at some specific index. Returns true when a page break is inserted.

  • TryInsertVerticalPageBreak(): Tries to insert a vertical page break at some specific index. Returns true when a page break is inserted.

  • TryRemoveHorizontalPageBreak(): Tries to remove a horizontal page break at some specific index. Returns true when a page break is removed.

  • TryRemoveVerticalPageBreak(): Tries to remove a vertical page break at some specific index. Returns true when a page break is removed.

  • TryInsertPageBreaks(): Tries to insert horizontal and vertical page break at some specific index. Returns true when at least one page break is inserted.

  • TryRemovePageBreaks(): Tries to remove horizontal and vertical page break at some specific index. Returns true when at least one page break is removed.

  • Clear(): Clears all existing page breaks from the page breaks collection.

Figure 4 shows a preview of large amount of data.

Figure 4: Initial preview of data

Rad Spread Processing Features Worksheet Page Setup 04

In order to separate semantically-correct the print data onto several pages, we are going to place horizontal page breaks at the place where we need the splitting to happen. Example 3 shows how this can be achieved.

[C#] Example 3: Insert PageBreaks

PageBreaks pageBreaks = workbook.ActiveWorksheet.WorksheetPageSetup.PageBreaks;

pageBreaks.TryInsertHorizontalPageBreak(5, 0);
pageBreaks.TryInsertHorizontalPageBreak(8, 0);
pageBreaks.TryInsertHorizontalPageBreak(11, 0);
pageBreaks.TryInsertHorizontalPageBreak(14, 0);
pageBreaks.TryInsertHorizontalPageBreak(17, 0);
pageBreaks.TryInsertHorizontalPageBreak(20, 0);
pageBreaks.TryInsertHorizontalPageBreak(23, 0);

As a result of inserting these horizontal page breaks we have eight pages to print. The first one is shown in Figure 5.

Figure 5: Result of PageBreaks

Rad Spread Processing Features Worksheet Page Setup 05