Hi Monks,

I'm using Spreadsheet::WriteExcel to write an excel spreadsheet. In the docs on CPAN it says that the write() method should be smart enough to tell the difference between "abcd" and "1,223" and "13.98".

However excel complains about my spreadsheet, saying that the numbers are being "stored as text". I get my data by iterating over each row of a tab delimited text file.

Here's my code: 

use strict;
use CGI;
use Spreadsheet::WriteExcel;

 my $q                  = new CGI;
 my $inv                = $q->param('stinv');
 my $mntdir             = $q->param('mntdir');
 my $second_line_format = $q->param('tap');
 my $file_name          = $q->param('file_name');
 my $download           = $q->param('download');

 if ( $inv eq "dev" ) {                                         ## Def
+ind Root path for execute file
    $file_name          = "\/home\/httpd\/html\/newdev2\/m\/".$mntdir.
+"/".$file_name."\.txt";
 } elsif ( $inv eq "test" ) {
    $file_name          = "\/home\/httpd\/test\/m\/".$mntdir."/".$file
+_name."\.txt";
 } elsif ( $inv eq "stg" ) {
    $file_name          = "\/home\/httpd\/staging\/m\/".$mntdir."/".$f
+ile_name."\.txt";
 } else {
    $file_name          = "\/home\/httpd\/html\/m\/".$mntdir."/".$file
+_name."\.txt";
 }

 print "Content-type: application/vnd.ms-excel\n\n";            ## Out
+puting spreadsheet header

 my $workbook  = Spreadsheet::WriteExcel->new("-");
 my $worksheet = $workbook->add_worksheet();
 my $plain     = $workbook->add_format();
 my $bold      = $workbook->add_format(bold => 1);
 my $italic    = $workbook->add_format(italic => 1,text_wrap => 1);
 my $format;

 $worksheet->set_column(0,0,45);                                # Set 
+column width for company name
 $worksheet->set_column(1,20,18);                               # Set 
+column width for rest of data


 open(IN,"$file_name") or die "Cannot open :$!\n";              ## Ope
+n file

 my $rnum = 0;
 my $last_row_empty;
 while( my $line = <IN>) {                                            
+  ## Input Data from a file
   chomp $line;
   my @row = split(/\t/,$line);
   my $col = 0;
   foreach my $field ( @row ) {
     if ( $last_row_empty eq 1 ) {
       $worksheet->write($rnum, $col++,$field,$bold);
     } elsif ( $file_name =~ /peer/ && $field =~ /Company Name/ ) {
       $worksheet->write($rnum, $col++,$field,$italic);
     } else {
       $worksheet->write($rnum, $col++,$field,$plain);
     }
   }
   $last_row_empty = 0;
   $last_row_empty = 1 if ( @row eq 0 );
   $rnum++;
 }
 close IN;                                                      ## Clo
+se file
 exit;

[download]

Thanks for any insight you can offer.

AH

---------- 
Using perl 5.6.1 unless otherwise noted.
Apache 1.3.27 unless otherwise noted.
Redhat 7.1 unless otherwise noted.

[download]

In reply to Spreadsheet::WriteExcel, numbers v strings? by alienhuman

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.