To automatically create barcodes for numbers in an Excel sheet, you can use the xlsxwriter library to write the Excel file and the python-barcode library to generate the barcode images. First, install the required libraries if you haven’t already:

pip install XlsxWriter python-barcode

Full Code for generating barcode automatically for numbers

import xlsxwriter
from barcode import Code128
from barcode.writer import ImageWriter
from PIL import Image  # Import the Image module from PIL
from io import BytesIO

# Sample data
data = [123456, 789012, 345678, 901234]

# Create a workbook and add a worksheet
workbook = xlsxwriter.Workbook('barcodes.xlsx')
worksheet = workbook.add_worksheet()

# Set the row and column where the barcodes will start
start_row = 0
start_col = 0

# Define the cell width and height
cell_width = 50
cell_height = 50

# Iterate over the data and insert the numbers and their corresponding barcodes
for i, num in enumerate(data):
    # Write the number in the Excel sheet
    worksheet.write(start_row + i, start_col, num)

    # Generate the barcode image
    code128 = Code128(str(num), writer=ImageWriter())
    stream = BytesIO()
    code128.write(stream)
    stream.seek(0)

    # Calculate the scale factors for resizing the image
    img = Image.open(stream)
    img_width, img_height = img.size
    x_scale = cell_width / img_width
    y_scale = cell_height / img_height

    # Insert the barcode image into the Excel sheet
    worksheet.insert_image(start_row + i, start_col + 1, 'barcode.png', 
                           {'image_data': stream, 'x_scale': x_scale, 'y_scale': y_scale})

# Automatically adjust column widths based on the content
for i, num in enumerate(data):
    worksheet.set_column(start_col, start_col, len(str(num)) + 1)

# Set the row height to fit the barcode images
for i in range(len(data)):
    worksheet.set_row(start_row + i, cell_height)

# Close the workbook
workbook.close()

Output:

Let us understand each line of the code above:

1. import xlsxwriter: Imports the xlsxwriter module, which is used to create Excel files.
2. from barcode import Code128: Imports the Code128 class from the barcode module, which is used to generate Code 128 barcodes.
3. from barcode.writer import ImageWriter: Imports the ImageWriter class from the barcode.writer module, which is used to save barcode images.
4. from PIL import Image: Imports the Image module from the PIL (Python Imaging Library) package, which is used to work with images.
5. from io import BytesIO: Imports the BytesIO class from the io module, which is used to handle binary data in memory.
6. data = [123456, 789012, 345678, 901234]: Defines a list of sample data (numbers) for which barcodes will be generated.
7. workbook = xlsxwriter.Workbook('barcodes.xlsx'): Creates a new Excel workbook named ‘barcodes.xlsx’.
8. worksheet = workbook.add_worksheet(): Adds a new worksheet to the workbook.
9. start_row = 0 and start_col = 0: Defines the starting row and column for inserting the barcodes in the worksheet.
10. cell_width = 50 and cell_height = 50: Defines the width and height of the cells in which the barcode images will be inserted.
11. Iterates over the sample data:
    • Writes each number in the Excel sheet.
    • Generates a barcode image for each number using the Code128 class and saves it to a BytesIO stream.
    • Calculates the scale factors (x_scale and y_scale) for resizing the image to fit the cell.
    • Inserts the barcode image into the Excel sheet using the insert_image method, along with the scale factors.
12. Automatically adjusts the column widths based on the content.
13. Sets the row height to fit the barcode images.
14. Closes the workbook, saving the changes.

This code creates an Excel workbook with a worksheet containing barcodes for the sample data provided, where each barcode is inserted into a cell and resized to fit the cell dimensions.

Note: you can adjust the size of the cell according to the image size.

Understand the most important part of the code which is below

# Iterate over the data and insert the numbers and their corresponding barcodes
for i, num in enumerate(data):
    # Write the number in the Excel sheet
    worksheet.write(start_row + i, start_col, num)

    # Generate the barcode image
    code128 = Code128(str(num), writer=ImageWriter())
    stream = BytesIO()
    code128.write(stream)
    stream.seek(0)

    # Calculate the scale factors for resizing the image
    img = Image.open(stream)
    img_width, img_height = img.size
    x_scale = cell_width / img_width
    y_scale = cell_height / img_height

    # Insert the barcode image into the Excel sheet
    worksheet.insert_image(start_row + i, start_col + 1, 'barcode.png', 
                           {'image_data': stream, 'x_scale': x_scale, 'y_scale': y_scale})

Explanation:

Automating barcode generation in Excel with Python can significantly improve efficiency and accuracy in various industries, such as retail, inventory management, and logistics. By leveraging libraries like xlsxwriter and barcode, users can create Excel workbooks with dynamic barcode images based on their data. This automation eliminates the need for manual barcode generation, reducing errors and saving time. Moreover, the flexibility to customize barcode settings and seamlessly integrate with existing workflows makes Python a powerful tool for barcode automation in Excel.

Dont forget to like and share ! Sharing is caring