How can it set the file for "Fit all columns on one page" setting

Question

I have a requirement where files generated by Apache POI need to produce a fie with the fit all columns on one page setting set. I've tried a bunch of variations with the API but so far I haven't been able to do it. Nor can I really find if it can be done.

It seems like the setFitToPage(true) function resizes both the height and width not just the width like I want. Using setFitWidth and setFitHeight like I find in various other stack overflow questions doesn't seem to affect anything.

Here is what I have so far:

public void setPrintSettings(Sheet sheet) {

   sheet.setFitToPage(true); //this will resize both height and width to fit
   sheet.getPrintSetup().setLandscape(true);

   sheet.getPrintSetup().setFitWidth((short) 1);
   sheet.getPrintSetup().setFitHeight((short) 1);

}

Answer 1

It's not the call to setFitToPage(true) that makes Excel resize both the height and width to fit one page. This call is necessary, but for a different reason. In Excel's Page Setup screen, this method call controls which radio button is active in the dialog box. The value true sets the radio button for "Fit to:", allowing you to control the page(s) wide by page(s) tall boxes. The value false sets the radio button for "Adjust to: ", percentage normal size.

In this case, you want to fit it to 1 page wide by "don't care" pages tall. The value to use for pages tall (setFitHeight method) is 0 here.

sheet.setFitToPage(true);
PrintSetup ps = sheet.getPrintSetup();
ps.setFitWidth( (short) 1);
ps.setFitHeight( (short) 0);

When I write out a Workbook containing a Sheet with these settings, and I open it in Excel, the Page Setup dialog box has the "Fit to:" radio button selected, and "1 page(s) wide by (blank) tall". In the "Print Preview" screen, the print settings options lists "Fit All Columns on One Page" as selected.