B4J Tutorial Excel Charts and Templates with jPOI library

The jPOI library allows you to read and write Excel workbooks: https://www.b4x.com/android/forum/t...t-excel-xls-and-xlsx-workbooks.57392/#content

The library doesn't support creating new charts (or directly modifying existing charts).

You can read an Excel file with a chart, modify the data and write it. This is a simple and good solution if the data range is always the same.
However if the data range can change then you need to use dynamic named ranges.
This requires a bit more work but it is not too complicated.

First we start with the template file:

SS-2015-08-27_15.44.29.png


There are two defined names. Open Template.xlsx from the attached project to see them.
Press Ctrl + F3 to open the names editor:

SS-2015-08-27_15.45.54.png


(Step by step tutorial about dynamic named ranges: https://support.microsoft.com/en-us/kb/183446)

The actual ranges are not really important as they will be later overwritten. They must refer to a function (such as OFFSET) to be dynamic.

The program will:
  • Read the template file.
  • Fill the data.
  • Update the two ranges based on the number of items.
  • Save the workbook as a new file.
Updating the ranges is done with this code:
B4X:
Private Sub SetNamedRange(wb As PoiWorkbook, SheetName As String, Name As String, Cells As String)
   Dim jo As JavaObject = wb
   Dim NameJO As JavaObject = jo.RunMethod("getName", Array(Name))
   NameJO.RunMethod("setRefersToFormula", Array($"${SheetName}!${Cells}"$))
End Sub

Example:
B4X:
SetNamedRange(wb, "Sheet1", "Range_Name", "$C$5:$C$" & (numberOfItems + 4))

The result:

SS-2015-08-27_15.54.22.png
 

Attachments

  • ExcelChart.zip
    10.3 KB · Views: 1,012

jinyistudio

Well-Known Member
Licensed User
Longtime User
Hi

Does jPOI could use in Linux/Debian system ?
 

StarinschiAndrei

Active Member
Licensed User
Longtime User
Hi,
Thank you for your help,but in this case i lose all setting for this cell llike border and other previous setting. There is no other method to simply replace the cell value?
 

MukeshMakwana

New Member
Licensed User
Erel, This is great.
Am intersted in converting XL Chart to Android App...Have B4A Licence...Please suggest modification from B4J to B4A to implement this ...
Alternatively, can I invoke B4J in B4A environment ???
Thanks in advance.
 

Erel

B4X founder
Staff member
Licensed User
Longtime User
Alternatively, can I invoke B4J in B4A environment ???
No.

This is quite a heavy library. In the past it wasn't possible to use it with B4A due to the dex size limits. You can try it now and set #MultiDex to True. Not the jPOI doesn't provide any user interface. For further discussion please start a new thread in B4A questions forum.
 
Top