Perl Language
Win32 :: OLE 모듈이있는 Windows Excel 용 Perl 명령
수색…
소개
이 예제는 Win32 :: OLE 모듈을 통해 Excel을 조작하기 위해 Perl에서 가장 많이 사용되는 명령을 소개합니다.
통사론
- $ 시트 -> 범위 (셀 1, [셀 2]) 셀 또는 셀 범위 #Select
- $ Sheet-> Cells ( rowIndex , columnIndex ) # 행과 열의 인덱스로 셀 선택
매개 변수
매개 변수 | 세부 |
---|---|
셀 1 (필수) | 범위의 이름입니다. 이것은 매크로 언어로 된 A1 스타일 참조 여야합니다. 범위 연산자 (콜론), 교차 연산자 (공백) 또는 공용어 연산자 (쉼표)를 포함 할 수 있습니다. |
Cell2 (선택 사항) | 지정된 경우 셀 1 은 범위의 왼쪽 위 모서리에 해당하고 셀 2 는 범위의 오른쪽 하단 모서리에 해당합니다. |
비고
Excel의 색상에 대한 정보 링크 : http://dmcritchie.mvps.org/excel/colors.htm
Excel 상수에 대한 정보 링크 : http://msdn.microsoft.com/en-us/library/aa221100%28office.11%29.aspx
Win32 :: OLE 모듈의 링크 : http://search.cpan.org/~jdb/Win32-OLE-0.1712/lib/Win32/OLE.pm#EXAMPLES
이 주소 에서 Excel 사용에 대한 유용한 정보를 찾을 수 있습니다.
1. Excel / Workbooks 열기 및 저장
#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. 워크 시트의 조작
#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. 세포의 조작
#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
});
주의 : 하이퍼 링크 목록을 검색하려면 다음 게시물 을보십시오. Perl Win32 :: OLE를 사용하여 Excel 워크 시트의 하이퍼 링크 목록 가져 오기
4. 행 / 열의 조작
#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)
Modified text is an extract of the original Stack Overflow Documentation
아래 라이선스 CC BY-SA 3.0
와 제휴하지 않음 Stack Overflow