next »

[francesco f.]

Hi, my name is Francesco and i desperatelly need some help. i'm trying to load a file excel but i'm literally dashing my head into the screen struggling with HSSFWorkbook, FileInputStream etc etc. there are not a single example to learn it from. while is clear how to write a file because there's one example even in genero docs and in gitHub, there are zero posts showing how to import a file.
maybe is not possible but i don't think is the case because it's so important a thing that it must be possibile!

I'm NOT talking of apache poi in pure java but loaded from a 4gl file and i already know the other way using frontCall.

If you already did that please show me just the first instructions.

i'd really appreciate that!

thanks, bye
 

[Reuben B.]

Hi

Its not something I've done before.  In past I'd normally code an INPUT ARRAY and have the user copy and paste in one hit into the INPUT ARRAY.  So for things like General Ledger Budget entry, accountant would prepare a spreadsheet that had a sheet with 2 columns, account code and budget value.  I'd have an INPUT ARRAY with 2 columns, account code and budget value, and with a single select, copy, and paste operation, the Accountant would copy the entire budget into the INPUT ARRAY.  The INPUT ARRAY shouldn't have any BEFORE ROW, AFTER ROW, BEFORE FIELD, AFTER FIELD that would confuse the paste operation, and validation was contained to AFTER INPUT

Anyway I had a look at my GitHub example for Apache POI https://github.com/FourjsGenero/fgl_apache_poi to see what would be required to open an existing spreadsheet and return values.  I was able to do it ok.  I have not committed my changes but

In fgl_excel.4gl I added to allow you to open an existing workbook instead of creating a new one ...

Code

IMPORT JAVA java.io.FileInputStream
...
FUNCTION workbook_open(filename)
DEFINE filename STRING
DEFINE fi FileInputStream
DEFINE w workbookType
 
    LET fi = FileInputStream.create(filename)
    LET w = XSSFWorkbook.create(fi)
    RETURN w
END FUNCTION

The test program I used reads the excel worksheet that was created by the other test program.  The code I have at moment is ...

Code

IMPORT FGL fgl_excel
IMPORT util
 
MAIN
DEFINE workbook     fgl_excel.workbookType 
DEFINE sheet        fgl_excel.sheetType  
DEFINE row          fgl_excel.rowType  
DEFINE cell         fgl_excel.cellType
 
    -- Read fgl_excel_test.xlsx
    LET workbook = fgl_excel.workbook_open("fgl_excel_test.xlsx")
 
    -- Number of sheets
    DISPLAY SFMT("Number of sheets = %1",workbook.getNumberOfSheets())
 
    -- Number of rows in sheet
    LET sheet = workbook.getSheetAt(0)
    DISPLAY SFMT("Number of rows in sheet = %1", sheet.getPhysicalNumberOfRows())
 
    -- Get row 12, java index 0 based so subtract 1
    LET row = sheet.getRow(11)
    DISPLAY SFMT("Last cell in row 12 = %1", row.getPhysicalNumberOfCells())
 
    -- Get cell A12,"Total" java index 0 based so subtract 1
    -- Java strongly typed so have to check Type and then use appropriate method to return
    LET cell = row.getCell(0)
    DISPLAY SFMT("Celltype in cell A12 = %1", cell.getCellType())
    DISPLAY SFMT("Value in cell A12 = %1", cell.getStringCellValue())
 
    -- Get cell B12, "Formula with total", java index 0 based so subtract 1 
    -- Java strongly typed so have to check Type and then use appropriate method to return
    -- As formula, should be able to get cached result but this not working
    -- Have to evaluate formula
    LET cell = row.getCell(1)
    DISPLAY SFMT("Celltype in cell B12 = %1", cell.getCellType())
    DISPLAY SFMT("Formula in cell B12 = %1", cell.getCellFormula())
 
    DISPLAY ""
    DISPLAY "Not sure why these return 0"
    DISPLAY SFMT("Get cached formula result type in cell B12 = %1", cell.getCachedFormulaResultType())
    DISPLAY SFMT("Value in cell B12 = %1", cell.getNumericCellValue())
 
    DISPLAY ""
    DISPLAY "This works but I should be able to read last value rather than evaluating"
    DISPLAY SFMT("Evaluated value in cell B12 = %1",  workbook.getCreationHelper().createFormulaEvaluator().evaluate(cell).getNumberValue())
 
 
END MAIN

and I'd get the following output ...

Number of sheets = 1
Number of rows in sheet = 13
Last cell in row 12 = 2
Celltype in cell A12 = 1
Value in cell A12 = Total
Celltype in cell B12 = 2
Formula in cell B12 = SUM(B2:B11)

Not sure why these return 0
Get cached formula result type in cell B12 = 0
Value in cell B12 = 0.0

This works but I should be able to read last value rather than evaluating
Evaluated value in cell B12 = 443.0
*** Execution of 'fgl_excel_import_test' finished. Exit code: 0 ***

So it worked.  I didn't fully understand what was happening when I attempt to read value in formula cell.  My understanding was that I should be able to get the last cached result for a formula rather than having to evaluate it like I did.

Before I commit I will need to create some additional wrapper functions to hide the Java complexity away from the 4gl developer.

I realise I used XSSF and not HSSF but I'd expect there to be HSSF equivalents.

Hope that helps,

Reuben

[francesco f.]

Hi Reuben,
thanks so much for your time.
using FileInputStream that way works fine!

i want to build a simple xls xlsx loader inside a 4gl file, now i know where to start.
i know i'm not a genius but i find that porting those java statements inside a 4gl it's not so easy.

i'm not sure if all of those instructions works in 4gl or just a subset of them and obviously i see that they are call in
another way.

i was looking through github in search of your modified  file but i didn't find it, there was just the older version
but maybe it's me. i'm new to github and maybe i miss it.

[Reuben B.]

...

i was looking through github in search of your modified  file but i didn't find it, there was just the older version
but maybe it's me. i'm new to github and maybe i miss it.

I had not checked it in at that point.  Thats what I meant by "I have not committed my changes but ..." 

I have now today checked in just the additional function in fgl_excel.4gl.    Long story short, I'm using Genero Studio 3.10 from the Early Access Program so I have to be careful I don't check in anything that will only work with 3.10.  If I check in updated .4pw file then only someone participating in the Early Access Progress will be able to open the project until 3.10 is released.