dimmesdale has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to change some code I have using SpreadSheet::WriteExcel to Win32::OLE compatible. Well, I don't know where to begin (well, actually, I've begun, just not much). I've got a script using WriteExcel, but when mixing it with OLE, I was getting some wierd things, so I'm going to change it all to OLE. My code is thus: 

# All non-excel stuff taken out for your viewing ease ;)

#!/usr/bin/perl -w

use strict;


use Spreadsheet::WriteExcel;
use Spreadsheet::WriteExcel::Utility;

...

my $file = 'test.txt';
open (TABFILE,$file) or die "$file: $!";

# Create a new Excel workbook
my $workbook  = Spreadsheet::WriteExcel->new('TEST2.xls');
my $worksheet = $workbook->addworksheet('all_avg');
my $group_avg = $workbook->addworksheet('gr_avg');
my $h_frmt    = $workbook->addformat( 
          border => 2, fg_color => 'silver', pattern => 1);
my $headers1  = [ 'TS# and Condition', 'Rest HRT',
                 'IMC HRT', 'Rest SKT', 'IMC SKT',
                 'Rest EMG', 'IMC EMG' ];
my $headers2  = [ 'Condition', 'Rest HRT', 'IMC HRT',
                  'Rest SKT', 'IMC SKT' ];
my $row       = 1;
my ($cells,$formula,$ts2);

for (<TABFILE>) {
...
  my $col = 0;
  for (@vals) {
...
    $worksheet->write($row, $col, $_);
    $col++;
  }
  $row++;
}

$worksheet->write(0,0, $headers1, $h_frmt);

for(2,17,32,47) { $group_avg->write_col($_,0, \@cats) }

$group_avg->write(0,1,  'Low-Hour VFR',$h_frmt);
$group_avg->write(15,1, 'Low-Hour Instruments',$h_frmt);
$group_avg->write(30,1, 'High-Hour Instruments',$h_frmt);
$group_avg->write(45,1, 'All Groups',$h_frmt);

$group_avg->write_row(1,5, ['Rest EMG', 'IMC EMG'], $h_frmt);

for(1,16,31,46) {
  $group_avg->write_row($_,0, $headers2, $h_frmt)
}

for my $gr (0..3) {
  for my $avg(0..5) {
    for my $cond(0..$#cats) {
      my $ts = shift @{$ts2->[$gr][$avg][$cond]};
      if( ($avg < 4) || ($gr == 0 && $ts >= 21) ) {
        my $sheet = $worksheet->get_name();
        my $form = "=AVERAGE(${sheet}!" . 
                    join(",${sheet}!",
                    @{$formula->[$gr][$avg][$cond]}) . ')';
        $group_avg->write_formula( $cells->[$gr][$avg][$cond], $form )
+;
      }
    }
  }
}

[download]

The massive amount I could figure from the (in my view sketchy) documentation is this: 

my $file_name = 'test.xls';
my $xl        = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;
+}) or die "Excel failed to load: Win32::OLE->new";
my $book      = $xl->Workbooks->Add();
my @sheets    = $xl->Worksheets(2);

$sheets[0]->{Name} = 'all_avg';
$sheets[1]->{Name} = 'grp_avg';

$book->SaveAs($file_name)

[download]

I saw some things (e.g., Workbooks->Add()) on some nodes here, and an article in the perl journal, but the documentation never mentioned anything about these methods. Where can I find a list of them (preferably with some explanation/mention of calling convention)

