HOWTO: Automate Excel Using Perl for Win32

• Microsoft Excel 2000
• Microsoft Office 2000 Developer
• Microsoft Excel 97 for Windows

SUMMARY

Developers familiar with using Perl can take advantage of the Automation capabilities in Perl for Win32 to integrate with applications such as Microsoft Office. This article gives you some example Perl code that sends data to Microsoft Excel and then creates a Chart and PivotTable.

MORE INFORMATION

Before executing any Perl scripts, you need to have Perl installed and configured on your machine. Because Perl is freely distributed, anyone can provide an implementation of Perl for Win32. Two commonly used ported versions are provided by ActiveState, formerly ActiveWare, and Mortice Kern System (MKS). This example uses ActiveState's Perl for Win32.

Follow the steps below to create and run the sample:

1. If you don't have the latest ActiveState's ActivePerl package installed on your machine, and want to try this example, download it from:




http://www.activestate.com/ActivePerl

2. Start notepad.exe and create a new file named perlxl.pl with the following Perl script:




use Win32::OLE;

# Start Excel and make it visible
$xlApp = Win32::OLE->new('Excel.Application');
$xlApp->{Visible} = 1;

# Create a new workbook
$xlBook = $xlApp->Workbooks->Add;

# Our data that we will add to the workbook...
$mydata = [["Item",     "Category", "Price"], 
           ["Nails",    "Hardware",  "5.25"],
           ["Shirt",    "Clothing", "23.00"],
           ["Hammer",   "Hardware", "16.25"],
           ["Sandwich", "Food",      "5.00"],
           ["Pants",    "Clothing", "31.00"],
           ["Drinks",   "Food",      "2.25"]];

# Write all the data at once...
$rng = $xlBook->ActiveSheet->Range("A1:C7");
$rng->{Value} = $mydata;

# Create a PivotTable for the data...
$tbl = $xlBook->ActiveSheet->PivotTableWizard(1, $rng, "", "MyPivotTable");

# Set pivot fields...
$tbl->AddFields("Category", "Item");
$tbl->PivotFields("Price")->{Orientation} = 4; # 4=xlDataField

# Create a chart too...
$chart = $xlBook->Charts->Add;
$chart->SetSourceData($rng, 2);
$chart->{ChartType} = 70; # 3D-pie chart
$chart->Location(2, "Sheet4");

# Wait for user input...
print "Press <return> to continue...";
$x = <STDIN>;

# Clean up
$xlBook->{Saved} = 1;
$xlApp->Quit;
$xlBook = 0;
$xlApp = 0;

print "All done."; 

3. Type perlxl.pl from the command-line in the directory where you created the file, or double-click it from Explorer. You should see Microsoft Excel start, and a new workbook get created with a PivotTable and a Chart.

Additional query words:

Keywords : kb3rdparty kbAutomation kbExcel kbUNIX kbGrpDSO kbOffice2000 kbExcel97 kbDSupport kbexcel2000
Version : WINDOWS:2000,97; :
Platform : WINDOWS
Issue type : kbhowto