B4J Tutorial Excel Charts and Templates with jPOI library

Discussion in 'B4J Tutorials' started by Erel, Aug 27, 2015.

  1. Erel

    Erel Administrator Staff Member Licensed User

    The jPOI library allows you to read and write Excel workbooks: http://www.b4x.com/android/forum/th...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:

    [​IMG]

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

    [​IMG]

    (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:
    Code:
    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:
    Code:
    SetNamedRange(wb, "Sheet1""Range_Name""$C$5:$C$" & (numberOfItems + 4))
    The result:

    [​IMG]
     

    Attached Files:

  2. jinyistudio

    jinyistudio Active Member Licensed User

    Hi

    Does jPOI could use in Linux/Debian system ?
     
  3. Erel

    Erel Administrator Staff Member Licensed User

    Yes. However this is not the correct place to post this question...
     
  4. StarinschiAndrei

    StarinschiAndrei Active Member Licensed User

    Hello ,
    Is it possible to update an existing cell with my own value ?
     
  5. Erel

    Erel Administrator Staff Member Licensed User

    Yes. No need to do anything special. Call PoiSheet.GetRow and then create new cells.
     
  6. StarinschiAndrei

    StarinschiAndrei Active Member Licensed 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?
     
  7. Erel

    Erel Administrator Staff Member Licensed User

    Get the relevant row (PoiSheet.GetRow) and then get the cell with GetCell.

    Now update the cell value.
     
  8. StarinschiAndrei

    StarinschiAndrei Active Member Licensed User

    Thank you, it works
     
  9. MukeshMakwana

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

    Erel Administrator Staff Member Licensed User

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

    StarinschiAndrei Active Member Licensed User

    Hi ,
    Is it possible to show the chart in B4J form ?
     
  12. Erel

    Erel Administrator Staff Member Licensed User

    jPOI has nothing to do with user interface. It allows you to read and write Excel workbooks.
     
  13. StarinschiAndrei

    StarinschiAndrei Active Member Licensed User

    Thank you
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice