I was running into problems with conflicting autoloaders when I tried to use PHPExcel with the Yii framework. There are instructions out on the web on how to resolve this, but they are only useful if you’re not creating instances of Yii active record models while you’re using PHPExcel. That’s fine if you’re using Yii to create an Excel spreadsheet from already instantiated Yii classes, but my need is to read an Excel spreadsheet and import its data into my database.

It turns out that this is fairly straightforward as well. The trick is in realizing that PHPExcel is registering its autoloader when it’s instantiated, so you don’t need to register it yourself.

public function actionImport()
  {
  $message = '';
  if (!empty($_POST))
     {
    $file = CUploadedFile::getInstanceByName('import');
    $spec = Yii::app()->basePath.'/data/imports/'.$file->name;
    $file->saveAs($spec);
    spl_autoload_unregister(array('YiiBase','autoload'));
    require(Yii::app()->basePath.'/extensions/phpexcel/Classes/PHPExcel.php');
    spl_autoload_register(array('YiiBase', 'autoload'));
    try {
         $inputFileType = PHPExcel_IOFactory::identify($spec); 
         $objReader = PHPExcel_IOFactory::createReader($inputFileType);  
         if ($inputFileType != 'CSV')
            {
            $objReader->setReadDataOnly(true);
            }
        $objPHPExcel = $objReader->load($spec); 
        $objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
        $highestRow = $objWorksheet->getHighestRow();
        for ($row = 1;$row < $highestRow+1; $row++)
             {
             $myObjThing = new MyObject; // Yii AR model
             $myObjThing->someField = $objWorksheet->getCellByColumnAndRow(1, $row)->getValue();
             $myObjThing->otherField = $objWorksheet->getCellByColumnAndRow(5, $row)->getValue();
             $myObjThing->save(false);
             $myObjThing->detachBehaviors(); // PHP < 5.3 memory management
             unset($myObjThing);
             }
        }
    catch (Exception $e)
       {
       $message = 'There was a problem handling your file. Technical details: '.$e->getMessage();
       }
    if (! empty($message))
       {
       Yii::app()->user->setFlash('error',$message);
       }
    }		
  $this->render('import');
  }

Pretend WordPress didn’t hose the formatting on that …

I’ve been publishing a bunch of old projects that I may have posted here, or simply left on my hard drive to suffer the slings and arrows of outrageous bit-rot. Most of these are projects that I created for some specific purpose or another, and have either coded to the point where I’m satisfied with them, or abandoned them.

I’m publishing this stuff in the hopes that it’ll be useful to somebody somewhere. In some cases, the code’s primary use may be as an example of how not to accomplish a task. In other cases, they’re projects that are being used in mission-critical operations, and so are reasonably robust.

I’ll be maintaining them on GitHub, if you want to get creative with the definition of “maintaining.”

I’ve been processing a collection of product videos which came to me in a huge variety of sizes, aspect ratios, and qualities. I need to re-encode them to work in HTML 5, but, more importantly, I need to make them fit into a common player space on the web page.

It turns out that newer versions of ffmpeg support not only cropping, but also padding, and you can even do both operations at once!

For example, I had a source video that was originally 16:9, but had been letterboxed to 4:3, and then had two different sets of labels added. I needed to crop out the letterboxed portion and the top set of labels, and make the result fit nicely into 16:9. So I used VLC, a screen capture utility, and Photoshop to get the measurements. Then I used ffmpeg to crop the relevant section and pad it out to fit into my space (in this case, I’m left aligning the video in the padded output):


ffmpeg -i original/converted.wmv -vf crop=394:295:6:0,pad=524:295:0:0:0xFFFF00 -sameq converted.mov


That’s cropping a 394 x 295 piece out of the original video (with the origin at 6 pixels from the left, and 0 pixels from the top), and then padding it out to 524 x 295 filling the padded area with bright yellow. The 524 x 295 is really close to 16:9 — and in a later process, it gets resized to the more standard 480 x 272¹.

You can string together the padding and cropping in either order, depending on the effect you’re trying to achieve.

¹I’m sure some educated person out there could tell me why video standards are so confused/confusing, down to the non-square pixels. While a true 16:9 would dictate 480 x 270 pixels, everybody seems to use 480 x 272. Why? The only thing I can figure out is that 272 is evenly divisible by a power of 2, which probably made display hardware cheaper to manufacture. As you can see, my resizing adds a bit of distortion, but at these resolutions, it doesn’t really matter.

Several years ago, we replaced our commodity hardware firewall (a Sonicwall SOHO from ’01) with pfSense running on an unused Dell 4100 desktop from that same year.

pfSense was a little confusing to configure the first time through (doing 1-to-1 NAT with virtual IPs and CARP was initially confusing, but the pfSense forums and The Google came to our rescue). Once in place, though, it did a great job. And when I say a great job, I mean that we could pretty much forget about its existence. It just hummed away in the background, and everything worked. When we needed to check up on our ISP, we discovered that quality of service logging was already supported, as well as pretty graphs of various connection properties. Very nice!

Over the last weekend, the 4100 locked up, and our connection was interrupted. Rebooting gave a firmware error about a bad disk in drive A: — but there was no disk in the drive. Power cycling, opening the machine, wiggling some cables, and blowing out some dust brought it back up, and all was well. Except it wasn’t, really. The machine spontaneously rebooted a number times over the next few days, and occasionally got into the “bad disk in drive A:” boot failure, requiring a hard power cycle. As I watched on the console, I saw the kernel fault out after too many memory checksum errors. The old machine was giving up the ghost.

After commissioning another old desktop (an ’07 vintage Dell, this time), I was able to install pfSense on it. I had to disable some of the extraneous hardware in the BIOS, but after about an hour I had it installed, booting, and ready to go. I was able to simple dump the configuration from the old firewall, load it into the new machine, reassign the LAN and WAN interfaces to the proper devices, and swap the boxes out. voila! Back in business!

With any luck, I won’t have to repeat this process for another five years.

I just received my paper copies.

You can get a copy too!