ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

How to convert a AS400 spool file (report) to a formatted excel sheet?

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

  • How to convert a AS400 spool file (report) to a formatted excel sheet?

    Dear all,

    Hope evry1 is doing great...

    We have a requirement, where in the AS400 spool file (report) needs to be converted to a formatted excel sheet and mailed to the respective people.


    Details:
    1. We have designed the layout of the report using an externally described printer file ==> We have developed a RPG program for this
    2. The AS400 spool (report) is generated. ==> Output of RPG program
    3. We need to convert it to a formatted excel sheet ==>
    4. We need to mail this as an attachment to the required people ==> keep the document in IFS and use SNDDST to mail the attachment.

    I have attached a sample of how the excel sheet should come up...


    Regards,
    Manty
    Attached Files

  • #2
    Re: How to convert a AS400 spool file (report) to a formatted excel sheet?

    There are examples to do all of this on this site...Have you tried any of them?

    jamie
    All my answers were extracted from the "Big Dummy's Guide to the As400"
    and I take no responsibility for any of them.

    www.code400.com

    Comment


    • #3
      Re: How to convert a AS400 spool file (report) to a formatted excel sheet?

      Hi Jamie,

      Thanks for the quick response.

      I have seen the examples posted.

      The problem is we can't go for JAVA or VB or EXCEL Micros or Third Party Tools, as given in many of the examples.

      We would require it to be done using AS400 itself, primarily through RPG. Minimal JAVA is fine, but we can't fully depend on JAVA programs to do this.

      Please provide your suggestions/assistance.

      Warm Regards,
      Manty.

      Comment


      • #4
        Re: How to convert a AS400 spool file (report) to a formatted excel sheet?

        You may check if it is possible and sufficient to use CGIDEV2 creating an Excel-XML-Document.

        Here are the steps:
        1. Use CPYSPLF to copy your spoolfile into a physical file.
        2. Read this pyiscal file and split the rows into columns.
        3. Create an excel sheet with the appropriate colums and heades and fill it using CGIDEV2.
        Here is a nice article written by Paul Tuohy
        Serving Up Spreadsheets

        Birgitta

        Comment


        • #5
          Re: How to convert a AS400 spool file (report) to a formatted excel sheet?

          as always Birgitta is right on.....

          I have been building all of my reports with 3 options...
          • Printing regular report
          • Email as PDF
          • Email as CSV


          For printing it easy...just create print file and go...
          the PDF I use the tool from this site to convert to PDF then I have a procedure that
          used the mail API to email.

          The csv file I use SQL to create a temp global file and populate it with inserts at the detail print line in RPG..
          Then i can use CPYTOIMPF to get sql table to csv file on IFS.... I can do multiple CSV files now that I have the
          Zip function down. -- Once again using mail API I can email back to user.
          All my answers were extracted from the "Big Dummy's Guide to the As400"
          and I take no responsibility for any of them.

          www.code400.com

          Comment


          • #6
            Re: How to convert a AS400 spool file (report) to a formatted excel sheet?

            Lots of ways to tackle this one... if you can write PGMs then Birgitta and Jamie have got you covered. There are also lots of commercial packages that can do this stuff for you automagically.

            Lastly if its just querying tables you can do that using perl on a Win32 system and ump the results directly into a spreadsheet and email it.
            Introducing The IBM i and AS/400 Training That 100% Guarantees You Will Learn Key Administration Tasks...

            Comment


            • #7
              Re: How to convert a AS400 spool file (report) to a formatted excel sheet?

              Also check out this article which discusses how to copy spool files to your PC via Operations Navigator:


              and this one for how to import into excel very easily:


              -JA
              Introducing The IBM i and AS/400 Training That 100% Guarantees You Will Learn Key Administration Tasks...

              Comment


              • #8
                Site www.easy400.net provides a modern solution to create Excel spreadsheets and features a command to create Excel sheets from a data base file without any programmimg.
                See page http://www.easy400.net/hssfcgi/html/page1.htm .

                Comment

                Working...
                X