[Wellington-pm] Batch processing Excel spreadsheets on Win32

[greens at maf.govt.nz]

Hi Mongers,

At the end of the last meeting there was some discussion about working with M$
Excel spreadsheets with as little user interaction as possible. While some may
consider not using Excel is the best way to achieve this, we don't all have
that luxury.

The issue when using Win32::OLE is the dialog boxes thrown up by Excel itself.

Note: Win32::OLE requires the application to be installed so it won't work on a
      *nix box. I've not tried so feel free to prove me wrong.

Lets start by getting an Excel object

  use Win32::OLE::Const 'Microsoft Excel';
  use Win32::OLE qw(in with);
  my $Excel = Win32::OLE->GetActiveObject('Excel.Application') ||
              Win32::OLE->new('Excel.Application', 'Quit');

some dialogs can be killed like this

  $Excel->{DisplayAlerts} = 0;
  $Excel->{EnableEvents}  = 0;
  my $Book = $Excel->Workbooks->Open("$some_dir/$some_file");
  # do stuff...

but if the file contains links to other spreadsheets you will still be asked if
you want to update the links.

I recently discovered how to stop that annoyance:
(ref http://www.perlmonks.org/?node=185754)

  my $Book = $Excel->Workbooks->Open("$some_dir/$some_file",0,1);

The key here is the second and third parameters passed to Open(). They are
interpreted as boolean expressions for UpdateLinks and ReadOnly.

Now it is possible batch process a bunch of files without having to watch and
accept or reject updates.


Another useful tidbit is how to iterate through all the used cells in a sheet:
(ref http://www.perlmonks.org/?node=10799)

  foreach my $Sheet (in $Book->{Worksheets}) {
    print "<<$Sheet->{Name}>>\n";
    my $everything = $Sheet->UsedRange()->{Value}; ## the Magic ##
    foreach my $row (@$everything) {
      my $tab = '';
      foreach my $cell (@$row) {
        print $tab, defined($cell) ? $cell : '';
        $tab = "\t" unless ($tab);
      }
      print "\n";
    }
    print "\n\n";
  }

--
Simon Green [simon.green at maf.govt.nz] 



#####################################################################################
This e-mail message has been scanned for Viruses and Content and cleared 
by NetIQ MailMarshal
#####################################################################################

########################################################################
This email message and any attachment(s) is intended solely for the
addressee(s) named above. The information it contains is confidential
and may be legally privileged.  Unauthorised use of the message, or the
information it contains, may be unlawful. If you have received this
message by mistake please call the sender immediately on 64 4 4744 100
or notify us by return email and erase the original message and
attachments. Thank you.

The Ministry of Agriculture and Forestry accepts no responsibility for
changes made to this email or to any attachments after transmission from
the office.
########################################################################