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.
   

[Giuliano1969]

Update for April 2025
:
Solution a- CODE EAN13)

1. Have a cell with a VALID EAN13 code (i.e.=12numbers+1check num=13numbers).
2. apply the Google ean13 ttf font from https://fonts.google.com/specimen/Libre+Barcode+EAN13+Text

Solution B- CODE128B)
1)have your product code in a cell (e.g. A1)
2)apply the function below to generate a valid code128b code (w/chk digit) (e.g. in cell A2 have =Code128B(A1) ).
3)apply the Google barcode128 ttf font : https://fonts.google.com/specimen/Libre+Barcode+128
Create this macro in LibreofficeBASIC:

	
	Function Code128B(sText As String) As String
    Dim codeValues(127) As Integer
    Dim i As Integer, sum As Integer
    Dim checkChar As Integer
    Dim encoded As String
    Dim c As String

    ' Mappa ASCII (Code Set B)
    For i = 32 To 126
        codeValues(i) = i - 32
    Next i

    ' Start Code B = 104
    sum = 104
    For i = 1 To Len(sText)
        c = Mid(sText, i, 1)
        sum = sum + (codeValues(Asc(c)) * i)
    Next i

    checkChar = sum Mod 103

    ' Convert Code128 values to characters using font
    ' Start Code B = CHR(204)
    ' Check character and stop = based on the Code128 font used
    encoded = Chr(204) ' Start Code B
    encoded = encoded & sText
    encoded = encoded & Chr(Code128CharFromValue(checkChar))
    encoded = encoded & Chr(206) ' Stop Code

    Code128B = encoded
	End Function


	
' Funzione helper per ottenere il carattere corrispondente al valore Code128
Function Code128CharFromValue(val As Integer) As Integer
    ' Questa mappa dipende dal font Code128 usato
    ' Adattata per il font Code128.ttf di Grand Zebu
    If val >= 0 And val <= 94 Then
        Code128CharFromValue = val + 32
    ElseIf val >= 95 And val <= 106 Then
        Code128CharFromValue = val + 100
    Else
        Code128CharFromValue = 32 ' spazio fallback
    End If
End Function
REM -----------------------------