B4J Tutorial jPOI to the rescue

Discussion in 'B4J Tutorials' started by Erel, Mar 24, 2019.

  1. Erel

    Erel Administrator Staff Member Licensed User

    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:

    [​IMG]

    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:
    Code:
    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 StringAs 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
     
    jmon, victormedranop, juvanum and 7 others like this.
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