Liste de Perl commandes pour manipuler Windows Excel avec Win32::OLE du module

C'est la liste des opérations que j'ai recueillies lors de l'écriture de mes scripts sur Excel avec Win32 module. Je voudrais partager.

N'hésitez pas à commenter/modifier afin de l'améliorer.

1. L'ouverture et l'Enregistrement Excel/Classeurs

#Modules to use
use Cwd 'abs_path';
use Win32::OLE;
use Win32::OLE qw(in with);
use Win32::OLE::Const "Microsoft Excel";
$Win32::OLE::Warn = 3;

#Need to use absolute path for Excel files
my $excel_file = abs_path("$Excel_path") or die "Error: the file $Excel_path has not been found\n";

# Open Excel application
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');

# Open Excel file
my $Book = $Excel->Workbooks->Open($excel_file);

#Make Excel visible
$Excel->{Visible} = 1;

#___ ADD NEW WORKBOOK
my $Book = $Excel->Workbooks->Add;
my $Sheet = $Book->Worksheets("Sheet1");
$Sheet->Activate;

#Save Excel file
$Excel->{DisplayAlerts}=0; # This turns off the "This file already exists" message.
$Book->Save; #Or $Book->SaveAs("C:\\file_name.xls");
$Book->Close; #or $Excel->Quit;

2. La Manipulation des Feuilles de calcul

#Get the active Worksheet 
my $Book = $Excel->Activewindow;
my $Sheet = $Book->Activesheet;

#List of Worksheet names
my @list_Sheet = map { $_->{'Name'} } (in $Book->{Worksheets});

#Access a given Worksheet
my $Sheet = $Book->Worksheets($list_Sheet[0]);

#Add new Worksheet
$Book->Worksheets->Add({After => $workbook->Worksheets($workbook->Worksheets->{Count})});

#Change Worksheet Name
$Sheet->{Name} = "Name of Worksheet";

#Freeze Pane
$Excel -> ActiveWindow -> {FreezePanes} = "True";

#Delete Sheet
$Sheet -> Delete;

3. La Manipulation de cellules

#Edit the value of a cell (2 methods)
$Sheet->Range("A1")->{Value} = 1234;
$Sheet->Cells(1,1)->{Value} = 1234;
#Edit the values in a range of cells
$Sheet->Range("A8:C9")->{Value} = [[ undef, 'Xyzzy', 'Plugh' ],
[ 42,    'Perl',  3.1415  ]];
#Edit the formula in a cell (2 types)
$Sheet->Range("A1")->{Formula} = "=A1*9.81";
$Sheet->Range("A3")->{FormulaR1C1} = "=SUM(R[-2]C:R[-1]C)";      # Sum of rows
$Sheet->Range("C1")->{FormulaR1C1} = "=SUM(RC[-2]:RC[-1])";      # Sum of columns
#Edit the format of the text (font)
$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;
#Edit the number format
$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
#Activate Cell
$Sheet -> Range("A2") -> Activate;
$Sheet->Hyperlinks->Add({   
Anchor          =>  $range, #Range of cells with the hyperlink; e.g. $Sheet->Range("A1")
Address         =>  $adr, #File path, http address, etc.
TextToDisplay   =>  $txt, #Text in the cell
ScreenTip       =>  $tip, #Tip while hovering the mouse over the hyperlink
});

N. B: pour récupérer la liste de liens hypertexte, avoir un regard sur le post suivant
Obtenir la liste des liens hypertexte à partir d'une feuille de calcul Excel avec Perl Win32::OLE

4. La Manipulation de Lignes /Colonnes

#Insert a row before/after line 22
$Sheet->Rows("22:22")->Insert(xlUp, xlFormatFromRightOrBelow);
$Sheet->Rows("23:23")->Insert(-4121,0);  #xlDown is -4121 and that xlFormatFromLeftOrAbove is 0
#Delete a row
$Sheet->Rows("22:22")->Delete();
#Set column width and row height
$Sheet -> Range('A:A') -> {ColumnWidth} = 9.14;
$Sheet -> Range("8:8") -> {RowHeight}   = 30;
$Sheet -> Range("G:H") -> {Columns} -> Autofit;
# Get the last row/column
my $last_row = $Sheet -> UsedRange -> Find({What => "*", SearchDirection => xlPrevious, SearchOrder => xlByRows})    -> {Row};
my $last_col = $Sheet -> UsedRange -> Find({What => "*", SearchDirection => xlPrevious, SearchOrder => xlByColumns}) -> {Column};
#Add borders (method 1)
$Sheet -> Range("A3:H3") -> Borders(xlEdgeBottom)       -> {LineStyle}  = xlDouble;
$Sheet -> Range("A3:H3") -> Borders(xlEdgeBottom)       -> {Weight}     = xlThick;
$Sheet -> Range("A3:H3") -> Borders(xlEdgeBottom)       -> {ColorIndex} = 1;
$Sheet -> Range("A3:H3") -> Borders(xlEdgeLeft)         -> {LineStyle}  = xlContinuous;
$Sheet -> Range("A3:H3") -> Borders(xlEdgeLeft)         -> {Weight}     = xlThin;
$Sheet -> Range("A3:H3") -> Borders(xlEdgeTop)          -> {LineStyle}  = xlContinuous;
$Sheet -> Range("A3:H3") -> Borders(xlEdgeTop)          -> {Weight}     = xlThin;
$Sheet -> Range("A3:H3") -> Borders(xlEdgeBottom)       -> {LineStyle}  = xlContinuous;
$Sheet -> Range("A3:H3") -> Borders(xlEdgeBottom)       -> {Weight}     = xlThin;
$Sheet -> Range("A3:H3") -> Borders(xlEdgeRight)        -> {LineStyle}  = xlContinuous;
$Sheet -> Range("A3:H3") -> Borders(xlEdgeRight)        -> {Weight}     = xlThin;
$Sheet -> Range("A3:H3") -> Borders(xlInsideVertical)   -> {LineStyle}  = xlDashDot
$Sheet -> Range("A3:H3") -> Borders(xlInsideVertical)   -> {Weight}     = xlMedium;
$Sheet -> Range("A3:I3") -> Borders(xlInsideHorizontal) -> {LineStyle}  = xlContinuous;
$Sheet -> Range("A3:I3") -> Borders(xlInsideHorizontal) -> {Weight}     = xlThin;
#Add borders (method 2)
my @edges = qw (xlInsideHorizontal xlInsideVertical xlEdgeBottom xlEdgeTop xlEdgeRight);
foreach my $edge (@edges)
{
with (my $Borders = $Sheet->Range("A3:H3")->Borders(eval($edge)), 
LineStyle =>xlContinuous, Weight => xlThin , ColorIndex => 1);
}
#Merge Cells
$Sheet -> Range("H10:J10") -> Merge;
#Group Rows
$Sheet -> Range("7:8") -> Group;
#Hide Columns
$Sheet -> Range("G:H") -> EntireColumn -> {Hidden} = "True";

5. Divers

#Add a name (i.e. reference)
$Book->Names->Add($name =>"=$ref"); #Where $name points to the range $ref
#Insert a picture
$Sheet -> Pictures -> Insert("picture_name");                # Insert in upper-left corner
$Excel -> ActiveSheet -> Pictures -> Insert("picture_name"); # Insert in active cell
#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 a Page break
$Excel -> ActiveWindow -> SelectedSheets -> HPageBreaks -> Add({Before => $Sheet -> Range("3:3")});

6. Liens utiles

Lien pour plus d'informations sur les Couleurs sur Excel:
http://dmcritchie.mvps.org/excel/colors.htm

Lien pour plus d'informations sur Excel constantes:
http://msdn.microsoft.com/en-us/library/aa221100%28office.11%29.aspx

Des liens à partir de Win32::OLE du module: http://search.cpan.org/~jdb/Win32-OLE-0.1712/lib/Win32/OLE.mp#EXEMPLES

Informations utiles à propos de l'utilisation d'Excel peut être trouvé à cette adresse