The Surly Admin

Father, husband, IT Pro, cancer survivor

Generate Barcodes in Powershell

Work has been pretty busy, and is likely to be for the foreseeable future!  We’re remodeling our manufacturing area, adding a new lab on the 2nd floor (we never occupied the 2nd floor before!), adding 10 new wireless access points (VLANS, multiple SIDs, new wireless hardware platform, oh my!), implementing a new ERP system, planning for a new CRM system, rolling out new PC’s and laptops, new conference rooms, planning a remodel in our HQ building and a bunch of other things too!  If you noticed I didn’t post anything last week, that’s why.  Not to mention, nothing really interesting has come across my desk from Spiceworks or PowerGUI.  Then the boss mentioned he was working on something, and the more he talked about it the more I realized I might be able to do what he wanted in Powershell, especially since this wasn’t going to be a one-off kind of thing, but something we’d have to do on a regular basis!

We need Barcodes, lots of Barcodes…

The driving force behind the wireless project I mentioned above was we were going to be implementing barcode reading all throughout the warehouse and manufacturing areas (in separate buildings just to make it more interesting).  That meant we would need two different kinds of barcodes, one set to be attached to the shelves and then another to be attached to all of our bins, our thousands and thousands of bins.  Yikes.  The initial thought my boss had been to find a little plugin for Excel and then simply select all the fields, hit the Generate button and be done with it.  He even found a program that would do just that!  But there was a problem.  When you hit Generate it didn’t put the barcode nice and neat to the right of the selected field.  No, it plopped all of them along the top of the spreadsheet piled on top of each other!  That would mean hours of work grabbing a picture, locating the text in the spreadsheet and dragging it down.

Of course, looking at this I couldn’t help but start thinking that this is screaming to be an automated process, right?  Read the data, generate the barcode, output the data, over and over again.  So I mentioned to him that I might be able to do something, assuming I could find a command line application that could generate a barcode.  In my head I was thinking people have been making barcodes for years, surely someone has written something?  I was even ok if we had to pay for it!

Zint

Break out my trusty web browser and go to Google and type in a search, and about 3 down from the top is a little free project called Zint (link here).  Command line, generates barcodes, free, what more could you ask for?  Download and install–has a nice little GUI, but I’m interested in the command line ability–navigate to the proper directory and give it a whirl.  Ok, I did RTFM a little here.

Zint TestAnd I got this:

Test BarcodeWordPress is eating the preview something awful, but if you click on the link the picture loads fine and looks exactly like what I wanted.  Now all I need to do is read the Excel spreadsheet, create a barcode and put it in the spreadsheet.  How hard can that be?

How I hate ComObjects

ComObjects have been around for a long time, and their name implies they’re just like any other Powershell object.  But, of course, they aren’t.  They suck in every way possible and Office automation sucks even worse.  While there’s plenty of documentation out there for doing simple processes simply expanding a cell and inserting a picture turned out to be pretty challenging.  But after many test runs, and a lot of stubbornness I finally got something working.  Here’s what it looked like:

$ZintPath = "C:\Program Files (x86)\Zint"
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $True
$Excel.Workbooks.Open("c:\utils\Barcodes.xls") | Out-Null

First define the to my Zint install, then create the Excel ComObject and open up the spreadsheet.  I pipe that to Out-Null because I like to suppress output I don’t really need to see and the Office automation ComObjects are very chatty.  You can also assign the statement to a variable to suppress the output if you want to.

