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);

#-------------------------------------------------------------------------------