Create barcode 1D / QR-code 2D in Calc

[Albireo]

Hello!
Trying to create 1D- / 2D-codes in LibreOffice Calc.
(hope it works the same way in OpenOffice Calc)

Barcodes (1D-code)
I installed a font "Code128" (But the wish is Code 128B which can handle more characters eg. Swedish åäö)
Yes, if the font is used in a cell, it becomes a visible barcode, but it cannot be read with a scanner.
The font - Code128 - I found, can't handle ÅÄÖ.
Is it somethong wrong with the font?
Or some other configuration in LO?

_______________________

QR-code (2D-code)
Are these codes more difficult to create in Calc?
How to solve this desire?

Maybe, QR-code is a specific 2D-code?
I have tested PDF417 and Datamatrix - both can handle swedich ÅÄÖ

[Villeroy]

viewtopic.php?t=103924&p=503253#p503253

[Mountaineer]

Hello,

a Barcode-Font shows the right lines for the used characters, but it will no do any encoding.

If I read the text from the following site
https://softmatic.com/de/barcode-code-128.html you need to generate an additional character for error-checking.

Also mentioned is the possible coding of undefined characters with an introducing char.
So you need special encoding for special characters.

And as mentioned there you wont need another font for 128B...

The other option is to generate an image with tha bar- or qr-code to be embedded in your text.
For this you could follow Villeroys link.

J.

[Albireo]

Thank you!
If I understood correctly, it is not easy, and I have to create my own routine for barcodes / QR codes.
I found an extension (LibreOffice) Code 128B It works, but do not understand if I can use it for my wishes.


On Wikipedia - Code 128 - specification the structure of a barcode is described quite well.
The QR-code has a more complicated structure QR-code design

[Villeroy]

If I understood correctly, it is not easy, and I have to create my own routine for barcodes / QR codes.

As my database demo demonstrates, you do not have to create your own routine. Extension "QR Code Generator JS" does the job for me. My database collects URLs, writes svg graphics for these URLs into a directory and displays them in a report. I did not even look at the JavaScript. I installed the service and looked at the StarBasic demo where Function QrCode(sText, sPath, sColor, nSize) is the working routine.
There is a tight limitation in URL lengths (60 I think). For base URLs ("home pages") it should be good enough.

[Albireo]

Thanks for your tiime (but I'm not so good on this.)
I can't try QR.odb because many errors eg. "Could not connect to data source "QR"." - I don't have any database in this case.

I have tried to understand the content of the link Printing QR codes out of database - but do not understand how I would make it work (or test)

[Villeroy]

-------- Install once -----------
1) Install the QR code extension and restart the office.
2) Tools>Options>Security, button [Macro Security...]
Security level = Highest
On tab "Trusted sources" you add one or more directories where you want to put documents with trusted code embedded (trust me!). It must not be your downloads directory but your documents directory would be fine.
3) Save this new database document in a trusted directory. Any subdirectory of a trusted directory works as well.
4) Add a directory named "qr" in the same directory where you put the document. This is where the pictures go.
--------- Use ----------------
5) Open the database document. It has 4 containers for tables, queries, forms and reports. Ignore tables and queries. Open the only form "QR" and push the button. This generates QR codes for the entered URLs and shows them in the square as you navigate the rows.
6) Add more URLs (max. 62 characters each, I think), push the save button and and then the write button again. It will add more QR codes to the qr subdirectory.
7) Now go back to the database main window, click "Reports" and open the contained report. A report is a printable document filled with database data.

[Albireo]

Thank you!
"1) Install the QR code extension and restart the office."
Stuck already here.
Is QR code extension built into OpenOffice?

a) Is there any extension for LibreOffice (or just for OpenOffice)?
b) I have the Swedish version of LO (which complicates it somewhat). (freely translated)
- I selected .: Tools / Extensions
- I press .: More extensions online
and Created a search "QR code" and got this result.: LO Extensions - QR code
One result is shown goQR.me Integration (But does not seem to work so well - this extension comes from China)

