Split a worksheet into pages

GroupDocs.Viewer uses page breaks to split a worksheet into separate pages. Microsoft Excel adds automatic page breaks based on paper size and page settings, but you can also insert manual horizontal and vertical page breaks. Switch to Page Break Preview in Microsoft Excel to see where page breaks appear on a worksheet.

In the image below, the vertical page break is inserted after the column E, and the horizontal page break is located under the row 30.

Preview page breaks in Microsoft Excel

To render a worksheet based on the inserted page breaks, call the SpreadsheetOptions.ForRenderingByPageBreaks static method and assign the returned SpreadsheetOptions instance to the ViewOptions.SpreadsheetOptions property of your view (depending on the output file format).

The following example converts a worksheet to PDF and uses the page breaks to split this worksheet into pages:

using GroupDocs.Viewer;
using GroupDocs.Viewer.Options;
// ...

using (var viewer = new Viewer("products.xlsx"))
{
    // Convert the spreadsheet to PDF.
    var viewOptions = new PdfViewOptions("output.pdf");
    // Split using page breaks.
    viewOptions.SpreadsheetOptions = SpreadsheetOptions.ForRenderingByPageBreaks();
    viewer.View(viewOptions);
}
Imports GroupDocs.Viewer
Imports GroupDocs.Viewer.Options
' ...

Module Program
    Sub Main(args As String())
        Using viewer = New Viewer("products.xlsx")
            ' Convert the spreadsheet to PDF.
            Dim viewOptions = New PdfViewOptions("output.pdf")
            ' Split using page breaks.
            viewOptions.SpreadsheetOptions = SpreadsheetOptions.ForRenderingByPageBreaks()
            viewer.View(viewOptions)
        End Using
    End Sub
End Module

The image below illustrates the result.

Render a worksheet to PDF based on page breaks

Split a worksheet into pages by rows

The following code snippet splits a worksheet into two pages and renders them to PDF:

using GroupDocs.Viewer;
using GroupDocs.Viewer.Options;
// ...

using (var viewer = new Viewer("two-pages.xlsx"))
{
    // Specify number of rows for every page.
    int rowsPerPage = 15;
    // Convert the spreadsheet to PDF.
    var viewOptions = new PdfViewOptions("output.pdf");
    // Split by number of rows.
    viewOptions.SpreadsheetOptions = SpreadsheetOptions.ForSplitSheetIntoPages(rowsPerPage);
    viewer.View(viewOptions);
}
Imports GroupDocs.Viewer
Imports GroupDocs.Viewer.Options
' ...

Module Program
    Sub Main(args As String())
        Using viewer = New Viewer("two-pages.xlsx")
            ' Specify number of rows for every page.
            Dim rowsPerPage As Integer = 15
            ' Convert the spreadsheet to PDF.
            Dim viewOptions = New PdfViewOptions("output.pdf")
            ' Split by number of rows.
            viewOptions.SpreadsheetOptions = SpreadsheetOptions.ForSplitSheetIntoPages(rowsPerPage)
            viewer.View(viewOptions)
        End Using
    End Sub
End Module

The image below demonstrates the output PDF file.

Break a worksheet into two pages

Split a worksheet into pages by rows and columns

The following code snippet splits a worksheet into four pages and renders them to PDF:

using GroupDocs.Viewer;
using GroupDocs.Viewer.Options;
// ...

using (var viewer = new Viewer("four-pages.xlsx"))
{
    // Specify number of rows and columns for every page.
    int rowsPerPage = 15;
    int columnsPerPage = 7;
    // Convert the spreadsheet to PDF.
    var viewOptions = new PdfViewOptions("output.pdf");
    // Split by number of rows and columns.
    viewOptions.SpreadsheetOptions = SpreadsheetOptions.ForSplitSheetIntoPages(rowsPerPage, columnsPerPage);
    viewer.View(viewOptions);
}
Imports GroupDocs.Viewer
Imports GroupDocs.Viewer.Options
' ...

Module Program
    Sub Main(args As String())
        Using viewer = New Viewer("four-pages.xlsx")
            ' Specify number of rows and columns for every page.
            Dim rowsPerPage As Integer = 15
            Dim columnsPerPage As Integer = 7
            ' Convert the spreadsheet to PDF.
            Dim viewOptions = New PdfViewOptions("output.pdf")
            ' Split by number of rows and columns.
            viewOptions.SpreadsheetOptions = SpreadsheetOptions.ForSplitSheetIntoPages(rowsPerPage, columnsPerPage)
            viewer.View(viewOptions)
        End Using
    End Sub
End Module

The image below demonstrates the output PDF file.

Break a worksheet into four pages

Render a print area

In Microsoft Excel, you can designate one or more cell ranges in a worksheet as the only region to print (a print area). A worksheet can contain multiple print areas. Each print area prints on its own page.

