Excel Reports form IBMi
Blogs

How to generate an Excel report from IBM i

By PIO TEAM on May 14, 2023

Reporting is required in any application whether it’s a legacy application or any other application. One of the reporting documents is generated in the form of Excel reports. Customers/Clients/Users like to view the reports in Excel format, as they can easily do filtration, and summation of data if needed in Excel. So here in this post, we will be discussing the ways to generate the Excel report from the IBM i system.

CPYTOIMPF:

This is an inbuilt command in the IBM i system that can generate a CSV file at the IFS location. This IFS file can be opened in Excel. But if you want to make headers as bold or add some colour, this command does not give facility for any kind of formatting on the data.

Below is an example of CPYTOIMPF command:
CPYTOIMPF  FROMFILE(Lib_Name/File_Name) 
TOSTMF('/Home/csvFileName.csv') 
RCDDLM(*CRLF)

POI/HSSF:

POI is a set of JAVA routines to work with Microsoft office documents. It is an open-source software. For this HSSF libraries and JAR files should be downloaded and installed on your IBM i machine. In HSSF libraries you will get sample codes to generate excel reports that can be used as a reference. You don’t need to learn JAVA to use the HSSF codes. The creator (Scott Klement) already wrapped the Java classes in RPGLE procedures that can be easily used and understandable by RPGers.These HSSF libraries are provided by Scott Klement and you can get more details about these libraries on his website.

By using POI/HSSF formatting can be done easily. We can do filtering, text colour, cell formatting, create formulas, Merge cell, Freeze Panes, Add new sheets and many other formatting.

Below is an example taken from the HSSF website.

XLCRTDEMO.RPGLE

      * Demonstration of using POI to create a complex Excel workbook
      *
      * To compile:
      * Make sure you've already created HSSFR4. See the instructions
      * on that source member for details.
      *
      *>  ign: CRTPF DIVSALES SRCFILE(QDDSSRC)

It can generate complex excel spreadsheet which have a lot of formatting’s.

The only problem that we face is performance as it is slow. So, it is preferred only for small reports. For big reports it takes a lot of time.

CGIDEV2:

CGIDEV2 is a free tool and mostly used for interacting the RPG programs with the web pages, but it can be used for generating the excel report on IFS.

  1. Use Excel to create a skeleton spreadsheet
  2. Save it as an .xml file
  3. Change the XML skeleton spreadsheet by inserting CGIDEV2 section names and output variables
  4. Copy this XML skeleton to an IFS directory
  5. Write a CGIDEV2-based CGI program that
  • loads the XML skeleton spreadsheet via subprocedure GetHtmlIfs() or GetHtmlIfsMult()
  • fills in the output variables and writes the appropriate XML sections
  • writes the output buffer to a stream file with extension .XLS

and you are done.

Create XML from RPG:

Excel report can be created by creating the XML file tags from program itself. This will create the XML file and when this is opened it will open in excel.

Create excel and generate the XML skeleton.

Now in RPG program create a file on IFS and write all the tags similar to the XML skeleton. For the actual data we can get the data from files and write to the XML file.

In this method coding efforts are more. It will generate excel file in 2003 excel format which is old. Some knowledge of XML is also required.

SEQUEL/EXECUTE:

Sequel/Execute is a third party tool. It is used to create the excel report on IFS directory from the database file directly. Below is the example to generate Excel report from this command. Before running the command the tool needs to be installed on the IBM i system.

SEQUEL/EXECUTE 
SQL('Select EmpId   ColHdg( "Emp ID" ),EmpNam   ColHdg("Name" ), 
EmpAge   ColHdg( "Age"),EmpExp   ColHdg( "Experience") From EmpFile') 
PCFMT(*XLS) 
TOSTMF('/Home/FileName.xls') 
REPLACE(*YES)

This command also is not able to provide functionalities to format the data. It can generate simple excel spreadsheets.

Conclusion:

CSV files fulfil the most of the users requirement. And if some formatting is required then they can opt for other methods like HSSF which is slow but effective and can generate a lot of excel formatting’s. HSSF CGIDEV2 are free tools that can be used. But if someone don’t want to install these he can then use third party tools like Sequel/Execute or can use to generate XML by doing programming.

SHARE: