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

I am using Perl 5.1.8 and Win32::OLE 0.1101 and having problems with the following:

Trying to read the formatting style of a particular cell:
$format = $Sheet->Cells(1,2)->Style->{'Name'};
where $Sheet is a reference to my current worksheet.

The $format variable contains "Percent", but when I open Excel directly and look at the cell format, it is "Number", and the cell value is displayed as "209", not "20900%".

Could this be a version problem?
Thanks in advance!
Kerry

Replies are listed 'Best First'.
Re: Win32::OLE Excel Format problem
by cacharbe (Curate) on Jun 26, 2001 at 08:02 UTC
    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-.

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom