Perl Win32::OLE Excel.Application

use Win32::OLE qw(in with);
use Win32::OLE::Const ‘Microsoft Excel’;
use Win32::OLE::Variant;
$Win32::OLE::Warn = 3;

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

#___ 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
# Currency — red negatives
$sheet -> Range(«G7:H7») -> {NumberFormat} = «\$#,##0.00_);[Red](\$#,##0.00)»;
# Numbers with decimals
$sheet -> Range(«G7:H7») -> {NumberFormat} = «0.00_);[Red](0.00)»;
# Numbers with commas
$sheet -> Range(«G7:H7») -> {NumberFormat} = «#,##0»;
# Numbers with commas — red negatives
$sheet -> Range(«G7:H7») -> {NumberFormat} = «#,##0_);[Red](#,##0)»;
$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;

———————————————————————————

use Win32::OLE;
use Win32::OLE qw(in with);
use Win32::OLE::Variant;
use Win32::OLE::Const ‘Microsoft Excel’;

$Excel = Win32::OLE->GetActiveObject(‘Excel.Application’) ||
Win32::OLE->new(‘Excel.Application’);
$Excel->{‘Visible’} = 0; #0 is hidden, 1 is visible
$Excel->{DisplayAlerts}=0; #0 is hide alerts

# Open File and Worksheet
my $Book = $Excel->Workbooks->Open
(‘C:\report\results\check_all.csv’); # open Excel file
$Sheet = $Book->Worksheets(1);

# Create New Workbook
$Excel->{SheetsInNewWorkBook} = 1;
$Book2 = $Excel->Workbooks->Add();
$Sheet2 = $Book2->Worksheets(1);
$Sheet2->{Name} = ‘My test worksheet’;

# Find Last Column and Row
my $LastRow = $Sheet->UsedRange->Find({What=>»*»,
SearchDirection=>xlPrevious,
SearchOrder=>xlByRows})->{Row};

my $LastCol = $Sheet->UsedRange->Find({What=>»*»,
SearchDirection=>xlPrevious,
SearchOrder=>xlByColumns})->{Column};
$mylastcol = a;
for (my $m=1;$m<$LastCol;$m++){$mylastcol++;}
$mylastcol2 = a;
for (my $m=1;$m<($LastCol — 1);$m++){$mylastcol2++;}

# Draw Borders
my @edges = qw (xlInsideHorizontal xlInsideVertical);
$range = «a1:$mylastcol$LastRow»;
foreach my $edge (@edges)
{
with (my $Borders = $Sheet->Range($range)->Borders(eval($edge)),
LineStyle =>xlContinuous,
Weight => xlThin ,
ColorIndex => 1);
}

# Cell Values
$Sheet->Range(‘a1’)->{Value} = $Sheet->Range(‘b2′)->{Value};

# Resize Columns
my @columnheaders = qw(A:B);
foreach my $range(@columnheaders){
$Sheet->Columns($range)->AutoFit();
}
# Order Rows
$tmp = «$mylastcol2».’3’;
$Rangea = $Sheet->Range(«$tmp»);
$Rangeb = $Sheet->Range(«a3″);
$Excel->Selection->Sort({Key1 => $Rangea,
Order1 => xlDescending,
Key2 => $Rangeb});

# Merge Cells
$mynextcol = ‘b’;
for (my $n=1;$n<$LastCol;$n+=2)
{
my $range = $mynextcol++ . ‘1:’ . $mynextcol++ . ‘1’;
$Sheet->Range($range)->Merge();
$Sheet->Range($range)->{HorizontalAlignment} = xlHAlignCenter;
}

# Pie Chart
my $Range = $Sheet->Range(‘a1:d2’);
my $Chart = $Book->Charts->Add;
$Chart->ChartWizard($Range,xl3DPie,7,xlRows,1,0,2,»Sales Percentages»);

# Bar Graph and Rotate
my $Range = $Sheet->Range(‘a1:a3’);
my $Chart = $Excel->Charts->Add;
$Chart->{Type} = xl3DColumn;
for (my $i = 30; $i <=180; $i+=10)
{
$Chart->{Rotation} = $i;
sleep(1);
}

# Line Chart and Save
my $Range = $Sheet->Range(‘a1:d2’);
my $Chart = $Excel->Charts->Add;
$Chart->{ChartType} = xlLine;
$Chart->SetSourceData({Source => $Range, PlotBy => xlColumns});
$Chart->{HasTitle} = 1;
$Chart->ChartTitle->{Text} = «Some Title»;

my $ChartObj = $Sheet->ChartObjects;

$Chart->Export({
FileName => $filegraphname,
FilterName => ‘GIF’,
Interactive => 0});

# Save as PDF
$Excel->ActiveWindow->SelectedSheets->PrintOut({
Copy => 1,
ActivePrinter => ‘Acrobat PDFWriter’});

# Save as Excel
$Book->SaveAs({Filename =>’C:\report\results\check_all.xls’,
FileFormat => xlWorkbookNormal});
$Book->Close();
$Excel->Quit();