Win32::API and a search on MSDN for GetTextExtentPoint32 should get you started.

Hope this helps you get started...

    #!usr/bin/perl -wl

    use Font::TTFMetrics;

    my $font = Font::TTFMetrics->new('c:\windows\fonts\arial.ttf');

    my $font_size    = 10;
    my $dpi          = 96;
    my $units_per_em = $font->get_units_per_em();
    my $font_width   = $font->string_width("Some sort of long string")
+;

    # The following expression is from the TTFMetrics docs.
    my $pixel_width  = $font_width *$font_size *$dpi /(72 *$units_per_
+em);

    # The following expression is from the Spreadsheet::WriteExcel int
+ernals.
    my $cell_width   = ($pixel_width -5) /7; # For cell widths > 1

    print $cell_width;
[download]

--
John.

Here's a first go at Excel::Template::Element::Cell:AutoSize. AUTOSIZE needs to be added to Excel::Template::Factory, and Font::TTFMetrics needs to be installed.


package Excel::Template::Element::Cell::AutoSize;

use strict;

BEGIN {
    use vars qw(@ISA);
    @ISA = qw(Excel::Template::Element::Cell);

    use Excel::Template::Element::Cell;
    use Font::TTFMetrics;
}

sub render
{
    my $self = shift;
    my ($context) = @_;

    my $font_path = '/usr/share/fonts/corefonts';
    my $font_name = lc($context->active_format->{'_font'});
    $font_name .= 'bd' if $context->active_format->{'_bold'} == 700;
    my $font_size = $context->active_format->{'_size'};
    my $font = Font::TTFMetrics->new("$font_path/$font_name.ttf");

    my $dpi          = 96;
    my $units_per_em = $font->get_units_per_em();
    my $font_width   = $font->string_width($self->get_text($context));

        #The following expression is from the TTFMetrics docs.
    my $pixel_width  = $font_width *$font_size *$dpi /(72 *$units_per_
+em);

        #The following expression is from the Spreadsheet::WriteExcel 
+internals.
    my $cell_width   = (($pixel_width -5) /7) + 1; # For cell widths >
+ 1

    #Set larger column width if $cell_text_length is greater than curr
+ent max for column
    my $max_width_key = '_COL_WIDTH_' . $context->get($self, 'COL');
    #Initialize current max column width if this is our first access o
+f this column
    $context->active_worksheet->{$max_width_key} = $context->active_wo
+rksheet->{$max_width_key} || 0;
    
    #print $font_name . "," . $context->active_format->{'_bold'} . "\n
+";
    if ($cell_width > $context->active_worksheet->{$max_width_key}){
        $context->active_worksheet->{$max_width_key} = $cell_width;
        $context->active_worksheet->set_column($context->get($self, 'C
+OL'), $context->get($self, 'COL'), $cell_width);
       }
    $context->active_worksheet->write(
        (map { $context->get($self, $_) } qw(ROW COL)),
        $self->get_text($context),
        $context->active_format,
    );

    return 1;
}

1;
__END__

=head1 NAME

Excel::Template::Element::Cell::AutoSize - Excel::Template::Element::C
+ellAutoSize

=head1 PURPOSE

To provide a cell that is correctly sized for inserted text

=head1 NODE NAME

CELLAUTOSIZE

=head1 INHERITANCE

Excel::Template::Element::Cell

=head1 ATTRIBUTES

=over 4

=item * TEXT

This is the text to write to the cell. This can either be text or a pa
+rameter
with a dollar-sign in front of the parameter name.

=item * COL

Optionally, you can specify which column you want this cell to be in. 
+It can be
either a number (zero-based) or an offset. See Excel::Template for mor
+e info on
offset-based numbering.

=back 4

There will be more parameters added, as features are added.

=head1 CHILDREN

Excel::Template::Element::Formula

=head1 EFFECTS

This will consume one column on the current row. 

=head1 DEPENDENCIES

None

=head1 USAGE

  <cellautosize text="Some Text Here"/>
  <cellautosize>Some other text here</cellautosize>

  <cellautosize text="$Param2"/>
  <cellautosize>Some <var name="Param"> text here</cellautosize>

In the above example, four cells are written out. The first two have t
+ext hard-
coded. The second two have variables. The third and fourth items have 
+another
thing that should be noted. If you have text where you want a variable
+ in the
middle, you have to use the latter form. Variables within parameters a
+re the
entire parameter's value.

Please see Spreadsheet::WriteExcel for what constitutes a legal formul
+a.

=head1 BACK-REFERENCES

Currently, you can only use a hard-coded formula. The next release wil
+l add the
capability to have a formula reference other nodes in the template dyn
+amically.

=head1 AUTHOR

Tim Howell (tim@fefcful.org)
Based on Excel::Template::Element::Cell by Rob Kinyon

=head1 SEE ALSO

CELL, ROW, VAR, FORMULA

=cut
[download]

Excel calculates its cell width in points (1/72 of an inch). Given the width of each character in a font face for a given font size. Take your lengthiest string (which may not neccessarily need the most room (but should on most occassions) and then calculate how many points are needed to hold that string by

#assuming char width values in inches
my %char_width(a=>1, 
                b=>1.5); #etc.etc.

#pseudo-code
my $cell_width;

while(length($string) != 0)
{
   my $char = ($string =~ s/^(.{1})//);
   
   $cell_width += $char_width{$char};
};

$cell_width /= 72;
[download]

my $cell_width = length($string) * $char_width;
[download]

Also, just a question..When you say auto-sized cells, I assume you mean at point of initial template creation. To my knowledge there is no means to keep a cell auto-sized as the data in it changes.

As for calculating ... Excel does funky stuff when it comes display stuff. jmcnamara might know more.