Perl Win32::OLE Excel Cheat Sheet

VBA로 excel관련된 일을 automation하려다가 project를 잠궈논데 짜증나서 perl을 찾아봤는데. 훨씬 훌륭합니다. 아래 cheat sheet은 필요할때마다 참고하면 되겠네요..

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;

---------------------------------------------------------------------------------

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();

Perl/Tk에서 한글사용하기.

Perl/Tk 모듈(그래픽 사용자 인터페이스 툴킷)은 인코딩을 정의해 주지 않으면 한글이 아예 나오지 않고 에러가 납니다..다음과 같이 인코딩을 한글로 지정하면 됩니다. 아래코드는 펄사용자 모임의 Tk tutorial 코드입니다.

#!/usr/bin/perl
use strict;
use encoding "cp949";
use Tk;
my $mw = MainWindow->new(-title=>"자! 시작입니다");
$mw->geometry("=600x400");

#$mw->Label(-text=>"Hello world 1!!")->pack(-side=>'bottom');
$mw->Label(-text=>"Hello world 2!!")->pack();
$mw->Label(-text=>"Hello world 3!!")->pack();
my $l_hello = $mw->Label(-text=>"Hello world!!")->pack();

$mw->Button(
-text=>"configure",
-command=>\&get_env
)->pack(-side=>'bottom');

$mw->Button( -text=>"close", -command=>sub { exit; } )->pack(-side=>'bottom');

MainLoop();

###
sub get_env {
my($k,$v, $txt);
while(($k,$v) = each(%ENV)) {
$txt .= "$k=$v\n";
}
$l_hello->configure(-text=>$txt);
}

# 단, 유니코드(UTF-8)일 경우에는
# use encoding 'utf8';# 을 사용
use encoding 'cp949'; 라고 해주면 됩니다.
확장완성형입니다.use encoding 'euc-kr'; 도 되긴 되는데 이러면 "똠방각하"의 "똠" 같은 글자가 나오지 않습니다.
그냥 완성형입니다.use encoding 'utf8'; 로 하면 물론 똠방각하도 되지만, 이때는 소스 자체의 인코딩이 유니코드(UTF-8)이어야 합니다.

위글은 mwultong Blog ― 디카 / IT에서 발췌한 글을 수정하였습니다.