Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling

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 (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
[1nickt]: marto would like to hear that. Of course he speaks very deliberately and dramatically, allowing pauses between words, so if anybody could pull it off it would be him, or someone impersonating him ...
[Eily]: Discipulus that's a chess joke BTW right? Because bishops don't walk straight :D
[choroba]: stop making chessy jokes about bishops!
[choroba]: I mean cheesy
[LanX]: lol
[ambrus]: no, the chess bishop itself is a joke on real bi-shops
[ambrus]: or at least on the stereotype of bi-shops
[choroba]: we call chess bishops "archers"
[Eily]: choroba sorry, I had to get that off my chess
[ambrus]: chess certainly hasn't started those stereotypes, like how kings aren't that powerful but has convinced their whole country to work for them, etc. it's just poured them to a nice clean form that would easily get propagated.

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (14)
As of 2017-09-26 12:13 GMT
Find Nodes?
    Voting Booth?
    During the recent solar eclipse, I:

    Results (294 votes). Check out past polls.