Scale to Fit spreadsheet to one Page

[rkwolf0211]

Trying to find the right combination to scale a spreadsheet to one page.

I have inherited this code that was referencing the following BNDDIR and /copy statements.

H BNDDIR('HSSF')
* Load JAVA Routines
/copy hssf_h

I am trying the following statements, with program dump results. So doing something wrong.

SSPrintSetup_setFitHeight( sheet:1);
SSPrintSetup_setFitWidth( sheet:1);

Any and all help would be appreciated.
Keith

[Scott Klement]

These routines work on the PrintSetup class. You are passing an object called 'sheet', which I'm guessing is a Sheet class rather than a PrintSetup class...

[rkwolf0211]

Use this instead? I am pretty lost on this...
*...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+...
1615+D .HSSFClientAnchor'
1616+D HSSF_PRINTSETUP_CLASS...
1617+D C 'org.apache.poi.hssf.usermodel-
1618+D .HSSFPrintSetup'
1619+
1620+D HSSFWorkbook S O CLASS(*JAVA: HSSF_WORKBOOK_CLASS)
1621+D HSSFSheet S O CLASS(*JAVA: HSSF_SHEET_CLASS)

[Scott Klement]

I apologize, it has been FOREVER (more than 10 years) since I've worked with this stuff. In my prior job, I ended up building our own spreadsheet creation code because POI was too slow. Then, I changed jobs, and have not used it since... so it's been a REALLY long time.

From another example I found online, I cobbled something together thatt seems to work. I apologize that the H/D specs are still in fixed-format, but I'm super, super busy right now and did not have time to convert the old code to free format. Give this a try:

Code: Select all

     H DFTACTGRP(*NO) BNDDIR('HSSF')

      /copy hssf_h

     D SSSheet_setFitToPage...
     D                 PR                  ExtProc(*JAVA
     D                                     : SHEET_CLASS
     D                                     : 'setFitToPage')
     D   fitToPage                    1N   value

     D book            s                   like(SSWorkbook)
     D text            s                   like(SSCellStyle)
     D sheet           s                   like(SSSheet)
     D row             s                   like(SSRow)
     D ps              s                   like(SSPrintSetup)

      /free
           ss_begin_object_group(100);

          // Create a new workbook

           book = new_XSSFWorkbook();

           Text = SSWorkbook_createCellStyle(book);

          // Create a sheet named 'Test' containing 1 cell
          //  with some test data in it.

           sheet = ss_newSheet(book: 'Test');
           row = SSSheet_createRow(sheet: 1);
           ss_text( row: 1: 'Testing Page Fit': Text);

          //  Set up autofit

           SSSheet_setFitToPage(sheet: *ON);
           ps = SSSheet_getPrintSetup(sheet);
           SSPrintSetup_setFitWidth(ps: 1);
           SSPrintSetup_setFitHeight(ps: 1);

          //  Write it to disk

           SS_save(book: '/tmp/scale_fit.xlsx');
           ss_end_object_group();

           *inlr = *on;
      /end-free 

[Scott Klement]

Adding to my last post.. I did not actually print it, but when I go to the 'Print' screen within Excel, I see this:

printSettings.png (14.42 KiB) Viewed 72946 times

And if I click "Page Setup" from there, I see this:

pageSetup.png (16.46 KiB) Viewed 72946 times

So, it looks to me like the program set these correctly.

[rkwolf0211]

Thanks sir!! Will give it a try today.

[rkwolf0211]

Okay, I copied your code. Got your copy compiled and ran with huge success.

Now trying to merge into my existing code.

getting RNF5406 - The call passed fewer parameters than the prototype indicates
are required.

I understand you are very busy, I am working through and researching what the difference would be.

Keith

[Scott Klement]

That's a normal RPG compile error -- has nothing to do with HSSFR4. It just means you aren't passing all of the parameters required to a given prototype.

Look at the compile listing and see where the error is occuring. Examine that line, and determine which parameter you are missing. Fix the bug.

[rkwolf0211]

Well, been trying to get this to work. Moved code to be before the SaveSheet(book);
Debugged, see the code being executed.

I am saving the spreadsheet as a XLS, thought that may be the issue. so change to XLSX, to match the provided code.

Just not having any luck.

[rkwolf0211]

moved the fittopage
to the SS_save in my code, still not working:

1994.00 // >> Set up autofit
1995.00
1996.00 SSSheet_setFitToPage(sheet: *ON);
1997.00 ps = SSSheet_getPrintSetup(sheet);
1998.00 SSPrintSetup_setFitWidth(ps: 1);
1999.00 SSPrintSetup_setFitHeight(ps: 1);
2000.00 // >>
2001.00 ss_save(books:IFSDIRS);
2002.00 /end-free
2003.00 P E