DevzS DevzS - 7 months ago 40
Perl Question

how to read column in excel sheet using perl

I have an excel sheet with 5 columns, in which I want details of column C.
below is my code, which I tried

use Win32::OLE::Const 'Microsoft Excel';
use Spreadsheet::Read;
use Spreadsheet::ParseExcel;
my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('abc.xls');
my $sheet_got;
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;
$cell = $cell->value();
$sheet_got .= "$cell ";
}
}
}

Answer
for my $row ( $row_min .. $row_max ) 
    {
        for my $col ( $col_min .. $col_max ) 
        {
           my $cell = $worksheet->get_cell( $row, $col );
           next unless $cell;
           $cell = $cell->value();
           $sheet_got .= "$cell ";               
           }           
    }

Which clearly states that you are reading all values of excel file. Mention the column you need to extract,

Try like this,

say excel data like,

Name - Age - Gender - Result

jack - 21 - M - ok - PASS

dane - 32 - M - ok - PASS

stuart - 45 - M - ok - PASS

jane 65 F ok PASS

po - 24 - M - ok - PASS

So here column C is, Gender, so the result should be M M F M

use Win32::OLE::Const 'Microsoft Excel';
use Spreadsheet::Read;
use Spreadsheet::ParseExcel;
my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('test.xls');
my $sheet_got;
for my $worksheet ( $workbook->worksheets() ) 
{
my ( $row_min, $row_max ) = $worksheet->row_range();
my $col = 2;
for (;$row_min<$row_max;)
    {
        my $cell = $worksheet->get_cell( $row_min, $col );
        next unless $cell;
        $cell = $cell->value();
        $sheet_got .= "$cell ";
        $row_min = $row_min + 1;
    }
}
print $sheet_got;

The output will be,

>perl parseexcelsample.pl
M M M F