Spreadsheet::WriteExcel, numbers v strings?

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

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.

----------

Using perl 5.6.1 unless otherwise noted.
Apache 1.3.27 unless otherwise noted.
Redhat 7.1 unless otherwise noted.
[download]

Comment on Spreadsheet::WriteExcel, numbers v strings? Select or Download Code

Replies are listed 'Best First'.
Re: Spreadsheet::WriteExcel, numbers v strings? by jmcnamara (Monsignor) on Nov 12, 2003 at 23:33 UTC
I am the author of Spreadsheet::WriteExcel. I've never seen that warning. What version of Excel are you using? Does the following generate the same warning? `#!/usr/bin/perl -w use strict; use Spreadsheet::WriteExcel; my $workbook = Spreadsheet::WriteExcel->new("test.xls"); my $worksheet = $workbook->add_worksheet(); $worksheet->write ('A1', 12345); $worksheet->write_string('A2', 12345); __END__` [download] If not could you isolate the data field that causes the warning. -- John.	[reply] [d/l]
Re: Re: Spreadsheet::WriteExcel, numbers v strings? by alienhuman (Pilgrim) on Nov 13, 2003 at 14:34 UTC
John Thanks for your attention. I'm using Excel 2002 (10.3506.3501) SP-1. The code you supplied creates a spreadsheet that does show the same message--however, I'm not sure characterizing it as a warning is accurate. Here's what I see: in the A2 cell, I see 12345 with a little green tab in the upper left corner of the cell. I believe that means something like "this is a number stored as a string". Then if I select the cell, a little yellow icon with a "!" appears next to the cell. Clicking on that produces a menu with these optinons: Number stored as text Convert to number Help on this error* Ignore error Edit in formula bar Error checking options Show formula auditing toolbar I am, admittedly, an excel illiterate. I just write the scripts that create the damn things for my colleagues for whom they seem to have near religious significance (ie banker-types). I don't even know what the implication of having a number stored as text is, except that it seems to be left-aligned by default instead of right aligned like the other numbers. I looked more closely at the docs last night after I went home and noticed that you provide the regexp that is used to determine which incarnation of `write()` to use--and that explains why numbers in the format "10,000" are being interpreted as strings. However, I'm still weirded out by the fact that "16.036" is being treated as a string by excel, if not by your module. I'll also take a closer look at my excel preferences--it occurs to me that excel could be getting too "smart" for its own good (why does that always seem to happen with MS apps?) and this might have nothing to do with your module. Thanks for your help. AH * naturally, the built-in help docs make no mention of this :) ---------- `Using perl 5.6.1 unless otherwise noted. Apache 1.3.27 unless otherwise noted. Redhat 7.1 unless otherwise noted.` [download]	[reply] [d/l] [select]
Re^2: Spreadsheet::WriteExcel, numbers v strings? by sanjus (Novice) on Mar 19, 2008 at 01:01 UTC
Hello John, I am facing the same issue and don't think after reading this thread i got the answer to the issue. $worksheet->write ('A1', 12345); $worksheet->write ('A2', 1,2345.67); The second statement writes the value as a string in excel rather a formatted number. ie in Excel the value in A2 is stored as text and hence no mathematical operation can be performed on this value. Any suggestions? Since I am writing the values from a file - i don't know before hand if the value is numeric or string & hence i would have expected the write statement to take care of it appropriately. Appreciate your help, Thanks, Sanju	[reply]
Re^3: Spreadsheet::WriteExcel, numbers v strings? by jmcnamara (Monsignor) on Mar 20, 2008 at 04:30 UTC
The problem is caused by the comma in your number strings. Remove that and you should get the result that you expect. -- John.	[reply]
Re^4: Spreadsheet::WriteExcel, numbers v strings? by chrisjej (Initiate) on Mar 28, 2008 at 11:54 UTC
Re^5: Spreadsheet::WriteExcel, numbers v strings? by jmcnamara (Monsignor) on Mar 28, 2008 at 18:54 UTC
Re^4: Spreadsheet::WriteExcel, numbers v strings? by jamesgerard1964 (Novice) on Oct 09, 2014 at 23:15 UTC
Re^4: Spreadsheet::WriteExcel, numbers v strings? by Anonymous Monk on Oct 21, 2014 at 20:15 UTC
Re: Spreadsheet::WriteExcel, numbers v strings? by jsprat (Curate) on Nov 12, 2003 at 23:14 UTC
You could always explicitly write a number with `write_number()` (or a string with `write_string()`) HTH	[reply] [d/l] [select]
Re: Spreadsheet::WriteExcel, numbers v strings? by duff (Parson) on Nov 12, 2003 at 23:10 UTC
Are you sure the fields that are "numbers" don't start with some whitespace or some other character ("$" comes to mind) that is fooling Spreadsheet::WriteExcel? duff	[reply]