Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

Re: Excel to Perl

by cacharbe (Curate)
on Mar 20, 2002 at 19:23 UTC ( #153094=note: print w/ replies, xml ) Need Help??


in reply to Excel to Perl

Alright, I'll finish the Tutorial and get it up by the week-end.

Read my Scratch Pad for a complete discussion on getting data from and manipulating data in an excel worksheet using Win32::OLE.

Honestly, if you're going to be opening the workbooks anyway, there is no reason to use two scripts to accomplish this task, just iterate through the Sheets object of each workbook starting from the 1 position. Otherwise use the SaveAs function of the Worksheet object

use strict; use Win32::OLE qw(in with); use Win32::OLE::Const 'Microsoft Excel'; $Win32::OLE::Warn = 3; # Die on Errors. ####################################### ## ::Warn = 2 throws the errors, but ## ## expects that the programmer deals ## ####################################### my $excelfile = 'c:\temp\win32\tabexample.xls'; my $excelout = 'c:\temp\win32\tabexample.tab'; my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit'); $Excel->{DisplayAlerts}=0; my $Book = $Excel->Workbooks->Open($excelfile); $Book->SaveAs({Filename =>$excelout, FileFormat => xlCurrentPlatformText});
C-.

Update: I thought I should make a slight clarification about iterating through the Worksheets set based on sheet count. I was trying to say that the Worksheets object is 1 based, not 0 based. If, indeed, you wanted to skip the first work sheet, you would start with 2.

my $sheetcnt = $Book->Worksheets->Count(); foreach (2..$sheetcnt){ print $Book->Worksheets($_)->{Name} ."\n"; my $sheet = $Book->Worksheets($_); ##Do something with the sheet object## }


Comment on Re: Excel to Perl
Select or Download Code

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://153094]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (3)
As of 2014-09-20 03:28 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (152 votes), past polls