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

Hello Monks. I am working on a script that prints a modifiable list of numeric, percentage, and currency values into an .xls file. This file will be viewed by users so formatting matters in this case. When I write a number formatted with commas and a dollar sign or a percentage it displays the information correctly but it also has green triangles, which display a message that states "the number in this cell is formatted as text", in the top left corner of each cell with a formatted number in it. is there a way to tell the excel file to ignore it when this happens or a way to format the values as text that will fix this? i've already tried the below method, it did not work.

$worksheet->write_string(x, y, $value)

here is some code that works perfectly as an example of what i'm talking about. thanks in advance.

use Spreadsheet::WriteExcel;

my $workbook  = Spreadsheet::WriteExcel->new("Example.xls");
my $worksheet = $workbook->add_worksheet();

$worksheet->write_string(0, 4, '$77,777');
$workbook->close();

[download]

Replies are listed 'Best First'.
Re: Spreadsheet::WriteExcel cell formatting.
by fishmonger (Chaplain) on Sep 22, 2015 at 19:19 UTC

    If you want to display numbers, why are you using the write_string() method instead of the write_number(),method?

    Your method call is missing the format specification (the 4th parameter). You should be passing the format instead of hard coding it as a string, like you're currently doing.

    The 3rd example Spreadsheet::WriteExcel#Example_3 in the module documentation shows how to specify a currency format.

[reply]
[d/l]
[select]

      I want it to be formatted as a string. and i am passing the formatting parameter on the actual script, the green warning triangle still appears.

[reply]

        I just ran a test using your code snippet and couldn't reproduce your problem.

        Maybe the problem is due to some other portion of code in your script?

[reply]
        I want it to be formatted as a string

        Then your only option is to muck around with Excel itself (either a different version, or some macro to get rid of the warning).

        From what you describe, you want the cell to be formatted as text, however Excel is detecting that there is a number in the cell formatted as text and is attaching a warning to the cell indicating this. There is nothing Perl can do about that.

        fishmonger's previous suggestion to correctly format the cell is the only way Perl can affect the warning triangle you are seeing in Excel.

[reply]
        If you tell Excel it should be treated as string, you actually forbid Excel to use it in calculations. That's why it doesn't want to work the way you want.

        Of course, you could write an Excel-alike in Perl. A CPAN search finds Spreadsheet::Perl, Tk::TableMatrix::Spreadsheet and others.

[reply]
Re: Spreadsheet::WriteExcel cell formatting.
by locked_user sundialsvc4 (Abbot) on Sep 23, 2015 at 14:43 UTC

    I am suspicious that you are writing the value with the dollar-sign and commas included.   Try omitting all this, and simply write the number, 77777.   It’s Excel’s responsibility to format the content of the cell, as currency or what-have-you.

    Excel has a certain amount of “de-formatting” that it applies in its user interface to keep the accountants happy, but these do not hold when you are directly writing data into the spreadsheet file.

Back to Seekers of Perl Wisdom