Excel time

Erel

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
 
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.
 
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.
 
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 :-)
 
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
 
StarinschiAndrei said:
Hi,
It would be nice to have a save as pdf.
Click to expand...

jPDFjet

jPDFjet is a PDF document authoring library for B4J jPDFjet wraps the PDFjet for Java library from pdfjet.com. Here are some of it's features: Drawing support for: Points Lines Boxes Circles Bezier Curves Polygons Stars Complex paths and shapes Text Unicode support Text kerning when using...
www.b4x.com www.b4x.com
 
Chris2 said:

jPDFjet

jPDFjet is a PDF document authoring library for B4J jPDFjet wraps the PDFjet for Java library from pdfjet.com. Here are some of it's features: Drawing support for: Points Lines Boxes Circles Bezier Curves Polygons Stars Complex paths and shapes Text Unicode support Text kerning when using...
www.b4x.com www.b4x.com
Click to expand...
I know this library, but I'm using excel templates and it's easier to have a save as function.
 
Some ideas..

1. It would be good if it can insert and read a cell with 1 line of code:
Example:
Code: 
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:
Code: 
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:
Code: 
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:
Code: 
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:
Code: 
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:
Diceman said:
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.
Click to expand...
... 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:
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"
 
You must log in or register to reply here.
Back
Top