19

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);

}
Paolo Forgia
  • 6,572
  • 8
  • 46
  • 58
superbAfterSemperPhi
  • 1,292
  • 1
  • 14
  • 27

2 Answers2

33

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.

rgettman
  • 176,041
  • 30
  • 275
  • 357
  • 2
    This worked for me but I also had to add `sheet.setAutobreaks(true);` for the sheet to switch to the "Fit to" Scaling option. – Chad Cook May 03 '17 at 20:43
  • For me `ps.setFitWidth( (short) 1);` `ps.setFitHeight( (short) 1);` what was worked. – SiGe Feb 01 '18 at 13:02
0
public static void ExportPartsToExcel(this System.Data.DataTable DataTable, string ExcelFilePath = null)
    {
        int ColumnsCount;
        int RowShift = 7;

        ColumnsCount = DataTable.Columns.Count;

        // load excel, and create a new workbook
        Application Excel = new Application();
        Excel.Workbooks.Add();

        // single worksheet
        _Worksheet Worksheet = Excel.ActiveSheet;
        Excel.Sheets[1].Name = "TEST";

        Excel.PrintCommunication = false; //This has to be set, or FittoPagesWide crashes
        Worksheet.PageSetup.Zoom = false;
        Worksheet.PageSetup.FitToPagesWide = true;
        Worksheet.PageSetup.FitToPagesTall = false;
        Worksheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
        Worksheet.PageSetup.TopMargin = 0.5;
        Worksheet.PageSetup.BottomMargin = 0.5;
        Worksheet.PageSetup.RightMargin = 0.5;
        Worksheet.PageSetup.LeftMargin = 0.5;
        Excel.PrintCommunication = true;

        // check fielpath
        if (ExcelFilePath != null && ExcelFilePath != "")
        {
            Worksheet.SaveAs(ExcelFilePath);
            Excel.Quit();
            Marshal.FinalReleaseComObject(Worksheet);
            Marshal.FinalReleaseComObject(TitleRange);
            Marshal.FinalReleaseComObject(HeaderRange);
            Marshal.FinalReleaseComObject(CellRange);
            Marshal.FinalReleaseComObject(Excel);
        }
        else
        // no filepath is given
        {
            Excel.Visible = true;
        }

This works for me, basically I've set both Worksheet.PageSetup.FitToPagesWide = true; and Worksheet.PageSetup.FitToPagesTall = false;

Adephx
  • 187
  • 10