How to create barcode labels with data from an Excel Spreadsheet(.xlsx or .xlsm)?


This video and the following tutorial shows you how to use Barcode & Label to create labels with data from an Excel spreadsheet.



Note - This video and tutorial also applies to the Poster & Flyer app, Greeting Cards app, and the Envelope & Address Print app.

Step-by-step Tutorial

1. First, tap or click on the "Setup Fields" button from the top Ellipsis button in Barcode & Label (or Poster & Flyer).



2. Select "Excel Spreadsheet" as the Variable Type. Please refer to the Serialized Counter tutorial if you want to use the “Counter” Variable Type.

3. Click or tap on the Select button and pick a .xlsx or .xlsm file. We have prepared a Products.xlsx that you can download for this tutorial. You may also continue this tutorial with your spreadsheet. When a label is saved into a file, the spreadsheet that is linked to that label will automatically be stored as part of the file. This allows you to load a label without having to set up the spreadsheet again.




4. Once you click or tap on the Open button, all the different worksheets available in the spreadsheet will be loaded into the Selected Worksheet dropdown list. Sample values from the selected worksheet are displayed to help you verify you are working with the correct worksheet. Pick the worksheet that you will like to use.




The "First Row as Column Headers" check box lets you specify whether the first row of the worksheet is used as a row of data or column names (headers). When used as column names, the first row will not appear as data in your label. We will leave this option on by default. The “Clear” button will remove the spreadsheet so that it will no longer be saved as part of the label file.

6. We are now ready to create text and barcode objects that use (derive their) data from the Excel spreadsheet. Click or tap on the Text object in the toolbar at the bottom of the label.

7. Drag your finger or mouse on the label to create the object. Double-tap on the label to bring up the Text Properties Flyout.

8. In the Type dropdown list, select "Excel Column 01 : ECL01". This selection indicates that we will use the first column of the worksheet as data for this Text object.




You will notice the column name "Product" is also displayed in this field (and in the object). This is because you have specified to use the "First Row as Field Alias" in step 4. If you had not checked "First Row as Field Alias" option, Barcode & Label would display the default short name "ECL01", representing the first column of the Excel worksheet.

9. Next, you can click or tap on the Print button to preview and print the labels.




10. (Optional from this step onwards) Click or tap on the Barcode object in the toolbar at the bottom of the label.

11. Drag your finger or mouse on the label to create the object. Double-tap on the label to bring up the Barcode Properties Flyout.

12. In the Type section, select "Excel Column 02 : ECL02". This means we will use the second column of the Excel worksheet as data for this Barcode object.

Notes

  • In the above tutorial, we have set up the Excel spreadsheet in the Setup Fields Flyout before assigning the worksheet columns to a Text or Barcode object. This sequence is not strictly required. You may also choose to create the objects and specify which columns to use before setting up the spreadsheet.
Limitations

  • Barcode & Label only supports the first 20 columns and first 500 rows of EACH worksheet. This limitation will be lifted as more devices are shipped with a higher amount of memory (RAM).
  • Barcode & Label only supports spreadsheets in the Office Open XML format (.xlsx or .xlsm from Office 2007 or onwards).


Back to Barcode & Label