ForEach ($Sheet in $Excel.Worksheets)
{  $Excel.Worksheets.Item($Sheet.Name).Activate()
   $Range = $Sheet.Range("F1")
   $Range.ColumnWidth = 35
   $Sheet.Cells.RowHeight = 110
   $NullCount = 0
   $Row = 0
   $ImageLocation = 10

In the test spreadsheet all of the barcodes were in several worksheets, so I decided to have the script loop through all of the worksheets.  The Activate line isn’t strictly needed but I used it so I could see the sheet as it’s being worked on.  $Sheet.Range is defining where I want to work in the worksheet.  Then simply set some column width, row height (to fit our incoming picture).  $NullCount is interesting because as I was writing this script it occurred to me that there’s no real end in Excel, so how do I know when I’m done?  Just checking for a $null cell isn’t quite enough because there were blanks in the spreadsheet so I decided to use a counter called $NullCount.  If I get to 3 $null cells in a row then I’ll consider the spreadsheet over–and, of course, there was one column of barcode data that had about 20 $null cells before it started, but I just cut and paste that column back up to the top and moved on!  Potential blog post?  Talking about having regular data?  Mmm… sounds boring?!

OK, we have the Excel spreadsheet open and our column widened to our needs.  Got some variables set on what row to start on ($Row) and where to place the image ($ImageLocation–nothing fancy here, I threw a number in there and then ran the script about 10 times until I got the image right where I wanted it).  Let’s generate some barcodes!

While ($NullCount -le 3)
{  $Row ++
   [string]$CellValue = $Sheet.Cells.Item($Row,1).Text
   If ([string]::IsNullOrEmpty($CellValue))
   {   $NullCount ++

This sets up a loop where our $NullCount is less than 3.  Increment the row #, read the cell into a variable and test if it’s a null.  If it is a null we increment our $NullCount and loop through again.  If it’s NOT a null, we generate the barcode:

Else
{  $NullCount = 0
   Set-Location $ZintPath
   .\zint.exe --data=$CellValue
   $Sheet.Shapes.AddPicture("$ZintPath\out.png",0,1,350,$ImageLocation,150,90) | Out-Null
   $ImageLocation = $ImageLocation + 110

Reset the $NullCount variable, change our current location to $ZintPath (why didn’t I just run $ZintPath\Zint.exe?  Honestly, I just noticed this!), run Zint with our data, insert the picture into the spreadsheet and move our $ImageLocation down to the next cell.  Worked like a million bucks!  Except for one thing.  $ImageLocation is a pixel count, and the size of the cell is a little bit less than 110 pixels–but not 109 pixels–so when I was scrolling down the spreadsheet I noticed the barcodes were drifting.  And since the image isn’t really in the cell (though it looks like it) this was a problem.  Obviously I was going to have to adjust $ImageLocation every now and then to keep the images lined up properly.  But how to do it?  I stared at this for a while, to be honest.  I need to make a small adjustment about every 10 rows, which would be fairly easy to do by just keeping a counter and incrementing it as we go.  But I felt this inelegant as I didn’t want to have to keep track of another variable all the time.  Now what?  I have the $Row counter all ready, I could test that for every 10th row, right?  So any time $Row divided by 10 came out as a whole number make the adjustment!

If ($Row / 10 -is [int])
{  $ImageLocation = $ImageLocation - 5
}

Since integers are always whole numbers I can test using “is” and check if the result of our division makes an integer then we’re on a 10th row.  Adjust $ImageLocation up just a little.  Had to do this check in the $true section of the NullCheck too because the script has to move down on null lines too.

Proof of Concept

Not the most elegant code I’ve ever written, I didn’t even close and save the document (just left it open and when I got my final run I just saved it using Excel), but it definitely proved that the work could be done using Powershell and with no cost upfront (beyond my time).  Since it’s a script it can be redone an infinite number of times.  At this point we broke out our barcode scanners and successfully scanned everything in.

Of course, as with most Proof of Concept projects, it’s not the final product by a long shot.  Turns out our warehouse guy wants some labels to go on the sides of the shelves and others to go into little laminated pockets that snap onto our bins (thousands of bins) so two very different sizes and formats are needed.  In the final code I ended up outputting to Word (and boy, was that fun) with two different scripts.  But in the end I think we were able to output something very easily.

Now, it turns out another group wants a bunch of barcodes on a sheet or two of paper and they may end up changing those sheets periodically!  So not only something ripe for automation but runnable by the user, oh my!  Still don’t have a clear set of requirements on that project so not much more I can say at this point.  If it turns into something interesting maybe I’ll write about it!

Advertisements

April 29, 2013 - Posted by | PowerShell | , , ,

11 Comments »

  1. Thanks!

    Comment by iceflame (@iceflame) | April 29, 2013 | Reply

  2. Would it be easier if you used a barcode font and put everything in an HTML table? That way you could just read in the Excel as a CSV file and not have to mess with COM objects at all.

    Comment by Art Beane | April 29, 2013 | Reply

    • There are barcode fonts?! NOW you tell me?!

      Comment by Martin9700 | April 29, 2013 | Reply

    • On a more serious note, I still don’t have the clearest picture of how this project will ultimately play out so I was going with something that was the most portable. I did consider HTML using image files, but that required thousands of image files and the HTML, which isn’t too portable (read email). I never considered a barcode font, but that has an even worse problem in that if you don’t have the font installed it’s not going to work too well.

      With Word, despite the great pain and suffering it imposed on me, I can save the final file and send it to the business unit and let THEM print the thing out, or do whatever they want to it.

      I mentioned it in the post, but the second project (third?) is from another business unit and that may ultimately be HTML but again, the requirements are so vague right now I’m just not sure what the final outcome will be!

      Comment by Martin9700 | April 29, 2013 | Reply

  3. (forgot to mention) Pretty elegant solution you came up with.

    Comment by Art Beane | April 29, 2013 | Reply

  4. Been reading over this one again, and realized that there’s more available info for working with Excel. Here’s an example that could help you in this project. So, you’ve opened an existing spreadsheet and activated one of the tabs as $Sheet. Then, $Sheet.UsedRange contains all the rows and columns that have something in them. Let’s call that $Used. $Used.Rows.Count is the number of rows in the sheet (which should help with the $nullcount issue) and $Used.Rows.Item(1) is the header row (Excel indexes begin with 1 instead of the normal PS 0).Likewise, $Used.Columns.Count is the number of columns (also indexed starting with 1).

    Comment by Art Beane | April 29, 2013 | Reply

    • Yes, so now I have to decide if I want to rework the script, or jab my eye with an ice pick… wait wait… don’t rush me….

      Comment by Martin9700 | April 30, 2013 | Reply

  5. Ha! Looking for coding a tool to generate barcodes from Powershell… and here I am 😀

    You’re EVERYWHERE Martin!

    Thanks for sharing this, it will be really useful, as barcode software are really a joke (licence price is a pain…) and my customer really needs it!

    Comment by Nicolas1847 | March 23, 2016 | Reply

    • Right? And it all looks like it was designed in the 90’s. Glad I could help!

      Comment by Martin9700 | March 23, 2016 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: