0

I am new to perl and I have just started using the module Spreadsheet::ParseExcel. I do not want to write anything to the Excel sheet that I am using. The script should basically run through the Excel sheet and print the values row by row. I have around 250 rows and 3 columns. So output has to be something like :

   Glendale Academy  Mark  40%
   Glendale Academy  Tom   60%
   .....
   .....
   .....

On the terminal.

I am using this Windows ( Should I consider Win32::OLE and Win32::OLE::Const 'Microsoft Excel'. ( What should be the format of the sheet ? .xls , .xlsx , .csv ) Here is what I have done so far: After going through a lot of scripts on this website, I thought using 'Split' would be the easiest.That was super easy now that it is excel I am not able to understand how to go with this.

My script so far is this;

use strict;
use warnings;
use Spreadsheet::ParseExcel;

my $reader   = Excel::Write::XLSX->new();  # I am sure something is wrong here
my $workbook  = Spreadsheet::WriteExcel->new('Draft.xls');
#my $workbook = $reader->read_file( 'Draft.xlsx' );

if ( !defined $workbook ) {
    die $reader->error(), "\n";
}

for my $worksheet ( $workbook->worksheets() ) {
    my $sheetname = $worksheet->name();
    print "Sheet = $sheetname\n";
    while ( my $row = $worksheet->next_row() ) {
        while ( my $cell = $row->next_cell() ) {
            my $row   = $cell->row();
            my $col   = $cell->col();
            my $value = $cell->value();
            print "  Cell ($row, $col) = $value\n";
        }
    }
}

Any kind of help would be greatly appreciated guys; Stuck for like a week now....

TLP
  • 66,756
  • 10
  • 92
  • 149
user2121709
  • 11
  • 2

1 Answers1

1

There are a few things wrong in the initial program:

  1. The program is using Excel::Write::XLSX which contains a typo, isn't imported and doesn't have anything to do with Spreadsheet::ParseExcel or reading data.
  2. The program then uses Spreadsheet::WriteExcel which isn't imported and doesn't have anything to do with Spreadsheet::ParseExcel or reading data.
  3. The program then seems to use the Excel::Reader::XLSX interface (next_row and next_cell).

If you want to read data from an Excel XLS file just use the interface and example program from the Spreadsheet::ParseExcel docs:

#!/usr/bin/perl -w

use strict;
use Spreadsheet::ParseExcel;

my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('Draft.xls');

if ( !defined $workbook ) {
    die $parser->error(), ".\n";
}

for my $worksheet ( $workbook->worksheets() ) {

    my ( $row_min, $row_max ) = $worksheet->row_range();
    my ( $col_min, $col_max ) = $worksheet->col_range();

    for my $row ( $row_min .. $row_max ) {
        for my $col ( $col_min .. $col_max ) {

            my $cell = $worksheet->get_cell( $row, $col );
            next unless $cell;

            print "Row, Col    = ($row, $col)\n";
            print "Value       = ", $cell->value(),       "\n";
            print "Unformatted = ", $cell->unformatted(), "\n";
            print "\n";
        }
    }
}

__END__

Which when run against a sample spreadsheet like you show above gives:

Row, Col    = (0, 0)
Value       = Glendale Academy
Unformatted = Glendale Academy

Row, Col    = (0, 1)
Value       = Mark
Unformatted = Mark

Row, Col    = (0, 2)
Value       = 40%
Unformatted = 0.4

Row, Col    = (1, 0)
Value       = Glendale Academy
Unformatted = Glendale Academy

Row, Col    = (1, 1)
Value       = Tom
Unformatted = Tom

Row, Col    = (1, 2)
Value       = 60%
Unformatted = 0.6
jmcnamara
  • 38,196
  • 6
  • 90
  • 108
  • But how do I format the output in a streamline, as in If i want all of the cell values to be printed in 1 line. – user2121709 Mar 01 '13 at 23:48
  • Like Glendale Academy Mark 40% , do i need to specify rows and column ranges or is there an efficient way of doing this !!! – user2121709 Mar 01 '13 at 23:49
  • John also can you give me any idea how to compare the last column values with some threshold say 35%. – user2121709 Mar 02 '13 at 01:09
  • As in all the values from that column,say Mark has 40%, Tom has 60%, Jenny has 70%, I want to compare this with the threshold of 35% and give a verdict whether they have passed or failed? Any Ideas.... – user2121709 Mar 02 '13 at 01:10