Specify a print area in Microsoft Excel

GroupDocs.Viewer also supports this option. Call the SpreadsheetOptions.ForRenderingPrintArea static method and assign the returned SpreadsheetOptions instance to the ViewOptions.SpreadsheetOptions property to display only the worksheet’s print area in the output HTML, PDF, or image file.

The following example renders the print area displayed in the image above to PDF:

using GroupDocs.Viewer;
using GroupDocs.Viewer.Options;
// ...

using (var viewer = new Viewer("invoice.xlsx"))
{
    // Convert the spreadsheet to PDF.
    var viewOptions = new PdfViewOptions("output.pdf");
    // Render the print area only.
    viewOptions.SpreadsheetOptions = SpreadsheetOptions.ForRenderingPrintArea();
    viewer.View(viewOptions);
}
Imports GroupDocs.Viewer
Imports GroupDocs.Viewer.Options
' ...

Module Program
    Sub Main(args As String())
        Using viewer = New Viewer("invoice.xlsx")
            ' Convert the spreadsheet to PDF.
            Dim viewOptions = New PdfViewOptions("output.pdf")
            ' Render the print area only.
            viewOptions.SpreadsheetOptions = SpreadsheetOptions.ForRenderingPrintArea()
            viewer.View(viewOptions)
        End Using
    End Sub
End Module

The image below illustrates the result.

Render the print area to PDF

Render a worksheet on one page

If you want to display all worksheet data on one page, call the SpreadsheetOptions.ForOnePagePerSheet static method and assign the returned SpreadsheetOptions instance to the ViewOptions.SpreadsheetOptions property for a target view.

The following example converts each worksheet to one page in the PDF file:

using GroupDocs.Viewer;
using GroupDocs.Viewer.Options;
// ...

using (var viewer = new Viewer("Products.xlsx"))
{
    // Convert the spreadsheet to PDF.
    var viewOptions = new PdfViewOptions("output.pdf");
    // Render each worksheet to one page.
    viewOptions.SpreadsheetOptions = SpreadsheetOptions.ForOnePagePerSheet();
    viewer.View(viewOptions);
}
Imports GroupDocs.Viewer
Imports GroupDocs.Viewer.Options
' ...

Module Program
    Sub Main(args As String())
        Using viewer = New Viewer("Products.xlsx")
            ' Convert the spreadsheet to PDF.
            Dim viewOptions = New PdfViewOptions("output.pdf")
            ' Render each worksheet to one page.
            viewOptions.SpreadsheetOptions = SpreadsheetOptions.ForOnePagePerSheet()
            viewer.View(viewOptions)
        End Using
    End Sub
End Module

The image below illustrates the result. The output PDF file contains one page that displays all worksheet data.

Fit sheet on one page

Render worksheet by page breaks and print area

GroupDocs.Viewer uses page breaks to split a worksheet into separate pages. Microsoft Excel adds automatic page breaks based on paper size and page settings, but you can also insert manual horizontal and vertical page breaks. Switch to Page Break Preview in Microsoft Excel to see where page breaks appear on a worksheet.

Also, you can designate one or more cell ranges in a worksheet as the only region to print (a print area). A worksheet can contain multiple print areas. Each print area prints on its own page.

When printing, Microsoft Excel splits a worksheet into pages using both page breaks and print areas. In the following image the red line shows the print area, and the blue line shows page breaks:

Specify page breaks and a print area in Microsoft Excel

GroupDocs.Viewer also supports this option. Call the SpreadsheetOptions.ForRenderingPrintAreaAndPageBreaks static method and assign the returned SpreadsheetOptions instance to the ViewOptions.SpreadsheetOptions property to display only the worksheet’s print area in the output HTML, PDF, or image file.

The following example renders the Microsoft Excel spreadsheet using page breaks and print areas displayed in the image above to PDF:

using GroupDocs.Viewer;
using GroupDocs.Viewer.Options;
// ...

//render spreadsheet to PDF
using (var viewer = new Viewer("products.xlsx"))
{
    var viewOptions = new PdfViewOptions("output.pdf");
    viewOptions.SpreadsheetOptions = SpreadsheetOptions.ForRenderingPrintAreaAndPageBreaks();
    viewer.View(viewOptions);
}
Imports GroupDocs.Viewer
Imports GroupDocs.Viewer.Options
' ...

Module Program
    Sub Main(args As String())
        'render spreadsheet to PDF
        Using viewer = New Viewer("products.xlsx")
            Dim viewOptions = New PdfViewOptions("output.pdf")
            viewOptions.SpreadsheetOptions = SpreadsheetOptions.ForRenderingPrintAreaAndPageBreaks()
            viewer.View(viewOptions)
        End Using
    End Sub
End Module

The image below illustrates the result:

Render page breaks and a print area to PDF