Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

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
Replies are listed 'Best First'.
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 dasgar (Curate) 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.
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.

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (8)
As of 2015-07-31 05:28 GMT
Find Nodes?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...

    Results (274 votes), past polls