Excel time

Erel

B4X founder
Staff member
Licensed User
Longtime User
In the next couple of weeks I'm going to focus on making it easier to work with MS Excel workbooks using B4J. I want to extend jPOI library with more features and utilities.

If you are missing any related feature then this is a good time to post it.

1618149959198.png
 

Charly68

Member
Licensed User
Hello Erel, I would be very grateful if you could include support for B4i.
Thank you for tour fantastic products!
 

Diceman

Active Member
Licensed User
It would also be nice to have a class that would export data from a map, CSV, JSON or from a Sqlite table into a MS Excel spreadsheet.
This would help to get data out of the app and into Excel easily instead of having to code a different set export rtns.
 

JWT

Member
I have yet not used the jPOI library, but I would like like to change the color of a cell or range of cells. Also to execute a macro or VBA script. And of course inserting text or other values in cells and reading those values.
The same applies for MSWord.
 

RWK

Member
Licensed User
Yes handling VBA script would be nice.
And of course interfacing the upcoming TypeScript implementation (available at Office365 up to now), which will replace VB Script in the long run.
And something like FormWorks. Building Forms in Excel is somewhat limited, so it would be nice if I could show some B4J Forms build with the Designer an bind the fields to some Excel cells.... tabBuchungen.cells(8,1) = b4JMonth.text and vice versa to Bj4-Form b4JMonth.text = tabBuchungen.cells(8,1) (.value)

Of course I think it isn't to easy to implement.
Cause if I am working in excel, which is opend an called from my B4J App.... I need a way back to show some B4J Forms if (as example) click a Textfield (assigned to a Macro) in Excel.

Only Wish-Thinking :)
 

moore_it

Well-Known Member
Licensed User
Longtime User
Hi,
i use jPoi to insert a image in a cell, it's possible to move the image in the center of the cell ?
Thnaks in advice
 

Chris2

Active Member
Licensed User
Hi,
It would be nice to have a save as pdf.
 

StarinschiAndrei

Active Member
Licensed User
Longtime User
I know this library, but I'm using excel templates and it's easier to have a save as function.
 

aaronk

Well-Known Member
Licensed User
Longtime User
Some ideas..

1. It would be good if it can insert and read a cell with 1 line of code:
Example:
B4X:
excel.get("sheet1","A",3)  'based on the below excel it would return Bill
excel.get("sheet1","B",3) ' based on the below excel it would return Jones
excel.put("sheet1","A",5, "Tommy") ' and it replace the value or insert the value

1618206125466.png



2. It would be good if that you can add a formula with 1 line of code.
Example:
B4X:
excel.setformula("sheet1","C",6,"=SUM(C2:C5)")  'based on the example it would add the formula in C6.
excel.getformula("sheet1","C",6)  'this would return the formula currently set in C6

3. It would be good if that you can change the font for a cell with 1 line of code.
Example:
B4X:
excel.makebold("sheet1","A",4)  'based on the example it would make A4 have bold font.
excel.removebold("sheet1","A",4)   'based on the example it would remove the bold text on cell A4.

Or maybe:
B4X:
excel.setfont("sheet1","A",4, font_style)' font_style would be a object the contains if it's bold, underline, selected font to use etc.

4. It would be good if we can sort a row or column with 1 line of code.
Example:
B4X:
excel.sortcolumn("sheet1","A","A","ASC")  'based on the example it would sort column A (A-Z)
excel.sortcolumn("sheet1","B","B","DESC")  'based on the example it would sort column B (Z-A)
excel.sortcolumn("sheet1","B1","B3","DESC")  'based on the example it would sort part of the column, B1 to B3 only (Z-A)

I know we can pretty much do this already, but by having it part of the library like the above with 1 line of code it will make it a lot easier to insert and read values since it would use 1 line of code won't require us to create our own functions.
 
Last edited:

Chris2

Active Member
Licensed User
It would also be nice to have a class that would export data from a map, CSV, JSON or from a Sqlite table into a MS Excel spreadsheet.
This would help to get data out of the app and into Excel easily instead of having to code a different set export rtns.
... or from any database.
Maybe this is being gready, but a direct method to add the results from DBResult from jRDC2 to a POISheet starting at a specified cell would be great....
B4X:
Dim req As DBRequestManager = CreateRequest
    Dim cmd As DBCommand = CreateCommand("select_data", Array(param1, param2))
    Wait For (req.ExecuteQuery(cmd, 0, Null)) JobDone(j As HttpJob)
    If j.Success Then
        req.HandleJobAsync(j, "req")
        Wait For (req) req_Result(res As DBResult)
        POISheet.AddQueryResults(res, sheet1, A1)
        ....
 
Last edited:

Chris2

Active Member
Licensed User
It may also be useful to have more options for the formating of cells, e.g. dates, currency, and custom options for a datetime string for example (e.g. yyyy-MM-dd HH:mm:ss)...
B4X:
PoiRow.CreateCellDate(DateFormat as string)
PoiRow.CreateCellCurrency(currency as string)                   e.g. "$", or just pick up local device setting.
PoiRow.CreateCellCustom(customFormat As string)           e.g. "yyyy-MM-dd HH:mm:ss"
 
Top