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:
There are two defined names. Open Template.xlsx from the attached project to see them.
Press Ctrl + F3 to open the names editor:
(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:
Example:
The result:
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:
There are two defined names. Open Template.xlsx from the attached project to see them.
Press Ctrl + F3 to open the names editor:
(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.
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 SubExample:
B4X:
SetNamedRange(wb, "Sheet1", "Range_Name", "$C$5:$C$" & (numberOfItems + 4))The result:
