Jan 4, 2011

Microsoft Excel manipulation with PERL

PERL with Microsoft Excel manipulation:

use OLE;
use Win32::OLE::Const "Microsoft Excel";

###################################################################################################################################

#___ DEFINE EXCEL

$excel = CreateObject OLE "Excel.Application";

#___ MAKE EXCEL VISIBLE

$excel -> {Visible} = 1;

#___ ADD NEW WORKBOOK

$workbook = $excel    -> Workbooks -> Add;
$sheet    = $workbook -> Worksheets("Sheet1");
$sheet                -> Activate;

#___ OPEN EXISTING WORKBOOK

$workbook = $excel    -> Workbooks -> Open("$file_name");
$sheet    = $workbook -> Worksheets(1) -> {Name};
$sheet    = $workbook -> Worksheets($sheet);
$sheet                -> Activate;

#___ ACTIVATE EXISTING WORKBOOK

$excel -> Windows("Book1") -> Activate;
$workbook = $excel    -> Activewindow;
$sheet    = $workbook -> Activesheet;

#___ CLOSE WORKBOOK

$workbook -> Close;

#___ ADD NEW WORKSHEET

$workbook -> Worksheets -> Add({After => $workbook -> Worksheets($workbook -> Worksheets -> {Count})});

#___ CHANGE WORKSHEET NAME

$sheet -> {Name} = "Name of Worksheet";

#___ PRINT VALUE TO CELL

$sheet -> Range("A1") -> {Value} = 1234;

#___ SUM FORMULAS

$sheet -> Range("A3") -> {FormulaR1C1} = "=SUM(R[-2]C:R[-1]C)";                # Sum rows
$sheet -> Range("C1") -> {FormulaR1C1} = "=SUM(RC[-2]:RC[-1])";                # Sum columns

#___ RETRIEVE VALUE FROM CELL

$data = $sheet -> Range("G7") -> {Value};

#___ FORMAT TEXT

$sheet -> Range("G7:H7") -> Font -> {Bold}       = "True";
$sheet -> Range("G7:H7") -> Font -> {Italic}     = "True";
$sheet -> Range("G7:H7") -> Font -> {Underline}  = xlUnderlineStyleSingle;
$sheet -> Range("G7:H7") -> Font -> {Size}       = 8;
$sheet -> Range("G7:H7") -> Font -> {Name}       = "Arial";
$sheet -> Range("G7:H7") -> Font -> {ColorIndex} = 4;

$sheet -> Range("G7:H7") -> {NumberFormat} = "\@";                             # Text
$sheet -> Range("A1:H7") -> {NumberFormat} = "\$#,##0.00";                     # Currency
$sheet -> Range("G7:H7") -> {NumberFormat} = "\$#,##0.00_);[Red](\$#,##0.00)"; # Currency - red negatives
$sheet -> Range("G7:H7") -> {NumberFormat} = "0.00_);[Red](0.00)";             # Numbers with decimals
$sheet -> Range("G7:H7") -> {NumberFormat} = "#,##0";                          # Numbers with commas
$sheet -> Range("G7:H7") -> {NumberFormat} = "#,##0_);[Red](#,##0)";           # Numbers with commas - red negatives
$sheet -> Range("G7:H7") -> {NumberFormat} = "0.00%";                          # Percents
$sheet -> Range("G7:H7") -> {NumberFormat} = "m/d/yyyy"                        # Dates

#___ ALIGN TEXT

$sheet -> Range("G7:H7") -> {HorizontalAlignment} = xlHAlignCenter;            # Center text;
$sheet -> Range("A1:A2") -> {Orientation} = 90;                                # Rotate text

#___ SET COLUMN WIDTH/ROW HEIGHT

$sheet -> Range('A:A') -> {ColumnWidth} = 9.14;
$sheet -> Range("8:8") -> {RowHeight}   = 30;
$sheet -> Range("G:H") -> {Columns} -> Autofit;

#___ FIND LAST ROW/COLUMN WITH DATA

$last_row = $sheet -> UsedRange -> Find({What => "*", SearchDirection => xlPrevious, SearchOrder => xlByRows})    -> {Row};
$last_col = $sheet -> UsedRange -> Find({What => "*", SearchDirection => xlPrevious, SearchOrder => xlByColumns}) -> {Column};

