Subscribe for automatic updates: RSS icon RSS

Login icon Sign in for full access | Help icon Help
Advanced search

Pages: [1]
  Reply  |  Print  
Author Topic: Ask Reuben 62 - Excel  (Read 5030 times)
Reuben B.
Four Js
Posts: 949


« on: January 18, 2021, 03:45:56 am »

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/

Product Consultant (Asia Pacific)
Developer Relations Manager (Worldwide)
Author of https://4js.com/ask-reuben
Contributor to https://github.com/FourjsGenero
Tony E.
Posts: 2


« Reply #1 on: April 09, 2021, 05:14:46 pm »

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.
Four Js
Posts: 949


« Reply #2 on: April 13, 2021, 04:35:13 am »

Hi Tony,

Quote
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 :-)

Quote
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

Quote
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
  1. IMPORT JAVA  org.apache.poi.ss.util.CellUtil
  2. IMPORT JAVA org.apache.poi.ss.usermodel.IndexedColors
  3. IMPORT JAVA org.apache.poi.ss.usermodel.BorderStyle
  4. IMPORT JAVA java.util.HashMap
  5. IMPORT JAVA java.lang.Integer
  6. ...
  7.  
  8. FUNCTION cellutil_set_cell_property_experiment(c cellType)
  9.  
  10. DEFINE h  java.util.HashMap
  11.  
  12.    LET h = java.util.HashMap.create()
  13.  
  14.    CALL h.put(CellUtil.BORDER_TOP, BorderStyle.MEDIUM)
  15.    CALL h.put(CellUtil.BORDER_BOTTOM, BorderStyle.MEDIUM)
  16.    CALL h.put(CellUtil.BORDER_LEFT, BorderStyle.MEDIUM)
  17.    CALL h.put(CellUtil.BORDER_RIGHT, BorderStyle.MEDIUM)
  18.    CALL h.put(CellUtil.TOP_BORDER_COLOR, java.lang.Integer.create(IndexedColors.RED.getIndex()))
  19.    CALL h.put(CellUtil.BOTTOM_BORDER_COLOR, java.lang.Integer.create(IndexedColors.BLUE.getIndex()))
  20.    CALL h.put(CellUtil.LEFT_BORDER_COLOR, java.lang.Integer.create(IndexedColors.YELLOW.getIndex()))
  21.    CALL h.put(CellUtil.RIGHT_BORDER_COLOR, java.lang.Integer.create(IndexedColors.GREEN.getIndex()))
  22.  
  23.    CALL CellUtil.setCellStyleProperties(c, h)
  24. END FUNCTION
  25.  

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

Code
  1. 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
« Last Edit: April 13, 2021, 05:48:30 am »

Product Consultant (Asia Pacific)
Developer Relations Manager (Worldwide)
Author of https://4js.com/ask-reuben
Contributor to https://github.com/FourjsGenero
Tony E.
Posts: 2


« Reply #3 on: April 14, 2021, 10:47:55 am »

Great stuff Reuben. Thanks.
Matthew B.
Posts: 4


« Reply #4 on: December 01, 2021, 04:10:47 am »

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:
Quote
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.
Posts: 4


« Reply #5 on: December 01, 2021, 04:12:46 am »

I've downloaded the Apache POI 4.0.1 jar files
Sorry, 4.1.0.
Reuben B.
Four Js
Posts: 949


« Reply #6 on: December 01, 2021, 06:46:12 am »

Quote
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.

Product Consultant (Asia Pacific)
Developer Relations Manager (Worldwide)
Author of https://4js.com/ask-reuben
Contributor to https://github.com/FourjsGenero
Matthew B.
Posts: 4


« Reply #7 on: December 01, 2021, 09:38:17 pm »

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.
Pages: [1]
  Reply  |  Print  
 
Jump to:  

Powered by SMF 1.1.21 | SMF © 2015, Simple Machines