Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

Re: XLSX read and dump

by talexb (Canon)
on Jan 18, 2012 at 16:51 UTC ( #948587=note: print w/ replies, xml ) Need Help??


in reply to XLSX read and dump

Have you installed Spreadsheet::XLSX yet? If not, do that, then and read the documentation. It contains a simple program that opens a spreadsheet and dumps the contents. That should provide you with enough information that you'll be able to extract the data you want.

Jump in, try it out, and let us know where you get stuck!

Alex / talexb / Toronto

"Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds


Comment on Re: XLSX read and dump
Re^2: XLSX read and dump
by chirp84 (Novice) on Jan 18, 2012 at 17:47 UTC
    Yes, I started with Spreadsheet::XLSX but got an error as below: IO error: opening test.xlsx for read : No such file or directory at /System/Library/Perl/Extras/5.12/Archive/Zip/Archive.pm line 546 Archive::Zip::Archive::read('Archive::Zip::Archive=HASH(0x7f87628288d8)', 'test.xlsx') called at /Library/Perl/5.12/Spreadsheet/XLSX.pm line 33 Spreadsheet::XLSX::new('Spreadsheet::XLSX', 'test.xlsx') called at parse.pl line 6 Cannot open test.xlsx as Zip archive at /Library/Perl/5.12/Spreadsheet/XLSX.pm line 33.

      First of all, a meta-comment: you should wrap your errors in code tags to a) make that part of it easier to read and b) make it stand out from the rest of your post. Ideally, the errors would look like

        IO error: opening test.xlsx for read : No such file or directory at /System/Library/Perl/Extras/5.12/Archive/ +Zip/Archive.pm line 546 Archive::Zip::Archive::read('Archive::Zip::Archive=HASH(0x7f87628288d8 +)', 'test.xlsx') called at /Library/Perl/5.12/Spreadsheet/XLSX.pm line 33 Spreadsheet::XLSX::new('Spreadsheet::XLSX', 'test.xlsx') called at parse.pl line 6 Cannot open test.xlsx as Zip archive at /Library/Perl/5.12/Spreadsheet +/XLSX.pm line 33
      I've added a few line breaks to make it easier to read.

      Second, it looks like (and I'm guessing, because you haven't posted any code yet) you are accessing the spreadsheet while it's inside a zip file. To make it easier, can you just extract one of the spreadsheets and operate on that?

      Let's try to solve one problem at a time (aka, "You've got to walk before you can run.")

      Alex / talexb / Toronto

      "Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds

        The test.xlsx file isn't in a zip file. I thought it was going to create a new file called test.xlsx, but didn't. So then I tried renaming one of the files to test.xlsx and placed it in the same directory as the script below, named parse.pl, thinking that it would read it. Either way, I still get the same error about a zip archive.
        use strict; use warnings; use Spreadsheet::XLSX; my $excel = Spreadsheet::XLSX -> new ('test.xlsx'); foreach my $sheet (@{$excel -> {Worksheet}}) { printf("Sheet: %s\n", $sheet->{Sheet1}); $sheet -> {B45} ||= $sheet -> {B37}; foreach my $row ($sheet -> {B37} .. $sheet -> {B45}) { $sheet -> {R45} ||= $sheet -> {C45}; foreach my $col ($sheet -> {C45} .. $sheet -> {R45}) +{ my $cell = $sheet -> {Cells} [$row] [$col]; if ($cell) { printf("( %s , %s ) => %s\n", $row, $col, +$cell -> {Val}); } } } }

        FYI, XLSX files ARE zipped. If you look at it in an editor or hex viewer, the first two characters are PK, which indicates it's a ZIP file (from PKZIP, the forerunner of modern ZIP, where PK stands for Phil Katz, who invented it).

        If you run unzip (or pkunzip, etc.) on the file, it creates some directories that are full of XML files and RELS files (MS Office 2007+ relationship files). Any module working with an XLSX file would need to include something from the Archive::ZIP family.

        That said, I don't work on a Mac, but I would have assumed Spreadsheet::XLSX would have installed Archive::Zip::Archive as a dependency. Maybe the OP should try installing it manually.

        --marmot

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://948587]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (8)
As of 2014-08-02 08:37 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Who would be the most fun to work for?















    Results (55 votes), past polls