The Surly Admin

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.

And I got this:

WordPress 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!

April 29, 2013 - Posted by Martin9700 | PowerShell | Barcodes, Excel, Word, Zint