Perl Language Perl commands for Windows Excel with Win32::OLE module 3. Manipulation of cells
Example
#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: to retrieve the list of hyperlinks, have a look at the following post Getting list of hyperlinks from an Excel worksheet with Perl Win32::OLE
