Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change

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.

Replies are listed 'Best First'.
Re: Read and Edit XLSX files
by Tux (Abbot) on Apr 12, 2012 at 11:36 UTC
      Thats great...sure..I'll follow that.....
Re: Read and Edit XLSX files
by dasgar (Priest) 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 all is quiet...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (6)
As of 2018-02-18 07:22 GMT
Find Nodes?
    Voting Booth?
    When it is dark outside I am happiest to see ...

    Results (251 votes). Check out past polls.