Page setup on Excel file in C#, VB.NET, Java, C++, PHP

Page setup

EasyXLS™ library provides the methods for setting the page setup options such as page orientation (portrait and landscape), paper size, scaling factor, fit paper width and paper height, paper margins, print area, rows to be repeated, print order, print gridlines, black and white printing, draft quality, row and column headings and comments printing.

Source code sample
Page orientation
Page scaling
Paper size
Page margins
Header and footer
Print area
Row and columns repeated when printing
Page break preview

Source code sample

The below example shows how to export an Excel file with a specified print area, rows repeated at top, page orientation as portrait, paper size as A4 and other related settings.

C++

// Create an instance of the class that exports Excel files
EasyXLS::IExcelDocumentPtr workbook;
hr = CoCreateInstance(__uuidof(EasyXLS::ExcelDocument),
		      NULL,
		      CLSCTX_ALL,
		      __uuidof(EasyXLS::IExcelDocument),
		      (void**) &workbook) ;

...

// Create two sheets
workbook->easy_addWorksheet_2("First tab");
workbook->easy_addWorksheet_2("Second tab");

// Get the first sheet
EasyXLS::IExcelWorksheetPtr xlsFirstTab = 
        (EasyXLS::IExcelWorksheetPtr)workbook->easy_getSheetAt(0);

// Get the object that stores the page setup options for the first sheet
EasyXLS::IExcelPageSetupPtr xlsPageSetup = xlsFirstTab->easy_getPageSetup();

// Set print area
xlsPageSetup->easy_setPrintArea_2("A1:E101");

// Set the rows to repeat at top
xlsPageSetup->easy_setRowsToRepeatAtTop_2("$1:$1");

// Set center horizontally option
xlsPageSetup->setCenterHorizontally(true);

// Set page orientation
xlsPageSetup->setOrientation(PAGESETUP_ORIENTATION_PORTRAIT);

// Set page order
xlsPageSetup->setPageOrder(PAGESETUP_PAGE_ORDER_DOWN_THEN_OVER);

// Set paper size
xlsPageSetup->setPaperSize(PAGESETUP_PAPER_SIZE_A4);

// Set where the comments to be printed
xlsPageSetup->setPrintComments(PAGESETUP_COMMENTS_AT_END_OF_SHEET);

// Set the gridlines to be printed
xlsPageSetup->setPrintGridlines(true);

// Set page break preview for the sheet
xlsFirstTab->setPageBreakPreview(true);

...

// Export Excel file
workbook->easy_WriteXLSXFile("C:\\Samples\\Page Setup.xlsx");

Click here to see Continuous Code Listing


C++.NET

// Create an instance of the class that exports Excel files, having two sheets
ExcelDocument ^workbook = gcnew ExcelDocument(2);
    
...

// Get the first sheet
ExcelWorksheet ^xlsFirstTab = 
        safe_cast<ExcelWorksheet^>(workbook->easy_getSheetAt(0));

// Get the object that stores the page setup options for the first sheet
ExcelPageSetup ^xlsPageSetup = xlsFirstTab->easy_getPageSetup();

// Set print area
xlsPageSetup->easy_setPrintArea("A1:E101");

// Set the rows to repeat at top
xlsPageSetup->easy_setRowsToRepeatAtTop("$1:$1");

// Set center horizontally option
xlsPageSetup->setCenterHorizontally(true);

// Set page orientation
xlsPageSetup->setOrientation(PageSetup::ORIENTATION_PORTRAIT);

// Set page order
xlsPageSetup->setPageOrder(PageSetup::PAGE_ORDER_DOWN_THEN_OVER);

// Set paper size
xlsPageSetup->setPaperSize(PageSetup::PAPER_SIZE_A4);

// Set where the comments to be printed
xlsPageSetup->setPrintComments(PageSetup::COMMENTS_AT_END_OF_SHEET);

// Set the gridlines to be printed
xlsPageSetup->setPrintGridlines(true);

// Set page break preview for the sheet
xlsFirstTab->setPageBreakPreview(true);

...

// Export Excel file
workbook->easy_WriteXLSXFile("C:\\Samples\\Page Setup.xlsx");