A barcode generator for OpenOffice / LibreOffice / MS Office (But I'm not sure how to use it).
This is an QRCode generator for OpenOffice (I do not know if it can be used by LibreOffice)
and this is only for MS Office QR Code Generator Add-In in Excel Spreadsheet (only for Excel and not free)

[robleyd]

Try https://extensions.openoffice.org/en/pr ... enerator-0 - I believe that is the extension Villeroy refers to.

[Villeroy]

Try https://extensions.openoffice.org/en/pr ... enerator-0 - I believe that is the extension Villeroy refers to.

Thank you.

[Albireo]

(Installed also OpenOffice ) and tested the proposal .: QRCode Generator for OpenOffice
Failed to install this extension in OpenOffice. (some JRE problem)
Did the same thing with LibreOffice and got no installation issues.

In the toolbar under "Insert" there is the option "QR-Code Js ..."
Selecting that option, opens a message box "Error / Only text can be selected / OK"
After "OK" is pressed, a message "QR-Code Text content:" opens with an editable field.
Now It is possible to enter a message for the 2D-code and confirm with "OK"
And a valid 2D-code is created. (good)

But.. I do not know how to use the QR-extension.
(It works in a way, but only with manual handling)

Do not know how the message in a cell (Calc) should be able to generate a 2D code.
Do not know if it is possible to write a text with several lines.
If a text contains "é" (Pensé) or Swedish characters such as ÅÄÖ / åäö. no 2D code is created - nothing happens at all.

Right now it feels difficult to get a dynamic 2D code from a cell in Calc. (eg. If the value in a cell is changed, the 2D code changes)

[Villeroy]

It does not work with Calc. Calc is the least suitable application to store qr codes which is why I made my little database.
In Writer, Impress, Draw select an URL and call Insert>QR code... The selected URL will be replaced by a graphic. If you don't select an URL, an input box pops up where you enter the URL.
In my database you open the form, the URL column, the save button and the write button.

[pharmankur]

Hello!
Trying to create 1D- / 2D-codes in LibreOffice Calc.
(hope it works the same way in OpenOffice Calc)

Barcodes (1D-code)
I installed a font "Code128" (But the wish is Code 128B which can handle more characters eg. Swedish åäö)
Yes, if the font is used in a cell, it becomes a visible barcode, but it cannot be read with a scanner.
The font - Code128 - I found, can't handle ÅÄÖ.
Is it somethong wrong with the font?
Or some other configuration in LO?

_______________________

QR-code (2D-code)
Are these codes more difficult to create in Calc?
How to solve this desire?

Maybe, QR-code is a specific 2D-code?
I have tested PDF417 and Datamatrix - both can handle swedich ÅÄÖ

@Albireo
Generating bar / QR codes based on cell value is possible and easy [ all you have to do is copy paste macro present in demo file to your desired file ].

with the reference of , Madeta a.s. Jiří Gabriel
https://stackoverflow.com/questions/161 ... 9#31663859

Dynamic QR code can be generated by custom function "EncodeBarcode" [ set in macro - Barcody ]
The usage is as follows:
=EncodeBarcode(CELL("SHEET"),CELL("ADDRESS"),A2,51,1,0,2)
Leave the CELL("SHEET) and CELL("ADDRESS") as they are since it's just giving reference to the worksheet and cell address you have the formula
A2 is the cell that you have your string to be encoded. In my case it's cell A2 You can pass "Text" with quotes to do the same. Having the cell makes it more dynamic
51 is the option for QR Code. Other options are 1=EAN8/13/UPCA/UPCE, 2=two of five interleaved, 3=Code39, 50=Data Matrix, 51=QRCode
1 is for graphical mode. The barcode is drawn on a Shape object. 0 for font mode. I assume you need to have the font type installed. Not as useful.
0 is the parameter for the particular barcode type. For QR_Code, 0=Low Error Correction, 1=Medium Error Correction, 2=Quartile error correction, 3=high error correction.
2 only applies to 1D codes. It's the buffer zones / Protection zone for 1D codes (width)

Once the QR code is generated, select and Drag the QR code image to the desired size
Next time you will get the QRCode of the set size.

Important Note -
ENCODEBARCODE has a limitation.
This works only if input is a text / alphanumeric.
This does not work for absolute numbers (but works for decimals)
Therefore in the formula we have to convert a number in a text with a preceding SPACE [ IF(ISNUMBER(J5),TEXT(H5,"@")&" ",H5) ]
OR convert the number in a decimal [ IF(ISNUMBER(H5),TEXT(H5,"0.00"),H5) ]
Resultant QR will show the number but will have a space at the end or with decimal number like 53.00

Find the example file enclosed.

EncodeBarcode_Function.ods

Demo EncodeBarcode

(49.45 KiB) Downloaded 138 times

Hope it helps you !

[Albireo]

Thanks! (exiting solution)

I'm not used to working with Macron and do not know how to make this work for me.

First I was upgrade Open Office to 4.1.11 (32-bit Swedish version - Windows 10)
Then JRE was missing in OO... (Had trouble finding Java 32-bit) - But in the end I chose this place and version .: https://www.filehorse.com/download-java-runtime-32/

When I was opened your test file, this message was displayed (freely translated)
"This document was created by a newer version of OpenOffice.
it may contain features not supported by your current version. (OK!)"
Then the spreadsheet opens with three QR-codes. (great)

Changed the content (in the yellow fields). But the 2D code does not change.
Will the 2D-code change dynamically?

With Libre Office 7.1.5.2 (x64), the document was opened without problems (but even here the QR-codes are not updated)

Have more thoughts, but want to make the test QR-spreadsheet to work.
Have I missed something to make this spreadsheet work?

[Villeroy]

But why does it have to be a spreadsheet? This is absurd. 100 QR-codes bloat the document by ~4MB and you can not even sort or filter the cells properly without spoiling the relative positions to the anchor cells. A spreadsheet is an arithmetic calculator in the first place. Any pictures are mere decoration or illustration but you look at a pictures as part of an record set. A spreadsheet has very poor database capabilities.

[Albireo]

Thanks (@Villeroy) and every one!

Tried to follow your example, but could not get it to work. (No idea why)
_________________________________________________

Absolutely - a database is better (in many ways).
Last time I tested OO / LO database management, I encountered problems "everywhere" - came to the conclusion that this was not fully developed.
(maybe better now?)

Have CSV-files that I want to manage.
Creating a formatted spreadsheet with an API is not difficult, and seems to work well.

Have no idea if the same thing can be done in OO / LO Base.
Assume a list of the following fields in a CSV-file .: QR information ; image name ; Article number ; Name ; quantity ; Price ; Amount

I do not think it is difficult to insert an image (from the imgae name) in a spreadsheet via an API.
(the option in this case would have been to create an image of the QR code with an external program - but have not found any extern program as create the QR-code to an Image)

Another example is to be able to create a document/spreadsheet that suits me from a PDF file.
Reading and interpreting the contents of the PDF file is not difficult.
But creating a formatted result is more difficult (with a QR-code).

[pharmankur]

Changed the content (in the yellow fields). But the 2D code does not change.
Will the 2D-code change dynamically?
With Libre Office 7.1.5.2 (x64), the document was opened without problems (but even here the QR-codes are not updated)
Have more thoughts, but want to make the test QR-spreadsheet to work.
Have I missed something to make this spreadsheet work?

Your macros are not having permissions, hence its not changing automatically.
To enable it, adjust macro security level to medium / low [ Tools-Options-Security--Macro Security- Adjust to desired level - Apply]
Now save and close the file.
Upon reopening you will find QRcodes will be dynamically updated in response to the text in yellow cells.

To copy this functionality to your existing file, copy the macro from demo file.

You can view actual macro go to - Tools-Macros-Edit Macros - open tree (Under EncodeBarcode_Function.ods) - Standard - (Double click) Barcody
Now at right side you can see actual macro (around 2380 lines).

Copy this code and paste to macro in your existing file where you have to use formula ENCODEBARCODE.

--- There might be some apprehensions for reducing security level of macro. But its ok if you are in control and you don't randomly open files from unknown sources.
I am using it in my office work for last 5 years and I absolutely love flexibility and ease of use provided by CALC.
Although database solution might be better one, I did not have time to learn it and CALC does my job exactly the way I want.

Enjoy !

[MrProgrammer]

have not found any extern program as create the QR-code to an Image

libqrencode which includes the command line program qrencode to generate PNG (and other) images

[Villeroy]

Have CSV-files that I want to manage. Creating a formatted spreadsheet with an API is not difficult, and seems to work well.

CSV is a database exchange format and Calc is a very poor database surrogate.

[Albireo]

... adjust macro security level to medium / low [ Tools-Options-Security--Macro Security- Adjust to desired level - Apply]
Now save and close the file. ...

Yes! - Thank you! it works with (it even works with LibreOffice)
But find no difference between the three QR-codes. This test text ("Åäö & ÅÄÖ - Works - 9870") works with all three QR codes.

... To copy this functionality to your existing file, copy the macro from demo file.
You can view actual macro go to - Tools-Macros-Edit Macros - open tree (Under EncodeBarcode_Function.ods) - Standard - (Double click) Barcody
Now at right side you can see actual macro (around 2380 lines).
Copy this code and paste to macro in your existing file where you have to use formula ENCODEBARCODE.

No... (Edit Macros - is missing)
I go to Tools-Macros but here I only have .: (freely translated from Swedish)

• Record macro
  Run macro
  Organize macros
  Digital signature ...
  Arrange dialogs ...

... libqrencode which includes the command line program qrencode to generate PNG (and other) images

This program seems to be developed for Linux. (not Windows)
Searched further based on this tip and found .: SimpleCodeGenerator - NirSoft
Can be controlled with commands, and can create an image with QR code. Only an exe-file is required.
Also found a nice link that describes what QR codes can be used for (more than storing alphanumeric text).
Barcode Contents With content such as .:

• E-mail address
  Telephone numbers
  vCard
  SMS
  MMS
  FaceTime
  Maps, Geographic information
  Wi-Fi Network config

... CSV is a database exchange format and Calc is a very poor database surrogate.

Agree - to some extent
In my case, a number of columns are created in Calc from csv-files and experience a fast result.
But, would have liked to have used Base for the same thing (if possible).

-------- Install once -----------
1) Install the QR code extension and restart the office.
2) Tools>Options>Security, button [Macro Security...]
Security level = Highest
On tab "Trusted sources" you add one or more directories where you want to put documents with trusted code embedded (trust me!). It must not be your downloads directory but your documents directory would be fine.
3) Save this new database document in a trusted directory. Any subdirectory of a trusted directory works as well.
4) Add a directory named "qr" in the same directory where you put the document. This is where the pictures go.
--------- Use ----------------
5) Open the database document. It has 4 containers for tables, queries, forms and reports. Ignore tables and queries. Open the only form "QR" and push the button. This generates QR codes for the entered URLs and shows them in the square as you navigate the rows.
6) Add more URLs (max. 62 characters each, I think), push the save button and and then the write button again. It will add more QR codes to the qr subdirectory.
7) Now go back to the database main window, click "Reports" and open the contained report. A report is a printable document filled with database data.

