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

I'm writing data to excel cells and would like to have the format be something other than "General". I'd like to write the string "0200", for example, and have is displayed as "0200". Any help would be appreciated. I'm not too good at deciphering the OLE Viewer type output. Here's what I've tried: 
use strict;
use Win32::OLE;
use Win32::OLE::Enum;
use Cwd;

my $home = getcwd;
my $xls = "$home/t.xls";

my $excel = Win32::OLE -> GetActiveObject('Excel.Application') ||
            Win32::OLE -> new('Excel.Application', 'Quit');

my $wb = $excel->Workbooks->Add();

my $ws = $wb->Worksheets(1);
my $value = "0200";
$ws->{Name} = "test";
$ws->Cells(1, "A")->{Format} = 'Text';
$ws->Cells(1, "A")->{Value} = $value;

$ws->Cells(2, "A")->{Format}->{Number} = 'Text';
$ws->Cells(2, "A")->{Value} = $value;

$ws->Cells(3, "A")->{NumberFormat} = "Text";
$ws->Cells(3, "A")->{Value} = $value;

$ws->Cells(4, "A")->{TextFormat} = "Text";
$ws->Cells(4, "A")->{Value} = $value;

$wb->SaveAs($xls);
$wb->Close;

$excel->Quit();

[download]
But, what I get is "200" in rows 1, 2, 4, and "T1900xt" in row 3 (with "7/18/1900" displayed in the formula box). Thanks in advance, David

Replies are listed 'Best First'.
Re: Formatting cells in Excel
by davies (Monsignor) on Feb 02, 2010 at 23:47 UTC
    Surprisingly, it's not totally clear what you want. Do you want "0200" as a string (which is what you say) or the number 200 formatted with a leading zero (which is what you seem to be trying to do)? The following code illustrates both: 
    use strict;
use warnings;
use diagnostics;
use Win32::OLE;

my $xl = Win32::OLE->new('Excel.Application');
$xl->{Visible} = 1;
$xl->Workbooks->Add;

my $rng = $xl->Workbooks(1)->Sheets(1)->Range("A1");
$rng->{Value} = 200;
$rng->{NumberFormat} = "0000";

$rng = $xl->Workbooks(1)->Sheets(1)->Range("A2");
$rng->{Value} = "'0200";

    [download]
    A1 contains the number formatted with a leading zero (you may need some variant on this depending on what you are doing). A2 contains the text string. Note on the last line that the assignment starts with a double quote followed by a single quote. The text string doesn't (usually) need formatting.

    Hope this helps and regards,

    John Davies
[reply]
[d/l]
Re: Formatting cells in Excel
by ww (Archbishop) on Feb 03, 2010 at 00:03 UTC
    If you really want "0200" to show up as a text string, put a single quote before the first "0" -- despite M$'s explanatory promise in the "format" option that numbers will be displayed "exactly" emphasis supplied as entered. TTBOMK, excel does not provide any means to treat something that looks like a number with leading zeros, EXCEPT via the "Custom" option. You may want to pursue that if you really want "0200" to appear verbatim as a (text) string but (I think) also remain useable as a number, in the style of perl magic.

    (But note davies' method posted above as I tried to sort out your intent).

    The "7/18/1900" looks that date of the 200th day after Windows epoch date (1/1/1900). For a guess, the leading "T" and trailing "xt" reflect the fact that the Time element is missing from the 4 digits "0200."

    More generally, perldoc WIN32::OLE contains some Excel examples... followed by a section on VARIANT(s). That may be worth perusing. And some folk (search for it) have reported good success learning about methods/quirks/etc in the interface between perl and Excel at the Micro$oft sit. That's probably worthwhile too.

[reply]
[d/l]
      Thanks for the responses. Sorry I wasn't clear about the intent. I want to take a string that is the result of a 'dump' type operation. So, more of what I'm parsing is: 
      0000: e0 01 00 00 d0 02 00 00 00 00 00 00 00 00 00 00  |..............
+..|

      [download]
      So, I would want the number '0000' to be in a cell, and 'eo', '01', '00', ... also in individual cells. I'll try the suggestions tomorrow, but wanted to thank you for the responses.
[reply]
[d/l]

Back to Seekers of Perl Wisdom