ravi45722 has asked for the wisdom of the Perl Monks concerning the following question:

I want to parse a xlsx and written this code. Its working normally when I give exit before

 my $workbook = $parser->parse("NSN_SGSN_3G_Report_20151116.xlsx");

If there is no exit before it waiting simply even not printing any statements too. I checked my file name trice. Later I through module I came to know that MSOFFICE 2007 files cant be read by that module. Is there any other way to do this????

#!/usr/bin/perl use warnings; use strict; my $config_dir = "/root/prac/NSN_SGSN/Config/"; my @circle_list_2g = (); my @circle_list_3g = (); my %seen_cir_2g = (); my %seen_cir_3g = (); my %circle_name = (); my @circle_mapping_list=`cat $config_dir/circle_name_2g`; foreach my $entry(@circle_mapping_list) { chomp $entry; my ($name,$id)=split(/\:/,$entry); $circle_name{$id}=$name; push (@circle_list_2g,$id) unless $seen_cir_2g{$id}++; } @circle_mapping_list=`cat $config_dir/circle_name_3g`; foreach my $entry(@circle_mapping_list) { chomp $entry; my ($name,$id)=split(/\:/,$entry); $circle_name{$id}=$name; push (@circle_list_3g,$id) unless $seen_cir_3g{$id}++; } print @circle_list_3g; use Spreadsheet::ParseXLSX; my $parser = Spreadsheet::ParseXLSX->new; my $workbook = $parser->parse("NSN_SGSN_3G_Report_20151116.xlsx"); if ( !defined $workbook ) { die $parser->error(), ".\n"; } my $worksheet = $workbook->worksheet(6); 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"; } }

Replies are listed 'Best First'.
Re: Problem in Parsing XLSX
by Corion (Pope) on Dec 16, 2015 at 12:11 UTC

    Have you tried adding more print statements to find out where your program stops?

    The most likely suggestion would be that the seventh workbook is empty, in the sense that it has no filled rows or columns:

    my $worksheet = $workbook->worksheet(6);

    I really, really recommend using a name-based approach instead of using indices to access the sheets if you have problem with converting from one-based offsets to zero-based offsets.

Re: Problem in Parsing XLSX
by MidLifeXis (Monsignor) on Dec 16, 2015 at 13:35 UTC

    As an abstraction for reading various types of spreadsheet-type items, I can recommend Spreadsheet::Read. Install the support modules for your particular flavor of spreadsheet, and you don't need to modify your underlying code that reads spreadsheets. The resulting format is a bit low level, but that is also the type of output that many of the other format-specific modules provide.

    I have not had a need to find a similar module for a writer.


Re: Problem in Parsing XLSX
by Athanasius (Archbishop) on Dec 16, 2015 at 12:09 UTC

    Hello ravi45722,

    Later I through module I came to know that MSOFFICE 2007 files cant be read by that module.

    I don’t know what you mean here. The Spreadsheet::ParseXLSX module is designed to read .xlsx files, which are the default workbook files for Excel 2007 (see Microsoft_Excel#Current_file_extensions). If you need to read files in the older .xls format, use the Spreadsheet::ParseExcel module instead. If this doesn’t answer your question, please clarify the problem.

    BTW, the Unix cat command returns the contents of a file as a single string. So the statement:

    my @circle_mapping_list=`cat $config_dir/circle_name_2g`;

    populates only the first element of the array, namely $circle_mapping_list[0]. Therefore the following foreach loop:

    foreach my $entry(@circle_mapping_list)

    can have at most one iteration. I doubt this is what you intended.

    Update: See correction by Tux, below.

    Hope that helps,

    Athanasius <°(((><contra mundum Iustus alius egestas vitae, eros Piratica,

      No it does not:

      $ perl -wE'my @x=`ls -1`;say $#x' 102

      backticks are the equivalent of the qx operator:

      $ perldoc -f qx "qx/STRING/" A string which is (possibly) interpolated and then execute +d as a system command with /bin/sh or its equivalent. Shell wildc +ards, pipes, and redirections will be honored. The collected sta +ndard output of the command is returned; standard error is unaff +ected. In scalar context, it comes back as a single (potentially multi-line) string, or "undef" if the command failed. In l +ist ^^^^ +^^^ context, returns a list of lines (however you've defined l +ines ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ with $/ or $INPUT_RECORD_SEPARATOR), or an empty list if t +he command failed.

      Enjoy, Have FUN! H.Merijn
Re: Problem in Parsing XLSX
by u65 (Chaplain) on Dec 16, 2015 at 12:04 UTC

    I have had success with Data::Table::Excel for reading MS Excel xlsx files. It's awkward to use, but it works.

    Hope that helps.