Barcode in LibreOffice Calc

An example of making barcodes in LibreOffice Calc with plain-DLL (Windows) version of StrokeScribe barcode generator.

The DLL-based barcode generator allows to create barcode pictures in EMF format. The pictures may be loaded into a worksheet using Shape objects.

Generating barcodes in LibreOffice Calc using a custom formula and a DLL-based version of the Strokescribe barcode generator simplifies the process by allowing users to create and customize barcodes directly within their spreadsheet. This approach streamlines the workflow and enhances the convenience.

This method boosts productivity by automating barcode creation and ensuring accurate data integration. It's especially useful for tasks like inventory management and shipping.

How to Proceed

Download and install the barcode generator. You need at least version 5.2.29 to test this example.
Open the Basic editor in Calc and use the following code to make barcodes.
Make sure you have configured the macro security.

Creating a New Macro Module

Press Alt + F11. Select My Macros -> Standard and press New:

Creating a Basic macro in LibreOffice Calc

Placing a Barcode Anywhere on a Calc Sheet

1. Type any text in the cells A1, B1, C1.

2. Paste the following code into a macro module.

3. Move the caret into the TestBarcode body and press F5.

The Code Example

Expand/Collapse

' An example how to place barcodes of different types in arbitrary cells in LibreOffice Calc
' OOOBasic + StrokeScribe barcode generator (plain-DLL version)

' Function definitions for 64-bit Calc
Private Declare Function Initialize Lib "StrokeScribeDL64.dll" Alias "Initialize" () as Long
Private Declare Function GetVer Lib "StrokeScribeDL64.dll" Alias "GetVer" (ByRef major as Long, ByRef major2 as Long, ByRef minor as Long) as Long
Private Declare Function SetTextProp Lib "StrokeScribeDL64.dll" Alias "SetTextPropA" (ByVal name as String, ByVal val as String) as Long
Private Declare Function SetLongProp Lib "StrokeScribeDL64.dll" Alias "SetLongPropA" (ByVal name as string, ByVal val as Long) as Long
Private Declare Function GetLongProp Lib "StrokeScribeDL64.dll" Alias "GetLongPropA" (ByVal name as String, ByRef val as Long) as Long
Private Declare Function SavePicture Lib "StrokeScribeDL64.dll" Alias "SavePictureA" (ByVal filename as String, ByVal format as Long, ByVal width as Long, ByVal height as Long, ByVal dpi as Long) as Long

' Barcode type definitions. More constants.
Const QRCODE = 25
Const DATAMATRIX = 8
Const CODE128 = 5
Const PDF417 = 6

' Usage example:
' InsertBarcode("$A$1", QRCODE, 10, 10, 20, 20)
' Creates a QR CODE of 20x20mm placed (10mm, 10mm) at the top-left corner of the worksheet. 
' The barcode contains data from the cell A1.

Private Sub InsertBarcode(cellName as String, barcodeType as Long, x as Long, y as Long, w as Long, h as Long)
  doc = ThisComponent 
  sht = doc.getcurrentController.getActiveSheet() ' Placing a barcode on the currently active worksheet.
  cell = sht.getCellRangeByName(cellName)
  data = cell.String
  dp = sht.getDrawPage() 

  Dim rc as Long
  rc = Initialize()
  if rc<>0 then
    Print "Initialize() error: " & rc
    exit sub
  endif

  SetLongProp("Alphabet", barcodeType) 

  rc = SetTextProp("Text",  data) ' A text to encode in the barcode
  if rc<>0 then
    Print "SetTextProp() error: " & rc
    exit sub
  endif
  
  fileName = Environ ("TEMP") & "\barcode.emf" ' A temporary file to store the barcode
  rc = SavePicture(fileName, 4, w, h, 0)
  if rc<>0 then
    Print "SavePicture() error: " & rc
    exit sub
  endif

  shp = doc.createInstance("com.sun.star.drawing.GraphicObjectShape") 
  dp.add(shp) 
  shp.GraphicURL = "file:///" & fileName  ' Loading the barcode drawing into the shape

  dim p as new com.sun.star.awt.Point ' The X and Y of the barcode shape (with 1/100 of mm precision).
  p.X = x*100
  p.Y = y*100
  shp.setPosition(p) 
  
  dim s as new com.sun.star.awt.Size
  s.Width = w*100
  s.Height = h*100
  shp.setSize(s) 
  shp.Name = "barcode-shape"
End Sub

Sub TestBarcode
  DeleteBarcode
  InsertBarcode("$A$1", QRCODE, 5, 10, 20, 20)  ' x, y, width, height values are in millimeters
  InsertBarcode("$B$1", DATAMATRIX, 30, 10, 20, 20)
  InsertBarcode("$C$1", CODE128, 50, 10, 20, 10)
End Sub

' Deletes all barcode shapes that have the name "barcode-shape" 
Private Sub DeleteBarcode
  dp = ThisComponent.getcurrentController.getActiveSheet().getDrawPage() 
  For i = dp.getCount() - 1 To 0 Step -1
    shp = dp.getByIndex(i)
    if shp.Name="barcode-shape" then
      dp.Remove(shp)
    endif
  Next i
End Sub

Inserting a Barcode Into a Cell Using Formula

1. Choose a cell and paste the following into the formula field:

= CREATEBARCODE(C12; "AAAB")

