in reply to Excel file data - mail

Update: I am curious...The problem appears to be that multiple folks need the current values of a spreadsheet on a daily basis. Instead of parsing the spreadsheet and emailing results, why isn't the idea to post this spreadsheet on a website so that each user can just login and see current status whenever they wish? I am wondering if the "right" question is being asked?

Update2: in the Chatterbox, I got turned onto Spreadsheet::Read. That looks extremely promising.

====
I am also currently working on a spreadsheet parsing problem at the moment, There are a number of confusing options for reading spreadsheets. Win32::OLE is one of them. There are other ways to read the spreadsheet file (.xls,.xlsx) directly. I haven't found a great option yet, but there are some out there.

This google search perl+spreadsheet+xlsx turned up some interesting stuff.

Yesterday to get started, I implemented some code with an old module (Spreadsheet::DataFromExcel) that can only read .xls files, but this was very easy and got me past a blocking point and onto the more difficult parts of my current assignment.

use Spreadsheet::DataFromExcel;
my $p = Spreadsheet::DataFromExcel->new;
my $web = $p->load('Roster.xls', 'For Web',3) or die $p->error;
# $web is ref to AoA for that sheet

[download]
This is all the functionality that I need, except that it doesn't work with .XLSX. Maybe some Monk here can advise on the easiest way to get that 2 lines of functionality with .XLSX? If so, then that probably satisfies both of our requirements.

Replies are listed 'Best First'.
Re^2: Excel file data - mail
by cormanaz (Deacon) on Aug 01, 2016 at 22:00 UTC
    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;

    [download]
[reply]
[d/l]
      Thanks for the code++ - very close to what I need. And your "cheat sheet is great". I'd never used Win32:OLE before.
[reply]

In Section Seekers of Perl Wisdom