Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

Re^2: Excel file data - mail

by cormanaz (Deacon)
on Aug 01, 2016 at 22:00 UTC ( [id://1168966]=note: print w/replies, xml ) Need Help??


in reply to Re: Excel file data - mail
in thread Excel file data - mail

IDK why that module won't work with XLSX, but the solution is to just use Win32::OLE directly. It's not that hard. This cheatsheet is another resource I've found useful. Here is some code to open a workbook and print out the content of the first five columns as a tab separated list.
#!/usr/bin/perl -w use strict; use Win32::OLE qw(in with); use Win32::OLE::Const 'Microsoft Excel'; use Win32::OLE::Variant; $Win32::OLE::Warn = 3; # die on errors. +.. my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32: +:OLE->new('Excel.Application', 'Quit'); # get already active Excel o +r create new one as appropriate $Excel->{'Visible'} = 1; # see what's going on if you want my $Book = $Excel->Workbooks->Open('C:\somefile.xlsx'); # open Excel f +ile -- requires explicit DOS path my $Sheet = $Book->Worksheets('Sheet1'); # tab name or sheet number (f +rom 1) my $lastrow = $Sheet->UsedRange->Find({What=>"*",SearchDirection=>xlPr +evious,SearchOrder=>xlByRows})->{Row}; for my $row (1..$lastrow) { foreach my $col (qw(A B C D E)) { print $Sheet->Range("$col$row")->{'Value'},"\t"; # range is co +lumn letter, row number; can also be the target of an assignment } print "\n"; } $Book->close;

Replies are listed 'Best First'.
Re^3: Excel file data - mail
by Marshall (Canon) on Aug 04, 2016 at 15:20 UTC
    Thanks for the code++ - very close to what I need. And your "cheat sheet is great". I'd never used Win32:OLE before.

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others musing on the Monastery: (3)
As of 2024-03-29 06:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found