Back in the days when I did not know perl (which is not long ago), I wrote an elegant csv to excel converter in JAVA using POI to generate some reports. While POI is much more than a simple excel writer, the coding time was comparatively high (more so because that solution supported many features like advanced designs etc.). Perl provides a quick solution, if you require a script to generate reports from any data source. Either you may modify the example below to use another data source, or write a wrapper script to get data in csv from sql etc., and then call the perl script.

The prerequisites for running this script are:

1. Parse-RecDescent-1.94
2. OLE-Storage_Lite-0.19
3. Spreadsheet-WriteExcel-2.37

For those who know perl, the script is simple enough. For those who don’t, please learn . Either way, you can use this script in a very simple way.

#!/usr/bin/perl

use Spreadsheet::WriteExcel;

if ($#ARGV != 1){
        print "format: excelFileName commaSaperatedListOfCSVs\n";
        print "example: example.xls sheet1.csv,sheet2.csv\n";
        exit ;
}

$sheetName=$ARGV[0];
$csvs=$ARGV[1];

@csv_a = split(/,/, $csvs);
my $workbook = Spreadsheet::WriteExcel->new($sheetName);

for my $csv (@csv_a){
        print "processing $csv\n";
        ($name = $csv ) =~ s/\.csv//g;
        $sheet=$workbook->add_worksheet($name);
        open (CSV, $csv) || die ("Could not open $csv");
        my $row = 0;
        while ( my $line = <CSV>){
                my $col = 0;
                @entries=split (/,/, $line);
                for my $entry (@entries){
                        $sheet->write($row, $col, $entry);
                        $col++;
                }
                $row++;
        }
        close (CSV);
}

$workbook->close();

Here is a sample run:

$ perl csvToExcel.pl test.xls test1.csv,test2.csv
processing test1.csv
processing test2.csv

And here is the generated Excel file… voila!! Time to code: 10 minutes.