So Martin Berends is staying with me, having had his travel arrangements stuffed by the weather. We decide on a little “knowledge transfer”, and as there were some unexpected things we found, we thought it might be worth sharing the code, thoughts and discoveries.

Background

I do a bit of photography using the (still lovely) Pentax K2 my parents bought me when I finished school in 1976. I develop the film myself and then scan it, printing those shots I want. I file the negatives in sleeves that aren’t far from A4 in size. I once mentioned to Martin that I would like to be able to print out thumbnails for reference to put in the same lever arch file as the negatives, and since I had several sleeves of negatives out, this led Martin to suggest that he teach me how to do the job in Perl and I teach him how to do it in Excel VBA.

First attempt in Perl

#!perl
use strict;
use warnings;
use diagnostics;

my $dir = shift || ".";
my @image_filenames = sort(glob("$dir\\*.jpg")); # sort just to be sur
+e...

# Dimension an almost square rectangle
my $x = int( sqrt(scalar @image_filenames) );
my $y = (scalar @image_filenames) / $x;
if ( $y != int($y)  ) { $y = int($y) + 1; }
my $table_rows = "";
my $count = 0;
for my $row (0..$y-1) {
    $table_rows .= "<TR>";
    for my $col (0..$x-1) {
        if ($count < @image_filenames) {
            $table_rows .= "<TD>
<img src='$image_filenames[$count]' HEIGHT=100><BR>
$image_filenames[$count++]
</TD>";
        }
    }
    $table_rows .= "</TR>";
}
my $htmlpage = "<HTML>
<HEAD>
<TITLE>Thumbnails for '$dir'</TITLE>
</HEAD>
<BODY>
<TABLE BORDER=1>
$table_rows
</TABLE>
</BODY>
</HTML>";
open(my $fh, ">", "$dir\\Thumbs.html")  or die "Can't open output file
+";
print $fh $htmlpage;
close $fh;

[download]

I accept all blame for any stylistic aberrations. Martin’s idea was that the pictures could be assumed to be square, despite the 24x36mm dimensions. This is because some of them are portrait, and all will take up a cell the same size. With the file name (which corresponds to the negative number) appearing below, this would be close enough to A4. We felt this would be good enough for at least our first attempts.

The code above generates an HTML file that points to all the scans. Depending on which scanner I was using and what I thought was a good idea at the time, many of the files are quite large. We thought that generating HTML would enable us to open the file in FireFox and use that to print it out using the “print to fit” options. Tragically, FireFox used up my computer’s 4Gb RAM with only a dozen or so pictures. Even more tragically, Internet Exploder did better, but still ran out of memory at 18 pictures. At this point, Martin was not optimistic about Excel, which is, after all, about crunching numbers rather than rendering graphics.

Excel’s VBA

Option Explicit
Const ksFactor As Double = 0.177346958265686
Const knMaxWidth As Long = 100

Sub Main()
    Dim pic As Picture
    For Each pic In Sheet1.Pictures
        pic.Delete
    Next pic
    Sheet1.UsedRange.Clear
    Dim row As Long
    Dim col As Long
    Dim dirName As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Choose a directory"
        .Show
        If .SelectedItems.Count = 0 Then End
        dirName = .SelectedItems(1)
    End With
    Dim nFile As Long
    Dim sFile As String
    Dim sFiles() As String
    sFile = Dir(dirName & "\*.jpg")
    Do While Len(sFile) > 0
        nFile = nFile + 1
        ReDim Preserve sFiles(1 To nFile)
        sFiles(nFile) = sFile
        sFile = Dir
    Loop
    Dim nWidth As Long
    Dim nHeight As Long
    nWidth = Int(Sqr(nFile))
    nHeight = Int(nFile / nWidth)
    If nWidth * nHeight < nFile Then nHeight = nHeight + 1
    Dim nCount As Long
    nCount = 0
    For row = 1 To nHeight
        Dim nMaxHeight As Long
        nMaxHeight = 0
        For col = 1 To nWidth
            If nCount < nFile Then
                nCount = nCount + 1
                Dim picCurrent As Picture
                Set picCurrent = Sheet1.Pictures.Insert(sFiles(nCount)
+)
                picCurrent.Top = Sheet1.Cells(row, col).Top
                picCurrent.Left = Sheet1.Cells(row, col).Left
                Dim nPicWidth As Double
                Dim nPicHeight As Double
                nPicWidth = picCurrent.Width
                nPicHeight = picCurrent.Height
                If nPicWidth > knMaxWidth Then
                    picCurrent.Width = knMaxWidth
                    picCurrent.Height = knMaxWidth / nPicWidth * nPicH
+eight
                End If
                nMaxHeight = Application.Max(picCurrent.Height, nMaxHe
+ight)
                Sheet1.Cells(row, col).Value = sFiles(nCount)
                If row = 1 _
                Or Sheet1.Columns(col).ColumnWidth < picCurrent.Width 