#___ ADD BORDERS

$sheet -> Range("A3:I3") -> Borders(xlEdgeBottom)       -> {LineStyle}  = xlDouble;
$sheet -> Range("A3:I3") -> Borders(xlEdgeBottom)       -> {Weight}     = xlThick;
$sheet -> Range("A3:I3") -> Borders(xlEdgeBottom)       -> {ColorIndex} = 1;
$sheet -> Range("A3:I3") -> Borders(xlEdgeLeft)         -> {LineStyle}  = xlContinuous;
$sheet -> Range("A3:I3") -> Borders(xlEdgeLeft)         -> {Weight}     = xlThin;
$sheet -> Range("A3:I3") -> Borders(xlEdgeTop)          -> {LineStyle}  = xlContinuous;
$sheet -> Range("A3:I3") -> Borders(xlEdgeTop)          -> {Weight}     = xlThin;
$sheet -> Range("A3:I3") -> Borders(xlEdgeBottom)       -> {LineStyle}  = xlContinuous;
$sheet -> Range("A3:I3") -> Borders(xlEdgeBottom)       -> {Weight}     = xlThin;
$sheet -> Range("A3:I3") -> Borders(xlEdgeRight)        -> {LineStyle}  = xlContinuous;
$sheet -> Range("A3:I3") -> Borders(xlEdgeRight)        -> {Weight}     = xlThin;
$sheet -> Range("A3:I3") -> Borders(xlInsideVertical)   -> {LineStyle}  = xlContinuous;
$sheet -> Range("A3:I3") -> Borders(xlInsideVertical)   -> {Weight}     = xlThin;
$sheet -> Range("A3:I3") -> Borders(xlInsideHorizontal) -> {LineStyle}  = xlContinuous;
$sheet -> Range("A3:I3") -> Borders(xlInsideHorizontal) -> {Weight}     = xlThin;

#___ PRINT SETUP

$sheet -> PageSetup -> {Orientation}  = xlLandscape;
$sheet -> PageSetup -> {Order}        = xlOverThenDown;
$sheet -> PageSetup -> {LeftMargin}   = .25;
$sheet -> PageSetup -> {RightMargin}  = .25;
$sheet -> PageSetup -> {BottomMargin} = .5;
$sheet -> PageSetup -> {CenterFooter} = "Page &P of &N";
$sheet -> PageSetup -> {RightFooter}  = "Page &P of &N";
$sheet -> PageSetup -> {LeftFooter}   = "Left\nFooter";
$sheet -> PageSetup -> {Zoom}         = 75;
$sheet -> PageSetup -> FitToPagesWide = 1;
$sheet -> PageSetup -> FitToPagesTall = 1;

#___ ADD PAGE BREAK

$excel -> ActiveWindow -> SelectedSheets -> HPageBreaks -> Add({Before => $sheet -> Range("3:3")});

#___ HIDE COLUMNS

$sheet -> Range("G:H") -> EntireColumn -> {Hidden} = "True";

#___ MERGE CELLS

$sheet -> Range("H10:J10") -> Merge;

#___ INSERT PICTURE

$sheet -> Pictures -> Insert("picture_name");                # Insert in upper-left corner
$excel -> ActiveSheet -> Pictures -> Insert("picture_name"); # Insert in active cell

#___ GROUP ROWS

$sheet -> Range("7:8") -> Group;

#___ ACTIVATE CELL

$sheet -> Range("A2") -> Activate;

#___ FREEZE PANES

$excel -> ActiveWindow -> {FreezePanes} = "True";

#___ DELETE SHEET

$sheet -> Delete;

#___ SAVE AND QUIT

$excel    -> {DisplayAlerts} = 0; # This turns off the "This file already exists" message.
$workbook -> SaveAs ("C:\\file_name.xls");
$excel    -> Quit;

PERL sleep Function

PERL sleep Function:

Syntax:

sleep EXPR
sleep

Definition and Usage:

