Add Excel output to your own 4GL reports

If you are a Pronto site that has a licence to use the Pronto 4GL, you can now easily add the ability to create Excel reports to your programs. I have extracted the Excel routines from my 'GHOSTWRITER' 4GL report writer software and packaged them up into a convenient include file and a component library file.

The include file provides a suite of simple macros that can be used to perform all the processing necessary to easily create Excel output files. This includes macros for opening and naming output files, creating worksheets, setting column headings and formats, inserting data into cells, closing the Excel file and emailing the file.

By providing individual macros for the various Excel functions, as a programmer you have full control over the layout and contents of the Excel spreadsheet. For example, you can control the sheet titles and column headings, create multiple worksheets within the file, and add additional processing information if you wish.

Just like the Excel files created using my new 'SQL/Output' software, the Excel output files that are created using the macros in the include file are in ‘XML Spreadsheet 2003’ format. This is a native Excel format that is supported by Excel 2003 or later versions.

The macros provide you with the ability to directly create the Excel output files from programs that are run in batch mode. If a program is being run interactively, and the Pronto output file directory is mapped to a PC directory, the user will be asked whether they want to open the file immediately using Excel.

My website has more information about the Excel include file and component library, including a summary of the provided macros and a document showing how to convert a normal 4GL report to create an Excel output file.

Create an Excel file from SQL in batch mode

With my new program called 'SQL/Output' you can automatically turn the data retrieved using a Pronto SQL report into an Excel spreadsheet file. It can produce the Excel file even if you run the SQL as part of your overnight batch processing. No extra user action is required to create the Excel file from the SQL data (apart from setting up the batch queue). SQL/Output can also be configured so that the Excel file is automatically emailed to one or more recipients.

A specification is setup once within SQL/Output for each SQL report that defines the column headings, column types and cell formats to be used, together with details of totals to be placed at the end of the spreadsheet. The output directory and filename structure is also specified, together with optional email destinations.

To automatically create the Excel output, an entry to run the SQL/Output program is added to a Pronto batch sequence immediately after the entry to run the SQL report itself. If an SQL report is run manually by a user, SQL/Output can be manually run after the SQL has finished. Note that a site does not require a 4GL licence to use the SQL/Output program.

The Excel file created by SQL/Output is in ‘XML Spreadsheet 2003’ format. This is a native Excel format that is supported by Excel 2003 or later versions.

I've called this software 'SQL/Output' because the first general software for Pronto that I released back in 1994 was called 'SQL/Input'. That software allows entry screens to be created for SQL queries with smart defaults for entry parameters, including date defaults and help screen lookups for system table fields. SQL/Input has been available at no charge from my website for many years.

There is more information about both SQL/Input and SQL/Output on the website.

Welcome

This blog will contain information about software created by me to help you work more efficiently with your Pronto system, as well as some tips and tricks about Pronto itself.

I have recently written some exciting new software tools, so I will be writing about those initially. However, I have written a lot of software over the last 18 years so I will also be writing about some of my earlier software that still provides additional functionality for working with Pronto.

Just to make things clear from the start, this blog is about software written by Stephen Wilbourne of Lantana Systems Pty Ltd. My software works as an add-on to the PRONTO Enterprise Management System produced by Pronto Software and is written using their PRONTO 4GL programming language. Any comments made here are entirely mine and in no way represent the views of Pronto Software. I regard Pronto Software very highly, and enjoy programming using their 4GL. My software is generally designed to do something that cannot be done directly in the standard Pronto software, and to help companies maximize their investment in a Pronto system.

I hope you find this blog a convenient way to learn about my software. I welcome your comments and feedback.