Generating Excel Spreadsheets Programatically

Back to Capacity Forecasting Tool

Perl

• Write Excel Spreadsheets
• [1]

Examples

PERL

Translate CSV to Excel Format

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. To convert the passwd file to a spreadsheet use the folloing command:

$ ./txt2xls ":" /etc/passwd