Proof of concept: Generate QR code in Calc

Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This section is not for asking questions about writing your own macros.
Locked
User avatar
MrProgrammer
Moderator
Posts: 5099
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Proof of concept: Generate QR code in Calc

Post by MrProgrammer »

The attached spreadsheet uses the text in cell QR1 to generate a QR code in cells A2:BL65. After supplying the text in that cell, say the address of this web page, use Tools → Macros → Run Macro → QR1.ods → Standard → Module1 → QR1 → Run. The attachment has one sheet, but the macro would process every sheet if there are multiple.

It requires installation of the qrencode package, which is available for MacOS, Linux, and Windows. Line 2 of the program specifies its location in the file system. The qrencode command typically creates PNG images for the QR code, but the -t ASCII option asks it to generate plain text. A Line Input statement reads this text and sets 0 or 1 values in A2:BL65. The QR code is made visible with conditional formatting, and my QR code scanner can read it to display this page.

I am calling this program a "Proof of Concept". I just wanted to see if the idea works. The program doesn't have the kind of validation and error checking that it should. It works for me, and I hope for others, but there are probably ways to improve the idea to make it easier to use, perhaps as an extension. The code below is contained in the attachment, but is repeated here so it can be viewed without downloading the file. Please post improvements, questions, or comments about the concept in a separate topic to avoid cluttering this one with side-discussions. I can merge or link any significant changes here later.
Generated QR code
Generated QR code
Sub QR1 ' Create QR code in A2:BL65 from text in cell QR1
Const QRencode = "/usr/local/bin/qrencode" ' https://fukuchi.org/works/qrencode/
Const QOptions = "-s 1 -m 0 -t ASCII -o "  ' -s «dots» -m «margin» -t «outtype» -o «outfile»
Const Numbers  = 1                         ' com.sun.star.sheet.CellFlags.VALUE
Dim Col   As Integer : Dim File  As Integer : Dim Line  As String
Dim Parm  As String  : Dim Pixel As String  : Dim Row   As Integer
Dim Sheet As Object  : Dim Temp  As String  : Dim Text  As String
Temp = ENVIRON("TMPDIR") & FORMAT(RND()*1E15,"000000000000000") & ".txt"
File = FREEFILE() : Pixel = SPACE(2)
For Each Sheet In ThisComponent.Sheets
   Text = Sheet.getCellByPosition(459,0).String      ' Cell QR1
   If LEN(Text) > 0 Then
      Parm = QOptions & Temp & " " & Text       ' Options and text to encode
      SHELL(QRencode,6,Parm,TRUE)               ' Run program in minimized window and wait
      Sheet.getCellRangeByPosition(0,1,64,65).clearContents(Numbers) ' Clear A2:BL65
      Open Temp For Input As #File : Row = 0
      While Not EOF(File)
         Line Input #File, Line : Row = Row + 1                      ' Read row of QR code
         For Col = 0 To LEN(Line)/2-1                                ' 1-row 2-column pixels
            LSet Pixel = Line : Mid Line,1,2,""                      ' Split first 2 bytes
            Sheet.getCellByPosition(Col,Row).Value = ABS(Pixel="##") ' Set cell to 0 or 1
         Next Col
      Wend ' While more lines
      Close #File
   End If
Next ' Sheet
If FILEEXISTS(Temp) Then Kill Temp
End Sub
Attachments
QR1.ods
Spreadsheet and program
(12.2 KiB) Downloaded 509 times
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.7, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Locked