Generate QR Code from a cell

Discussions about using 3rd party extension with OpenOffice.org
Post Reply
razor_amd
Posts: 2
Joined: Tue Feb 09, 2021 11:57 am

Generate QR Code from a cell

Post by razor_amd »

 Edit: Moved from Calc forum to Extensions because Calc alone cannot create QR codes and one must use an extension to add that feature. Thus the topic is about using an extension or finding one to accomplish the desired task. 
Dear OpenOffice community,

I am wondering if it is possible to generate QR Code in OpenOffice Calc from a cell containing text (resulted from CONCATENATE formula) in multiple lines, at most 3 lines?
The text is always longer than maximum 160 characters that QRCode generator extension allows.
OpenOffice 4.1 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Generate QR Code from a cell

Post by Villeroy »

Data collections belong into a database: viewtopic.php?f=42&t=103924&p=503253#p503253 (this one also relies on the QR extension)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
razor_amd
Posts: 2
Joined: Tue Feb 09, 2021 11:57 am

Re: Generate QR Code from a cell

Post by razor_amd »

I need to generate one IPS QR code on a document I will print for customers. Only one QR code must read multiple lines in text. It contains data for quick mobile payment.
Attachments
NBS IPS QR.png
OpenOffice 4.1 on Windows 10
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: Generate QR Code from a cell

Post by Sébastien C »

Hello, All,

Typically, the original extension, written in javaScript, is fine. It can also be verified on the site of the original author (Kazuhiko Arase).

Where the shoe pinches is that this is an adaptation in a language hardly worn in the office suite. And this adaptation (by Cyril Beaussier, alias Bidouille) does not save itself from writing a temporary SVG file reinserted in Basic via the dispatcher. Why so much triviality? Simply because this is apparently the only solution to incorporate a SVG file into an ODF via macro than just link it. But in our office suite, the management of javaScript is obviously dependent on Java. And the overflows are particularly laborious. So making Libre/OpenOffice javaScript process more than 160 characters is just impossible...

At that point, I think it's just better to completely outsource the processing to a dedicated program, obviously from the command line. Installing “qrencode” on any GNU-Linux system is the infancy of The Art. Under the system you are using, you will need to install “Cygwin” first, then “qrencode” itself. Likewise, you will probably also have to adapt my code slightly (“shell” line). I'll leave it to someone else to continue this adaptation for “you-know-who”'s use, since that shoe doesn't suit my feet.

However, don't forget that cells in a spreadsheet are not ideal (euphemism) for receiving images... But I guess you don't have 999. So the example given attached to this post is first given by way of illustration. It contains two sheets, respectively named “qrencode” and “Bill”.

For the “qrencode” sheet, I suppose that you will also appreciate that the call to the macro by hyperlink in column A which places in column B the QRcode containing the text of column C. The Copy and paste of the cells from column A works very simply and adapts, which is reminiscent of a principle pushed quite far in another context and for another purpose. But delete a line which contains a QRcode linked to a cell in column B and you will not delete said QRcode... This is where we see that the management of images, in Calc, could be, nevertheless, significantly improved.

I guess you set up some kind of invoice. Although the “Bill” sheet is obviously not one, the purpose was to have the contents of a cell ($E$2) very fattish for a proportional QRcode. Note in passing that I prefer the alphanumeric operator “&” to the “CONCATENATE” function (but the result is exactly the same). So here we have to produce several QRcodes in a single cell. We must therefore manage the deletion of previous products. Otherwise, they will stack on top of each other every time the “Update” button is clicked. The code does this pretty much correctly, although I've found some irregularities that I attribute to bugs in LibreOffice rather than my code.

Either way, this little demo really highlights two things:
  • Calc is not made to manage database images.
  • Sometimes it is much better to completely outsource a job and have it run by a dedicated program, rather than using an extension that is not suitable. The “shell” command as well as the "com.sun.star.system.SystemShellExecute" service (which is sometimes more suitable) really work wonders, especially when you know that you will not save yourself from going through a (or more) temporary files. Here we are typically in a Unix spirit that does not reinvent the wheel, but delegates the work to that we know it is already doing it very well.
