merrymonk has asked for the wisdom of the Perl Monks concerning the following question:

I want to set the width of one column to be the same width as the cell in another column where I have added text and then used AutoFit to set the set the cell width.
Therefore the steps are:
1. Add the text to the 1st cell;
2. Use AutoFit on the 2nd cell;
3. Measure the width of the 1st cell ;
4. Set the 2nd column to the width of the 1st cell;
5. Measure the width of the 2nd column.;

I have done this in the Perl below and as the printout shows the 1st cell’s width is 108 and the 2nd column is 570.75.
This is an increase of 5.285.
I also set another column to 60. The measured width is 318.75.
This is an increase of 5.3125.

Can anyone explain:
1. Why this increase is happening;
2. I can reliably get exactly what I want?
use OLE;
use Win32::OLE::Const "Microsoft Excel";
use strict "vars";
my ($excel, $workbook, $sheet);   
my ($w1, $w2, $w3, $w4);
#___ DEFINE EXCEL
$excel = CreateObject OLE "Excel.Application";
#___ MAKE EXCEL VISIBLE
$excel -> {Visible} = 1;
#___ ADD NEW WORKBOOK
$workbook = $excel    -> Workbooks -> Add;
$sheet    = $workbook -> Worksheets("Sheet1");
$sheet                -> Activate;
# find initial width of B4
$w1 = $sheet->Range("B4")->Width;
# add text to B4 and autofit the cell
$sheet -> Range("B4") -> {Value} = "The cat sat on the mat";
$sheet -> Range("B4") -> Columns -> {AutoFit} = "True";
# find new width of B4
$w2 = $sheet->Range("B4")->Width;
# set column D to cell width found for cell F4
$sheet  -> Range("D4") -> {ColumnWidth} = $w2;
# find width of column D
$w3 = $sheet->Range("D4")->Width;
# set column F to 60
$sheet  -> Range("F4") -> {ColumnWidth} = 60;
# find width of column F
$w4 = $sheet->Range("F4")->Width;
print "F4 width 1<$w1> 2 <$w2> H4 width <$w3> F4 width <$w4>\n";

[download]

Replies are listed 'Best First'.
Re: Problems setting Excel cell width
by hominid (Priest) on Jan 27, 2010 at 15:14 UTC
    The problem is with mixed units. Width deals with pixel width (I think). ColumnWidth deals with character width (I think). So, for example, you are setting D4 width in characters to B4 width in pixels. I modified part of your code to illustrate the difference. 
    
...

# find initial width of B4
$w1 = $sheet->Range("B4")->Width;
my $cw1 = $sheet->Range("B4")->{ColumnWidth};

# find new width of B4
$w2 = $sheet->Range("B4")->Width;
my $cw2 = $sheet->Range("B4")->{ColumnWidth};

# set column D to cell width found for cell F4
$sheet  -> Range("D4") -> {ColumnWidth} = $w2;

# find width of column D
$w3 = $sheet->Range("D4")->Width;
my $cw3 = $sheet->Range("D4")->{ColumnWidth};

# set column F to 60
$sheet  -> Range("F4") -> {ColumnWidth} = 60;

# find width of column F
$w4 = $sheet->Range("F4")->Width;
my $cw4 = $sheet->Range("F4")->{ColumnWidth};

print "B4 width 1<$w1> 2 <$w2> D4 width <$w3> F4 width <$w4>\n";
print "B4 colwidth 1<$cw1> 2 <$cw2> D4 colwidth <$cw3> F4 colwidth <$c
+w4>\n";

    [download]
    Which gives the result: 
    B4    width 1<48>   2 <105>      D4 width <555>    F4 width <318.75>
B4 colwidth 1<8.43> 2 <19.29> D4 colwidth <105> F4 colwidth <60>

    [download]
[reply]
[d/l]
[select]
      Thanks for that. I wondered if it was a units issue .
      Do you happen to know if there is some way of retrieving and setting a cell or column width that uses the same unit?
[reply]
        ColumnWidth will do it.

        To set: 

        $sheet  -> Range("F4") -> {ColumnWidth} = 60;

        [download]
        To get: 
        my $columnwidth = $sheet  -> Range("F4") -> {ColumnWidth};

        [download]

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom