in reply to Spreadsheet::WriteExcel, numbers v strings?


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.

Replies are listed 'Best First'.
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]

      The problem is caused by the comma in your number strings. Remove that and you should get the result that you expect.

      --
      John.

[reply]
        I have a similar problem: if I do something like: $worksheet->write_string('A2', '0123'); I get the number in my Excel 2002 spreadsheet displayed as "0123" as desired - but with the annoying green triangle in Excel suggesting I have "Number displayed as string". Is there a way from the Perl WriteExcel side of making the green triangle disappear?
[reply]
        Hey Mr McNamara, I have a question if you wouldn't mind. When working with multiple excel spreadsheets that are exactly the same with just the difference being in the data, is it possible to just create another keeping all the headers, columns, rows, colors and any other field and just replacing the data. I have to take in about a weeks worth of reports and add the numbers. Instead of having to create headings and all the bells and whistles I was hoping I could just incorporate whatever is in the existing Spreadsheets. Thanks.
[reply]
        Hey John, is there a way to write numbers into the xlsx sheet, but comma separated.
[reply]

In Section Seekers of Perl Wisdom