1#!/usr/local/bin/perl -w 2 3############################################################################## 4# 5# Simulate Excel's autofit for column widths. 6# 7# Excel provides a function called Autofit (Format->Columns->Autofit) that 8# adjusts column widths to match the length of the longest string in a column. 9# Excel calculates these widths at run time when it has access to information 10# about string lengths and font information. This function is *not* a feature 11# of the file format and thus cannot be implemented by Spreadsheet::WriteExcel. 12# 13# However, we can make an attempt to simulate it by keeping track of the 14# longest string written to each column and then adjusting the column widths 15# prior to closing the file. 16# 17# We keep track of the longest strings by adding a handler to the write() 18# function. See add_handler() in the S::WE docs for more information. 19# 20# The main problem with trying to simulate Autofit lies in defining a 21# relationship between a string length and its width in a arbitrary font and 22# size. We use two approaches below. The first is a simple direct relationship 23# obtained by trial and error. The second is a slightly more sophisticated 24# method using an external module. For more complicated applications you will 25# probably have to work out your own methods. 26# 27# reverse('�'), May 2006, John McNamara, jmcnamara@cpan.org 28# 29 30use strict; 31use Spreadsheet::WriteExcel; 32 33my $workbook = Spreadsheet::WriteExcel->new('autofit.xls'); 34my $worksheet = $workbook->add_worksheet(); 35 36 37############################################################################### 38# 39# Add a handler to store the width of the longest string written to a column. 40# We use the stored width to simulate an autofit of the column widths. 41# 42# You should do this for every worksheet you want to autofit. 43# 44$worksheet->add_write_handler(qr[\w], \&store_string_widths); 45 46 47 48$worksheet->write('A1', 'Hello'); 49$worksheet->write('B1', 'Hello World'); 50$worksheet->write('D1', 'Hello'); 51$worksheet->write('F1', 'This is a long string as an example.'); 52 53# Run the autofit after you have finished writing strings to the workbook. 54autofit_columns($worksheet); 55 56 57 58############################################################################### 59# 60# Functions used for Autofit. 61# 62############################################################################### 63 64############################################################################### 65# 66# Adjust the column widths to fit the longest string in the column. 67# 68sub autofit_columns { 69 70 my $worksheet = shift; 71 my $col = 0; 72 73 for my $width (@{$worksheet->{__col_widths}}) { 74 75 $worksheet->set_column($col, $col, $width) if $width; 76 $col++; 77 } 78} 79 80 81############################################################################### 82# 83# The following function is a callback that was added via add_write_handler() 84# above. It modifies the write() function so that it stores the maximum 85# unwrapped width of a string in a column. 86# 87sub store_string_widths { 88 89 my $worksheet = shift; 90 my $col = $_[1]; 91 my $token = $_[2]; 92 93 # Ignore some tokens that we aren't interested in. 94 return if not defined $token; # Ignore undefs. 95 return if $token eq ''; # Ignore blank cells. 96 return if ref $token eq 'ARRAY'; # Ignore array refs. 97 return if $token =~ /^=/; # Ignore formula 98 99 # Ignore numbers 100 return if $token =~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/; 101 102 # Ignore various internal and external hyperlinks. In a real scenario 103 # you may wish to track the length of the optional strings used with 104 # urls. 105 return if $token =~ m{^[fh]tt?ps?://}; 106 return if $token =~ m{^mailto:}; 107 return if $token =~ m{^(?:in|ex)ternal:}; 108 109 110 # We store the string width as data in the Worksheet object. We use 111 # a double underscore key name to avoid conflicts with future names. 112 # 113 my $old_width = $worksheet->{__col_widths}->[$col]; 114 my $string_width = string_width($token); 115 116 if (not defined $old_width or $string_width > $old_width) { 117 # You may wish to set a minimum column width as follows. 118 #return undef if $string_width < 10; 119 120 $worksheet->{__col_widths}->[$col] = $string_width; 121 } 122 123 124 # Return control to write(); 125 return undef; 126} 127 128 129############################################################################### 130# 131# Very simple conversion between string length and string width for Arial 10. 132# See below for a more sophisticated method. 133# 134sub string_width { 135 136 return 0.9 * length $_[0]; 137} 138 139__END__ 140 141 142 143############################################################################### 144# 145# This function uses an external module to get a more accurate width for a 146# string. Note that in a real program you could "use" the module instead of 147# "require"-ing it and you could make the Font object global to avoid repeated 148# initialisation. 149# 150# Note also that the $pixel_width to $cell_width is specific to Arial. For 151# other fonts you should calculate appropriate relationships. A future version 152# of S::WE will provide a way of specifying column widths in pixels instead of 153# cell units in order to simplify this conversion. 154# 155sub string_width { 156 157 require Font::TTFMetrics; 158 159 my $arial = Font::TTFMetrics->new('c:\windows\fonts\arial.ttf'); 160 161 my $font_size = 10; 162 my $dpi = 96; 163 my $units_per_em = $arial->get_units_per_em(); 164 my $font_width = $arial->string_width($_[0]); 165 166 # Convert to pixels as per TTFMetrics docs. 167 my $pixel_width = 6 + $font_width *$font_size *$dpi /(72 *$units_per_em); 168 169 # Add extra pixels for border around text. 170 $pixel_width += 6; 171 172 # Convert to cell width (for Arial) and for cell widths > 1. 173 my $cell_width = ($pixel_width -5) /7; 174 175 return $cell_width; 176 177} 178 179__END__ 180 181