Create barcode with value from table

[maxholgasson]

Hi How can I use this function to create a barcode next to a cell value?

[marekduciuc]

I would need the same function. The idea is that I have a sheet with 1000+ product numbers, and I need to generate barcodes in the cell.
Best would b if ti can be function used like this:
I insert this code in cell c4 =inserBarcoe(c1, format) and it will generate barcode with that format in that cell C4

[JBruceDawson]

Would be nice if it would generatie labels with values from a spreadsheet or base object.

[Jermor]

I wrote myself such a function for the spreadsheet. The problem is that the barcode is not inserted into the cell but placed in a separate layer on the sheet. The barcode generates the codes that are anchored relative to the sheet, not the cell. In my function, you need to enter the address of the cell above which the barcode will be placed. So once barcodes have been inserted, rows and columns cannot be resized anymore because barcodes don't move with cells. My function automatically adjusts the barcodes to the height of the row where they will be placed. If the barcode were to be smaller than 10% of the standard size, the code would not be generated.

[bigretromike]

yes, please

[ckruijntjens]

I wrote myself such a function for the spreadsheet. The problem is that the barcode is not inserted into the cell but placed in a separate layer on the sheet. The barcode generates the codes that are anchored relative to the sheet, not the cell. In my function, you need to enter the address of the cell above which the barcode will be placed. So once barcodes have been inserted, rows and columns cannot be resized anymore because barcodes don't move with cells. My function automatically adjusts the barcodes to the height of the row where they will be placed. If the barcode were to be smaller than 10% of the standard size, the code would not be generated.

Hi,

Can you share the code? I am looking for such a funtion.

[Jermor]

UDF function ICB(code; where; codetype; checksum)
This feature requires the installation of the Barcode extension, available at:
https://extensions.libreoffice.org/en/extensions/show/barcode
https://github.com/LibreOffice/barcode
The function does not generate a barcode. It calculates the required parameters which it passes to the proper code generator, the BarCode extension. This function can only be used in Calc. The function code was created with the Barcode extension version 2.3.0.
The inserted code is adjusted to the height of the row by rescaling the original height and placed at the position of the cell given in the parameter. The width of the code is always 100% and can extend beyond the right edge of the cell. If the scaling factor is less than 10% of the original size, the code will not be inserted. The result of the function is the name of the barcode and the scaling percentage or error message.
The function can, and even is advisable, be placed in a different location than the one provided for the barcode, because when removing the graphics, you can also inadvertently delete the contents of the cell.
Attention! In case of changing the coded characters, the function does not remove the graphic form of the code and inserts a new code overlaying it on the previous one. You must first remove the previous code graphic.
Code – means the set of characters to be converted to bar form.
Where - is the address of the cell, given as a string, at which the code is to appear. The barcode is not inserted into the cell, because in Calc the graphic is on a separate layer and is only placed in the position of the indicated cell with its location anchored in relation to the beginning of the sheet. Therefore, changes made to the layout of the sheet after inserting the code, such as changing the height of the rows, do not affect the position of the code. The code anchor is performed by the already mentioned Barcode extension. The cell address can be entered using the function CELL("address";A1), thanks to which when copying the function to subsequent cells, the destination address of the code will also change.
Codetype - Type of code. It is an integer informing what type of code is to be generated.
0 - EAN-13
1 - ISBN-13
2 - ISBN-13 from ISBN-10
3 - UPC-A
4 - JAN
5 - EAN-8
6 - UPC-E
7 - Standard 2 of 5 (Code 25)
8 - Interleaved 2 of 5
9 - Code 128
If the parameter is omitted, the value 0 (EAN-13) will be assumed.
Checksum – a logical value informing whether the system should generate a checksum.
If the parameter is omitted, it will be set to TRUE meaning that the check digit is to be generated. If the check digit is not to be created (FALSE), enter the code as long as it would have with the check digit.
The generated code is inserted on the background of the cell with margins of 1 mm from the top and from the left edge of this cell.
The function takes error messages generated by the Barcode extension and displays them in three successively appearing information windows. The third one explains the reason for the error.

