What's The Best Way to Extract Excel Cell Comments Using Perl or Ruby

What's the best way to extract Excel cell comments using Perl or Ruby?

One option is to use Ruby's win32ole library.

The following (somewhat verbose) example connects to an open Excel worksheet and gets the comment text from cell B2.

require 'win32ole'

xl = WIN32OLE.connect('Excel.Application')
ws = xl.ActiveSheet
cell = ws.Range('B2')
comment = cell.Comment
text = comment.Text

More info and examples of using Ruby's win32ole library to automate Excel can be found here:

http://rubyonwindows.blogspot.com/search/label/excel

What's the best way to parse Excel file in Perl?

The best way is to use Spreadsheet::ParseExcel.

Here is an example:

#!/usr/bin/perl -w

use strict;
use warnings;

use Spreadsheet::ParseExcel;

my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('Book1.xls');

for my $worksheet ( $workbook->worksheets() ) {

    my ( $row_min, $row_max ) = $worksheet->row_range();
    my ( $col_min, $col_max ) = $worksheet->col_range();

    for my $row ( $row_min .. $row_max ) {
        for my $col ( $col_min .. $col_max ) {

            my $cell = $worksheet->get_cell( $row, $col );
            next unless $cell;

            print "Row, Col    = ($row, $col)\n";
            print "Value       = ", $cell->value(),       "\n";
            print "Unformatted = ", $cell->unformatted(), "\n";
            print "\n";
        }
    }
}

To convert an Excel file to text with Perl I'd recommend excel2txt which uses Spreadsheet::ParseExcel.

Read Excel Cell Comment using Python on Linux?

I was going to say that it's too bad that xlrd doesn't handle comments, but then I stumbled upon this What's the best way to extract Excel cell comments using Perl or Ruby?.

Key passage:

The Python xlrd library will parse
cell comments (if you turn on
xlrd.sheet.OBJ_MSO_DEBUG, you'll see
them), but it doesn't expose them from
the API. You could either parse the
dump or hack on it a bit so you can
get to them programmatically.

Parsing SpreadsheetML Using Perl

Searching CPAN for 'spreadsheet excel xml' results in Spreadsheet::ParseExcel, which mentions in the description:

The module cannot read files in the Excel 2007 Open XML XLSX format. See the Spreadsheet::XLSX module instead.

Worth to try...

Writing an app with Perl and Ruby?

There's also the speadsheet gem, although I've never used it for writing.

I don't think there's anything intrinsically wrong with using backticks or %x(), although I might instead expose the Perl stuff as a service to make it a bit more general purpose. Likely overkill, though.

---

Related Topics