Excel time

[Erel]

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.

 

[Kope]

wow.. that will be great

thanks sir

 

[Charly68]

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

 

[angel_]

I think it would be useful to be able to create Excel tables

 

[Diceman]

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]

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]

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]

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

 

[Erel]

The implementation is based on Apache POI. It doesn't depend on Excel and it doesn't call Excel in any way. It directly reads and writes to the workbook file. It can evaluate functions but it cannot run scripts.

 

[moore_it]

so should i ask jpoi developers ?

 

[Erel]

No. I've replied to the previous post about VBA.

 

[moore_it]

Ops sorry

 

[StarinschiAndrei]

Hi,
It would be nice to have a save as pdf.

 

[Chris2]

I think direct creation of Excel charts would be useful.
I am aware of the work-around - https://www.b4x.com/android/forum/t...nd-templates-with-jpoi-library.57689/#content, and have used it successfully.
But with unknown/dynamic data ranges there are a few hurdles to overcome.

 

[Chris2]

Hi,
It would be nice to have a save as pdf.

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

 

[StarinschiAndrei]

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

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

 

[aaronk]

Some ideas..

1. It would be good if it can insert and read a cell with 1 line of code:
Example:

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

2. It would be good if that you can add a formula with 1 line of code.
Example:

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:

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:

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:

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.

 

[Chris2]

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

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

 

[Chris2]

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

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"

 

[Erel]

Good points ?