Get Worksheet Pagination

DsExcel allows you to determine where a worksheet will be divided into pages before exporting it to PDF. Use the GetPaginationInfo method of the PrintManager class to retrieve the page boundaries based on the worksheet's page setup.

Call the method separately for horizontal and vertical pagination. The returned collection contains the zero-based column or row indexes after which page breaks occur.

If IPageSetup is not specified, DsExcel uses the range from cell A1 to the last cell that contains data.

Get Page Boundaries

The following example retrieves the horizontal and vertical page boundaries and then exports the worksheet to PDF.

 // Create a new workbook.
 Workbook workbook = new Workbook();
 IWorksheet worksheet = workbook.getWorksheets().get(0);

 // The row and column headings are printed.
 worksheet.getPageSetup().setPrintHeadings(true);
 
 // The range "B6:N80" will be printed.
 worksheet.getPageSetup().setPrintArea("B6:N80");

 // Set data.
 worksheet.getRange("B6:S8").setValue(10);

 // Add a table.
 worksheet.getTables().add(worksheet.getRange("B6:N20"), true);
 PrintManager printManager = new PrintManager();
           
 // The columnIndexs is [9, 13], this means that the horizontal direction is split after the column 10th and 14th.
 List<Integer> columnIndexs = printManager.GetPaginationInfo(worksheet, PaginationOrientation.Horizontal);
 
 // The rowIndexs is [50, 79], this means that the vertical direction is split after the row 51th and 80th.
 List<Integer> rowIndexs = printManager.GetPaginationInfo(worksheet, PaginationOrientation.Vertical);
    
 worksheet.save("Export.pdf", SaveFileFormat.Pdf);

Get Page Boundaries with Keep-Together and Repeat Settings

The following example applies keep-together ranges and repeat settings when calculating page boundaries and exporting the worksheet to PDF.

// Create a new workbook.
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getWorksheets().get(0);

// The row and column headings are printed.
worksheet.getPageSetup().setPrintHeadings(true);

// The range "B6:N80" will be printed.
worksheet.getPageSetup().setPrintArea("B6:N80");

// Set data.
worksheet.getRange("B60:N80").setValue(1);

// Add a table.
worksheet.getTables().add(worksheet.getRange("B6:N20"), true);

// The row 6th will be printed at the top of each page.
ArrayList<RepeatSetting> repeatSettings = new ArrayList<RepeatSetting>();
RepeatSetting repeatSetting = new RepeatSetting();
repeatSetting.setTitleRowStart(5);
repeatSetting.setTitleRowEnd(5);
repeatSetting.setRange(worksheet.getRange("B6:N80"));
repeatSettings.add(repeatSetting);

// The rows from 25th to 60th should be paged to one page.
ArrayList<IRange> keepTogetherRanges = new ArrayList<IRange>();
keepTogetherRanges.add(worksheet.getRange("$25:$60"));
PrintManager printManager = new PrintManager();

// The columnIndexs is [9, 13], this means that the horizontal direction is
// split after the column 10th and 14th.
List<Integer> columnIndexs = printManager.GetPaginationInfo(worksheet, PaginationOrientation.Horizontal,
keepTogetherRanges, repeatSettings);

// The rowIndexs is [23, 66, 79], this means that the vertical direction is
// split after the row 24th, 67th and 80th.
List<Integer> rowIndexs = printManager.GetPaginationInfo(worksheet, PaginationOrientation.Vertical,
keepTogetherRanges, repeatSettings);
List<PageInfo> pages = printManager.paginate(worksheet, keepTogetherRanges, repeatSettings);

// Export the generated pages to a PDF file.
printManager.savePageInfosToPDF("GetPaginationRangesRepeatSettings.pdf", pages);