Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to read data from xlsx in perl

Tags:

perl

xlsx

Need a few good people help me with reading excel file with extension "xlsx" my script works for "xls" but not "xlsx", here is the code I get error: Can't call method "worksheet" on an undefined value if the file is "xlsx" here is the code I do have:

#!/usr/bin/perl -w

use warnings;
use strict;
use Spreadsheet::ParseExcel;
use Spreadsheet::XLSX;
use Date::Format;

my $filename = "../test.xlsx";
#Parse excel file
my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse("$filename");

#Get cell value from excel sheet1 row 1 column 2
my $worksheet = $workbook->worksheet('Sheet1');
my $cell = $worksheet->get_cell(0,1);

# Print the cell value when not blank
if ( defined $cell and $cell->value() ne "") {
    my $value = $cell->value();
    print "cell value is $value \n";
}
like image 837
user2912312 Avatar asked Nov 05 '13 04:11

user2912312


3 Answers

Spreadsheet::XLSX is an close equivalent of Spreadsheet::ParseExcel for .xlsx files; you need to say:

my $parser = Spreadsheet::XLSX->new();

instead of using ParseExcel.

like image 95
ysth Avatar answered Oct 21 '22 05:10

ysth


You can also use CPAN module Spreadsheet::ParseXLSX to parse xlsx files.

from documentation:

use Spreadsheet::ParseXLSX;

my $parser = Spreadsheet::ParseXLSX->new;
my $workbook = $parser->parse("file.xlsx");

see Spreadsheet::ParseExcel for further documentation.

like image 44
serenesat Avatar answered Oct 21 '22 04:10

serenesat


Assuming you have installed Spreadsheet::Read perl module which can determine the actual parser module to use for reading a file, below code fragments read and prints the cell of 1st worksheet of the input workbook. You can examine $workbook object to see all the options available to configure. This module can be used to read files in other formats like "csv", "xls" as well. Here is the link to tutorial which I found to be useful:

http://search.cpan.org/~hmbrand/Spreadsheet-Read/Read.pm

use Spreadsheet::Read;
############################################################################
# function input  : file in xlsx format with absolute path 
# function output : prints 1st worksheet content if exist
############################################################################
sub print_xlsx_file{

    my $file_path = shift;
    my $workbook = ReadData($file_path,cells => 0 );
    if(defined $workbook->[0]{'error'}){
        print "Error occurred while processing $file_path:".
              $workbook->[0]{'error'}."\n";
        exit(-1);
    }
    my $worksheet = $workbook->[1];
    my $max_rows = $worksheet->{'maxrow'};
    my $max_cols = $worksheet->{'maxcol'};

    for my $row_num (1..($max_rows))
    {
        for my $col_num (1..($max_cols)){
            print $worksheet->{'cell'}[$col_num][$row_num]."\n";
        }
    }
}
# call above function
# print_xlsx_file("/home/chammu/mybook.xlsx");
like image 40
chammu Avatar answered Oct 21 '22 03:10

chammu