Replies are listed 'Best First'.
Re: WriteExcel to OLE?
by cacharbe (Curate) on Jul 15, 2002 at 19:45 UTC
    You really aren't going to find these methods specifically. The WriteExcel module is an abtraction created to write XLS binary files directly, not an interface into the OLE object itself (cf. IDISPATCH interface for COM objects), which is what you are now dealing with.

    Because of this, It would probably behoove you to figure out the tasks that are involved from an excel stand point.

    • Create Header Columns
    • Insert Data into Proper Columns
    • Create Formulas based locations of certain data points
    • Format Foregrounds, Backgrounds, Fonts, line styles, etc
    • Insert Charting objects
    etc, etc...

    You need to enter data into the proper columns in your header rows. Well, excel really doesn't pay attention to header rows, per se, but you can track what row you are on and what column you are in, and apply certain formatting to cells, rows, columns and other range objects.

    Formatting requires a little knowledge of the object model, and I urge you to re-read my tutorial, as I speak directly to changing backgrounds, font styles, etc. and you should be able to use that info and other tools (one mentioned below) to extrapolate others.

    As for formulas, you are looking to either set the Value property:

    $Excel->Range($mycell)->{Value} = "=your formula here"

    [download]
    or the Formula property
    $Excel->Range($mycell)->{Formula} = "=your formula here"

    [download]

    Which give you the same result.

    At the top of the tutorial, I suggest the object browser that comes packaged with the AS port of Perl. I'm assuming Jan put a lot of work into it, and I think it should be used. One can usually find it here:

    <PERLROOT>\html\site\lib\Win32\OLE\Browswer\Browser.html

    I hope this points you in the right direction. Please follow-up if you have any method or object specific questions, or need more help in general.

    C-.

    ---
    Flex the Geek

[reply]
[d/l]
[select]
Re: WriteExcel to OLE?
by jsprat (Curate) on Jul 15, 2002 at 20:06 UTC
    The objects, methods, and properties are thoroughly documented in the Excel VBA help file. It is not installed in a default installation of Office, so you may (or may not) already have it installed. You'll want to at least familiarize yourself with Excel's object model (Application->Workbooks->Worksheets->Range->...) Also, look at the OLE Browser included with AS Perl.

    As far as Perl documentation of OLE, ActivePerl-WinFAQ12.html should be available locally - if not, it's on the Active State website. perldoc Win32::OLE provides some useful information, too.

    As far as converting VB syntax in the help file to perl, the basic rules are:

    • Methods (as defined in the helpfile) are called just like your example, (e.g., Workbooks->Add()).
    • Properties (again, as defined by MS) are accessed with curly brackets:
      $sheets[0]->{Name}
    • For named constants, use an anonymous hash or hashref:
      $xls->ActiveChart->Location({ Where => xlLocationAsObject, Name =>'Sheet1'});

    The first thing I do usually is record a macro that does the basics of what I want. Then, I use Win32::OLE::Const 'Microsoft Excel'; to import all Excel constants, so I can copy examples from the from macro I've recorded (or from the helpfile) without looking up the values of the constants. Then I can translate until it all works like I expect it.

    All in all, controlling Excel is quirky at best, and ugly at its worst. I have some notes I've taken, but they aren't formatted in any useful way. If you want to see them, let me know.

    Hope all this helps in some small way...

[reply]
[d/l]
[select]
      That's what I got from some of the resources I looked at (the VBA files, that is). However, (unfortunately), I do not have those file installed on the computer (I just started using it a few weeks ago, and Excel was there).

      Is there some internet site that has these copied? also, what about the 'Object Model'? I hadn't used Excel before about two weeks ago, so I don't know much about it. Are these in the VBA files, or somewhere else?

[reply]
        Ask whoever admins your computer to install it. Or check http://msdn.microsoft.com/library. I don't know if msdn includes all docs, but look at the left pane for Office Solutions Development, then check under both Excel and Office. Then check http://office.microsoft.com.

        About the object model, a quick google search shows several resources. The OM shows how each object fits into the heirarchy of all Excel objects, which objects are collections, which methods and properties are associated with which object.

        Seriously, though, open excel, tools->macro->record macro. Then view the source Excel creates. It's VB code, and it is pretty easy to understand. It'll take you a little time, but you'll find it makes learning to write the automation code easier. Create the Excel object, open/add the workbook, insert translated and/or new code here, save (and save often), quit.

[reply]

Back to Seekers of Perl Wisdom