Hi,
We use Aspose.Cells for java for creating PDF and Excel reports with our application. Therefore we read in Excel report templates and fill in application data. The user can choose between PDF and Excel output format. In one of our report templates we sometimes insert many lines of text which - in case the content exceeds one page - results in broken page breaks in the PDF format. I.e. the last line on the first page is not shown completely - only the upper half of the line is shown on page 1 and the lower half is shown on page 2.
Print outs of the Excel version does not have this issue.
We have created demo code and files to show the issue if needed.
Best regards,
Benjamin
@global-format,
Please try our latest version/fix ( Aspose.Cells for Java 21.11 if you are not already using it). If you still find the issue with latest version, kindly do create a separate console program/code (runnable) and provide us. Also, share your template Excel file (please zip the file prior attaching here). We will check your issue soon.
We already tried 21.11.
Please find attached our demo code and our template Excel file:
pagebreak.zip (20.8 KB)
@global-format,
I have tested your scenario/case using our latest version/fix. I found the issue as you mentioned when rendering Excel file to PDF after inserting rows and setting HTML string to the cell. I have attached the screenshot for your reference. Is this the issue you are talking about, please confirm?
sc_shot1.png (76.0 KB)
In the meanwhile, I have logged a ticket with an id “CELLSJAVA-44129” for the issue. We will look into it soon.
Once we have an update on it, we will let you know.
Yes - this is the issue. Thank you very much for your quick response.
@global-format,
Ok got it, and thanks for the confirmation.
@global-format
The root cause is that the page break is on the merged cell. One of the text line in the merged cell will have opportunity to be just on the page break dividing line. See the file CELLSJAVA-44129_break_on_line.zip (19.8 KB) , I just change the row height of Row 76 a little, you can see Microsoft Excel will have the same issue.
The way to avoid the issue is re-designing the page layout. e.g. add page break before the merged cell.
Your attached example, when converted to PDF with Excel 2016 does not have any issue. But with the latest Excel version I confirm, that Excel has the same issue. 
The problem is, that we cannot set the page break before the merged cell because a) for the majority of cases the inserted text fits on one page and b) we don’t wont the first page to be almost blank.
At the moment we insert lines for long texts and by doing that extend the merged cells over one page.
Is there any possibility to check if the text inserted with setHtmlString exceeds the available space of a cell / merged cell area? If so, we could split the text and insert only so much that it perfectly fits and then insert the rest of the text in another cell (next page).
@global-format,
Thanks for sharing your concerns.
We will evaluate your issue further and especially your demand (as following) if this is feasible.
@global-format
The API Worksheet.GetPrintingPageBreaks(ImageOrPrintOptions options) can get page breaks for each page, and the API Cell.GetHeightOfValue() can evaluate text height in cell. You can try the following code to check if the text in the merged cell goes to next page.
Workbook wb = new Workbook("pagebreak-demo.xlsx");
Worksheet worksheet = wb.getWorksheets().get(0);
worksheet.getCells().insertRows(76, 20);
Cell commentCell = worksheet.getCells().get(75, 2);
commentCell.setHtmlString("Das<br>"
+ "ist000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000033333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333334444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444444455555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777788888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999<br>"
+ "ein<br>" + "text<br>" + "mit<br>" + "vielen<br>" + "Zeilen<br>" + "die<br>" + "angezeigt<br>" + "sein<br>" + "wollen.<br>" + "Besonders<br>" + "dann<br>"
+ "wenn <br>" + "der <br>" + "Text<br>" + "auf<br>" + "mehrere<br>" + "Seiten<br>" + "verteilt<br>" + "wird<br>");
//evaluate text height in cell
int textHeight = commentCell.getHeightOfValue();
//get page breaks of the sheet.
CellArea[] cellAreas = worksheet.getPrintingPageBreaks(new ImageOrPrintOptions() { });
int endRowOfPage0 = cellAreas[0].EndRow;
double rowHeightInPage0 = 0;
//Your Merged Cell starts from Row 75(zero based)
for(int row = 75; row <= endRowOfPage0; row++)
{
rowHeightInPage0 += worksheet.getCells().getRowHeight(row);
}
//text in merged cell goes to next page
if(textHeight > rowHeightInPage0)
{
//do work to split text here
//But it seems that there is no good way to get the accurate position where the text needs to be split.
//...
}
wb.save("pagebreak-demo.out.xlsx", SaveFormat.XLSX);
wb.save("pagebreak-demo.out.pdf", new PdfSaveOptions());
But it seems that there is no good way to get the accurate position where the text needs to be split.