The first argument is a "caller cell" reference. You need to put here the exact cell name where the formula is located.

If you specify a different cell here, the barcode picture will appear in the referenced cell and the formula's cell will contain an error code returned by the macro in a case of a failure.

The second argument is a text to encode in the barcode. Replace it with any text you want to encode.

2. Create a new macro module and paste the following code. Note that the VBASupport is set to 1. Do not copy this code into existing modules that do not require this option. The VBASupport option allows you to use direct cell references in the formula field without quotes or ROW/COLUMN functions.

The Code Example

Expand/Collapse

' An example how to build a formula to place barcodes in cells in LibreOffice Calc
' OOOBasic + StrokeScribe barcode generator (plain-DLL version)

Option VBASupport 1

Private Declare Function Initialize Lib "StrokeScribeDL64.dll" Alias "Initialize" () as Long
Private Declare Function SetTextProp Lib "StrokeScribeDL64.dll" Alias "SetTextPropA" (ByVal name as String, ByVal val as String) as Long
Private Declare Function SetLongProp Lib "StrokeScribeDL64.dll" Alias "SetLongPropA" (ByVal name as string, ByVal val as Long) as Long
Private Declare Function SavePicture Lib "StrokeScribeDL64.dll" Alias "SavePictureA" (ByVal filename as String, ByVal format as Long, ByVal width as Long, ByVal height as Long, ByVal dpi as Long) as Long

Const QRCODE = 25
Const DATAMATRIX = 8
Const CODE128 = 5
Const PDF417 = 6


Public function CreateBarcode(cell, data as String) as String
  doc = ThisComponent 
  sht = doc.getcurrentController.getActiveSheet()
  dp = sht.getDrawPage() 

  shpname = "barcode-shape-" & cell.CellRange.AbsoluteName
  DeleteBarcode dp, shpname

  Dim rc as Long
  rc = Initialize()
  if rc<>0 then
    CreateBarcode = "Initialize() error: " & rc
    exit function
  endif
  
  rc = SetLongProp("Alphabet", QRCODE) ' or DATAMATRIX or CODE128
  if rc<>0 then
    CreateBarcode = "SetLongProp() error: " & rc
    exit function
  endif
    
  rc = SetTextProp("Text",  data)
  if rc<>0 then
    CreateBarcode = "SetTextProp() error: " & rc
    exit function
  endif

  fileName = Environ ("TEMP") & "\barcode.emf"
  rc = SavePicture(fileName, 4, cell.Width, cell.Height, 0)
  if rc<>0 then
    CreateBarcode = "SavePicture() error: " & rc
    exit function
  endif

  shp = doc.createInstance("com.sun.star.drawing.GraphicObjectShape") 
  dp.add(shp) 
  shp.GraphicURL = "file:///" & fileName
  shp.Name = shpname

  dim p as new com.sun.star.awt.Point
  p.X = PtToMm(cell.left)
  p.Y = PtToMm(cell.top)
  shp.setPosition(p) 

  dim s as new com.sun.star.awt.Size
  s.Width = PtToMm(cell.width)
  s.Height = PtToMm(cell.height)
  shp.setSize(s) 

  CreateBarcode = ""
end function

Private Function PtToMm (p as Double) as Long
  PtToMm = p*2540/72
End Function

Private Sub DeleteBarcode (dp as Object, name as String)
  For i = dp.getCount() - 1 To 0 Step -1
    shp = dp.getByIndex(i)
    if shp.Name = name then
      dp.Remove(shp)
    endif
  Next i
End Sub

The same formula also works with merged cells:

=CREATEBARCODE(G24:I35;"abcd")

How to Customize the Barcode

Setting the QR CODE Error Correction Level

rc = SetLongProp("Alphabet", QRCODE)
rc = SetLongProp("QrEcl", 3) 'ECL=H

Modifying the Human-readable Text Label Below CODE 128 Barcodes

rc = SetLongProp("Alphabet", CODE128) 
rc = SetTextProp("TextBelow",  "Label Text")

Producing PDF417 Barcodes with Fixed Number of Rows or Columns

rc = SetLongProp("Alphabet", PDF417) 
rc = SetLongProp("PDF417Cols", 4) 
rc = SetLongProp("PDF417Rows", 15)

Forcing the Minimum Matrix Size of DATA MATRIX Barcodes

rc = SetLongProp("Alphabet", DATAMATRIX) 
rc = SetLongProp("DataMatrixMinSize", 40)

Setting Up Macro Security in LibreOffice Calc

1. Execute Tools -> Options.

2. Go to the LibreOffice -> Security -> Macro Security.

3. Set the security level to Medium or switch to the Trusted Sources tab and add your documents folder to the Trusted File Locations.

Making barcodes in LibreOffice Calc with fonts

Barcode in LibreOffice Calc

How to Proceed

Creating a New Macro Module

Placing a Barcode Anywhere on a Calc Sheet

The Code Example

Inserting a Barcode Into a Cell Using Formula

The Code Example

How to Customize the Barcode

Setting the QR CODE Error Correction Level

Modifying the Human-readable Text Label Below CODE 128 Barcodes

Producing PDF417 Barcodes with Fixed Number of Rows or Columns

Forcing the Minimum Matrix Size of DATA MATRIX Barcodes

Setting Up Macro Security in LibreOffice Calc

Related Tutorials