Automation of Windows using PERL

What is PERL ? What are the advantages of PERL?

Perl is a high level interpreted and dynamic programming language. Unlike, other programming languages, Perl is easy to write and understand. Perl can be operated on any operating system that has Perl interpreter installed in it; also it is available free. All Linux Operating system comes with Perl installed, so you can program on Linux easily using Perl without worrying about syntax changes of underlying shell commands. Perl language is best at text processing, and is mainly used for web development. Also, CGI (Common Gateway Interface) programming is what Perl is used for; it is the most common way for webservers to interact with users. One of the benefits of using Perl is that it can automatically generate mails and clean up systems.

Automating Windows Excel with PERL

To automate the window or excel, win32::OLE interface of PERL can be used. To manage, plan and evaluate business process, many companies depend on excel worksheet. Here, we will take an example of an excel automation. Let assume there is two different value in excel, and you want to display the total of both the values in a specific cell. So how you can automate the function?

First of all you have to download & install Active Perl package for windows. It comes bundles with an IDE.

Objectives of our PERL Automation Code-

  1. Create a new instance of MS excel
  2. Now, take any two values that you have to sum up, here we will refer this value as the operand, “operand 1” and “operand 2”. The value for “operand 1”=10, while for “operand 2”=20.
  3. Now, to get a total sum of both operand 10+20=30 in specific cell, we will put the value for operand 1 in cell (A1) and value for operand 2 in cell (A2) as shown in an example below. Once you apply the formula, your result will display in a cell (A4).

To run the program you have to follow the following steps:

  1. Open any code editor ex Notepad
  2. Write the program given below
  3. Save it as .pl extension for ex: New.pl
  4. Open command prompt and navigate to the path where you saved your Perl file
  5. Execute C:->cd perl->new.pl

PERL Code to Sum values in two cells

Use, Win32::OLE;

$operand1 = ’10’;

$operand2 = ’20’;

$xls_app = Win32::OLE->new (‘Excel.Application’);

# Launch Microsoft Excel.

$xls_app->{‘Visible’} = 1;

# Create a Workbook.

$xls_app->Workbooks->Add();

$xls_app->Cells(1,1)->{Value} = $operand1;

$xls_app->Cells(2,1)->{Value} = $operand2;

clip_image002

Fig.1 shows the excel sheet before the program executed

# apply formula

$xls_app->Cells(4,1)->{Formula} = ‘=sum(A1:A2)’;

$sum = $xls_app->Cells(4,1)->{Value};

# Print the result

print “MS Excel return, $operand1 + $operand2 = $sum.”;

clip_image004

Fig.2 shows the excel sheet after the program is executed and displays the total sum of the values.