• Simple programming
• Facilities for CGIDEV2-based programs
• Working with multiple Java frameworks

1-Simple programming

To write a program that generates a spreadsheet you do not have to know Java, nor you have to know ILE-RPG or the CGI techniques supported by CGIDEV2.

The only thing you have to do is to use your favorite programming language to build on the IBM i a program able to generate an IFS stream file containing an XLSGEN script.
Once this is done, use command

• xlsgen to generate the XLS spreadsheet as an IFS stream file, OR
• xlsxgen to generate the XLSX spreadsheet as an IFS stream file

If you were able to do this manually, as a programmer you would love to have it done from a program.

2-Facilities for CGIDEV2-based programs

Of course, if you are an ILE-RPG programmer and expecially if you are trained to use CGIDEV2, then you will start smiling, because the next part of this page is for you.

In HSSFCGI/QRPGLESRC there are three sample CGI programs providing teaching demos for this job:

1. The first sample program, named FAMACC (Family Accounting), reads a family-accounting data basefile to generate a single sheet workbook.
   Though pretty simple, it features HSUM, VSUM and VAVG formulas, plus a true Excel formula.
   • Press this link to have it generating a spreadsheet and sending it to your browser
   • Press this link to display the XLSGEN script generated by this program
   • Press this link to display the source of this program.
2. The second sample program, named GUB (Giovanni's Used Boats), reads a boats-for-sale database file to come up with a two sheets workbook.
   The fun of it is that it features cells including pictures.
   • Press this link to have it generating a spreadsheet and sending it to your browser
   • Press this link to display the printout from this spreadsheet
   • Press this link to display the XLSGEN script generated by this program
   • Press this link to display the source of this program.
3. The third sample program, named XVSUMYTRY (XVSUM example), reads a sales summary database file and shows how to use XLGEN formulas VSUM and XVSUM to create subtotals and grand totals.
   • Press this link to have it generating a spreadsheet and sending it to your browser
   • Press this link to display the XLSGEN script generated by this program
   • Press this link to display the source of this program.
4. The fourth sample program, named GOCATALOG (Products catalog), reads a product catalog database file to come up with a product catalog worksheet.
   In this program, each picture height is individually computed.
   • Press this link to have it generating a spreadsheet and sending it to your browser
   • Press this link to display the XLSGEN script generated by this program
   • Press this link to display the source of this program.

Write your program following one or the other of the example programs:

1. Use subprocedure GetHtmlIfs or GetHtmlIfsMult to load the external script /hssfcgi/html/XLSGEN_template.txt. Always use this script as a skeleton for your XLSGEN script.
2. Use subprocedures UpdHtmlvar and WrtSection to create the XLSGEN script in the output buffer.
3. Use subprocedure WrtHtmlToStmf to create the XLSGEN-script IFS stream file.
4. Use subprocedure ClrHtmlBuffer to clear the output buffer.
5. Run command hssfcgi/xlsgen to interpret the XLSGEN-script and to generate the XLS or XLSX spreadsheet stream file.
6. If running under HTTP, use subprocedure SndStmfToBrowser to send the XLS or XLSX spreadsheet stream file to the browser. Subprocedure SndStmfToBrowser is from service program hssfcgi/hssfcgix.

Command WEBXLSGEN
In writing you CGI program you may also take advantage of command HSSFCGI/WEBXLSGEN. This command is very similar to commands XLSGEN and XLSXGEN, but provides further functions:

1. Creates the XLS or XLSX stream file from the XML-like XLSGEN stream file (as commmands XLSGEN or XLSXGEN would do). Then, if running in a HTTP instance, ...
2. Sends the XLS or XLSX stream file to the browser
3. Submits a job to delete the XLS/XLSX stream file after 5 minutes.
   (Note that it is a program responsibility to decide to delete the XML-like XLSGEN stream file.)

Subprocedure GetColName()
In your program creating the XLSGEN XML-like script, you may have the need specify in a formula some cell names in the way it is done by Excel. As an example, cell name AK82 would be the cell in column number 37 and row 82.
Usually your program has full control on the column and row numbers. So, at a given point of your program you know that you are on row 82 and column 37. However, how do you convert 37 to the "AK" Excel notation?
You do that with subprocedure GetColName(), example:
colName=GetColName(37) .
See the sample program in HSSFCGI/QRPGLESRC member GETCOLNAME.

3-Working with multiple Java frameworks

Java environment variables CLASSPATH, QIBM_RPG_JAVA_PROPERTIES and JAVA_HOME must be set in a job before the Java Virtual Machine (JVM) is started. Trying to replace them after JVM has started, is no use.

This may cause problems when your program tries two use more than one Java framework.

As an example, let us assume that your program

• sets up the Java environment to use Scott Klement's JDBC framework to create a table
• creates the table
• tries to run command HSSFCGI/TABLEXLS to generate a spreadsheet from that table.

There are some ways to solve this problem:

1. Setup a complete Java environment before starting the JVM.
   This approach requires that the Java environment variables are setup for all the Java frameworks used by the program before using the first framework. This could be not so easy to be coded, as the environment variables needed by the HSSFCGI commands are hidden in a service program. However, your program may use subprocedure Get_HSSFCGI_Java() to retrieve these environment variables. Source member SAMPLEJPGM in HSSFCGI/QRPGLESRC provides the example of how to perform Java environment initialisation when using two frameworks, JDBC and HSSFCGI.
   Note- It should however be noted that once your program has started, the Java setup for the job can no longer be changed and that may prevent using other Java-based programs in the same job.
2. Run command HSSFCGI/JVMSTARTUP as soon as a job is started or before executing any application using the JVM.
   This command sets up the JVM environment for HSSFCGI and XLPARSE2 and optionally extends the classpath through a string provided in a parameter of this command.
   This makes sure that HSSFCGI and XLPARSE2 java code can run, whatever other mix of java programs is going to be executed.
3. Use SBMJOB(*YES) on HSSFCGI commands XLSGEN, XLSXGEN or TABLEXLS.
   If you specify parameter SBMJOB(*YES) on HSSFCGI commands XLSGEN, XLSXGEN or TABLEXLS, the Java part of the related program is executed in a submitted batch job. This is done in a completely seamless way. By doing this, you cannot have conflicts between HSSFCGI Java setup and the Java setup of your job.
   Note- That does not completely solve the problem of having a JVM been started in your job, unless you provide a way to run also the other Java frameworks in submitted jobs synchronized with your main job.