Have fun !
8-)

Code: Select all

 Option Explicit
 
 '   Coding : qrencode -o "myFile.svg" "My text as long as I want, parce que je le vaux bien, merci."
 ' Decoding : zbarimg "myFile.svg"
 
 
 ' ╔═══════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
 ' ║ Generate a QRcode with qrencode.                                            .                                 ║█
 ' ╚═══════════════════════════════════════════════════════════════════════════════════════════════════════════════╝█
 '  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
 
 Sub generateQRcode(myURL As String)
  Dim         mySheet As Object,          myCell As Object, myCellQR As Object, myCellText As Object, myQRcode As Object
  Dim myTemporaryFile As String,      mylineCode As String
 
  Dim    myDispatcher As Object, myDispatchFrame As Object
  Dim  myArguments(2) As New com.sun.star.beans.PropertyValue
 
          mySheet = thisComponent.currentController.activeSheet
 
       myCell     = mySheet.getCellRangeByName(getArgumentFromURL(myURL, "myCell"))
       myCellQR   = mySheet.getCellByPosition(myCell.CellAddress.column + 1, myCell.CellAddress.row)
       myCellText = mySheet.getCellByPosition(myCell.CellAddress.column + 2, myCell.CellAddress.row)
 
  myTemporaryFile = createUnoService("com.sun.star.util.PathSettings").temp & "/qrcode.svg"
       mylineCode = " -m 1 -o """ & convertFromUrl(myTemporaryFile) & """ -t SVG """ & myCellText.string & """"
 
  shell ("qrencode", 0, mylineCode)
 
 
  ' Insert the temporary file with the dispatcher. The only solution for INCORPORATE the SVG.
 
         myDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
      myDispatchFrame = thisComponent.currentController.frame
 
  myArguments(0).name = "ToPoint"    : myArguments(0).value = myCellQR.absoluteName
  myDispatcher.executeDispatch(myDispatchFrame, ".uno:GoToCell"     , "", 0, myArguments())
 
  myArguments(0).name = "FileName"   : myArguments(0).value = myTemporaryFile
  myArguments(1).name = "FilterName" : myArguments(1).value = "SVG - Scalable Vector Graphics"
  myArguments(2).name = "AsLink"     : myArguments(2).value = false
  myDispatcher.executeDispatch(myDispatchFrame, ".uno:InsertGraphic", "", 0, myArguments())
  
             myQRcode = mySheet.drawPage.getByIndex(mySheet.drawPage.count - 1)                                                      ' The last object inserted (hopely...).
 
  myArguments(0).name = "RowHeight" : myArguments(0).value = myQRcode.size.height
  myDispatcher.executeDispatch(myDispatchFrame, ".uno:RowHeight"    , "", 0, myArguments())
 
      myQRcode.anchor = myCellQR
 
  kill myTemporaryFile                                                                                                               ' Be clean in the temporary directory.
 End Sub
 
 
 ' ╔═══════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
 ' ║ In fact, not exactly a kind of bill.                                        .                                 ║█
 ' ╚═══════════════════════════════════════════════════════════════════════════════════════════════════════════════╝█
 '  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
 
 Sub billQRcode()
  Dim         mySheet As Object,        myCellQR As Object,   myCellText As Object,   myQRcode As Object
  Dim myTemporaryFile As String,      mylineCode As String, myQRcodeName As String, myMemoCell As String
 
  Dim    myDispatcher As Object, myDispatchFrame As Object
  Dim  myArguments(2) As New com.sun.star.beans.PropertyValue
 
 
          mySheet = thisComponent.currentController.activeSheet
         myCellQR = mySheet.getCellRangeByName("$B$36")
       myCellText = mySheet.getCellRangeByName("$E$2" )
       myMemoCell = thisComponent.currentController.selection.absoluteName
     myQRcodeName = "myIPS-QRcode"
 
  myQRcode = findObjectByName(mySheet.drawPage, myQRcodeName)
  If Not isNull(myQRcode) Then mySheet.drawPage.remove(myQRcode)                                                                     ' Cleanup if necessary.
 
  myTemporaryFile = createUnoService("com.sun.star.util.PathSettings").temp & "/qrcode.svg"
       mylineCode = " -m 1 -o """ & convertFromUrl(myTemporaryFile) & """ -t SVG """ & myCellText.string & """"
 
  shell ("qrencode", 0, mylineCode)
 
  ' Insert the temporary file with the dispatcher. The only solution for INCORPORATE the SVG.
 
         myDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
      myDispatchFrame = thisComponent.currentController.frame
 
  myArguments(0).name = "ToPoint"    : myArguments(0).value = myCellQR.absoluteName
  myDispatcher.executeDispatch(myDispatchFrame, ".uno:GoToCell"     , "", 0, myArguments())
 
  myArguments(0).name = "FileName"   : myArguments(0).value = myTemporaryFile
  myArguments(1).name = "FilterName" : myArguments(1).value = "SVG - Scalable Vector Graphics"
  myArguments(2).name = "AsLink"     : myArguments(2).value = false
  myDispatcher.executeDispatch(myDispatchFrame, ".uno:InsertGraphic", "", 0, myArguments())
  
             myQRcode = mySheet.drawPage.getByIndex(mySheet.drawPage.count - 1)                                                      ' The last object inserted (hopely...).
      myQRcode.anchor = myCellQR
        myQRcode.name = myQRcodeName
 
  myArguments(0).name = "ToPoint"    : myArguments(0).value = myMemoCell
  myDispatcher.executeDispatch(myDispatchFrame, ".uno:GoToCell"     , "", 0, myArguments())
 
  kill myTemporaryFile                                                                                                               ' Be clean in the temporary directory.
 End Sub
 
 
 ' ╔═══════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
 ' ║ Extracts the values of variables (text) passed in argument with the URL call.                                 ║█
 ' ║ https://forum.openoffice.org/en/forum/viewtopic.php?f=45&t=46391&p=214392&hilit=getArgumentfromurl#p214392    ║█
 ' ╚═══════════════════════════════════════════════════════════════════════════════════════════════════════════════╝█
 '  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
 
 Function getArgumentFromURL(sURL$, sName$) As String
  On Error Goto exitErr:
 
  Dim iStart%, i%, l%, sArgs$, a()
  
  iStart = instr(sURL, "?")
       l = len(sName)
 
  If (iStart = 0) or (l = 0) Then Exit Function
 
  sArgs = mid(sURL, iStart + 1)                                                                                                      ' sArgs behind "?".
 
  a() = split(sArgs, "&")
 
  For i = 0 To uBound(a())
   If instr(1, a(i), sName & "=", 1) = 1 Then                                                                                        ' Not case sensitive.
    getArgumentFromURL = Mid(a(i), l + 2)
    Exit for
   Endif
  Next i
 
  exitErr:                                                                                                                           ' Return "".
 End Function
 
 
 ' ╔═══════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
 ' ║ Find an object in a drawpage by its name. By Bernard Marcelly. Annexe B, page 859.                            ║█
 ' ║ https://www.editions-eyrolles.com/Archive/9782212125221/programmation-openoffice-org-3-macros-ooobasic-et-api ║█
 ' ╚═══════════════════════════════════════════════════════════════════════════════════════════════════════════════╝█
 '  ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
 
 Function findObjectByName(myPage As Object, objectName As String, Optional myService As String) As Object
  Dim objX As Object
  Dim    x As Long
 
  For x = 0 To myPage.Count - 1
   objX = myPage(x)
 
   If objX.Name = objectName Then
    If IsMissing(myService) Then
     findObjectByName = objX                                                                                                         ' Find object.
     Exit Function
    Else
     If objX.supportsmyService(myService) then
      findObjectByName = objX                                                                                                        ' Find object.
      Exit Function
     End If
    End If
   End If
  Next x
 End Function                                                                                                                        ' Return Null if fail.
Attachments
QRcodeSpreadsheets.ods
Need the program qrencode in system.
(58.44 KiB) Downloaded 701 times
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
Post Reply