Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

Re^4: Reading Excel

by ravi179 (Novice)
on Jan 27, 2017 at 10:07 UTC ( [id://1180457]=note: print w/replies, xml ) Need Help??


in reply to Re^3: Reading Excel
in thread Reading Excel

perl 5.008009 Win32::OLE version 0.1709....Running Excel 15.0 on. I am unable to deal with Excel also..Same error is coming in both the cases.

Replies are listed 'Best First'.
Re^5: Reading Excel
by Corion (Patriarch) on Jan 27, 2017 at 10:09 UTC

    If Excel cannot open the file, Perl-using-Excel will not be able to open the file either.

Re^5: Reading Excel
by marto (Cardinal) on Jan 27, 2017 at 10:34 UTC

    The lesson here is to check your input data and not make assumptions. If Excel can't open the file how would calling Excel using Perl be any different?

      Normally if i open in Excel it is opening.When iam trying to open it using Perl Script it is not opening

        Your code open creates a new excel instance, then dies because (seemingly) it can't open a file. You said:

        "I am unable to deal with Excel also."

        To many reading this it would seem that the situation you now report:

        "Normally if i open in Excel it is opening"

        The two can't be true. Perhaps spend some time on your posts, be detailed and descriptive in what is actually happening.

        Run this to create a known good xlsm file before then opening and reading it.

        #!perl use strict; use warnings; use Win32::OLE; use Data::Dump 'pp'; $Win32::OLE::Warn = 3; # get already active Excel application or open new my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit'); # create macro enabled workbook my $excelfile = "C:\\perlpractice\\testfile.xlsm"; my $range = 'B1:E40'; my $top_left = create_xlsm($excelfile,$range); my $Book = $Excel->Workbooks->Open($excelfile); # open Excel file # Read spreadsheet my $Sheet = $Book->Worksheets(1); my $array = $Sheet->Range($range)->{'Value'}; print "$top_left = ".$array->[0][0]."\n"; pp $array; $Book->Close; sub create_xlsm { my ($file,$range) = @_; my ($start,$end) = split ':',$range; my $Book = $Excel->Workbooks->Add(); # new Excel file my $Sheet = $Book->Worksheets(1); my $rng = $Sheet->Range($range); my $cols = $rng->{'Columns'}->Count; my $rows = $rng->{'Rows'}->Count; #print "$rows $cols\n"; my @data=(); for my $c (0..$cols-1){ for my $r ( 0..$rows-1 ){ $data[$r][$c] = $r.'_'.$c; } } $rng->{'NumberFormat'} = '@'; $rng->{'Value'} = \@data; $Sheet->Range($start)->{'Value'} = scalar localtime; # top left $Book->SaveAs({ Filename=>$file, FileFormat=>52 }); #xlsm $Book->Close; undef $Book; return $start; }
        Updated : removed hard coded range
        poj
Re^5: Reading Excel
by huck (Prior) on Jan 27, 2017 at 11:55 UTC

    perl 5.8.9 is rather old, you should try updating your perl

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://1180457]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others chilling in the Monastery: (6)
As of 2024-03-28 11:50 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found