Using Conditional Formatting for Zebra Striping in Microsoft Excel

Zebra Striping in Excel is a useful way to make it easier to differentiate between long (page-wide) rows of data especially when each column stores data which looks very similar. Simply put, you use alternating colors to define rows. I’ve found this to be most useful when printing picklists or other lists for use in our warehouse.

Achieving this effect is simple in Microsoft Excel with Excel’s Conditional Formatting tool. In any version of Office after 2003, click on “Conditional Formatting” in the “Home” ribbon and create a new rule. Here the instructions diverge slightly: In Office:Mac choose “Classic” style then “Use a formula to determine which cells to format”, in Office for Windows choose the “Use a formula to determine which cells to format” Rule Type.

At this point the instructions converge again: type in the following formula into the formula box:

=MOD(ROW(),2)

After this, in Office for Windows click on “Format…” to define the format you want to use for every other row. In Office:Mac click on the “Format With” drop down and choose “Custom Format” to define your format.

The formula above uses Modular Arithmetic (the MOD() function) with the row number (the ROW() function gets the current row) as the input and 2 as the divisor. If the result is greater than 0 (any uneven row since uneven numbers can’t divide cleanly by two) the row will be formatted with your defined format, otherwise the result is 0 and the row is left with its current format.

 

Restarting a MySQL Server from the Command Line

While it doesn’t come up frequently, every once in a while you’ll need to restart a MySQL server from the command line. On my current webserver we’re running a VPS with WHM. What this means is that accessing the MySQL daemon is not as simple as using “mysqld” from the shell. So here’s how to get it to work, based on information found at this blog post, and my own experience:


#/etc/init.d/mysql start

#/etc/init.d/mysql stop

#/etc/init.d/mysql restart

That will start, stop, and restart, respectively, the MySQL server on our WHM server.

Stripping Everything but Numbers in PHP using RegEx

Here’s a useful little regular expression that will strip everything but numbers from a string in PHP.

preg_replace(“/[^0-9]/”,””,”604-619-5135″);

This uses PHP’s preg_replace function to replace everything but numbers with nothing. I used it to strip the dashes from ZIP+4 codes and also reformat phone numbers without forcing the user to enter it in my preferred format (if possible, it’s always better to clean up user input yourself than yell at the user for not entering it your way).

I gleaned this solution from “Chris” on StackOverflow so all credit goes to him. I recommend using RegExr when building your expression. I’ve covered this in more detail previously.

Generating Barcodes in PHP

I’ve discovered some applications which will allow you to create barcodes in PHP but never came across one which had been issued under MIT license (which allows full use for free, including commercial use, with a few restrictions). All the other applications I had found were quite expensive or licensed only for not-for-profit use.

Then, I stumbled upon this post by David Scott Tufts who had developed a PHP script which will generate barcodes “in four barcode formats including Code 128, Code 39, Code 2of5, and Codabar”. I downloaded his code and tried it out and, sure enough, it worked perfectly for the application I was building! Thanks to David Scott Tufts for saving me a significant amount of time, or quite a bit of money! His solution is quick, accurate, and simple.

To use his script, download his PHP file (he uses the .phps extension on the file to allow download, I believe, so I just changed that to .php before using). Upload the script to your server and use it as follows in your HTML:

<img src=”/images/barcode.php” alt=”barcode” />

Available parameters are:


text=string (Default: “0”)

size=int (Default: “20”)

codetype=(Code128|Code39|Code25|Codabar) (Default: “Code128”)

orientation=(horizontal|vertical) (Default: “Horizontal”)

Text: Should be a url-escaped string (e.g. “%20″ for ” “).

Size: 50 worked well for me. Play around to find the best size for your application, especially if printing.

Code Type: I used Code 128 for my application. The code you use may depend on your application or the scanner you expect to read the barcode with.

Orientation: Self-explanatory. Just make sure your IMG tag widths and heights reflect the orientation correctly.

That’s all there is to it! It took a few minutes to play around with the settings and get something that worked for what I needed, but compared to writing my own script it was a breeze.

Notes:

  1. This script requires PHP’s GD Library to be installed and available on the server.

Generating EAN/ISBN-13 Check Digits in Excel

When you’re dealing with ISBN-13’s you’ll sometimes need to generate a check digit, either to verify  a number your employees are inputting during inventory, or scanning off a book or product, or possibly you’re receiving input from a publisher which uses the antiquated ISBN-10 standard and you need to convert it to ISBN-13’s.

ISBN-10 Check Digits

First, what is a check digit? On most barcodes or standardized numbers the last character is what’s called a check digit. This number is generated by using a formula on all the other numbers. In ISBN-10’s this number is generated by multiplying each of the first 9 digits by decreasing numbers starting with 10. For example, the number 0330258648 is a valid ISBN-10. To generate the check digit (which in this example is the 8 at the end of the ISBN-10) we use the following formula:

((0*10)+(3*9)+(3*8)+(0*7)+(2*6)+(5*5)+(8*4)+(6*3)+(4*2)) mod 11 = 3 11-3 = 8

What’s going on here is that each of the first 9 digits of the ISBN-10 are being multiplied by a descending sequence of numbers from 10 to 2. The sum of these multiplications are then taken and divided by 11 with the remainder being take (using modular arthimatic here). The remainder plus the check digit must equal 11 to be valid so to find the check digit subtract the remainder from 11. If the result is 11 (11-0=11), the check digit is written as an upper-case “X”.

ISBN-13 Check Digits

Check digit calculation with ISBN-13s is somewhat different. To find a check digit for a certain ISBN-13, for example 9780395489314, you can use the following formula:


((9*1)+(7*3)+(8*1)+(0*3)+(3*1)+(9*3)+(5*1)+(4*3)+(8*1)+(9*3)+(3*1)+(1*3)) mod 10 = 6

