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


This tutorial shows you how to use Barcode & Label to create labels with data from an Excel spreadsheet. You can see a video of this tutorial at the following :

www.barcoderesource.com/excelspreadsheetlabel.shtml#excelspreadsheetlabelvideo.

Note - This tutorial also applies to the Poster & Flyer app and the Envelope & Address Print app

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 Sample.xlsx that you can download for this tutorial. You may also continue this tutorial with your own 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 setup the spreadsheet again.




4. Once you click or tap on the Open button, all the different worksheets that are 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 as column names (headers). When used as a 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 uses (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 will simply display the default short name "ECL01", which represents the first column of the Excel worksheet.

9. Next, you can click or tap on the Preview button to preview 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 using the second column of the Excel worksheet as data for this Barcode object.

Notes

  • In the above tutorial, we have setup the Excel spreadsheet in the Variable Fields Flyout first 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 first 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 higher amount of memory (RAM).
  • Barcode & Label only supports spreadsheet in the Office Open XML format (.xlsx or .xlsm from Office 2007 or onwards).

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