Ask Reuben
Excel
How can I create an Excel document from a Genero application?
How can I create an Excel document that contains a formula?
How can I read a value in an Excel document into a Genero application?
How do I delimit commas, quotes etc when creating a .csv file?
How do I use the WinDDE, or WinCom front-call with the Genero Browser Client?
How can I calculate IRR, NPV (or some other financial calculation) that I can do in Excel?
Genero is intended for business applications. Microsoft Excel is an application that has wide adoption in business, and it is inevitable that there will be times a Genero application needs to interact with an Excel spreadsheet. There are a number of questions we get asked that suggest that the developer is not using the appropriate tool or methods in order to interact with Excel. In the course of this article I will refer to Microsoft Excel. The concepts can equally apply to other products from the Microsoft Office family such as Word, and can also apply to other applications e.g. OpenOffice in that space. Below are a number of different ways you can interact with Excel and some comments on their strengths and weaknesses. My preference is the ApachePOI libraries but first some of the historical methods that might have been used. Through the concept of file associations, most developers are aware that if you double-click on a file with extension .xls or .xlsx, Excel will be opened. Excel is also normally associated with the .csv extension and so if you can create a .csv file Excel will attempt to open that and seperate the contents of the file into rows and columns. If an end user wants raw data, they are normally capable of opening a .csv file and using that as the starting point for their work in Excel. You can use the UNLOAD statement in Genero to generate this .csv file from an SQL statement for the end-user. One thing you should be wary of is the value in the DELIMITER clause. The temptation is to use “,” when you may not be aware that the value of “CSV” is also accepted as a value for DELIMITER. As per the documentation … The CSV (comma separated values) format is similar to the standard format when using a simple comma delimiter, with the following differences: When using UNLOAD you also need to be wary of where the file is generated. The file is generated on the same server the fglrun process is running i.e the back-end, and in order for the user to open it in Excel it will typically need to be moved to somewhere that is visible from the front-end. This may involve use of FGL_PUTFILE to transfer the file from the back-end context to the front-end context, and may also involve the use of the shellExec front-call to open the file for the user to start their editing. Be aware of the differences in using these with GDC and GBC solutions. A weakness of the UNLOAD technique is that it is reliant on an SQL statement to generate the data that is output. If you want to do any form of processing on the data that is not possible in a SQL statement then an option is to generate the .csv file via 4gl code. There are many ways to generate a file via 4gl code and these can all be used to generate a file with extension .csv. These methods include START REPORT … TO FILE “filename.csv”, base.Channel, and various writeFile methods. The thing to be aware of is that you are then responsible for ensuring the data is in the correct format for .csv. In majority of cases that will mean ensuring the data is in rows and columns correctly with commas and new lines in the appropriate place, but you should also study the notes for DELIMITER=”CSV” in the previous section and ensure that you have applied the same rules if output has commas, quotes, backslashes, newlines etc. You should also study the File Import Wizard that appears in Excel when you open a .csv file and note the options that are available to you. This includes alternate delimiters, you are not forced to use comma but can use TAB, pipe (|) etc. Also note the first screen in the wizard asks if delimited or fixed width. From a 4gl perspective, in a REPORT statement it maybe easier to code a fixed width file to import … … as opposed to … I also find using SFMT handy in a REPORT statement that is creating a csv to reduce the forest of quotes and commas e.g. DDE (Dynamic Data Exchange) is an early form of inter-process communication used in early versions of Windows. The WinDDE family of front-calls allowed the GDC to communicate with Excel and other Windows products. Through front-calls, your Genero application could open a spreadsheet and read and write to cells. As per this Microsoft Developer blog written in 2007,… DDE has been dead as a shell interface for over ten years. So whilst you may find the DDE front-calls may still work, I would discourage their use as per the note in our documentation This feature is deprecated, its use is discouraged although not prohibited. The WinCom family of front-calls were seen as the successor to the WinDDE family of front-calls. They used the Component Object Model exposed by Microsoft. From an Excel perspective, you had more control as the key methods set/get property allow you to change any property rather than just a cell value. In our documentation we have the same Important note This feature is deprecated, its use is discouraged although not prohibited. Whilst WinCom is not as dated as WinDDE there are some limitations you need to be wary of. There is no browser equivalent, so if your plan is to move away from Genero Desktop Client to a Genero Browser Client solution, you will not be able to use these front-calls. Each setProperty, getProperty call is a front-call with some communication between the fglrun and gdc process. To create a spreadsheet of 10 columns and 1000 rows is 10,000 setProperty front-calls. One possible technique to alleviate the number of front-calls is to use clipboard and paste as discussed in this 2008 forum post. Genero Report Writer has the ability to create Excel documents, and to create Rich text i.e Word documents. From the developer perspective this is achieved by passing XLS, XLSX, RTF, OORTF to fgl_report_selectDevice instead of PDF. When using this technique, expectations need to be managed. I need to pick my words carefully lest they get misinterpreted but what this does is generate a spreadsheet, document etc that looks the same as if it was generated as a PDF. This does not necessarily equate to producing a spreadsheet, document suitable for future editing in Excel, Word. As examples, a total from a report will not generate a cell that says “=SUM(A1..A100)”, instead the total will be a value e.g. “123.45”. You cannot edit that spreadsheet, insert a row, and expect the total to be recalculated. Similar there was a support case where GRW had been used to create a Word document that was intended to be the beginning of a letter, the document was produced with appropriate letterhead, name, date, salutation etc and the end-user would then type the body of the letter. However when the user typed in the body of the letter, the bottom of the document did not move as they typed more. Simply put you do not have the ability to finely control each cell in the resulting spreadsheet or object in the resulting document. Apache POI is the Java library for interfacing with Microsoft Documents. By using IMPORT JAVA to import these libraries, you have the same ability as a Java developer to read and write to Excel spreadsheets and Word documents. These API’s also have the desired benefit that the operation is occurring in the back-end context, and can occur on non Windows operating systems, and so can be used without worrying about GDC v GBC, or Windows v Linux, or front-call performance across the network. Using these libraries you have the ability to operate at the cell-level as if you were using Excel or Word etc itself. So that the junior 4gl developer does not have to worry too much about IMPORT JAVA and the Java methods etc, i suggest writing a 4gl layer to shield them. This is what I have done with the fgl_apache_poi repository. The trickiest bit with the repository is in the initial configuration and setup. The required value for CLASSPATH changes over time. I have put a little Java program in the repository “ApachePOIExcelWrite.java” and you may find getting that to work helps you to find the incorrect CLASSPATH values. This repository contains a number of sample programs and it is a good idea to work your way through them. I would suggest working through them in the order in the README. This creates a new spreadsheet. Important things to note are that it is possible to specify a formula in a cell, look at line 80 … Also note line 84 that inserts the current date and time into the spreadsheet … I point this out in demonstrations like a magician would to show that it is real!
UNLOAD TO “filename.csv” …
" double-quote character generate quoted output value and the quote in the value is doubled.\ characters are written as is in the output value (i.e. not escaped).
#! csvwrite.4gl
MAIN
CONNECT TO ":memory:+driver='dbmsqt'"
CREATE TABLE foo(c CHAR(10), i INTEGER, d DATE)
INSERT INTO foo VALUES ("foo",1,TODAY)
INSERT INTO foo VALUES ("foo,bar",2, TODAY)
UNLOAD TO "csvwrite_comma.csv" DELIMITER "," SELECT * FROM foo
UNLOAD TO "csvwrite_csv.csv" DELIMITER "CSV" SELECT * FROM foo
END MAIN
foo,1,10/01/2021,
foo\,bar,2,10/01/2021,
foo,1,10/01/2021
"foo,bar",2,10/01/2021
START REPORT report-name TO FILE “filename.csv”
base.Channel.openFile(“filename.csv”)
*.writeFile(“filename.csv”)
PRINT COLUMN 1, rec.field1, COLUMN 11, rec.field2, COLUMN 15, rec.field3
PRINT rec.field1, ",", rec.field2, ",", rec.field3
PRINT SFMT("%1,%2,%3", rec.field1, rec.field2, rec.field3)
WinDDE Front-calls
WinCom Front-calls
Genero Report Writer
Apache POI
fgl_excel_test
CALL fgl_excel.cell_formula_set(cell,"SUM(B2:B11)")
CALL fgl_excel.cell_value_set(cell,SFMT("This document created on %1 at %2", TODAY, CURRENT HOUR TO SECOND))
fgl_word_test
This creates a Word Document. The key thing to understand is that a document is made up a hierarchy of document, paragraph, run. A run being a subset of a paragraph that has the same formatting. For each character, if the formatting changes, then this indicates a new run.
A paragraph such as …
This sentence is in bold and italics.
… is composed of 5 runs.
- This sentence is in
- bold
- and
- italics
- .
Like the Excel example, I have a line that inserts current date and time to prove that it is real
CALL run.text_set(SFMT("This document created on %1 at %2", TODAY, CURRENT HOUR TO SECOND))
There is also a coding difference between fgl_word.4gl and fgl_excel.4gl in that I have modified fgl_word.4gl to use methods after they were introduced to the Genero syntax. Interestingly what I then found is that the code in fgl_word.4gl was parroting the Java library but with a different coding convention e.g. from fgl_word.4gl …
FUNCTION (this runType) text_set(t STRING) RETURNS ()
CALL this.j_run.setText(t)
END FUNCTION
fgl_excel_calculation
This illustrates an interesting usage of the Apache POI libraries to create an Excel document in memory but never save it. Whilst it is in memory, a cell function is evaluated and the value in the cell returned to the 4gl program. This opens up the possibility of using any Excel function that does not have an equivalent in Genero, in particular the financial ones such as IRR.
fgl_excel_import
This shows how your 4gl program can open an existing spreadsheet and read the cell value into a 4gl variable. The library is not just for writing but also reading.
fgl_excel_generic_test
This program illustrates how you write a function where you simply pass an SQL statement, and a filename, and the result of the SQL statement that can be dumped in a spreadsheet. The difference between this and the earlier UNLOAD is that a .xlsx is being created, not a .csv.
IF sql_to_excel("SELECT * FROM foo", "foo.xlsx", TRUE)
fgl_financial_test
This was an interesting extension to fgl_excel_calculation which bypassed the creation of the spreadsheet in memory and called the financial functions in the Java library directly. Thus a junior developer could code a 4gl function like …
DISPLAY fgl_financial.present_value(.0375/12,240,-592.89,0, FALSE)
fgl_excel_fit_to_page
Again an interesting extension resulting from a support question of how to automate the fitting of an Excel document to fit to on page. Using methods available in Apache POI it is possible to automate that process and make it a simple 4gl call
CALL fgl_excel.workbook_fit_to_page("old_filename.xlsx","new_filename.xlsx")
Web Service API
Finally I’ll also mention that with Google Docs / Google Sheets etc, there are API’s that will allow you to read/write to a Google Docs / Google Sheets. You can see the API documentation via links such as https://developers.google.com/sheets/api and https://developers.google.com/docs/api and you can make calls to them via standard Genero Web Services code.
The advanced bit is the authentication process and I’ll look to cover that in a future Ask Reuben.
Summary
My preference is to utilise the ApachePOI library. Most supports call that involve one of the other methods, my thought is you would not have this trouble if you were using the ApachePOI library. If you can’t do something using the ApachePOI library then chances are neither can a Java developer.
One of the reasons for the introduction of IMPORT JAVA was opening up the world of Java libraries to Genero developers and this is a very good example.