10-6 = 4

This works very similarly except that the initial sum is developed by multiplying the first 12 digits of the ISBN-13 by 1 and 3 alternately and then using mod 10 instead of mod 11 do find the remainder of that sum. After that, again, the check digit + the remainder = 10 so 10 – the remainder = the check digit. In this case 4.

Performing the calculations in Excel

To do this, you’ll need to use the following formula:


=B2&(ROUNDUP(((MID(B2,2,1)+MID(B2,4,1)+MID(B2,6,1)+MID(B2,8,1)+MID(B2,10,1)+MID(B2,12,1))*3)+(MID(B2,1,1)+MID(B2,3,1)+MID(B2,5,1)+MID(B2,7,1)+MID(B2,9,1)+MID(B2,11,1)),-1)-(((MID(B2,2,1)+MID(B2,4,1)+MID(B2,6,1)+MID(B2,8,1)+MID(B2,10,1)+MID(B2,12,1))*3)+(MID(B2,1,1)+MID(B2,3,1)+MID(B2,5,1)+MID(B2,7,1)+MID(B2,9,1)+MID(B2,11,1))))

You’ll notice that this formula refers to cell B2. In cell B2 you’ll need to have one of the following:

  1. The first 12 digits of an ISBN-13 (A useful function here is =LEFT(B1,12) which will simply take the first 12 characters of cell B1).
  2. The first 9 digits of an ISBN-10 with “978” concatenated to the front of it (e.g. 978033025864)

And that’s how to generate a ISBN-13 check digit in Excel!

Notes

  1. Remember that an ISBN-13 is the same as an ISBN-10 with “978” prepended and a different check digit.
  2. If you’re validating an ISBN-13 which has all 13 digits, don’t forget to remove the checkdigit before passing it through the Excel formula, otherwise you’ll end up with an ISBN-14, which doesn’t exist and is definitely not valid.

Kiosk/Silent Printing in Google Chrome

As a web developer, the easiest way for me to solve a problem is normally to develop a web app that takes care of the issue. There are certain advantages to this approach: scalability in that multiple users can access the app simultaneously, accessibility in that it can be accessed from anywhere, and hardware independence meaning if the end user’s computer goes down, all I have to do as IT support is replace their computer. At times, however, there are limitations to this “when all you’ve got is a hammer everything looks like a nail” solution. Most often for me these issues are related to functionality which would be extremely simple in a native application being difficult to achieve from a browser. Thankfully, when I encounter these issues I’m also in control of the end user’s browser and computer.

Most recently, I ran into a situation where I needed a web app to print a packing slip for an online order once that order had been confirmed by the picker and packer. The system I’m using gives the packer easy access to only the computer’s number pad as it was much simpler to repurpose hotkeys–”*”, “/”, etc.–from the number pad than try to find space for a mouse on our packing line. Because of this lack of mouse access, however, I needed the system to be able to print without requiring a click on “Print” in Chrome’s print preview dialogue or the system print dialogue. What I discovered thanks to this page on Vend’s website is that Chrome has a little-known kiosk mode with the option for “silent” printing. That is, printing which doesn’t require the user to confirm the printing.

I have Chrome launching on startup for our packers (on a Windows 7 Starter netbook) and all I had to do was right click on the Chrome shortcut in the startup folder, click “Properties” and add the following flags to the end of the “Target:” field:

–kiosk –kiosk-printing

This means that when Chrome launches it’ll launch in full-screen mode (without that annoying “Press F11 to exit full-screen” message everytime your cursor is near the top of the screen) and when you press CTRL-P or call Javascript’s “window.print();” function it will print the page without needing your user to confirm that print action.

As the name of flag suggests, this would also be an extremely useful solution in a case where Chrome were running in a Kiosk computer.

Notes:

A few things to note if you do use this solution to silent printing in Chrome:

  1. “Print Preview” must be enabled in Chrome. Check “about:flags” and make sure.
  2. Slightly annoyingly, Chrome will still show the print preview for about a second before going ahead and printing so it’s not quite silent printing, more like muted printing, but it does not require user confirmation, which was my goal.
  3. This is not a way to auto-print on a users’s computer if you don’t have access to the machine yourself since it requires modifying Chrome’s launch flags. This is a good thing! Imagine visiting an unscrupulous webpage and having it auto-print itself wasting your ink and paper!
  4. Make sure you have the correct printer selected as the default since that’s the one Chrome will print to.
  5. Thanks to Matthew McIntyre for pointing out in a comment here that you can omit the -kiosk flag if you want to use silent printing but don’t want Chrome to open full-screen. Different scenario to mine, but useful to note.

Jumping Pictures in Microsoft Word

I normally use Adobe InDesign (after abandoning PageMaker a while ago, thankfully!) for print layouts but I recently ended up needing to do a relatively simple, plain-background layout in Microsoft Word (I did this on Microsoft Word:Mac 2011 on a MacBook Pro running Mac OS X, but it should be the same or similar for all versions of Word at least from 2007 on).

In doing this layout I used both the “Behind Text” and “In Front of Text” options from the “Wrap Text” option in the “Format Picture” ribbon. However, whenever I would try to resize or move a picture it kept jumping around! I could go from 2″ wide to 2.15″ wide but not from 2.15″ to 2.25″… it jumped to 2.5″ and moving the images was just as painful.

It turns out that Word has an invisible grid on by default and never having used Word for any significant or even relatively complicated layouts previously I had no idea that the grid even existed! I found this solution from “Rohn007” on this page in Microsoft’s Community site.

All you have to do to solve this issue is go to the “Layout” ribbon (where you would change your page orientation, size, margins, etc.) and under “Grid” uncheck the “Snap Objects to Grid” checkbox. After that, everything worked as expected!