Even more useful would be to know what versions of Excel and windows you are running. On my Win2k box running Office2k and ASBuild 5.6.1.626, the following returns the NumberFormat of each cell in a given range (@ranges). Each format is shown in ##.## format, rather than the constants format given in the 'Categories' portion of the Format->Cells->Number Dialog in excel.

A1 B1 C1
My Data: 2.00 23.00% 3.72E+04

The Output:
a1 : 0.00
b1 : 0.00%
c1 : 0.00E+00

The Code:

use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 2; # Don't Die on errors
my $file = 'c:\perl\projects\excel\format.xls';

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
        || Win32::OLE->new('Excel.Application', 'Quit');
     
     
my $Book = $Excel->Workbooks->Open($file);
my $Sheet = $Book->Worksheets("sheet1");

$Sheet->Activate();
my @ranges = qw(a1 b1 c1) ;
foreach my $cell(@ranges){
    my $format = $Sheet->Range($cell)->{NumberFormat};    
    print "$cell : $format\n";
} 
$Book = $Excel->Workbooks->Close();
undef $Excel

[download]

N.B.:
I noticed, however, that if I were to use
my $format = $Sheet->Range($cell)->Style->{NumberFormat}; instead of what I have listed, no matter what the style was set to, my return value was always General.
This means that I need to do more investigation before I can fully answer your question, but I hope this helps.

UPDATE: I just wanted to mention (now that I've had some sleep) that most of my experimentation was directed by examining the object model using the object browser in Visual Studio and the "MSDN Excel Visual Basic Reference"(tm). It was there that I noticed that both the Range object and the Stlye object both have a NumberFormat property. *sigh* If only there were notes on Precedence, and MS's "thinking" on it.

C-.

In reply to Re: Win32::OLE Excel Format problem by cacharbe
in thread Win32::OLE Excel Format problem by Anonymous Monk

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.