NirMH NirMH - 1 month ago 11
Perl Question

How to access excel 2010 sheet from an external tool?

I have an excel 2010 workbook.
I would like to extract a specific sheet (by name) from it and save it as csv file.

The main requirement is doing so from an external program (program language is not critical.. i can use perl/python/vb, whatever works...)

I browsed the web (google it) but couldn't find any documentation how to do it.

I did find that excel 2010 is actually a zip, but looking into it, i couldn't find the sheets' names....

Did anyone have any experience in such tasks?

Answer

You tagged the question with perl. So, you can definitely solve the problem using perl, just install few already developed perl-modules from the CPAN repository. Check http://metacpan.org .

It is easy task to google them, but if you're unable find the needed ones, here are:

  • Spreadsheet::ParseExcel - the basic module for reading Excel files using perl. You need to be familiar with this one - you will learn the basic "logic" how to deal with workbook, sheets, cells and so on.
  • Spreadsheet::XLSX - Like the above, but could read the 2007+ XLSX file-type. (Your case).

Using the above modules you will be able read your XLSX files. For the second part, (saving it as csv) - again exists already developed and well-tested modules, like:

  • Text::CSV_XS - Text::CSV_XS provides facilities for the composition and decomposition of comma-separated values.

Usually the real-world perl-script for such simple problem (using the above modules) is only few (10-15) lines long.

But, if you want deal with edge cases, for example character encoding conversion and like - maybe you will need to use few other modules too.