Two good options depending on your needs:

• 1) Spreadsheet::ParseExcel and Spreadsheet::WriteExcel
• 2) Win32::OLE

I can support winds recommendations.

For working with Excel, I am using Win32::OLE a lot -and I am happy with it.

• However it works only if you are in a windows-environment ... and have Excel installed
• While even executing macros embedded in my documents works, I try to keep things as easy as possible:
  • I try to avoid formatting cells
  • I try to avoid creating charts etc. dynamically
  Even if both are possible (and sometimes needed), I think it makes my code "nasty"(*) and hard to mantain. Instead I create pre-formatted Excel-templates manually, which then I fill from Perl. Works fine :-)

wind also proposes Spreadsheet::ParseExcel and Spreadsheet::WriteExcel. While I have never used them, they get recommended very often here ... so they are probably an excellent choice too.

Have fun! Rata

update: explanation of "nasty": (thanks for the feedback, davies)

I typically get a lot of data from our databases and create some grouping and statistics. The result should be reported in Excel. Of course the readers want the resulting Excel-sheets to look nice - some text should be bold, some normal, some cells need borders, different column widths, different background-colors ...

Of course it can be done with Win32::OLE. This link shows how. But soon I had hundreds of lines of pure Excel-formatting - many more than needed for retrieving, processing and writing the data. And I didn't like that. Especially after receiving requests to change column-orders and style-changes. That's why I consider it being "nasty" - and went to the manually-formatted template-solution.

I, too, have no experience of anything beyond Win32::OLE, but that's because I'm always on Windows and usually doing something that's pushing at or beyond the limits of WriteExcel. I have had no trouble at all with it, and have even found it to circumvent at least one VBA "issue" (see Re: Win32 Ole find 2nd to last column in Excel).

I very rarely work with charts, and never that I can remember in Perl, but I don't have your objection to formatting cells. I don't see what's "nasty" about Re: Formatting cells in Excel, except that I haven't put the format string in a constant. But I'm always keen to learn, so please tell me more!

Regards,

John Davies

I have made considerable use of Spreadsheet::WriteExcel and Spreadsheet::ParseExcel and have found them to be fine for my needs.

I can second both Spreadsheet::WriteExcel and Spreadsheet::ParseExcel. Both are excellent, and don't require Windows / Excel.

I would agree with Wind about Spreadsheet::ParseExcel and Spreadsheet::WriteExcel, they are both reliable modules that do a good job of navigating round Excel files, setting cell formats and so on.

However it is more common that I just want to send out a simple table of values, for those I would use a simple CSV format (via Text::CSV or similar). On a Windows machine files with the csv extension are treated as Excel files and have the benefit of working elsewhere as well.

If that is your primary goal, have you ever considered Spreadsheet::Read? It is a wrapper over the most common used Spreadsheet read/parse modules, including Spreadsheet::ParseExcel, and comes with a nice utility called xlscat, which can convert any selection from your spreadsheet to CSV using Text::CSV_XS. It also accepts .ods, .sxc, .csv, and .xlsx if the corresponding parsers are installed.

$ xlscat --help
usage: xlscat [-s <sep>] [-L] [-n] [-A] [-u] [ Selection ] file.xls
              [-c | -m]                 [-u] [ Selection ] file.xls
               -i                            [ -S sheets ] file.xls
    Generic options:
       -v[#]       Set verbose level (xlscat)
       -d[#]       Set debug   level (Spreadsheet::Read)
       -u          Use unformatted values
       --noclip    Do not strip empty sheets and
                   trailing empty rows and columns
        -e <enc>    Set encoding for input and output
        -b <enc>    Set encoding for input
        -a <enc>    Set encoding for output
    Input CSV:
       --in-sep=c  Set input sep_char for CSV
    Input XLS:
       --dtfmt=fmt Specify the default date format to replace 'm-d-yy'
                   the default replacement is 'yyyy-mm-dd'
    Output Text (default):
       -s <sep>    Use separator <sep>. Default '|', \n allowed
       -L          Line up the columns
       -n          Number lines (prefix with column number)
       -A          Show field attributes in ANSI escapes
    Output Index only:
       -i          Show sheet names and size only
    Output CSV:
       -c          Output CSV, separator = ','
       -m          Output CSV, separator = ';'
    Output HTML:
       -H          Output HTML
    Selection:
       -S <sheets> Only print sheets <sheets>. 'all' is a valid set
                   Default only prints the first sheet
       -R <rows>   Only print rows    <rows>. Default is 'all'
       -C <cols>   Only print columns <cols>. Default is 'all'
       -F <flds>   Only fields <flds> e.g. -FA3,B16
[download]


Enjoy, Have FUN! H.Merijn