Detect needed print orientation with Apache POI

Question

I'm using Apache POI to create xls spreadsheets. Is there a way to detect if the data fits in portrait mode or if I have to set the sheet to landscape mode? I know how to set the modes, but I don't know how to find out if the data fits the current print orientation.

Answer 1

   HSSFPrintSetup printSetup = sheet.getPrintSetup();
    sheet.getPrintSetup().setFitWidth((short) 1);
    sheet.getPrintSetup().setFitHeight((short) 0);
    sheet.setAutobreaks(true);
    printSetup .setLandscape(true);

   HSSFFooter footer = wygSheet.getFooter();
    footer.setCenter("Page " + HSSFFooter.page() + " of "+ HSSFFooter.numPages());

Answer 2

I've tried but cant see a way to get this working.

When you create the workbook, poi defaults the Fit Height and Width to 1 each.

Fit Height is the number of pages high to fit sheet in

and

Fit Width is the number of pages wide to fit sheet in

Unless you set the sheet print height and width to a higher value like so,

sheet.getPrintSetup().setFitHeight((short)10);

System.out.println (sheet.getPrintSetup().getFitWidth());
System.out.println (sheet.getPrintSetup().getFitHeight());

always return 1 and 1

The problem is Excel will always compress the data (in zoom size) down to 10% to fit the 1 x 1 page layout. [In MS_Excel, this shows up as Print Preview > Page Setup > Scaling > Down to X% of actual size ]

Once the zoom is at 10%, it then overflows the data onto page 2 and so on.

I've tried a sheet with lots of data and even sent a large PrintArea

workBook.setPrintArea(
                    0, //sheet index
                    0, //start column
                    50, //end column
                    0, //start row
                    520  //end row
            );

on a variety of print sizes.

sheet.getPrintSetup().setPaperSize((short)11); // A5 

So the default printable area / orientation are not changed unless you override them, so I dont think the data can be detected to be larger than the printable area - which is what you're trying to obtain.

This might be one for the POI mailing lists.

Updated to include link to this discussion on POI mailing lists as already asked by OP.

http://mail-archives.apache.org/mod_mbox/poi-user/201010.mbox/%[email protected]%3e