Add 1D barcode images based on cell contents

[joshua4]

My goal is to take one Calc sheet that has a list of alphanumeric serial numbers and be able to generate another sheet in Calc (or Writer) that will have barcode images of those serial numbers. I want to print the barcodes on Avery labels. Naturally, the barcode images will need to have human-readable captions so they can be applied to equipment properly.

I’ll be using Zebra/Symbol LS2208 units for reading the barcodes, so I think I’m wanting Code 128 1D barcodes.

LO Calc can create a barcode as a one-off image using Insert>Object>Barcode… Unfortunately the UNO dispatch behind this does not seem to support automation. I have only been able to launch the UI dialog, if anything at all, with the Dispatcher commands uno:EditQrCode or uno:InsertQrCode.

@elmau has graciously answered @Dilip with something very close via Python. The Python is fine, but I would like to avoid having to generate the barcodes as separate files, even if I am loading them back into the document under automation.

[elmau]

I assume that you already know Python and that you know how to install libraries, for this case python-barcode.

import uno
import io
from barcode import EAN13
from barcode.writer import SVGWriter
from com.sun.star.beans import PropertyValue
from com.sun.star.awt import Size

CTX = uno.getComponentContext()
SM = CTX.getServiceManager()


def create_instance(name, with_context=False):
    if with_context:
        instance = SM.createInstanceWithContext(name, CTX)
    else:
        instance = SM.createInstance(name)
    return instance


def get_stream(buffer):
    service = 'com.sun.star.io.SequenceInputStream'
    stream = create_instance(service, True)
    stream.initialize((uno.ByteSequence(buffer.getvalue()),))
    return stream


def main():
    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.CurrentController.ActiveSheet
    cell = sheet['A1']

    data = str(cell.String)
    in_memory = io.BytesIO()
    EAN13(data, writer=SVGWriter()).write(in_memory)
    stream = get_stream(in_memory)

    image = doc.createInstance('com.sun.star.drawing.GraphicObjectShape')
    gp = create_instance('com.sun.star.graphic.GraphicProvider')
    properties = (PropertyValue(Name='InputStream', Value=stream),)
    image.Graphic = gp.queryGraphic(properties)

    sheet.DrawPage.add(image)
    image.Size = cell.Size

    return

Cell A1

Result:

[joshua4]

Well, about that Python… . I’m not really worried. This is probably me moving from BASIC to Python in a significant way. I’m used to Eclipse for Java development (with Google APIs and Selenium). If PyDev for Eclipse isn’t terrible I’d likely use that. Do you have any advice? I’ve just never done vim.

BTW: Thanks for your

@elmau

sudo apt install libreoffice-script-provider-python

That would have been easy to miss setting up Python/APSO on my Ubuntu system.

[elmau]

Learn Python.

[Ratslinger]

Ran without issue. Just installed python-barcode and ran code using APSO:

.
Edit:
Runs fine without APSO also.

[joshua4]

@elmau, yes! I wasn’t questioning learning Python, but using Eclipse for Python via PyDev to stay with a familiar editor. Or did you mean “learn vim”? Anyway, it’s getting off topic…I’ll post a final result once I get a chance to try your solution with the barcode readers. Thank you very much!

[joshua4]

Works perfectly. LS2208 barcode reader was immediately recognized by Ubuntu and scanned Code128 alphanumeric codes printed using @elmau’s system.

Here are some notes to reduce fear for anyone wanting to try this:

1. To use the Python code without the APSO add-in, see vCard QR code using Libre Office as an example case. You do not need to know Python for this.
2. APSO is a Python script manager, sort of like the built-in BASIC macro editing system. To use the Python code with the APSO add-in install APSO (APSO - Alternative Script Organizer for Python » Extensions) then go back to the add-in manager and set the text editor of choice as the APSO editor (Tools>Extension Manager>Click on APSO>Options). Now it’s as easy as using BASIC macros, if not easier. To edit with APSO go to Tools>Macros>Organize Python Scripts. Don’t forget to try right-clicking things…the rest is obvious.
3. One can use Code128 instead of EAN13 in the Python code to use the alpha-capable Code 128 system.
4. The LS2208 used here is an industry workhorse. It likely represents most simple 1D barcode scanners.
5. Default scanner settings automatically added a carriage return, and no further settings were necessary. (No need to scan control barcodes to setup the scanner.) Because of the carriage return, it is possible to read in codes down a column or into any highlighted area of a spreadsheet just by pulling the trigger of the scanner.
6. The LS2208 is a laser-based (not camera-based) scanner. Unsurprisingly, it did not scan from an LCD display.
7. The shape of the SVG barcode graphic is determined for Calc, not for the SVG itself. If you copy-and-paste the barcodes individually then they may change size and shape. However, if you have several on a page, select one of them and press Ctrl+a, Ctrl+c, then all can be pasted as a graphic (say, in Writer) retaining shapes.
8. Curiously, one can add text to the image by clicking on a barcode and starting to type. By pressing Enter a few times it is possible to add extra text below barcodes that will copy with the barcodes themselves.