Pauses the script for EXPR seconds, or forever if EXPR is not specified. Returns the number of seconds actually slept. Can be interrupted by a signal handler, but you should avoid using sleep with alarm, since many systems use alarm for the sleep implementation.

Return Value:

Integer, number of seconds actually slept

Example:

Try out following example: You will understand the functionality of sleep.

$num = 5;
while($num--)

{
    print sleep(1);

print "\n";
}

PERL defined Function

PERL defined Function:

Syntax:

defined EXPR
defined

Definition and Usage:

Returns true if EXPR has a value other than the undef value, or checks the value of $_ if EXPR is not specified. This can be used with many functions to detect a failure in operation, since they return undef if there was a problem. A simple Boolean test does not differentiate between false, zero, an empty string, or the string .0., which are all equally false.
If EXPR is a function or function reference, then it returns true if the function has been defined. When used with entire arrays and hashes, it will not always produce intuitive results. If a hash element is specified, it returns true if the corresponding value has been defined, but does not determine whether the specified key exists in the hash.

Return Value:

0 if EXPR contains undef
1 if EXPR contains a valid value or reference

$var1 = "This is defined";

if( defined($var1) ){
  print "$var1\n";
}
if( defined($var2) ){
  print "var2 is also defined\n";
}else{
  print "var2 is not defined\n";
}

Output:

This will produce following result
This is defined
var2 is not defined

PERL q(),qq(),qx() Function

PERL q(),qq(),qx() Function:

qq():

Syntax:

qq ( string )

Definition and Usage:

qq() can be used instead of double quotes. This is not really a function, more like an operator, but you'll probably look here if you see it in another programmer's program without remembering what it is. You can actually use any set of delimiters, not just the parentheses.

Return Value:

A double-quoted string.

Example:

Try out following example:

$var = 10;
print(qq(This is a single quoted string with interpolation, $var));

It will produce following results:

This is a single quoted string with interpolation, 10

q():

Syntax:

q ( string )

Definition and Usage:

q() can be used instead of single quotes. This is not really a function, more like an operator, but you'll probably look here if you see it in another programmer's program without remembering what it is. You can actually use any set of delimiters, not just the parentheses.

Return Value:

A single-quoted string.

Example:

Try out following example:

$var = 10;
print(q(This is a single quoted string without interpolation, $var));

It will produce following results:

This is a single quoted string without interpolation ,$var

qx():

Syntax:

qx EXPR

Definition and Usage:

qx() is a alternative to using back-quotes to execute system commands. For example, qx(ls -l) will execute the UNIX ls command using the -l command-line option. You can actually use any set of delimiters, not just the parentheses.

Return Value:

The return value from the executed system command.

Example:

Try out following example:

# summarize disk usage for the /tmp directory
# and store the output of the command into the
# @output array.
@output = qx(dir);

print "@output\n";

It will produce following results:

Volume Serial Number is E8C9-0715

 Directory of C:\Documents and Settings\MOB100001692\My Documents\Downloads

01/04/2011  03:04 PM    <DIR>          .
01/04/2011  03:04 PM    <DIR>          ..
01/04/2011  01:11 PM                 0 Firefox Setup 3.6.13.exe
01/04/2011  01:11 PM         1,368,877 Firefox Setup 3.6.13.exe.part
01/04/2011  03:06 PM                 0 installed_versions'
...

Perl string lc() function

Perl string lc() function:

 $STRING = lc($STRING);

Perl's lc() function takes a string, makes the entire thing lowercase, and then returns the new string.

 $myWord = 'WORD';
 $myLowerWord = lc($myWord);

First, $myWord is set to a value of 'WORD', then the lc() function is run on $myWord. The lc() function takes the entire string $myWord and converts it to it's lowercase equivalent. The value of $myLowerWord is then equal to 'word'.

Perl string uc() function

Perl string uc() function:

$STRING = uc($STRING);

Perl's uc() function takes a string, makes the entire thing uppercase, and then returns the new string.

$myWord = 'word';
$myUpperWord = uc($myWord);

First, $myWord is set to a value of 'word', then the uc() function is run on $myWord. The uc() function takes the entire string $myWord and converts it to it's uppercase equivalent. The value of $myUpperWord is then equal to 'WORD'.