B4J Tutorial jPOI to the rescue

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

Thread Status:
Not open for further replies.
  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
     
    JOTHA, jmon, victormedranop and 8 others like this.
  2. JOTHA

    JOTHA Well-Known Member Licensed User

    Hello Erel,
    i like your idea to work with excel to fill data into a WORD-document.

    But there is an error message:
    Attached you see the code ...
     

    Attached Files:

  3. Erel

    Erel Administrator Staff Member Licensed User

    The error message means that you are trying to read from an empty or non-existent cell. It is not really related to this thread. You need to learn how to read data with jPOI.
     
  4. JOTHA

    JOTHA Well-Known Member Licensed User

    OK, thank you!
     
  5. micro

    micro Well-Known Member Licensed 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
     
  6. Erel

    Erel Administrator Staff Member Licensed User

  7. micro

    micro Well-Known Member Licensed User

  8. JOTHA

    JOTHA Well-Known Member Licensed User

    Hi community,

    1) Can someone tell me why the map is generated 6 times (see Logfile):
    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.
     
  9. Erel

    Erel Administrator Staff Member Licensed User

    Please start a new thread and also provide more information.
     
Thread Status:
Not open for further replies.
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