DeleteBlankRows failed to work

Aspose.Cells Product Family
1

I use the following code to export a excel to pdf:



Workbook workbook = new Workbook(sourcePath);



WorksheetCollection collection = workbook.getWorksheets();

for (int i = 0; i
Worksheet sheet = collection.get(i);

sheet.getCells().deleteBlankColumns();

sheet.getCells().deleteBlankRows();

}



PdfSaveOptions pdfSaveOptions = new PdfSaveOptions();

pdfSaveOptions.setAllColumnsInOnePagePerSheet(true);

workbook.save(targetPath, pdfSaveOptions);



But the output pdf still contains one blank page.

The file is attached.



And is it possible to contain sheet name to the output pdf file?

2

Hi Huangfu,

Thanks for your posting and using Aspose.Cells.

Please download and try the latest version: Aspose.Cells for Java (Latest Version) and see if it resolves your issue.

If your issue still persists, then please provide us your source Excel file which you are converting to PDF. We will look into your issue and help you asap.

If you want to show the sheet name inside the PDF page, then you will have to set the Header of the sheet with its name. Please see the following article that explains how to set Header & Footer of worksheet.


3

8.2.1.4 will still generate the empty page.

The source file is attached



And the header solution solved my second problem perfectly.

4

Still a problem with header.

The same source file, use the following command to set the header:sheet.getPageSetup().setHeader(1, “&A”);



The last page’s header will be lower than the real content. See the attachment

5

Hi Huangfu,


Please use the following piece of code to render the provided spreadsheet to PDF format.

Java

String fname = “预算.xlsx”;
Workbook wb = new Workbook(myDir + fname);
int worksheetCount = wb.getWorksheets().getCount();
for (int i = 0 ; i < worksheetCount; i++)
{
Worksheet sheet = wb.getWorksheets().get(i);
sheet.getCells().deleteBlankColumns();
sheet.getCells().deleteBlankRows();
}
PdfSaveOptions options = new PdfSaveOptions();
options.setOnePagePerSheet(true);
wb.save(myDir + fname + “.pdf”, options);

Actually, there are many empty rows and columns in your provided spreadsheet therefore when you render it to PDF format without deleting the empty rows & columns, the empty pages appear. In addition we have set the API to render one page for one worksheet so that the view is complete.

Regarding the header issue as you have narrated in the previous response, we believe the behavior is correct as you may see from the attached resultant PDF file. If you zoom the PDF a bit, you will notice that there is quite a space between the header & original contents, where header is always above the contents.
6

Actually, in your output pdf, there are still lots of blank at the bottom of the first page.
deleteBlankRows seems failed to recognize those blanks.

And for our business logic, the sheet may be very long. So we have to use AllColumnsInOnePagePerSheet, so that empty pages appear.

And for the headers, you can carefully examine your output pdf, the original content is much higher in the last page than in the others. If you set the header, the header in the last page will below the original content.

7

Hi Huangfu,


Thank you for writing back.

We have further evaluated the presented scenario on our end, and have noticed that Aspose.Cells generate an extra page when AllColumnsInOnePagePerSheet property is set to true. The reason for this problem is the print area of the first worksheet “费用预算说明”. You can further confirm this by saving the workbook after deleting blank rows & columns, you’ll notice that the print area for the aforesaid worksheet is not right. In order avoid this situation, please set the print area to empty string as demonstrated in below code snippet. Please also note that PageSetup configurations do not take effect with AllColumnsInOnePagePerSheet property set to true.

Java

for (int i = 0 ; i < worksheetCount; i++)
{
Worksheet sheet = wb.getWorksheets().get(i);
sheet.getCells().deleteBlankColumns();
sheet.getCells().deleteBlankRows();
sheet.getPageSetup().setPrintArea("");
}

Regarding the header issue, the behavior is similar as MS Excel in print preview. Again, this can be confirmed by saving the spreadsheet in XLSX format after deleting the blank rows/columns, and loading it in MS Excel to check the print preview.
8

After setting the print area, it’s ok now.

Thanks a lot.

9

Hi Huangfu,


Thank you for the confirmation. Please feel free to write back in case you need our further assistance with Aspose APIs.