Function IBC(code,Optional where as String, Optional codetype as Integer, Optional checksum as Boolean)
REM This feature requires the installation of the Barcode extension, available at:
REM https://extensions.libreoffice.org/en/extensions/show/barcode
REM https://github.com/LibreOffice/barcode/releases/tag/v2.4.0
REM "code" is a valid character set for the selected encoding.
REM The barcode inserted is adjusted to the row height and placed at the position of the cell whose address 
REM is given in the parameter "where".This parameter must be a string and may be a function CELL("address"; A1).
REM "codetype" corresponds to the index of the row in the "codes" array containing the barcode name.
REM "checksum" TRUE will perform the check digit calculation.
REM If the barcode were to be reduced by more than 90%, it would not be inserted into the sheet.
REM Author: Jermor (morchat at o2.pl) (November 2021)
Dim Const1 as Integer, percent as Integer
Dim oJob as Object, oKom as Object, Wys as Long, codes(9,2)
Const margin=100 'Top and left margins from border of cell.
On Local Error GoTo Brak
If IsMissing(checksum) then checksum=True
If IsMissing(codetype) then Codetype=0
if IsNumeric(code) then code=CStr(code)
If IsMissing(where) or where=0 then IBC="""where"" is missing" : Exit Function
If codetype<0 or codetype>9 then IBC="Wrong codetype" : Exit Function
    codes(0,0)="EAN-13" : Codes(0,1)="EAN13" : Codes(0,2)=3400
    codes(1,0)="ISBN-13" : Codes(1,1)="ISBN13" : Codes(1,2)=3400
    codes(2,0)="ISBN-13 from ISBN-10" : Codes(2,1)="Bookland" : Codes(2,2)=3400
    codes(3,0)="UPC-A" : Codes(3,1)="UPCA" : Codes(3,2)=3400
    codes(4,0)="JAN" : Codes(4,1)="JAN" : Codes(4,2)=3400
    codes(5,0)="EAN-8" : Codes(5,1)="EAN8" : Codes(5,2)=5400
    codes(6,0)="UPC-E" : Codes(6,1)="UPCE" : Codes(6,2)=5400
    codes(7,0)="Standard 2 of 5" : Codes(7,1)="STANDARD2OF5" : Codes(7,2)=3400
    codes(8,0)="Interleaved 2 of 5" : Codes(8,1)="INTERLEAVED2OF5" : Codes(8,2)=3000
    codes(9,0)="Code-128" : Codes(9,1)="CODE128" : Codes(9,2)=3000
	Const1=1000    '
    okom=ThisComponent.CurrentController.ActiveSheet.getCellRangeByName(where)
	Wys=okom.Rows.Height-2*margin
	Select Case codetype
		Case 0 to 7
			If codetype=1 or codetype=2 then Const1=2000
			percent=Fix((Wys-Const1)/(codes(codetype,2)-400)*100)
		Case 8,9
			percent=Fix((Wys-1380+248)/(codes(codetype,2)-248)*100)
	End Select
	If percent<10 then IBC="The cell height is too low." :Exit Function
    oJob = createUnoService("org.libreoffice.Barcode")

    Dim args(8) as new com.sun.star.beans.NamedValue
    args(0).Name = "Action"
    args(0).Value = "InsertBarcode"
    args(1).Name = "BarcodeType"
    args(1).Value = codes(codetype,1)
    args(2).Name = "BarcodeValue"
    args(2).Value = code
    args(3).Name = "BarcodeAddChecksum"
    args(3).Value = checksum
    args(4).Name = "WidthScale"
    args(4).Value = "100"
    args(5).Name = "HeightScale"
    args(5).Value = percent
    args(6).Name = "PositionX"
    args(6).Value = okom.Position.X+margin
    args(7).Name = "PositionY"
    args(7).Value = okom.Position.Y+margin
    args(8).Name = "TargetComponent"
    args(8).Value = ThisComponent
	oJob.execute(args)
 
 IBC=codes(codetype,0) & " " & percent &"%"
 Exit Function
 Brak:
 if err<>0 then print Error : IBC="Barcode error" : Exit Function
 IBC="Code is missing"
End Function