Guide: Creating Excel files on your IBM i

Why create Excel files on your AS400 (IBM i) server?

Why create an Excel file? Because that’s the format in which your users and customers want their data, especially if the data involves a lot of numbers and they would like to do further calculations on it.

Why create the files on your IBM i server? Because that’s usually where the data is and because creating them there makes everybody’s life easier.

In the past, creating an Excel spreadsheet from IBM i data normally meant having to run a file transfer on a PC. Sometimes that was fine, but often there were problems:

• Someone normally had to press the buttons to get the transfer to run. That meant relying on a user to do it during the day, when the system and network were busy, or paying an operator to work nights.
• What if the user ran things at the wrong time? If the data wasn’t ready yet, for some reason, the transfer could produce the wrong results.
• While it was possible to automate the running of the transfer by triggering it to start from the IBM i, this was tricky and error-prone and increasingly difficult as Windows security became tighter. And if the PC were down, being backed up, or not connected to the network, things would fail.
• And what if the data you wanted to convert to Excel was in a spooled file rather than a database file? You can’t do that with a file transfer!
• All in all, wouldn’t it be better to create the Excel files from your data (spooled files or database files, directly on the IBM i itself, as an integrated part of your IBM i batch jobs?

CoolSpools lets you create Excel spreadsheets from your IBM i data right on the server itself.

No PC is required to create the files so that you can integrate their creation with your overnight batch jobs and there’s no need to rely on your users to run file transfers at the right time.

Of course, once you’ve created the files on the IBM i, your users can access them from a PC. That’s easy, and there are several choices for how to do it (more…).

iSeries Excel Data Transfer

With CoolSpools, the process of creating Excel files from your IBM i data can be made:

• simple – just run a command
• reliable – everything happens on the IBM i itself managed by the IBM i
• convenient – run things overnight ready for the next morning

There are two types of IBM i data you can choose to convert to Excel format:

Spooled files (printer output)

• Stop sending users their stock report on paper in the internal mail: email it to them as a spreadsheet instead!
• You can even split your report up automatically into separate spreadsheets, maybe one per department or region, give each file a meaningful name and distribute or email it to the relevant people automatically.
• For this, you need CoolSpools Spool Converter.
  Why not download it now and check out the CVTSPLXLS or CVTSPLXL (Convert Spooled File to Excel) commands?
• Also produces: XML, PDF, CSV, HTML and text output.

Database files (physical, logical and DDM files)

• Extract data directly from your IBM i database files and save it as a spreadsheet!
• You can take the data directly from your physical or logical files, specifying field and record selection or sequencing.
• Alternatively you can save the output from a Query, QM Query or SQL as an Excel spreadsheet!
• For this, you need CoolSpools DatabaseWhy not download it now and check out the CVTDBFXL (Convert Database File to Excel) command?
• Also produces: XML, CSV, HTML and text output.
• Also retrieves Excel spreadsheet cell contents to a database file member with the CVTDBFXL (Convert Excel to Database) command.

The Excel files that are produced are fully functional, native Excel-format files and compatible with Excel 97 and later.

You have a choice of Excel 97-2003 format (.xls) or Excel 2007 (.xlsx) format.

Both of these product options let you enhance the appearance of your Excel output using styles to apply color, fonts, backgrounds, patterns, borders etc.

You can even apply conditional formatting to do things like coloring high values red and low values green!