Click here to see Continuous Code Listing

.NET:

// Create an instance of the class that exports Excel files
$workbook = new COM("EasyXLS.ExcelDocument");

// Create two sheets
$workbook->easy_addWorksheet_2("First tab");
$workbook->easy_addWorksheet_2("Second tab");

...


// Get the first sheet
$xlsFirstTab = $workbook->easy_getSheetAt(0);

// Get the object that stores the page setup options for the first sheet
$xlsPageSetup = $xlsFirstTab->easy_getPageSetup();

// Set print area
$xlsPageSetup->easy_setPrintArea_2("A1:E101");

// Set the rows to repeat at top
$xlsPageSetup->easy_setRowsToRepeatAtTop_2("$1:$1");

// Set center horizontally option
$xlsPageSetup->setCenterHorizontally(true);

// Set page orientation
$xlsPageSetup->setOrientation($PAGESETUP_ORIENTATION_PORTRAIT);

// Set page order
$xlsPageSetup->setPageOrder($PAGESETUP_PAGE_ORDER_DOWN_THEN_OVER);

// Set paper size
$xlsPageSetup->setPaperSize($PAGESETUP_PAPER_SIZE_A4);

// Set where the comments to be printed
$xlsPageSetup->setPrintComments($PAGESETUP_COMMENTS_AT_END_OF_SHEET);

// Set the gridlines to be printed
$xlsPageSetup->setPrintGridlines(true);

// Set page break preview for the sheet
$xlsFirstTab->setPageBreakPreview(true);

// Export Excel file
$workbook->easy_WriteXLSXFile("C:\Samples\Page Setup.xlsx");

Click here to see Continuous Code Listing


Java:

// Create an instance of the class that exports Excel files
$workbook = new java("EasyXLS.ExcelDocument");
	
// Create two sheets
$workbook->easy_addWorksheet("First tab");
$workbook->easy_addWorksheet("Second tab");

...


// Get the first sheet
$xlsFirstTab = $workbook->easy_getSheetAt(0);

// Get the object that stores the page setup options for the first sheet
$xlsPageSetup = $xlsFirstTab->easy_getPageSetup();

// Set print area
$xlsPageSetup->easy_setPrintArea("A1:E101");

// Set the rows to repeat at top
$xlsPageSetup->easy_setRowsToRepeatAtTop("$1:$1");

// Set center on page option
$xlsPageSetup->setCenterHorizontally(true);

// Set page orientation
$xlsPageSetup->setOrientation($PAGESETUP_ORIENTATION_PORTRAIT);

// Set page order
$xlsPageSetup->setPageOrder($PAGESETUP_PAGE_ORDER_DOWN_THEN_OVER);

// Set paper size
$xlsPageSetup->setPaperSize($PAGESETUP_PAPER_SIZE_A4);

// Set where the comments to be printed
$xlsPageSetup->setPrintComments($PAGESETUP_COMMENTS_AT_END_OF_SHEET);

// Set the gridlines to be printed
$xlsPageSetup->setPrintGridlines(true);

// Set page break preview for the sheet
$xlsFirstTab->setPageBreakPreview(true);
	
// Export Excel file
$workbook->easy_WriteXLSXFile("C:\Samples\Page Setup.xlsx");

Click here to see Continuous Code Listing

.NET:

# Create an instance of the class that exports Excel files, having two sheets
workbook = ExcelDocument(2)

...

# Get the table of data for the first worksheet
xlsFirstTable = workbook.easy_getSheetAt(0).easy_getExcelTable()

# Get the object that stores the page setup options for the first sheet
xlsPageSetup = xlsFirstTab.easy_getPageSetup()

# Set print area
xlsPageSetup.easy_setPrintArea("A1:E101")

# Set the rows to repeat at top
xlsPageSetup.easy_setRowsToRepeatAtTop("$1:$1")

# Set center on page option
xlsPageSetup.setCenterHorizontally(True)

# Set page orientation
xlsPageSetup.setOrientation(PageSetup.ORIENTATION_PORTRAIT)

# Set page order
xlsPageSetup.setPageOrder(PageSetup.PAGE_ORDER_DOWN_THEN_OVER)

