Simply-Data-Anlysis
Blogs

Simplify Data Analysis: IBM i’s Excel Report Generation

By PIO TEAM on May 14, 2023

IBM i (formerly AS/400 or iSeries) can generate Excel reports for businesses to leverage their data and present it in a presentation format that is user-friendly and easy to use.

Do they work? Yes, perfectly… but only if you know how to use them!

In this guide, you will learn:

  • How can you extract data from an IBM i system to include in an Excel report?
  • Why should POI/HSSF and CGIDEV2 be used?
  • Creating XML from RPG.
  • How can the use of the SEQUEL/EXECUTE command be beneficial?
  • Conclusion.

How can you extract data from an IBM i system to include in an Excel report?

Here are some standard methods:

1. Export the data to a CSV file.

One of the simplest ways to extract data from an IBM i system is to export it to a CSV (Comma-Separated Values) file. It is easy to open CSV files in Excel since they are widely used. To export data to a CSV file, you can use the CPYTOIMPF command. This command copies data from a file to a stream file in CSV format. For example:

CPYTOIMPF        FROMFILE(Lib_Name/File_Name)
                 TOSTMF('/Home/csvFileName.csv')
                 RCDDLM(*CRLF)

2. Use SQL to retrieve data.

Another way to extract data from an IBM i system is to use SQL (Structured Query Language) to retrieve data from a database file. SQL is a powerful language that can be used to filter, sort, and aggregate data. To retrieve data with SQL, you can use the SELECT statement. For example:

             SELECT CUST_NAME, TOTAL_SALES
             FROM SALES
             ORDER BY CUST_NAME

Why should POI/HSSF and CGIDEV2 be used?

 POI/HSSF

POI (Poor Obfuscation Implementation) is a popular Java API that supports working with Microsoft Office documents, including Excel spreadsheets. HSSF (Horrible Spreadsheet Format) is a part of POI that deals with Excel spreadsheets in binary format (.xls). Here are some common use cases for POI/HSSF:

1. Reading data from an Excel file

The data can be analyzed, or reports can be generated using the existing data and the sheet can be read individually, in ranges, or.

2. Writing data to an Excel file

 A sheet can be filled in with data in specific cells, in ranges of cells, or its entirety. Using this option, you can create reports or export data from a database.

3. Formatting Excel cells

Several cell properties can be changed, including fonts, alignments, background colors, borders, and more. By doing this, the spreadsheet data can become more visually appealing or easier to read.

4. Creating charts in Excel

 Charts can also be created using this software, such as bar charts, line charts, pie charts, and more. Data can be visualized and made easier to understand by using this technique.

Code For-  How to Generate Excel File in IBM i

CGIDEV2  

IBM i’s CGIDEV2 is a set of open-source utilities that simplify developing web applications. This library provides a high-level API for handling HTTP requests and responses, as well as utilities for generating HTML, CSS, and JavaScript. It can also be used to generate Excel reports on IFS (Integrated File System)

Here are some common use cases for CGIDEV2:

  1. Creating dynamic web applications.
  2. Generating HTML, CSS, and JavaScript.
  3. Processing web forms.
  4. Creating RESTful web services.
  5. Write a CGIDEV2-based CGI program that:
    a. Loads the XML skeleton spreadsheet via sub procedure GetHtmlIfs () or GetHtmlIfsMult ().
    b. Fills in the output variables and writes the appropriate XML sections.
    c. Writes the output buffer to a stream file with extension .XLS

 Creating XML from RPG

The IBM XML-INTO opcode can be used to create XML from an RPG program. A resulting XML can be used to interface with other systems or applications by defining a data structure that represents the data to be converted to XML, and then using the XML-INTO opcode to generate XML data.

A program header must include the XMLSERVICE binder directory, as well as XMLTOOLS_PR and XMLPARM_PR copy members. After the XML data is generated, it can be parsed and manipulated using the XML-INTO opcode. 

This method requires more coding effort. The output will be generated in 2003 Excel format which is old. It also requires some knowledge of XML.

How can the use of the SEQUEL/EXECUTE command be beneficial?

The SEQUEL/EXECUTE command is a powerful tool for generating reports and performing data analysis on IBM i systems. Here are some benefits of using the SEQUEL/EXECUTE command:

1. Simplifies report generation: The SEQUEL/EXECUTE command allows you to quickly and easily generate reports by specifying SQL statements. This can save you time and effort compared to manually creating reports using traditional programming techniques.

2. Flexible output options: The SEQUEL/EXECUTE command allows you to output reports in a variety of formats, including PDF, HTML, Excel, and CSV. This makes it easy to share reports with others or import data into other applications.

3. Customizable formatting: The SEQUEL/EXECUTE command allows you to customize the formatting of your reports, including fonts, colors, and alignment. This can help make your reports more visually appealing and easier to read.

Conclusion 

In conclusion, generating an Excel report from IBM i data is a straightforward process that requires a few simple steps. By following these steps, you can create professional-looking reports that provide valuable insights into your business operations. Excel’s flexibility and versatility make it an ideal tool for data analysis and reporting, and IBM i’s robustness and reliability ensure that your data is always secure and available.

SHARE: