• If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Announcement

Collapse
No announcement yet.

Utility / Mechanism To Convert Spool File To XLS

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Utility / Mechanism To Convert Spool File To XLS

    I wanted to double check to see if there are any utilities or mechanisms to take a spool file and convert it to an XLS. We currently use Compleo to do this but I am interested in getting rid of Compleo due to the complications behind the spool to xls process.

    Requirements:

    1. The end format has to be a spreadsheet (not pdf, not Word, not csv, not text, again csv is NOT an option)
    2. Only minimal effort can be spent on modifying the legacy programs to output to XLS (no rewrites to build an SQL for vvOut_execSqlToSS). I can foresee an opportunity to write to a PF record layout in lieu of a PRTF layout so that vvOut_execSqlToSS
    3. Some of our reports have 500,000+ rows
    4. We "can" use Valence 4.1, 4.2, or 5.0.

  • #2
    If you have the option of adjusting the RPG logic so it's outputting each printed row to a physical file rather than to a printer file, then that, followed by a simple vvOut_execSQLtoSS, would in my opinion be your best option. If your physical file has the same field names as your printer file it should be fairly straightforward.

    There may be other commercial products out there for converting spool files to spreadsheets, but I think the most foolproof approach would be to pull the data from a work file rather than trying to strip it out of a spool file.

    Comment


    • #3
      We have compleo also... can't seem to get away from it for "canned" reports from our ERP system.

      In any case, I have used two things for custom reports that I've used to write XLS directly to the IFS (both are open source).
      1. SQL2XLS - been around a while but still works great http://www.mcpressonline.com/tips-te...n-the-fly.html
      2. HSSFCGI - this can be found at http://www.easy400.net/hssfcgi/html/page1.htm

      Method 1 allows you to create XLS templates with header rows, macros, etc.
      Method 2 allows you to write each cell directly from RPG, you can have fill/colors and freeze the top row or two, even include images & formulas.

      I'm very curious about the vvOut_execSQLtoSS option now that we have Valence.

      Comment


      • #4
        gwilburn, if you create a grid inquiry app using Autocode and select the box to include the download option, then you'll see that the generated RPG code will have the vvOut_execSQLtoSS to create a spreadsheet. It works great and I include it in almost every Valence app I create. Users can look at records in the grid and then use the download button to get the records in Excel.

        Comment

        Working...
        X