Formatting data in Excel

All of the tools I have created to send data directly to Excel from Pronto share the same library of routines. Whether you create a 4GL report fully using GHOSTWRITER, use my Excel include files with your own 4GL program, or use SQL/Output to transform the data from a Pronto SQL query, in each case you are able to exercise a great deal of control over how the data is shown in an Excel cell.


For example, for a numeric field here is how the values -1234.56, zero and 1234.56 would be shown using several different format specifications:

Format	-1234.56	zero	1234.56
----------	----------	----------	----------
-(6)9.99	-1234.56	0.00	1234.56
-(6)zvzz	-1234.56		1234.56
-(6)9	-1235	0	1235
-(6)z	-1235		1235
-$(5)9.99	-$1234.56	$0.00	$1234.56
---,--9.99	-1,234.56	0.00	1,234.56
-$$$,$$9.99	-$1,234.56	$0.00	$1,234.56

Here are some different ways to display a date field:

Format	Date
----------	----------
dd-mm-yy	30-11-09
dd mmm yyyy	30 Nov 2009
ddd, dd mmm yyyy	Mon, 30 Nov 2009

As you can see, as a user you are fully in control of the Excel cell formatting with our Excel tools.

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.