Generating Excel Spreadsheets Programatically

Back to Capacity Forecasting Tool

Perl

• Write Excel Spreadsheets
• [1]

Examples

PERL

Translating a CSV File to an Excel Spreadsheet

Here is the script:

 #!/usr/bin/env perl
 #
 #-------------------------------------------------------------------------------
 
 use strict;
 use Spreadsheet::WriteExcel;
 
 #-------------------------------------------------------------------------------
 
 if ($#ARGV ne 1) {
    print "\n Usage: txt2xls \n Example: txt2xls \"|\" *.psv\n\n";
 }
 
 my $token;
 my $file;
 my $del;
 my $wb;
 
 my $separator = quotemeta($ARGV[0]);
 my @files     = @ARGV[1..$#ARGV];
 
 foreach $file (@files){
    open (TXTFILE, "$file") or die;
 
    my $wb      = Spreadsheet::WriteExcel->new("$file.xls");
    my $excel   = $wb->addworksheet();
    my $row     = 0;
    my $col;
 
    while (<TXTFILE>) {
       chomp;
 
       my @Fld = split(/$separator/, $_);
 
       $col = 0;
 
       foreach $token (@Fld) {
          $excel->write($row, $col, $token);
          $col++;
       }
 
       $row++;
    }
 }
 
 #-------------------------------------------------------------------------------

Here is how it is used:

 $ cat example  
 
 aaa,bbb,ccc,ddd
 aaa,bbb,ccc,ddd
 aaa,bbb,ccc,ddd
 aaa,bbb,ccc,ddd
 aaa,bbb,ccc,ddd
 
 $ ./txt2xls "," example
 
 $ ls -l example.xls
 -rw-r--r-- 1 user group 5632 2007-12-15 17:47 example.xls

The first argument is the separator character. So to convert the passwd file to a spreadsheet use the following command:

 $ ./txt2xls ":" /etc/passwd

Write a Simple Spreadsheet with Two Worksheets

 #!/usr/bin/env perl
 
 #-------------------------------------------------------------------------------
 
 use Spreadsheet::WriteExcel;
 
 #-------------------------------------------------------------------------------
 
 #  Create a new Excel workbook
 
 my $workbook = Spreadsheet::WriteExcel->new("perl.xls");
 
 #  Add a worksheet
 
 $worksheet = $workbook->addworksheet("Test 1");
 
 #  Add and define a format
 
 $format = $workbook->addformat(); # Add a format
 $format->set_bold();
 $format->set_color('red');
 $format->set_align('center');
 
 #  Write a formatted and unformatted string, row and column notation.
 
 $col = $row = 0;
 
 $worksheet->write($row, $col, "In sheet l!", $format);
 $worksheet->write(1,    $col, "Hi Excel!");
 
 #  Write a number and a formula using A1 notation
 
 $worksheet->write('A3', 1.2345);
 $worksheet->write('A4', '=SIN(PI()/4)');
 
 #  Now create a second worksheet
 
 $worksheet = $workbook->addworksheet("Test 2");
 
 $worksheet->write($row, $col, "In sheet 2!", $format);
 
 #-------------------------------------------------------------------------------