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

Here is what I'm trying to do: I'm trying to open an XLSX file to read the contents from the spreadsheets. I had the code working for xls files but now I'm having problems getting the script to work with xlsx files.

Below is the code I had to open xls files:

my $xls_file = "File Path is here";
my $xl_app = CreateObject OLE 'Excel.Application' || die $!;
$xl_app->{'Visible'} = 0; # Set Application Visibility, 0 = Not Visible
my $workbook = $xl_app->Workbooks->Open($xls_file);

Now i need to modify the 4th line so the script will open the file and be able to read the contents that are in the file.

Any help/suggestions will be greatly appreciated.

Replies are listed 'Best First'.
Re: Read XLSX Files
by steve (Deacon) on Apr 22, 2009 at 15:38 UTC
    XLSX files are actually a zip file containing various xml files. If you disregard Corion's wise suggestion to use a module available on CPAN, you will need to make your script deal with that. You may be able to learn more about how the file structure works by unzipping the file and looking at the various files and directories/folders contained therein (if you are running windows this can be as simple as changing the file extension to .zip and using the included shell extension to extract the files). This format is not nearly as simple and straightforward to parse as the old XLS format, so unless you plan on using one of the modules on CPAN you should expect to spend some time figuring out how the whole XLSX structure works.
[reply]
Re: Read XLSX Files
by dHarry (Abbot) on Apr 22, 2009 at 14:56 UTC

    Recently I had a similar problem but I used a QED approach: I downloaded some tool for converting xslx to the old format and from then on it was business as usual. You might want to take a look at xslx.

    HTH
    dHarry

[reply]
Re: Read XLSX Files
by Corion (Patriarch) on Apr 22, 2009 at 14:56 UTC

    Just install Excel 2007 and it will magically work. Alternatively, look after XLSX.

[reply]
Re: Read XLSX Files
by jmcnamara (Monsignor) on Apr 22, 2009 at 21:15 UTC

    If your Win32::OLE program is working with Excel 2003 and is able to read xls files then it may continue to work with the newer Excel 2007 xlsx files if you install the MS Office 2007 Compatibility pack for Office 2003.

    --
    John.

[reply]
Re: Read XLSX Files
by spectre9 (Beadle) on Apr 23, 2009 at 16:20 UTC
    Those of you replying this this "front page" article might also find my Meditation MS Excel One-Liner Challenge of interest.

    "Upgrade your gray matter, 'cause one day it may matter." -- DELTRON 3030
[reply]
Re: Read XLSX Files
by Anonymous Monk on Apr 22, 2009 at 15:00 UTC
    Yeah, that happens.

    What module are you using?
[reply]
      I have the XLSX.pm module installed in C:\Perl\site\lib\Spreadsheet
      Also Utility2007.pm & Fmt2007.pm are installed in C:\Perl\site\lib\Spreadsheet\XLSX

      I tried modifing the 4th line as shown below:
      #my $workbook = $xl_app->Workbooks->Open($xls_file);
      my $workbook = $xl_app->XLSX->Open($xls_file);

      But received the following error when the script ran:
      Can't call method "Open" on an undefined value at "script location"

      So this is where I was thinking I needed something else to open the file instead of just the word Open.
[reply]

        You didn't read the documentation of Spreadsheet::XLSX, did you?

        Win32::OLE is a vastly different thing that Spreadsheet::XLSX. You can't easily replace one by the other without rewriting at least parts of your program. Maybe it's time for you to understand what your old program is doing and to rewrite the parts according to the Spreadsheet::XLSX documentation.

[reply]

Back to Seekers of Perl Wisdom