B4J Tutorial jPOI to the rescue

Status
Not open for further replies.
I'm helping a group of first aid personnel with their organization. There are about 50 people. The main communication channel is a Whatsapp group.

Each one needed to fill a MS Word document with some information. I didn't want to send them an email with the document as an attachment. Many of them only use a phone and it can be challenging to fill such a document, attach it to an email and then send it back.

I chose a different solution:

1. Used Google Forms to create a form where they can fill the data.
2. Sent them a link to the form.
3. Google Forms produces a Google Sheet document. I've downloaded it as an MS Excel document.
4. Created a Word file based on the original form with replacements strings. Example:

SS-2019-03-24_17.59.59.png


Now for the interesting part, the following B4J code:
- Reads each line of the Excel workbook.
- Creates a Map with the replacement strings as keys and the values from the Excel file as values.
- Loads the input document.
- Replaces the replacements strings based on the map. The code is based on: https://stackoverflow.com/a/22269035/971547
Note that there is an assumption here that the text is not split into multiple "runs". This can happen with more complex documents.
- Saves the new document.

Complete program:
B4X:
Sub AppStart (Args() As String)

   Dim wk As PoiWorkbook
   wk.InitializeExisting("C:\Users\H\Downloads\1.xlsx", "", "")
   Dim sheet As PoiSheet = wk.GetSheet(0)
   Dim c As Int = 1
   For Each row As PoiRow In sheet.Rows
       If row.RowNumber = 0 Then Continue 'skip the header
       Dim m As Map = CreateMap("NAME1": row.GetCell(2).ValueString, "NAME2": row.GetCell(3).ValueString, _
       "ID": row.GetCell(1).ValueString, "PHONE": row.GetCell(4).ValueString)
       Dim doc As JavaObject = OpenDocx(File.DirApp, "input.docx")
       Dim paragraphs As List = doc.RunMethod("getParagraphs", Null)
       For Each p As JavaObject In paragraphs
           Dim runs As List = p.RunMethod("getRuns", Null)
           If runs.IsInitialized Then
               For Each r As JavaObject In runs
                   Dim text As String = r.RunMethod("getText", Array(0))
                   If text <> Null Then
                       For Each key As String In m.Keys
                           If text.Contains("$" & key & "$") Then
                               r.RunMethod("setText", Array(" " & m.Get(key) & " ", 0))
                           End If
                       Next
               
                   End If
               Next
           End If
       Next
       SaveDocument(doc, File.DirApp , "Document" & c & ".docx")
       c = c + 1
   Next
   wk.Close
End Sub

Sub SaveDocument(doc As JavaObject, Dir As String, FileName As String)
   Dim out As OutputStream = File.OpenOutput(Dir, FileName, False)
   doc.RunMethod("write", Array(out))
   out.Close
End Sub

Sub OpenDocx(Dir As String, FileName As String) As JavaObject
   Dim in As InputStream = File.OpenInput(Dir, FileName)
   Dim document As JavaObject
   document.InitializeNewInstance("org.apache.poi.xwpf.usermodel.XWPFDocument", _
       Array(in))
   Return document
End Sub

Sub CreateNewDocx() As JavaObject 'ignore not used in this example
   Dim document As JavaObject
   document.InitializeNewInstance("org.apache.poi.xwpf.usermodel.XWPFDocument", _
       Null)
   Return document
End Sub
 

JOTHA

Well-Known Member
Licensed User
Longtime User
Hello Erel,
i like your idea to work with excel to fill data into a WORD-document.

But there is an error message:
24
main._appstart (java line: 84)
java.lang.RuntimeException: Object should first be initialized (PoiCell).
at anywheresoftware.b4a.AbsObjectWrapper.getObject(AbsObjectWrapper.java:32)
at anywheresoftware.b4j.objects.PoiCellWrapper.getValueString(PoiCellWrapper.java:51)
at b4j.example.main._appstart(main.java:84)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:91)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:78)
at b4j.example.main.main(main.java:28)

Attached you see the code ...
 

Attachments

  • B4J - PoiRescue.zip
    1.7 KB · Views: 454

micro

Well-Known Member
Licensed User
Longtime User
Hi to all and thanks erel for this tips.
I directly open a docx file (no a excel files) and if the text to replace is simply in the page I find it and I replace it
but if this is present in a cell I can't find it.
Why?

P.S.
The word file is created with MSWord
 

JOTHA

Well-Known Member
Licensed User
Longtime User
Hi community,

1) Can someone tell me why the map is generated 6 times (see Logfile):
(MyMap) {NAME1=Klaus, NAME2=Muster, ID=ID_123456, PHONE=01525-12345678}
(MyMap) {NAME1=Klaus, NAME2=Muster, ID=ID_123456, PHONE=01525-12345678}
(MyMap) {NAME1=Klaus, NAME2=Muster, ID=ID_123456, PHONE=01525-12345678}
(MyMap) {NAME1=Klaus, NAME2=Muster, ID=ID_123456, PHONE=01525-12345678}
(MyMap) {NAME1=Klaus, NAME2=Muster, ID=ID_123456, PHONE=01525-12345678}
(MyMap) {NAME1=Klaus, NAME2=Muster, ID=ID_123456, PHONE=01525-12345678}

2) Can someone tell me how to generate new placeholders in the input.docx (copy and paste doesn't work)?
There are only 4 placeholder-fields and 2 placeholder-fields (NAME2 + ID) are not filled with data.
 
Status
Not open for further replies.
Top