# Set paper size
xlsPageSetup.setPaperSize(PageSetup.PAPER_SIZE_A4)

# Set where the comments to be printed
xlsPageSetup.setPrintComments(PageSetup.COMMENTS_AT_END_OF_SHEET)

# Set the gridlines to be printed
xlsPageSetup.setPrintGridlines(True)

# Set page break preview for the sheet
xlsFirstTab.setPageBreakPreview(True)

...

# Export Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\Page Setup.xlsx")

Click here to see Continuous Code Listing


Java:

# Create an instance of the class that exports Excel files, having two sheets
workbook = gateway.jvm.ExcelDocument(2)

...

# Get the table of data for the first worksheet
xlsFirstTable = workbook.easy_getSheetAt(0).easy_getExcelTable()

# Get the object that stores the page setup options for the first sheet
xlsPageSetup = xlsFirstTab.easy_getPageSetup()

# Set print area
xlsPageSetup.easy_setPrintArea("A1:E101")

# Set the rows to repeat at top
xlsPageSetup.easy_setRowsToRepeatAtTop("$1:$1")

# Set center on page option
xlsPageSetup.setCenterHorizontally(True)

# Set page orientation
xlsPageSetup.setOrientation(gateway.jvm.PageSetup.ORIENTATION_PORTRAIT)

# Set page order
xlsPageSetup.setPageOrder(gateway.jvm.PageSetup.PAGE_ORDER_DOWN_THEN_OVER)

# Set paper size
xlsPageSetup.setPaperSize(gateway.jvm.PageSetup.PAPER_SIZE_A4)

# Set where the comments to be printed
xlsPageSetup.setPrintComments(gateway.jvm.PageSetup.COMMENTS_AT_END_OF_SHEET)

# Set the gridlines to be printed
xlsPageSetup.setPrintGridlines(True)

# Set page break preview for the sheet
xlsFirstTab.setPageBreakPreview(True)

...

# Export Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\Page Setup.xlsx")

Click here to see Continuous Code Listing

The screen shot provides an example of an Excel report that has the following page setup options for a worksheet: print area, rows to be repeated at top, center horizontally, page orientation (portrait), page order (down), paper size (A4), print comments and gridlines, page break at row 21.

Page orientation

EasyXLS allows setting the page orientation as portrait or landscape using ExcelPageSetup.setOrientation method.

Page scaling

EasyXLS allows scaling the page by zoom using ExcelPageSetup.setScalingFactor. It also allows to scale the page automatically by page content using ExcelPageSetup.setFitToPagesHeight and ExcelPageSetup.setFitToPagesWidth methods.

Paper size

EasyXLS allows setting the paper size to A4, A5, letter and other page formats using ExcelPageSetup.setPaperSize method.

Page margins

EasyXLS allows setting the page margins: top and bottom margins, left and right margins accordantly using ExcelPageSetup.setTopMargin, ExcelPageSetup.setBottomMargin, ExcelPageSetup.setLeftMargin, ExcelPageSetup.setRightMargin methods.

It also allows setting the header and footer size using ExcelPageSetup.setHeaderMargin and ExcelPageSetup.setFooterMargin methods.

Header and footer

EasyXLS allows setting the header and footer sections.

Print area

EasyXLS allows setting the print area for the sheet using ExcelPageSetup.easy_setPrintArea method.

For multiple cell ranges in print area, the range parameter must be passed to the method as union of ranges (multiple ranges separated by comma).

Row and columns repeated when printing

EasyXLS allows you to set the rows to be repeated at top of the page and the columns to be repeated at left of the page when the Excel file is printed using ExcelPageSetup.easy_setRowsToRepeatAtTop methods and ExcelPageSetup.easy_setColumnsToRepeatAtLeft methods respectively.

Page break preview

In order to visualize the printable area and the pagination, the view must be set on "Page Break Preview" mode using ExcelWorksheet.setPageBreakPreview method.

Available for: Professional, Excel Writer, Excel Reader

EasyXLS Excel libraries:
.NET Excel Library
full .NET version to import, export or convert Excel files
COM+ Excel Library
full COM+ version to import, export or convert Excel files


Java Excel Library
full Java version to import, export or convert Excel files