Excel and Perl

[Mark Seger]

I just noticed the subject was spelled wrong and am hoping maybe that's
why nobody payed any attention to it as I've gotta believe someone must
know the answer.

I've been playing with the perl ole interface to talk to excel. Very
cool. The problem I'm having is virtually all the documentation on how
to do it in perl is too simplistic for anything more sophisticated and
the everything else is in VB - perhaps an opportunity for some
documentation enhancement? In any event the good news is I've managed
to figure out how to map from one syntax to the other except for VB arrays.

Specifically, I want to read in a text file that has space separated
field and the first one is in date format. To do everything but specify
the date one simply does:

$Book = $Excel->Workbooks->OpenText({
Filename=>$filename, ConsecutiveDelimiter=>1, Space=>1,});

and it works like a champ. However to tell Excel the format of specific
fields, you need to add "FieldInfo" and that is defined as an array of
2 element arrays. I've tried a number of things but to no avail.
Anybody know how?

If anyone is interested, the way this is captured as a macro in Excel
looks like:

Workbooks.OpenText Filename:= _
"C:\mjs\excel-perl\20050601-cx01-slab-s-days1.txt",
Origin:=437, StartRow:= 1,
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False,
Comma:=False, Space:=True, Other:=False,
FieldInfo:=Array(Array(1, 5), Array(2, 1),
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1),
Array(11, 1)), TrailingMinusNumbers:=True

but I don't need to set all the fields as most of them are already
defaulted the way I want them.

-mark

 

[Bob]

Anybody know how?

No idea - but I would look at CPAN. After DBI, Excel spreedsheets must
be the most popular categoy. Hundreds of them. Download a few modules
to use, or either examine the code. Someones certainly done it all
before, and encapsulated that knowledge in a module or two. That's the
beauty of scripts!

 

[Mark Seger]

I hear you and have already looked at quite a few example scripts -
that's how I got anything to work in the first place. I'm not too
confident I'll find it there because from what I've seen after looking
at a lot of VB macros that excel generates, the contrucut of an array of
arrays is not very common. I can certainly look at more examples but
was hoping someone out there may already know the answer.

I also tried posting on severl excel newsgroups but read those folks may
not even know how to spell perl because the questions seem to cover a
very broad set of topics, but it seems like the only real show in town.

Would it make sense to send a note to the maintainer of the perl FAW
that discusses the topic and request more examples in there?

-mark

 

[A. Sinan Unur]

I hear you

Who do you hear? Please adopt an effective follow-up style with no top-
posting and an appropriate amount of context. Please do read the posting
guidelines for this group.

confident I'll find it there because from what I've seen after looking
at a lot of VB macros that excel generates,

You should not be looking at the VBA macros Excel generates, but rather
the OLE interface it exposes.

Would it make sense to send a note to the maintainer of the perl FAW

ITYM "Perl FAQ". If that is so, I don't see how that is relevant, as
yours (a question that has now lots its place in this thread because of
two rounds of bad quoting) is not frequently asked.

Most of the relevant information to OLE programming can be found in
Microsoft's own VBA documentation.

So, recapping, your question is:

Specifically, I want to read in a text file that has space separated
field and the first one is in date format. To do everything but
specify the date one simply does:

Here is something that may get you started:

#! /usr/bin/perl

use strict;
use warnings;

use Win32::OLE::Const 'Microsoft Excel';

my $input_file = shift
or die "Provide the name of the input file on the command line\n";

my $excel;
eval {
$excel = Win32::OLE->GetActiveObject('Excel.Application')
};

die "$@\n" if $@;

unless(defined $excel) {
$excel = Win32::OLE->new(
'Excel.Application',
sub { $_[0]->Quit }
) or die "Oops, cannot start Excel: ", Win32::OLE->LastError, "\n";
}

my $book = $excel->Workbooks->Open($input_file)
or die "Cannot open input file: ", Win32::OLE->LastError;

$book->Worksheets(1)->Columns(1)->{ColumnWidth} = 24;
sleep 30;
$book->Close(0);

