Redirect the output to different worksheet in excel

Hi,

  I need to redirect of a script to different worksheet in a single excel file. I have four queries in that script. Each query output needs to be placed in separate worksheet of a excel file. can someone help me to find this?

If you're using CSVs to import the data into excel then there is no way to create multiple worksheets in a single file with simple redirection.

However, I have a feeling Perl gurus would say "I bet there's a module for that", as usual.

For this, you really do need an XLS file, not CSV.

In order to make worksheets in an XLS file, you'll need install a perl module like Spreadsheet::WriteExcel

How to use it depends on what you want to do with it, of course. Show the text data you want to put into the spreadsheet please.

First try:

#!/usr/bin/perl

use strict;
use warnings;
use Spreadsheet::WriteExcel;

my ($infile, $line, $worksheet, @cols, $row, $col);
my $workbook = Spreadsheet::WriteExcel->new('output.xls');

while($infile = shift)
{
        $row=0;

        $worksheet = $workbook->add_worksheet($infile); # Add a worksheet
        open(FIN, "<$infile") || die("Couldn't open $infile"); # open csv

        while(!eof(FIN)) {
                chomp($line=<FIN>);
                @cols=split(/,/, $line);

                for($col=0; $col <= $#cols; $col++) {
                        $worksheet->write($row, $col, $cols[$col]);
                }
                $row++;
        }

        close(FIN);
}

$ ./csv.pl a.csv b.csv
$ ls -l output.xls

-rw-r--r-- 1 username users 5632 Sep 18 11:36 output.xls

$

is it possible using the shell script?

Short answer, no.

Long answer, nnnnno. This is quite impractical to do in pure shell. XLS is a complicated, proprietary, binary, microsoft thing which was opaque to most anything for a surprisingly long time. WriteExcel was written because it was needed so badly.

Maybe you could find a utility to do so, but the utility would probably be written in perl, and demand WriteExcel or some other perl module.

I don't know about the perl scripting. Can you pls explain excatly what i need to do?

1) Install Spreadsheet::WriteExcel. How to do this depends on your distribution -- hopefully your distro has a package for it. It ought to, since it's a quite popular extension.

2) Paste this into csv.pl:

#!/usr/bin/perl

use strict;
use warnings;
use Spreadsheet::WriteExcel;

my ($infile, $line, $worksheet, @cols, $row, $col);
my $workbook = Spreadsheet::WriteExcel->new('output.xls');

while($infile = shift)
{
        $row=0;

        $worksheet = $workbook->add_worksheet($infile); # Add a worksheet
        open(FIN, "<$infile") || die("Couldn't open $infile"); # open csv

        while(!eof(FIN)) {
                chomp($line=<FIN>);
                @cols=split(/,/, $line);

                for($col=0; $col <= $#cols; $col++) {
                        $worksheet->write($row, $col, $cols[$col]);
                }
                $row++;
        }

        close(FIN);
}

3) Set csv.pl executable like chmod +x ./csv.pl
4) Run it like ./csv.pl file1.csv file2.csv file3.csv and watch it generate the file output.xls from that data.

Spreadsheet::WriteExcel package? I am using Sun solaris 5.8. How can i check whether this is installed or not? If not where can i get this one?

Well, does it work or not? If the program runs without complaining that it can't find Spreadsheet::WriteExcel, that's a pretty good indication that you have it!

You might have to install it with CPAN, by logging into root, running 'cpan', answering the questions, then doing install Spreadsheet::WriteExcel; inside CPAN.

Thanks for your help. I will check it and get back to you soon.