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