Change case of first letter in every cell in spreadsheet

Question

I have many cells which I'd like to convert such that the first letter in every cell is capitalized. E.g. cook, chef, fireman becomes Cook, Chef, Fireman.

• I have the spreadsheet in OpenOffice.org, but it seems to only have options for "all uppercase" or "all lowercase".
• I can edit it in OpenOffice.org or export to a CSV and edit the CSV with a BASH script, if OpenOffice.org cannot do this.

How can I change the first letter of every cell in the spreadsheet to uppercase?

Answer 1

I happen to do this task. You have to install Spreadsheet::ParseExcel and Spreadsheet::WriteExcel modules.

use strict;
use warnings;

use Spreadsheet::ParseExcel::SaveParser;

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

if ( !defined $workbook ) {
    die $parser->error(), ".\n";
}

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;

            # "ucfirst lc" make sure that only the first letter is capitalized
            # if you dont like that just remove lc
            $worksheet->AddCell( $row, $col, ucfirst lc $cell->value() );

        }
    }
}

# ofcouse save your work
$workbook->SaveAs('Book2.xls');