Bug 145358 - Dynamic QR / Barcode creation using spreadsheet cell reference
Summary: Dynamic QR / Barcode creation using spreadsheet cell reference
Status: UNCONFIRMED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: LibreOffice (show other bugs)
Version:
(earliest affected)
7.2.2.2 release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: QR-code
  Show dependency treegraph
 
Reported: 2021-10-28 09:09 UTC by pharmankur
Modified: 2022-12-08 15:29 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Demo of QR / Barcode auto generation from referenced cell (48.62 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-10-28 09:11 UTC, pharmankur
Details

Note You need to log in before you can comment on or make changes to this bug.
Description pharmankur 2021-10-28 09:09:53 UTC
Description:
Presently QR code can be "Inserted" from Menu. There are some updates going on at Library level for generating QR / BAR CODES [ Ref - https://bugs.documentfoundation.org/show_bug.cgi?id=139778 ] as seen from " Bug 139778 - Switch to ZXing-C++ library for generating QR codes  "

But all these methods require manual typing of the individual QR code text and then insert it in document. This is not comfortable for REAL use where whole list of texts needs to be converted dynamically in individual QR / Barcodes  

Is it possible in generate QR code dynamically using sheet cell reference ?

There is similar project "Barcody". The original 2013 version by Jiri Gabriel contained files for LibreOffice and MSOffice
Its uses code from  - http://stackoverflow.com/questions/16143331/generating-2d-pdf417-or-qr-barcodes-using-excel-vba/31663859#31663859
Also enclosing working barcody.ods vervion of sample file which demonstrates the functionality I was describing.

But this rendering of QR/ Barcodes needs to create custom formula / function and is not easy for average user to easily integrate it in the working document.

So now if Libreoffice has already got the IN-BUILT ability to generate QR Barcode, We must further extend it's function to use it directly through some spreadsheet formula or BASIC library in MACRO.

If its done, it can be a great help to everyone.

Steps to Reproduce:
Every barcode text needs to type individually from Insert--> Object --> QR Code.
Then we have to manually move the QR code image to required position.

Actual Results:
It generates QR / Bar codes but not in a useful bulk

Expected Results:
We should get Barcodes dynamically based on text appearing in the specified cells and also QR Code need to get placed in the cells specified


Reproducible: Always


User Profile Reset: No



Additional Info:
Please see attachment Barcody.ods for demo of the unction required.
Comment 1 pharmankur 2021-10-28 09:11:31 UTC
Created attachment 175969 [details]
Demo of QR / Barcode auto generation from referenced cell
Comment 2 Heiko Tietze 2021-11-08 14:21:22 UTC
Formulae require to be defined in the file format with cross-plattform and -application compatibility. So it's not working out of the box. And ultimately I don't see a good use case for this as QR or barcodes don't need to be generated in large numbers. Maybe for special cases but then you can run a macro.
Comment 3 Heiko Tietze 2021-11-11 15:03:36 UTC
The ticket was on the agenda of the design meeting but didnt receive more input. So let's close as WF based on arguments in comment 2.
Comment 4 pharmankur 2021-11-12 06:49:23 UTC
(In reply to Heiko Tietze from comment #3)
> The ticket was on the agenda of the design meeting but didnt receive more
> input. So let's close as WF based on arguments in comment 2.

(In reply to Heiko Tietze from comment #2)
> Formulae require to be defined in the file format with cross-plattform and
> -application compatibility. So it's not working out of the box. And
> ultimately I don't see a good use case for this as QR or barcodes don't need
> to be generated in large numbers. Maybe for special cases but then you can
> run a macro.

Hi, thanks for considering the case.
I was on Diwali Vacation so could not post reply earlier.

It is true that PRESENTLY there are not many use cases of Barcode/QRCode formula feature. But it is just because such feature DOES NOT EXIST in any office suites !
( Baby is yet not bourn and we are discussing how it won't change the world :P )

Just consider the fact that the world is now flooded with smart phones or scanners with QR reading ability and data entry point using QR / barcode is getting status of basic necessity. QR / Barcode generation ability with cell reference formula can add excellent flexibility and ease to Libreoffice Calc and help to penetrate its use.

Don't wait for MSoffice to add this functionality and then LO will copy it. This is a good feature to show LO's unique benefit and distinction from others.

If the feature is a reality there are tonnes of uses can be built around it in sectors like education / industry / retail / banking etc ... wherever data in the cells needs to get converted dynamically to QR / Barcode for getting back to other systems ... few wild examples of use...

Education - 
1) Library - List of books details >> QR >> Stickers >> Automate book management
2) Students - - List of Student details >> QR >> Student ID >> Automate attendance / school portal access
3) Assets - List of Asset-Furniture-Lab equipment details >> QR >> Asset ID sticker >> Automate audits / maintenance procedures
.. etc ...

Industry / Retail / Banking -
1) Inventory - List of material details >> QR >> Material ID Sticker >> Automate Material Issue / Costing / Stock etc
2) Employee / Guest Management - - List of personal details >> QR >> ID batch print  >> Automate attendance / access
3) Invoice / Internal Documents referencing - Invoice / Document details >> QR >> Invoice QR >> Automate order tracking / Payment / Document tracking
4) Assets - List of Assets-equipment details >> QR >> Asset ID sticker >> Automate audits / maintenance procedures
5) Serialisation - List of production line items >> QR >> Item ID sticker >> Automate Item tracking / warranty / track and tracing
5) MailMerge with autogenerated QR - Mailmerge Address details >> QR >> QR Printed along side of Mailmerge document / envelope address >> Automate letter issue / track and tracing
... etc many ... many...

Basically it will help last user to increase productivity without learning / using complicated ERPs OR if ERPs already there, it will be easier to integrate with ERP. As in every organisation even if EPRs exists, employees any how prefer to maintain their own data / records in some sort of Word / Excel documents.

Thus in my view adding this functionality in LibreOffice will be extremely helpful for users. Just add it and prepare good tutorial around it and people will find out newer usage of it we have not imagined yet.

AND it is perfectly OK to ignore this request as well if others feel so !
Comment 5 Heiko Tietze 2021-11-12 09:11:56 UTC
All good examples but not for a formula like =qrcode(a2;JAB). This formula is not standardized. And I'd expect a solution rather similar to the label wizard https://help.libreoffice.org/7.2/en-US/text/shared/guide/labels.html

(In reply to pharmankur from comment #4)
> AND it is perfectly OK to ignore this request as well if others feel so !

It's also perfectly okay to reopen the ticket :-)
Comment 6 pharmankur 2021-12-24 04:40:33 UTC
Recently read this comment on forum requesting Dynamic QR code feature ...

https://forum.openoffice.org/en/forum/viewtopic.php?f=47&t=106330
Comment 7 Heiko Tietze 2022-12-08 07:18:51 UTC
Changed my mind and we should make this available somehow. But I wonder if this is a duplicate of bug 138856.