PERL Miscellaneous - Excel and Perl

Author

Excel and Perl

Mark Seger

2005-06-09, 3:57 am

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

. 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

2005-06-09, 8:57 am

Mark Seger wrote:
> 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

2005-06-09, 8:57 pm

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

Bob wrote:
> Mark Seger wrote:
>
>
>
> 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!
>

A. Sinan Unur

2005-06-10, 3:59 am

Mark Seger <Mark.Seger@hp.com> wrote in news:42A8B86E.20705@hp.com:

> 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:

Mark Seger <Mark.Seger@hp.com> wrote in
news:42a6dcf7@usenet01.boi.hp.com:

> 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__

--
A. Sinan Unur <1usa@llenroc.ude.invalid>
(reverse each component and remove .invalid for email address)

comp.lang.perl.misc guidelines on the WWW:
http://mail.augustmail.com/~tadmc/c...guidelines.html

l v

2005-06-10, 3:59 am

Mark Seger wrote:
> 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.
>
[snip]
>
> -mark
>
> Bob wrote:
>
>

Mark

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

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] ]
});

Len

----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

Mark Seger

2005-06-10, 3:58 pm

>>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

2005-06-10, 3:58 pm

> 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.
8-)

-mark

l v

2005-06-11, 3:57 am

Mark Seger wrote:
>
>
>
> 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.
>
>
>
> 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.
> 8-)
>
> -mark

Glad it worked for you.

Spreadsheet::WriteExcel does have some short comings as you mention. I
too once wanted to create excel graphs but found that my workplace had
purchased an excellent graphing tool (Visual Mining's Netcharts Applets)
which easily allowed me to put my graphs on our intranet using Perl/CGI.

No sales pitch intended.

Len

----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

Sponsored Links