Tried to understand / test this guided tour - but does it contain all the steps? (Do not know why it does not work for me)

[pharmankur]

Yes! - Thank you! it works with (it even works with LibreOffice)
But find no difference between the three QR-codes. This test text ("Åäö & ÅÄÖ - Works - 9870") works with all three QR codes.

Did not get you.
It works or doest work ?
What problems you are facing exactly ?
Have you closed and reopened libreoffice after macro security settings modification ?
Have you observed formula in cell where QR code is displayed ?
Have you read and understood the things mentioned under "Important Notes?"

If you have put SAME text " Åäö & ÅÄÖ - Works - 9870 " in all 3 QR codes , it is supposed to give SAME output

Edit Macros ... Missing

In some old Oo (OpenOffice) versions the option is inside organise macros. Dig deep into all option or Google how to edit Macros for your specific version of Oo
.... Although I have never tried Oo and am not sure if this macro works there.

And try to share the file or screenshot of issues you are facing with.

[Albireo]

...It works or doest work ?
What problems you are facing exactly ?...

I was expecting restrictions like:

• This works only if input is a text / alphanumeric.
• This does not work for absolute numbers (but works for decimals)
  Therefore in the formula we have to convert a number in a text with a preceding SPACE [ IF(ISNUMBER(J5),TEXT(H5,"@")&" ",H5) ]
  OR convert the number in a decimal [ IF(ISNUMBER(N5),TEXT(N5,"0.00"),N5) ]
  Resultant QR will show the number but will have a space at the end or with decimal number like 53.00

