Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Read and Edit XLSX files

by tosaiju (Acolyte)
on Apr 12, 2012 at 11:05 UTC ( #964745=perlquestion: print w/ replies, xml ) Need Help??
tosaiju has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

I'm trying to read and also to modify an Excel (XLSX) file. I found Spreadsheet::XLSX, which helps to read data from XLSX file, but wondering how best I can modify the data - where the program should work on both Windows and Linux platform.

Many Thanks for your help.

Comment on Read and Edit XLSX files
Re: Read and Edit XLSX files
by Tux (Monsignor) on Apr 12, 2012 at 11:36 UTC
      Thats great...sure..I'll follow that.....
Re: Read and Edit XLSX files
by temporal (Pilgrim) on Apr 12, 2012 at 15:29 UTC

    Adding to the previous comment, I have successfully used Spreadsheet::ParseExcel and Spreadsheet::WriteExcel on a Windows platform to do what you're describing. Haven't tried it on Linux, but the docs claim that it should work.

      They do. I use them on Linux, HP-UX and AIX. But these are for .xls, not for .xlsx


      Enjoy, Have FUN! H.Merijn
        Oh! Right, missed that.

      While both Spreadsheet::ParseExcel and Spreadsheet::WriteExcel work on Windows and Linux, neither will read or write xlsx files. I've taken the non-Perl approach of saving an xlsx file in the older xls format and then using the former to parse the document. It's a little busy work, but gets the job done. One common gotcha with this approach is that the two Excel formats have different row limits. xls has a limit of 65k rows, while xlsx has a 1 million row limit. There are other incompatibilities but I find this one to be more common.

Re: Read and Edit XLSX files
by dasgar (Deacon) on Apr 12, 2012 at 16:16 UTC

    When I use Perl to manipulate Excel files, I'm usually on a Windows system that has Excel installed. As such, I primarily use Win32::OLE to control Excel directly to make the changes.

    In your case, I believe that you'll need to use Spreadsheet::XLSX to read in the contents of the Excel spreadsheet and use Excel::Writer::XLSX to do the writing/modifying.

      Spreadsheet::XLSX works fine for reading xlsx files. I think Excel::Writer::XLSX is to create new .xlsx files? I would like to edit existing .xlsx file. Also Excel::Writer:XLSX is not listed in PPM. May be required to download - but if it was there in PPM - it would have been easier.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://964745]
Approved by kcott
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (5)
As of 2014-09-20 02:59 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (152 votes), past polls