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 !
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.