__END__

The input file I used contained:

2005/12/21 Ithaca
2003/1/5 Istanbul
2004/11/2 Isfahan

Running the script resulted in the following columns being displayed in
Excel:

12/21/2005 Ithaca
01/05/2003 Istanbul
11/02/2004 Isfahan

An alternative to the OLE interface is to use

<URL: http://search.cpan.org/~erngui/Win32-GuiTest-1.3/>

The SendKeys function in this module allows you to do a whole bunch of
things rather easily:

#! /usr/bin/perl

use strict;
use warnings;

use Win32::GuiTest qw{
FindWindowLike GetWindowText SetForegroundWindow SendKeys
};

use constant EXCEL =>
q{"C:\Program Files\Microsoft Office\Office\EXCEL.EXE"};

system(qq{start "" @{[ EXCEL ]}}) == 0
or die "start @{[ EXCEL ]} failed: $?";

sleep 1;

my ($window) = FindWindowLike(0, '^Microsoft Excel', '^XLMAIN$');

my $input_file = shift
or die "Provide the name of the input file on the command line\n";

SetForegroundWindow($window);
SendKeys '%fo'.$input_file.'{ENTER}';
SendKeys '{TAB}{TAB}{TAB}{TAB}{SPC}';
SendKeys '%s{TAB}%r{SPC}{TAB}{TAB}{TAB}{TAB}{SPC}';
SendKeys '%d{TAB}%f';
SendKeys '%oca{ENTER}';

__END__

 

[Mark Seger]

I hear you

Who do you hear? Please adopt an effective follow-up style with no top-
posting and an appropriate amount of context. Please do read the posting
guidelines for this group.

sorry about that, but no need to get snippy...

You should not be looking at the VBA macros Excel generates, but rather
the OLE interface it exposes.

I guess you weren't able to figure out my question and so decided to
answer your own one. Looking at the EXCEL macros is EXACTLY what I want
because I want to see which method/properties are associated with a
particular option. Not being an OLE heavy, nor do I have the desire to
become on, trying to work my way though the microsoft documentation can
be painful.

ITYM "Perl FAQ". If that is so, I don't see how that is relevant, as
yours (a question that has now lots its place in this thread because of
two rounds of bad quoting) is not frequently asked.

gee, if I wanted to play 'syntax' police like you I'd point out you had
a typo on 'lost', but since it's pretty obvious what you meant it would
have been pointless and a waste of everybody's time.

....and in my opinion the FAQ is very relevant because it gives examples
of how to map VB syntax, which the excel macro recorder is kind enough
to capture and show you AND the FAQ provides in limited form. However,
the perl array mapping is not obvious, at least not to me, and that's
why I asked the question I did.

Here is something that may get you started:

#! /usr/bin/perl

use strict;
use warnings;

example it totally worthless as I'm way beyond what it shows. All I
want do know is how to map a bloody VB array (or to be more precise, the
FieldOpen parameter of the openText method) into perl syntax

sheesh...

-mark

 

[Mark Seger]

You should really look at the Spreadsheet::WriteExcel module and see if
if fits your needs. I have had much success using it.

Actually I did look at it and while it might meet part of my needs I
didn't think it would get me the whole solution as I'm trying to
automate pulling over some data files from a remote system and
generating some graphs with trendlines. It sounded like 'Spreadsheet'
could write me excel compatible files, but then I'd have to run execl
itself, admittedly that too could be automated, but it felt like more
work. I figured if I could control all aspects of excel it would be
something I could refer back to in the future and the arrays mapping was
my one stumbling block.

To your original question regarding array of arrays, have you tried the
following with the FieldInfo?

$Book = $Excel->Workbooks->OpenText({
Filename=>$filename, ConsecutiveDelimiter=>1, Space=>1,
FieldInfo => [ [1, 5], [2, 1], [3, 1], [4, 1] ]
});

Excellent! That does the trick. I'm embarassed it didn't occur to me
to try it like that - that's what newsgroups are for.

I'm also glad to see you were able to parse my poorly formatted posting.


-mark