in reply to Spreadsheet::XSLX parsing workbook, but not retrieving values from cells

Hello divyahk, and welcome to the Monastery!

There are two problems with your code as posted:

  1. You have use Spreadsheet::Read; instead of use Spreadsheet::XLSX;.

  2. In various places single- and double-quote characters are non-ASCII and so not recognised by the Perl parser.

With these issues fixed, your code works fine for me:

I created an Excel file called xyz.xlsx, containing one worksheet named Paper.1, and populated that worksheet as follows:

  A         B
1 Number  Name
2       1 Fred
3      15 Barney
4      27 Homer

[download]

Running this script:

#! perl
use strict;
use warnings;
use Spreadsheet::XLSX;
use Data::Dump;

my $excel = Spreadsheet::XLSX->new('xyz.xlsx');

if (!defined $excel) {
    die "Error: " . $excel->error() . "\n";
}

print "Read with Spreadsheet::XSLX\n";
dd $excel;

[download]

gives this output:

21:43 >perl 1017_SoPW.pl
Read with Spreadsheet::XSLX
bless({
  Flg1904    => 0,
  FmtClass   => bless({}, "Spreadsheet::XLSX::Fmt2007"),
  SheetCount => 1,
  Worksheet  => [
                  bless({
                    Cells       => [
                                     [
                                       bless({ _Value => "Number", For
+mat => "", Type => "Text", Val => "Number" }, "Spreadsheet::ParseExce
+l::Cell"),
                                       bless({ _Value => "Name", Forma
+t => "", Type => "Text", Val => "Name" }, "Spreadsheet::ParseExcel::C
+ell"),
                                     ],
                                     [
                                       bless({ _Value => 1, Format => 
+"", Type => "Numeric", Val => 1 }, "Spreadsheet::ParseExcel::Cell"),
                                       bless({ _Value => "Fred", Forma
+t => "", Type => "Text", Val => "Fred" }, "Spreadsheet::ParseExcel::C
+ell"),
                                     ],
                                     [
                                       bless({ _Value => 15, Format =>
+ "", Type=> "Numeric", Val => 15 }, "Spreadsheet::ParseExcel::Cell"),
                                       bless({ _Value => "Barney", For
+mat => "", Type => "Text", Val => "Barney" }, "Spreadsheet::ParseExce
+l::Cell"),
                                     ],
                                     [
                                       bless({ _Value => 27, Format =>
+ "", Type=> "Numeric", Val => 27 }, "Spreadsheet::ParseExcel::Cell"),
                                       bless({ _Value => "Homer", Form
+at => "",Type => "Text", Val => "Homer" }, "Spreadsheet::ParseExcel::
+Cell"),
                                     ],
                                   ],
                    DefColWidth => 8.43,
                    MaxCol      => 1,
                    MaxRow      => 3,
                    MinCol      => 0,
                    MinRow      => 0,
                    Name        => "Paper.1",
                    path        => "worksheets/sheet1.xml",
                  }, "Spreadsheet::ParseExcel::Worksheet"),
                ],
}, "Spreadsheet::ParseExcel::Workbook")

21:43 >

[download]

— so all the cells are being read correctly.

Perhaps the file being read is actually empty after all?

Hope that helps,

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

Replies are listed 'Best First'.
Re^2: Spreadsheet::XSLX parsing workbook, but not retrieving values from cells
by divyahk (Initiate) on Sep 17, 2014 at 12:38 UTC

    Hi Athanasius, Thanks for the reply, but the code still doesn't work for me.

    1. According to this page at CPAN

    "Spreadsheet::Read tries to transparently read *any* spreadsheet and return its content in a universal manner independent of the parsing module that does the actual spreadsheet scanning.

    For OpenOffice and/or LibreOffice this module uses Spreadsheet::ReadSXC

    For Microsoft Excel this module uses Spreadsheet::ParseExcel, Spreadsheet::ParseXLSX, or Spreadsheet::XLSX.

    For CSV this module uses Text::CSV_XS or Text::CSV_PP.

    For SquirrelCalc there is a very simplistic built-in parser"

    So, I think it wouldn't matter if I used Spreadsheet::Read instead of Spreadsheet::XLSX. In any case, I tried changing the use statement, and it still doesn't make a difference. The cells are still not read.

    2. Can you please explain what you mean when you say single- and double-quote characters are non-ASCII characters? Do you mean I should check if there are quotes in the Excel file itself? If so, no, there aren't any characters within quotes in the file.

    3. I'm pretty sure the file is not empty, and I verified it by opening it, with the Excel application.

[reply]

      But when using Spreadsheet::Read, you should also use the methods that Spreadsheet::Read provides and not use the methods the undelying parser might use. That defeats the purpose of the wrapper.

      First install Spreadsheet::ParseXLSX and then read the file using the right syntax:

      use strict;
use warnings;

use Spreadsheet::Read;

my $excel = ReadData ("xyz.xlsx");
defined $excel or die;

print "Read with ", $excel->[0]{parser}, "\n";

      [download]

      Regarding the quotes, that might be a cut-n-paste (windows) problem. The post of your code shows the use of "smart"-quotes “ U0201c \N{LEFT DOUBLE QUOTATION MARK} instead of plain duoble-quote ".


      Enjoy, Have FUN! H.Merijn
[reply]
[d/l]
[select]

        Hi,

        Thanks for the suggestion. I tried using Spreadsheet::ParseXLSX to read the file, and it seems to work just fine.

        So I think this problem is solved, but has taken me to the next problem which is that my file seems too big and therefore causes some kind of segmentation fault. That, according to the documentation, is to be expected for extremely big files.

        Any suggestion on using another library for bigger files?

        Thanks!

[reply]

In Section Seekers of Perl Wisdom