+* ksFactor Then
                   Sheet1.Columns(col).ColumnWidth = picCurrent.Width 
+* ksFactor
                End If
            End If
        Next col
        Sheet1.Rows(row).RowHeight = nMaxHeight + 11.75
    Next row
    For col = 1 To nWidth
        Sheet1.Columns(col).ColumnWidth = Sheet1.Columns(col).ColumnWi
+dth + 1
    Next col
End Sub

[download]

That weird constant factor at the start is due to one of Excel’s more imaginative quirks. The height and width of a columncell are given in different units. We did a bit of manual resizing, divided one number by the other and came up with this. It’s wrong, but it’s close enough. The long number is the one we calculated, and we didn’t see any point in doing any more work on it beyond cutting & pasting what we got.

We had to go through several iterations to get what we wanted, and needed the cleanup code to make sure we weren’t being misled by relic pictures, column widths and the like.

While the Perl had taken the directory from the command line, Excel has a fancy dialogue box available to let users choose directories.

VBA’s dir command is documented as being strange. It works if you can accommodate its quirks. We could, but anyone wanting further and better particulars should start with Lomax, “VB & VBA in a nutshell”, O’Really.

Not only did this work, it worked surprisingly quickly. The only thing that was slow was the print preview if a B&W printer was selected. The re-rendering of colour to B&W by Excel seemed to cause it problems.

Excel under Perl

use strict;
use warnings;
use Win32::OLE;
use File::Spec;

my $ksFactor = 0.177346958265686;
my $knMaxWidth = 100;

my $xl = Win32::OLE->new('Excel.Application');
$xl->{EnableEvents} = 0;
$xl->{ScreenUpdating} = 1;
$xl->{Visible} = 1;            
my $wb = $xl->Workbooks->Add;
my $sht = $wb->Sheets(1);

my $dialogue = $xl->FileDialog(4); #msoFileDialogFolderPicker = 4
$dialogue->{Title} = "Choose a directory";
$dialogue->Show;
if ($dialogue->SelectedItems->Count == 0) {die "No directory selected"
+;}
my $dirName = $dialogue->SelectedItems(1);

my $filemask = File::Spec->catfile($dirName, '*.jpg');
my @sFiles   = sort(glob($filemask));
my $nWidth   = int(sqrt(scalar @sFiles)) - 1;
my $nHeight  = int(scalar @sFiles / $nWidth);
if ($nWidth * $nHeight < scalar @sFiles) {$nHeight++};

my $nCount = 0;
for my $row (1..$nHeight) {
    my $nMaxHeight = 0;
    for my $col (1..$nWidth) {
        if ($nCount < scalar @sFiles) {
            my $picCurrent = $sht->Pictures->Insert($sFiles[$nCount]);
            $picCurrent->{Top}  = $sht->Cells($row, $col)->{Top};
            $picCurrent->{Left} = $sht->Cells($row, $col)->{Left};
            my $nPicWidth  = $picCurrent->{Width};
            my $nPicHeight = $picCurrent->{Height};
            if ($nPicWidth > $knMaxWidth) {
                $picCurrent->{Width}  = $knMaxWidth;
                $picCurrent->{Height} = $knMaxWidth / $nPicWidth * $nP
+icHeight;
            }
            $nMaxHeight = $xl->Max($picCurrent->{Height}, $nMaxHeight)
+;
            my ($vol, $path, $file) = File::Spec->splitpath($sFiles[$n
+Count]);
            $sht->Cells($row, $col)->{Value} = $file;
            if ($row == 1
            || $sht->Columns($col)->{ColumnWidth} < $picCurrent->{Widt
+h} * $ksFactor) {
               $sht->Columns($col)->{ColumnWidth} = $picCurrent->{Widt
+h} * $ksFactor;
            }
            $nCount++;
        }
    }
    $sht->Rows($row)->{RowHeight} = $nMaxHeight + 11.75;
}

for my $col (1..$nWidth) {
    $sht->Columns($col)->{ColumnWidth}++;
}

my ($vol, $path, $file) = File::Spec->splitpath($sFiles[0]);
my $setup = $sht->PageSetup;
$setup->{LeftHeader}     = $vol . $path;
$setup->{Orientation}    = 1;                 #xlPortrait
$setup->{Zoom}           = 0;                #False
$setup->{FitToPagesWide} = 1;
$setup->{FitToPagesTall} = 1;

[download]

We went our separate development paths at this stage. Martin came up with something intended to be OS independent to split out the file name from the directory. I duly incorporated it, but left out the cleanup code, as a new Excel instance was being started every time. I also added code to set up the print layout. I found that I got more of the A4 sheet used if I subtracted 1 from the number of columns, and this change appears in the code above.

Back to sensible software

