Ask Reuben 62 - Excel

[Reuben B.]

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 and other Office products.

In this article I discuss a number of different ways you can interact with Excel (and other Office products such as Word) and some things to watch out for.  My preference is to use the ApachePOI libraries as that gives you the most control and flexibility.

Read more at https://4js.com/ask-reuben/ig-62/

[Tony E.]

Hi Reuben,

I read this article with great interest, thanks, and decided to try to take on using JAVA POI to generate excel. I'm not a java programmer at all, but your sample project and code helped and I have something working.

I'm not sure if this is the correct place to ask, but ...

What I am now looking at is the formatting of cells using the CellUtil java libraries. The reason is, using CellUtil functions you can apply additional formatting to cells that have already been created with a certain style without affecting other cells with that same style.

I can get setCellStyleProperty() to work fine, but I want to make multiple style changes on a cell - e.g. borders all round + colours, bold, etc. - so it is recommended to use setCellStyleProperties(), which I can't get to work. I am struggling with the 'Map' java type, which I understand is an Interface type (https://docs.oracle.com/javase/8/docs/api/java/util/Map.html).

The definition for this function is:-
public static void setCellStyleProperties(Cell cell, java.util.Map<java.lang.String,java.lang.Object> properties)

Have you come across this before ? Would you be able to please explain how to declare the 'Map' and use this setCellStyleProperties function in 4GL code.

Much appreciated,
Tony.

[Reuben B.]

Hi Tony,

I'm not a java programmer at all,

I am not a natural Java programmer either, so I'll probably use a term incorrectly somewhere :-)

I'm not sure if this is the correct place to ask

Unless it related directly to something in this article, I would suggest in the future creating a dedicated thread in the Genero BDL board https://forum.4js.com/fjs_forum/index.php?board=2.0

I hadn't used CellUtil https://poi.apache.org/apidocs/dev/org/apache/poi/ss/util/CellUtil.html but I was reading it it thinking I perhaps should have used it in places.

Re: your question on Java.Util.Map

I can get setCellStyleProperty() to work fine, but I want to make multiple style changes on a cell - e.g. borders all round + colours, bold, etc. - so it is recommended to use setCellStyleProperties(), which I can't get to work. I am struggling with the 'Map' java type, which I understand is an Interface type (https://docs.oracle.com/javase/8/docs/api/java/util/Map.html).

The definition for this function is:-
public static void setCellStyleProperties(Cell cell, java.util.Map<java.lang.String,java.lang.Object> properties)

Have you come across this before ? Would you be able to please explain how to declare the 'Map'

I wonder if the pieces of the puzzle you are missing is point 1 in the limitations http://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_JavaBridge_001.html, and secondly with Map being an Interface type it is not a case of creating something of Map type but of something that implements the Map type.   You can use java.util.hashMap to implement something that matches that Interface.

After some trial and error I found that adding to fgl_excel.4gl

Code (genero) Select Expand


IMPORT JAVA  org.apache.poi.ss.util.CellUtil
IMPORT JAVA org.apache.poi.ss.usermodel.IndexedColors
IMPORT JAVA org.apache.poi.ss.usermodel.BorderStyle
IMPORT JAVA java.util.HashMap
IMPORT JAVA java.lang.Integer
...

FUNCTION cellutil_set_cell_property_experiment(c cellType)

DEFINE h  java.util.HashMap

   LET h = java.util.HashMap.create()

   CALL h.put(CellUtil.BORDER_TOP, BorderStyle.MEDIUM)
   CALL h.put(CellUtil.BORDER_BOTTOM, BorderStyle.MEDIUM)
   CALL h.put(CellUtil.BORDER_LEFT, BorderStyle.MEDIUM)
   CALL h.put(CellUtil.BORDER_RIGHT, BorderStyle.MEDIUM)
   CALL h.put(CellUtil.TOP_BORDER_COLOR, java.lang.Integer.create(IndexedColors.RED.getIndex()))
   CALL h.put(CellUtil.BOTTOM_BORDER_COLOR, java.lang.Integer.create(IndexedColors.BLUE.getIndex()))
   CALL h.put(CellUtil.LEFT_BORDER_COLOR, java.lang.Integer.create(IndexedColors.YELLOW.getIndex()))
   CALL h.put(CellUtil.RIGHT_BORDER_COLOR, java.lang.Integer.create(IndexedColors.GREEN.getIndex()))
   
   CALL CellUtil.setCellStyleProperties(c, h)
END FUNCTION


I then had a function I could call that utllised CellUtil.setCellStyleProperties passing in the appropriate cell

Code (genero) Select Expand

CALL fgl_excel.cellutil_set_cell_property_experiment(cell)

and it added a border around the appropriate spreadsheet cell.

The java.lang.Integer.create was required to turn the integer into an object.

One thought in general is for any complex Java that the Genero Java interface does not cater for is to create a Java class that simplifies it that you can then call with IMPORT JAVA.   That is not necessary in this case but I thought I was heading in that direction if I had needed a complex equivalent of   "  HashMap<String, XXX> h = new HashMap<String, XXX>();   "when I couldn't figure out what XXX was required to be.    As it was java.util.HashMap.create() created a suitable hashmap.

Hope that helps,

Reuben

[Tony E.]

Great stuff Reuben. Thanks.

[Matthew B.]

Hi, Reuben.

I've attempted to implement your fgl_excel wrappers.  I've downloaded the Apache POI 4.0.1 jar files, and I've compiled the 4GL programs by setting the classpath to include the eight nominated jars.  When I run fgl_excel_test, however, I'm getting a Java exception:

Program stopped at 'fgl_excel.4gl', line number 28.
FORMS statement error number -8306.
Java exception thrown: java.lang.NoClassDefFoundError: org/apache/poi/xssf/usermodel/XSSFWorkbook.

Is this a distinction between the build classpath and the runtime classpath?  Do I need to configure something to ensure that the appropriate jar files (in this example, poi-4.1.0.jar) can be found once the program is running?

[Matthew B.]

I've downloaded the Apache POI 4.0.1 jar files

Sorry, 4.1.0.

[Reuben B.]

Is this a distinction between the build classpath and the runtime classpath?

Not that I am aware of.

I have committed to the repository in prep for a presentation tomorrow where I am now using Apache 5.1.0.  There I found that I needed an additional Java classpath for log4j  (log4js is nothing to do with us).  I think I found as http://poi.apache.org/components/logging.html that told me that (and log4j was in the error message somewhere)

I have also tended to use a brute force approach, just reference all the .jar that are there in the library rather than trying to select the bare minimum.  Someone who knows Java better than me might be able to say this is how you determine the classpath you need.

[Matthew B.]

I found my issue - setting CLASSPATH in my makefile was required to compile, setting CLASSPATH (or passing a --java-option=-Djava.class.path=... CLA) before fglrun is required for runtime.