write() function truncating strings with E in them i.e. 44E0 becomes just 44 in the spreadsheet

[Mark Taylor]

Hi, I am playing around with this perl script

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


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 .. I have set the num_format to 0x31 globally and set keep_leading_zeros() .. I can dump the data in the data loop and the text is fine, but, when its written to the spreadsheet its changed from 44E0 to 44 in the column .. I can check the cell format and it shows as "Text" so .. not 100% sure whats going on .. any pointers anyone ..

##-- global formatting ..
my $format = $workbook->add_format(
                              

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


Any help greatly appreciated
Cheers
Mark

[Mark Taylor]

so .. I tried the add handler route http://cpansearch.perl.org/src/JMCNAMARA/Spreadsheet-WriteExcel-2.40/examples/write_handler1.pl  .. didnt work for me .. or should I say I couldnt get it working .. so .. I went back to my write_text route by dumping out the cols for each row and adding them in a loop .. and also dropped in the code to set up the array for the col widths

            #write text only
            if($writetext){
                #write text method
                my @columns2 = @{ $row };
                my $k=0;
                for my $column2 ( @columns2 ) {
                        print "DEBUG: row $i col $k $column2 \n" if $debug;
                        $worksheet->write_string($i, $k, "$column2" ,$format);

                        ## set up col widths
                        my $old_width    = $worksheet->{__col_widths}->[$k];
                        my $string_width = string_width($column2);
                        if (not defined $old_width or $string_width > $old_width) {
                                $worksheet->{__col_widths}->[$k] = $string_width;
                        }
                        ## end setup col widths

                        #inc col
                        $k++;
                }
                $i++;
            } else {
                $worksheet->write_row($i++, 0, $row, $format);
            }

There is prob an easier way to do all this, but it works for me ..

Thanks for all your help, it was invaluable ;)
cheers
Mark