Martin, meanwhile, was unimpressed that “that bloody thing” (he was by now refusing to speak the name) was faster than Perl, and decided to use the smallest, fastest graphics manipulation module to scale each picture individually rather than running out of memory. The result was:

#!perl
use strict;
use warnings;
use diagnostics;
use File::Spec;
use GD;

# Edit only these five values
my $WIDTHmm  = 201;     # printable area width in mm
my $HEIGHTmm = 288;     # printable area height in mm
my $BORDERmm = 5;       # white space between photos in mm
my $FONTmm   = 3;       # font height in mm
my $DPI = 96;           # dots per inch

# Remaining variables derived from the above
my $DPmm   = $DPI/25.4;    # dots per mm
my $WIDTH  = $WIDTHmm  * $DPmm;
my $HEIGHT = $HEIGHTmm * $DPmm;
my $BORDER = $BORDERmm * $DPmm;
my $POINTS = $DPmm * $FONTmm;
my $TEXTHEIGHT = $POINTS;
print "$POINTS points\n";

my $dir = shift || ".";
my $filemask = File::Spec->catfile( $dir, '*.jpg' );
my @image_filenames = sort(glob($filemask)); # sort just to be sure...

# Dimension an almost square rectangle
my $cols = int( sqrt(scalar @image_filenames) );
my $rows = (scalar @image_filenames) / $cols;
if ( $rows != int($rows)  ) { $rows = int($rows) + 1; }
# Calculate the cell size in pixels
# totalwidth = cols * cellwidth + ( cols - 1 ) * border
# (totalwidth - ( cols - 1 ) * border) / cols = cellwidth
my $cellWidth = ($WIDTH - ($cols - 1) * $BORDER ) / $cols;
my $cellHeight = ($HEIGHT / $rows) - $BORDER;

my $img = new GD::Image($WIDTH, $HEIGHT);

$img->transparent($img->colorAllocate(255,255,255));
my $black = $img->colorAllocate(0,0,0);
my ($row, $col) = (0,0);
for my $imgname (@image_filenames) {
    my $imgsrc = GD::Image->newFromJpeg($imgname);
    my ($widthsrc,$heightsrc) = $imgsrc->getBounds();
    # Scale the image, preserving the aspect ratio
    my $scalefactor = $cellWidth / $widthsrc;
    if ( $cellHeight / $heightsrc < $scalefactor ) { $scalefactor = $c
+ellHeight / $heightsrc; }
    my $dstX = $col * ($cellWidth + $BORDER);
    my $dstY = $row * ($cellHeight + $BORDER);
    my $destW = $scalefactor * $widthsrc;
    my $destH = $scalefactor * $heightsrc;
    $img->copyResampled($imgsrc, $dstX, $dstY, 0, 0, $destW, $destH, $
+widthsrc, $heightsrc);
    my ($vol, $path, $file) = File::Spec->splitpath($imgname);
    my @ret = $img->stringFT($black,$ENV{'windir'}."\\Fonts\\arial.ttf
+",$POINTS,0,$dstX,$dstY+$destH+$TEXTHEIGHT,$file);
    unless ( @ret ) { print $@; }
    if (++$col >= $cols) { $col=0; ++$row; } # next picture position
}

open(my $fh, ">", "$dir\\Thumbs.png") or die "Couldn't open output fil
+e.";
binmode $fh;
print $fh $img->png;
close $fh;

[download]

I don’t think this code is finished, as it relies on a hard coded directory for fonts and has a conflict of styles that come from two different programmers and two different styles. But the resulting file can be handled by both FireFox and Internet Exploder without memory issues. Unfortunately, the time taken to read the pictures still makes it about half the speed of Excel.

For the future

Martin is keen to do a version in Perl 6. We had a look at both the GD and Excel implementations in Perl 6. Martin is getting somewhere (slowly, I think, because of the curse of the drinking classes) with getting GD to work with Perl 6. We looked together at trying to get Excel to work under Perl 6, but had difficulty finding the necessary documentation to identify the DLLs we needed to refer to. But we did find a way around one problem, which Martin is hopeful may clear some bottlenecks in Perl 6 development. Don’t ask me to explain further – I don’t understand Perl 5, let alone 6.

Conclusions

Bill Gates is doing something very clever when handling graphics. IE is materially more memory efficient than FireFox, while the best solution of all remains Excel. This should not be.

The code we have developed is intended for the specific case of a reference print of a 35mm film. It is unlikely to be appropriate (although it may well be a useful starting point) for directories containing hundreds of pictures. The actual act of printing has been omitted deliberately, as I want to be able to see what I'm going to print before it goes to the wrong printer.

I’ve learned more about Perl and Martin’s learned more about Excel.

In reply to Perl, VBA, HTML, Excel, FireFox, Internet Exploder and 35mm photography by davies

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.