Aspose.Cells Java: PDF conversion from XLSX with many columns causes duplicate pages instead of wrapping columns to next page

Aspose.Cells Product Family
1

when converting xlsx file to pdf using aspose-cells library in java, if xlsx file has many columns , the columns which exceed the page width of pdf should move to next page of the pdf. what we observed is, sometimes the column which exceed the page width of pdf moves to next page, but sometimes it shows pages with duplicate contents across multiple pages, please help to resolve this issue

i observed same result with aspose-cells version: 24.4 and 25.5

xlsxinput.7z (43.5 KB)

xlsxoutput.7z (194.8 KB)

private void removeStyleFromCells(Cells cells, Workbook workbook) {
        Style defaultStyle = workbook.createStyle();
        StyleFlag flag = new StyleFlag();
        flag.setAll(true);
        cells.applyStyle(defaultStyle, flag);

        Range usedRange = cells.getMaxDisplayRange();
        usedRange.applyStyle(defaultStyle, flag);

    }
    PdfSaveOptions getPdfSaveOptions() {
        PdfSaveOptions saveOptions = new PdfSaveOptions();
        saveOptions.setOnePagePerSheet(false);
        saveOptions.setAllColumnsInOnePagePerSheet(false);
        saveOptions.setOutputBlankPageWhenNothingToPrint(false); // Prevent blank pages
        saveOptions.setPrintingPageType(PrintingPageType.IGNORE_BLANK); // Ignore blank
        saveOptions.setCheckWorkbookDefaultFont(false);
        saveOptions.setOptimizationType(PdfOptimizationType.MINIMUM_SIZE);
        return saveOptions;
    }


public void convert(FileInfo fileInfo) {
        try {
            System.out.println("Converting " + fileInfo.getFilename_with_ext() + " to PDF");
            long startTime = System.currentTimeMillis();

            String inputPath = Common.INPUT_DIR_PATH+fileInfo.getFilename_with_ext();
            String outputPath = Common.OUTPUT_DIR_PATH+fileInfo.getFilename_without_ext()+".pdf";

            String orientation = "LANDSCAPE";
            boolean includeComments = true;
            com.aspose.cells.LoadOptions options = new com.aspose.cells.LoadOptions();
            options.setMemorySetting(MemorySetting.MEMORY_PREFERENCE);  // try to optimize mem usage for large files
            options.setStandardFont("Arial");

            String currentOrientation = (orientation == null || orientation.isEmpty()) ? "DEFAULT" : orientation.toUpperCase().trim();
            Workbook workbook = new Workbook(inputPath, options);
            PdfSaveOptions saveOptions = new PdfSaveOptions();
            for (int i = 0; i < workbook.getWorksheets().getCount(); i++) {
                if (!workbook.getWorksheets().get(i).isVisible()) {
                    workbook.getWorksheets().get(i).setVisibilityType(VisibilityType.VISIBLE);
                }
                // allow Aspose to auto-orient if DEFAULT mode is selected for orientation, otherwise we'll orient as specified
                if (currentOrientation.equals("LANDSCAPE") || currentOrientation.equals("PORTRAIT")) {
                    workbook.getWorksheets().get(i).getPageSetup().setOrientation(currentOrientation.equals("LANDSCAPE") ? PageOrientationType.LANDSCAPE : PageOrientationType.PORTRAIT);
                }
                workbook.getWorksheets().get(i).getPageSetup().setBlackAndWhite(false);  // we want to keep the PDF in same colors as input Excel
                workbook.getWorksheets().get(i).getPageSetup().setHFScaleWithDoc(false);  // keep the scale of the header/footer consistent
                String firstPageFooter = workbook.getWorksheets().get(i).getPageSetup().getFirstPageFooter(1);
                if (firstPageFooter == null || firstPageFooter.isEmpty()) {
                    workbook.getWorksheets().get(i).getPageSetup().setFirstPageFooter(1, "Page &P of &N");  // set the default first page footer if none is specified
                }
                workbook.getWorksheets().get(i).getPageSetup().setPrintComments(includeComments ? PrintCommentsType.PRINT_SHEET_END : PrintCommentsType.PRINT_NO_COMMENTS);
                AutoFitterOptions autoFitterOptions = new AutoFitterOptions();
                autoFitterOptions.setAutoFitMergedCellsType(AutoFitMergedCellsType.EACH_LINE);
                workbook.getWorksheets().get(i).autoFitRows(autoFitterOptions);
                workbook.getWorksheets().get(i).autoFitColumns();
            }
            // finally, save the Excel file as a PDF
            workbook.save(outputPath, getPdfSaveOptions());
            long endTime = System.currentTimeMillis();
            System.out.println("Conversion completed in " + (endTime - startTime) + " ms");
        }
        catch (Exception e) {
            //e.printStackTrace();
            System.out.println("138 conversion exception "+e.getMessage());
        }
    }
2

@divine1,

Thanks for the sample Excel files and output PDF files.

I checked your both Excel files. I found for second worksheet (in both workbooks) in Page Setup/Sheet tab, there are options/setttings, i.e., “Rows to repeat at top” and “Columns to repeat at left” set. That’s why you are getting such (duplicated) results or getting pages with duplicate contents across multiple pages in the output PDF files. See the screenshot for your reference, I demonstrated it for “Report file (3).xlsx” file for reference. Other file “Xlsx test-DocumentAuditHistoryReport-2025-04-30-19-12-13-581 (2).xlsx” has the same issue/settings.
sc_shot1.png (94.2 KB)

For your requirements/needs, you need to remove those settings (as exhibited in the screenshot). Either you may do it in code or in MS Excel manually. See the sample lines of code on how to remove the settings for your reference.

workbook.getWorksheets().get(i).getPageSetup().setPrintTitleRows("");
workbook.getWorksheets().get(i).getPageSetup().setPrintTitleColumns("");

Let us know if you still have any issue or questions.

1 Like
3

@amjad.sahi by using your solution i think i can see better output for the attached input xlsx files. i need to test using larger datasets to confirm. Thank you.

i would like to know how you figured out this solution? just curious. where you part of the aspose-cells development team??

4

@divine1,

It’s good to hear that the suggested solution meets your needs. Feel free to take your time and apply it to your larger files.

I reviewed your sample code snippet and manually checked your Excel files in MS Excel. Upon inspection, I noticed that the “Rows to repeat at top” and “Columns to repeat at left” settings are enabled for the worksheet in the Page Setup. These settings cause duplicated rows and columns to appear across pages. You can confirm this by viewing the print preview of the second sheet in MS Excel, where the duplication is evident across different pages. To achieve your desired display in the output PDF, you need to remove these settings.

For your reference, I am part of the Aspose.Cells team, which consists of core management, engineering, and support leads operating from various locations worldwide. You can visit https://about.aspose.com/ for more information.

1 Like