(I do not understand what these restrictions mean for me
I thought the three QR codes would handle different content with different restrictions)

In some old Oo (OpenOffice) versions the option is inside organise macros. Dig deep into all option or Google how to edit Macros for your specific version of Oo

OK! there seem to be others with the same problem. Not able to Edit macros ( I have Win10 / OpenOffice 4.1.11 Swedish (latest version) )

And try to share the file or screenshot of issues you are facing with.

[pharmankur]

By using custom function ENCODEBARCODE, the QRcode will get generated in e.g. cell E5 when you type formula e.g.
(in cell E5 ) =ENCODEBARCODE(CELL("SHEET"),CELL("ADDRESS"),B5,51,1,0,2)
where B5 has got the input string you need to convert to QRCode.

Now for original functionality of formula ENCODEBARCODE one limitation is there, which is, ENCODEBARCODE converts Text / Alphanumeric input but cannot convert pure number input to QRCode.
It means (now I am feeling like I am a primary school teacher ) ...
(again for above example) ....
if B5 has something like , ABCD123 ... It will work
if B5 has something like , ABCD ... It will work

This works only if input is a text / alphanumeric.

if B5 has something like , 123 ... It will NOT work
BUT ... if B5 has something like , 123.00 ... It will work (it's called DECIMAL)

This does not work for absolute numbers (but works for decimals)

So the workaround for number problem provided in demo file is

Therefore in the formula we have to convert a number in a text with a preceding SPACE [ IF(ISNUMBER(H5),TEXT(H5,"@")&" ",H5) ]
OR convert the number in a decimal [ IF(ISNUMBER(N5),TEXT(N5,"0.00"),N5) ]
Resultant QR will show the number but will have a space at the end or with decimal number like 53.00

So your (Again our example) formula becomes e.g.
=ENCODEBARCODE(CELL("SHEET"),CELL("ADDRESS"),IF(ISNUMBER(B5),TEXT(B5,"@")&" ",B5),51,1,0,2)
OR
=ENCODEBARCODE(CELL("SHEET"),CELL("ADDRESS"),IF(ISNUMBER(B5),TEXT(B5,"0.00"),B5),51,1,0,2)

Resultant QR will show the number but will have a space at the end or with decimal number like 53.00

... I hope, you at least understand how to construct spreadsheet formula !

AND using ENCODEBARCODE is all worthy if you need a DYNAMIC QRCode. i.e. a value in a cell reference to be converted for QRCode.

If you just want QRCode ...
Install latest LIBREOFFICE Version ... Open Calc ...
Go to cell where you want to put QRCode ... Go to ... Insert - Object - QR Code --- In URL/Text type string ... Click OK ...
Your QR is ready ! ... and guess what ... It convert Numbers as Well to QRCode. No restrictions like ENCODEBARCODE !

BTW, did not understand reason of attaching screenshot ... What is the problem out there ?

[MrProgrammer]

This program seems to be developed for Linux. (not Windows)

This is in my qrencode link. I installed it (on a Mac) using Homebrew. You should be able to use it on Windows, if you like.

Edit: I was able to call it from a macro in Calc: Proof of concept: Generate QR code in Calc