Converting entire XLS/XLSX file content to pdf, ignore Print Area

Right now I’m converting Excel files to PDF files in Java by using Apose.Cells, but I’m limited only to Print Area in these Excel files. Everything else is ignored. It is really frustrating. How can I ignore Print Area and simply convert everything in every Excel file to PDF file?

@vvasovic,

Thanks for your query.

You may remove the printable area of the worksheets in the workbook using the following code segment:
e.g
Sample code:

[C#.NET]
        Workbook workBook = new Workbook("e:\\test2\\Book2.xlsx");
                    foreach (Worksheet sheet in workBook.Worksheets)
                        {
                            sheet.PageSetup.PrintArea = "";
                        } 
    ........

    [Java]
    Workbook workbook = new Workbook(“test8.xlsx”);
    //Remove the printable area for the sheet
    workbook.getWorksheets().get(0).getPageSetup().setPrintArea("");
    workbook.save(“out1.pdf”);

Hope, this helps a bit.

Yes, it is helpful. This would be the best solution for Java:

Workbook workbook = new Workbook(excelInputStream);

WorksheetCollection worksheets = workbook.getWorksheets();
int numberOfSheets = worksheets.getCount();

for (int i = 0; i <= numberOfSheets - 1; i++)
{
worksheets.get(i).getPageSetup().setPrintArea("");
}

workbook.save(outStream, com.aspose.cells.SaveFormat.PDF);
content = outStream.toByteArray();

No, actually this doesn’t work at all.

That code causes real mess when I extract some documents: for example, a document with 1-page is spitted into 2 pages, although in Office in ‘Print Preview’ is a 1-page after I cleared ‘Print Area’.

Are there any additional changes in the configuration I can make so that every Excel file content is extracted to pdf EXACTLY like in ‘Print Preview’ when I clear ‘Print Area’?

@vvasovic,

Kindly provide your template Excel file(s) and output PDF file(s), we will check and help you to sort out your issue.

Sorry, I cannot share it with anyone :slight_smile: It’s a top secret :slight_smile:

But I know what causes the problem: when I go to the ‘Print Preview’ in Excel and then to ‘Page Setup’, I see top, left, bottom, right margins and other staff, so I read those values and hard-code it in the Java code, just like this:

Workbook workbook = new Workbook(excelInputStream);

WorksheetCollection worksheets = workbook.getWorksheets();

int numberOfSheets = worksheets.getCount();

for (int i = 0; i <= numberOfSheets - 1; i++)

{

worksheets.get(i).getPageSetup().setPrintArea("");

worksheets.get(i).getPageSetup().setTopMargin(0.75);

worksheets.get(i).getPageSetup().setLeftMargin(0.75);

worksheets.get(i).getPageSetup().setBottomMargin(0.75);

worksheets.get(i).getPageSetup().setRightMargin(0.75);

worksheets.get(i).getPageSetup().setFooterMargin(0.50);

worksheets.get(i).getPageSetup().setHeaderMargin(0.50);;

worksheets.get(i).getPageSetup().setCenterHorizontally(true);

worksheets.get(i).getPageSetup().setPaperSize(PaperSizeType.PAPER_LETTER);

}

workbook.save(outStream, com.aspose.cells.SaveFormat.PDF);

content = outStream.toByteArray();

Then everything works just fine. No mess anymore.

But those are hard-coded values: can I somehow read those values from the Excel file?

You may modify the Excel file and replace actual data with dummy data. Also, remove any other information, which is not helpful in reproducing the issue. As requested by Amjad in the previous post, please provide your modified template Excel file(s) and output PDF file(s) for our analysis. Without the sample file, it is not possible to provide assistance in a timely manner.

Ok. Here they are:
two sheet document.pdf (43.1 KB)

https://ufile.io/0vq40upn

Like I said, if I hard-code top, left, botton and right margins and other stuff in the code with values same as in the Print Preview, then everything works just fine.

@vvasovic,

Thanks for the template file and PDF file.

I tested your scenario using Ms Excel 2003, 2007 and 2010, all work the same. I used MS Excel to open your template file and remove the Printable area of the sheet (see the screenshot for your reference). Now take the print preview of the sheet, it gives me two pages, see the screenshots for your reference:
https://i.imgur.com/YRV7UOg.png (first page)
https://i.imgur.com/J3PnTBb.png (second page)

I can see it also renders two pages so does Aspose.Cells (after removing printable area). In short, it is not an issue with Aspose.Cells APIs as Ms Excel itself renders two pages when removing the printable area.