autofit_columns issue'ette

[Mark Taylor]

Hi (hopefully John M)

I am playing around with this perl script

http://www.perlmonks.org/bare/?node_id=635437

which is great and uses some of your code to do the autofit part, but, changes I have made have stopped the autofit part working :(  .. it was working with write(), and now doesnt work with write_text() .. its probably got something to do with the $token, but for the life of me I am not seeing why ..

The problem I had was that it was converting some of the colums that had an "E" in the CSV and truncating them, for example 44E0 became just 44 .. so to work around this I changed the write() to write_text() dumping the elements of the $row and looping through those to write each col of each row .. I also set some format globally

##-- global
my $format = $workbook->add_format(
                                        bold        => 0,
                                        color       => 'black',
                                        size        => 8,
                                        valign      => 'vcentre',
                                        align       => 'left',
                                        num_format  => 0x31,
                                      );
$worksheet->keep_leading_zeros();
##--end global

The into the data loop ... this line ...

##--data loop

$worksheet->write($i++, 0,$row,$format);

now becomes ....

my @columns2 = @{ $row };  # Dereferencing my array reference
my $k=0;
for my $column2 ( @columns2 ) {
                print "DEBUG: row $i col $k $column2 \n" if $debug;
                $worksheet->write_string($i, $k, "$column2" ,$format);
                $k++;
}
$i++;

#auto fit colums
autofit_columns($worksheet);

#autofilter
$worksheet->autofilter('A1:M19999');

##--end of data loop

So, the question is, any idea why the new method may have stopped the auto fit cols working ?

Any help greatly appreciated .. many thanks in advance
Mark

[Mark Taylor]

k, so, I can answer my own quesiton ..
----
https://rt.cpan.org/Public/Bug/Display.html?id=40662

Hi, That is in correct. The handlers are only called for write() and not for any of the write_*() methods. That is the intention and I think (hope) that the documentation is clear about that. "This method is used to extend the Spreadsheet::WriteExcel write() method to handle user defined data". The difference between write() and write_date_time() is that write() is a generic method for handling different data types while write_date_time() is a specific method for handling a single data type. As such add_write_handler() exists to allow the user to extend or modify the behaviour of write(). This shouldn't be necessary for write_date_time(). John. --
-----

Which then leads me to the question .. if thats the case, then I need to revert to using write() or write my own function to sort out the col widths .. so, firstly, any idea how can I stop write() changing 44E0 to 44 using the write() method as that would seem to be the easiest path currently ..

Cheers
Mark

[Mark Taylor]

this one can be marked as fixed .. answere above ..