Using Perl to get Excel

This is a simple syntax that writes a Perl program to create an MS Excel spreadsheet using perl's Spreadsheet::WriteExcel.pm. Since I didn't know about tclperl I also wrote a small extension to load and eval perl code in tcl. Find the full source including all of the perl modules that you'll need here [1].

The code here rev 1 and out of date. If you'd like a working version please mail me JBR.

Then I wrote this to simply call perl, no fuss, no muss as long as perl is installed.

 proc perl { code } {
         set perl [open "| perl" w]

        puts  $perl $code
        close $perl
 }

There are four commands implemented

   workbook name { } - Begin a new excel workbook.
   worksheet name { } - Begin a new excel worksheet
   format name spec - define a cell format
   cell row col value - set a cell

I also show a command starbase2excel which creates a series of cell definitions from an array defining a table. My starbase package reads and writes ascii tables to and from tcl arrays with a structure very similar to the struct:matrix guy. This could easily be adapted to extract data from the matrix or from a list of lists.

The example:

 workbook WorkBook.xls {
        format fmt1 bold 1 color red font times

        worksheet Work {
                starbase_read tab.tab T
                set T(format,C1) fmt1

                starbase2excel T 3 3

                cell 1 1 "Cell 1 1"
                cell 1 2 value
                cell 1 3 value fmt1
        }
 }

And the tcl part:

 proc workbook { name contents } {
        lappend result {
 use lib "/home/john/perl/lib";
 use Spreadsheet::WriteExcel;

 }

        lappend result "\$workbook = Spreadsheet::WriteExcel->new(\"$name\");"
        eval $contents
        lappend result "\$workbook->close();"

        perl [join $result "\n"]
 }
 proc format { name args } {
                upvar result result

        foreach { prop value } $args {
                lappend proplist "$prop => \"$value\""
        }

        lappend result  "\$$name = \$workbook->addformat([join $proplist ", "]);
 }
 proc worksheet { name contents } {
                upvar result result

        lappend result  "\$worksheet = \$workbook->addworksheet(\"$name\");"
        eval $contents
 }

 proc starbase2excel { t { row 0 } { col 0 } } {
    upvar $t T

    set nrows [expr $T(Nrows) + $row]
    set ncols [expr $T(Ncols) + $col]

    for { set i $row } { $i < $nrows } { incr i } {
        for { set j $col } { $j < $ncols } { incr j } {
            set fmt {}
            catch { set fmt $T(format)                          }
            catch { set fmt $T(format,R[expr $i - $row + 1])             }
            catch { set fmt $T(format,C[expr $j - $col + 1])             }
            catch { set fmt $T(format,R[expr $i - $row + 1]C[expr $j - $col + 1]
            uplevel cell $i $j $T([expr $i - $row + 1],[expr $j - $col + 1]) $fm
        }
    }
 }
 proc cell  { row col cell { format {} } } {
        upvar worksheet worksheet
        upvar result    result

        if { $format != {} } { set format ", \$$format" }
        lappend result  "\$worksheet->write($row, $col, \"$cell\" $format);"
 }

Dave Griffin: If PERL isn't available, tCOM has pretty good access to all things Microsoft, with a similar interface.