bridge방식의 openvpn설정법은 자세히 나와있는 블로그를 보지 못했다.
아래 간략한 설치방법을 소개한다.
1.openvpn 관련 패키지 설치
yum install openvpn
yum install openssl
yum install lzo
2. brdige util설치
yum install bridge-utils
3.*easy-rsa디렉토리는 아래와 같다.
아래 디렉토리의 내용을 /etc/openvpn에 복사한다.
/var/share/openvpn/easy-rsa/2.0
4.var설정및 ca, key, dh생성
. ./vars
./clean-all
./build-ca
* build-ca생성시 common name은 'ca'로 넣는다.
./build-key-server server
* server key생성시 common name은 'server'로 넣는다.
./build-key client1
* client key생성시 common name은 'clinet1'로 넣는다.(client2가 있으면 client2처럼 각각의 common name을 다르게 넣는다)
./build-dh
*diffe hellman parameter를 생성한다.
5. 서버 config 생성
# Tunnel options
mode server # Set OpenVPN major mode
proto tcp-server # Setup the protocol (server)
port 1194 # TCP/UDP port number
dev tap0 # TUN/TAP virtual network device
keepalive 3600 7200 # Simplify the expression of --ping
daemon # Become a daemon after all initialization
verb 3 # Set output verbosity to n
comp-lzo # Use fast LZO compression
# OpenVPN server mode options
client-to-client # tells OpenVPN to internally route client-to-client traffic
duplicate-cn # Allow multiple clients with the same common name
#crl-verify /etc/openvpn/keys/crl.pem
# TLS Mode Options
tls-server # Enable TLS and assume server role during TLS hands
ca /etc/openvpn/keys/ca.crt # Certificate authority (CA) file
dh /etc/openvpn/keys/dh1024.pem # File containing Diffie Hellman param
cert /etc/openvpn/keys/server.crt # Local peer's signed certificate
key /etc/openvpn/keys/server.key
#TCP 1194포트를 기준으로 만들었다. 각각의 key들의 디렉토리를 본인의 서버에 맞게 변경한다.
#crl인증은 사용자 접속을 제한할때 사용한다.
6. startup스크립트 작성
#!/bin/bash
br="br0"
tap="tap0"
eth="eth0"
eth_ip="192.168.0.*"
eth_netmask="255.255.255.0"
eth_broadcast="192.168.0.255"
for t in $tap; do
openvpn --mktun --dev $t
done
brctl addbr $br
brctl addif $br $eth
for t in $tap; do
brctl addif $br $t
done
for t in $tap; do
ifconfig $t 0.0.0.0 promisc up
done
ifconfig $eth 0.0.0.0 promisc up
ifconfig $br $eth_ip netmask $eth_netmask broadcast $eth_broadcast
7. openvpn시작
openvpn /etc/openvpn/sever.conf
*위와같은 bridge.start 와 openvpn start 명령을 /etc/rc.local에 추가하여 boot시 시작되도록한다.
/etc/openvpn/bridge.start
openvpn /etc/openvpn/sever.conf
*참고
bridge stop스크립트
br="br0"
tap="tap0"
ifconfig $br down
brctl delbr $br
for t in $tap; do
openvpn --rmtun --dev $t
done
Howto
이것저것 관심있는 IT관련부분에 대해 정리해보겠습니다.
2009년 2월 22일
2009년 1월 26일
openvpn에서 crl.pem의 역할
openvpn의 client인증서를 강제로 revoke시키려 할때는 아래와 같은 방법으로 revoke시킨다.
. ./vars
./revoke-full client2
그러면 아래와 같은 메세지를 볼수 있다.
Using configuration from /root/openvpn/20/openvpn/tmp/easy-rsa/openssl.cnf
DEBUG[load_index]: unique_subject = "yes"
Revoking Certificate 04.
Data Base Updated
Using configuration from /root/openvpn/20/openvpn/tmp/easy-rsa/openssl.cnf
DEBUG[load_index]: unique_subject = "yes"
client2.crt: /C=KG/ST=NA/O=OpenVPN-TEST/CN=client2/emailAddress=me@myhost.mydomain
error 23 at 0 depth lookup:certificate revoked
마지막 error 23은 revoke된 인증서의 인증이 실패했다라는 의미이다.
세부적으로 revoke-full script는 CRL(certificate revocation list)이라고 불리는 crl.pem파일을 key디렉토리 하위에 생성한다.
openvpn server configuration파일에 아래 option을 추가하여 crl인증을 매 client인증때마다 수행하여 crl에 revoke된 정보가 들어있다면 connection을 drop시키도록 한다.
이러한 crl파일의 update는 openvpn서버가 실행되고 있는동안에도 수행될수 있으며 매 한시간 마다 update되며 매번 client가 접속시마다 체크된다.
crl-verify crl.pem
. ./vars
./revoke-full client2
그러면 아래와 같은 메세지를 볼수 있다.
Using configuration from /root/openvpn/20/openvpn/tmp/easy-rsa/openssl.cnf
DEBUG[load_index]: unique_subject = "yes"
Revoking Certificate 04.
Data Base Updated
Using configuration from /root/openvpn/20/openvpn/tmp/easy-rsa/openssl.cnf
DEBUG[load_index]: unique_subject = "yes"
client2.crt: /C=KG/ST=NA/O=OpenVPN-TEST/CN=client2/emailAddress=me@myhost.mydomain
error 23 at 0 depth lookup:certificate revoked
마지막 error 23은 revoke된 인증서의 인증이 실패했다라는 의미이다.
세부적으로 revoke-full script는 CRL(certificate revocation list)이라고 불리는 crl.pem파일을 key디렉토리 하위에 생성한다.
openvpn server configuration파일에 아래 option을 추가하여 crl인증을 매 client인증때마다 수행하여 crl에 revoke된 정보가 들어있다면 connection을 drop시키도록 한다.
이러한 crl파일의 update는 openvpn서버가 실행되고 있는동안에도 수행될수 있으며 매 한시간 마다 update되며 매번 client가 접속시마다 체크된다.
crl-verify crl.pem
openvpn의 revoke-full시 에러가 날때
아래처럼 openvpn의 인증서를 revoke할때 에러가 날경우는...
# ./revoke-full nick2
Using configuration from /etc/openvpn/easy-rsa/openssl.cnf
error on line 282 of config file '/etc/openvpn/easy-rsa/openssl.cnf'
21626:error:0E065068:configuration file routines:STR_COPY:variable has no value:conf_def.c:629:line 282
Using configuration from /etc/openvpn/easy-rsa/openssl.cnf
error on line 282 of config file '/etc/openvpn/easy-rsa/openssl.cnf'
21627:error:0E065068:configuration file routines:STR_COPY:variable has no value:conf_def.c:629:line 282
nick2.crt: OK
openssl.cnf의 pkcs11_section을 아래와 같이 주석처리 하면된다.
#[ pkcs11_section ]
#engine_id = pkcs11
#dynamic_path = /usr/lib/engines/engine_pkcs11.so
#MODULE_PATH = $ENV::PKCS11_MODULE_PATH
#PIN = $ENV::PKCS11_PIN
#init = 0
-끝-
# ./revoke-full nick2
Using configuration from /etc/openvpn/easy-rsa/openssl.cnf
error on line 282 of config file '/etc/openvpn/easy-rsa/openssl.cnf'
21626:error:0E065068:configuration file routines:STR_COPY:variable has no value:conf_def.c:629:line 282
Using configuration from /etc/openvpn/easy-rsa/openssl.cnf
error on line 282 of config file '/etc/openvpn/easy-rsa/openssl.cnf'
21627:error:0E065068:configuration file routines:STR_COPY:variable has no value:conf_def.c:629:line 282
nick2.crt: OK
openssl.cnf의 pkcs11_section을 아래와 같이 주석처리 하면된다.
#[ pkcs11_section ]
#engine_id = pkcs11
#dynamic_path = /usr/lib/engines/engine_pkcs11.so
#MODULE_PATH = $ENV::PKCS11_MODULE_PATH
#PIN = $ENV::PKCS11_PIN
#init = 0
-끝-
2009년 1월 20일
backtrack3(slackware)에서의 휠마우스(scroll mouse) 사용
Backtrack관련 글을 오랫만에 게시하는군요..
Backtrack을 사용하다보면 불편한점이 몇가지 있지요..
그중하나가 휠마우스(scroll wheel)의 사용입니다.
/etc/X11/xorg.conf 파일을 아래와 같이 수정하고 X를 재시작하면 휠마우스를 사용하실수 있습니다.
# Identifier and driver
Identifier "Mouse1"
Driver "mouse"
Option "Protocol" "IMPS/2"
Option "Device" "/dev/mouse"
Option "ZAxisMapping" "4 5"
Option "Buttons" "5"
감사합니다.
Backtrack을 사용하다보면 불편한점이 몇가지 있지요..
그중하나가 휠마우스(scroll wheel)의 사용입니다.
/etc/X11/xorg.conf 파일을 아래와 같이 수정하고 X를 재시작하면 휠마우스를 사용하실수 있습니다.
# Identifier and driver
Identifier "Mouse1"
Driver "mouse"
Option "Protocol" "IMPS/2"
Option "Device" "/dev/mouse"
Option "ZAxisMapping" "4 5"
Option "Buttons" "5"
감사합니다.
2008년 11월 11일
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();
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();
2008년 11월 9일
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에서 발췌한 글을 수정하였습니다.
#!/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에서 발췌한 글을 수정하였습니다.
2008년 9월 1일
dd-wrt에서 startup스크립트 작성
dd-wrt펌웨어 위에 openvpn등 각종 서비스를 올리고 그 서비스를 시작시 실행시키려 할때는 다음과 같은 디렉토리에 startup스크립트를 만들고 위치시키면 된다..
1. 위치
- DD-WRT는 아래의 디렉토리에서 순차적으로 startup 스크립트를 찾는다.
/etc/config/
/jffs/etc/config/
/mmc/etc/config/
/tmp/etc/config/
2. 확장자별 시작시점
.startup - will be executed on system startup, normally boot-time and: _before_ the firewall is configured
.wanup - will be executed whenever an interface goes up (ex: ppp connection is reestablished after a disconnect, goes up first time) and: _after_ firewall stuff, so good ending for custom QoS and stuf.
.if - will be executed whenever an interface goes up (ex: ppp connection is reestablished after a disconnect, goes up first time) and: _after_ firewall stuff, so good ending for custom QoS and stuff - .wanup - should be a better place for disconnect/reconnect events than the ones above
.ipup - is run when ppp connection is reestablished after a disconnect, _after_ firewall stuff.
.ipdown - is run when a ppp connection has been shut down
.sesbutton - is executed when the SES/AOSS/EZ-SETUP button is pressed
1. 위치
- DD-WRT는 아래의 디렉토리에서 순차적으로 startup 스크립트를 찾는다.
/etc/config/
/jffs/etc/config/
/mmc/etc/config/
/tmp/etc/config/
2. 확장자별 시작시점
.startup - will be executed on system startup, normally boot-time and: _before_ the firewall is configured
.wanup - will be executed whenever an interface goes up (ex: ppp connection is reestablished after a disconnect, goes up first time) and: _after_ firewall stuff, so good ending for custom QoS and stuf.
.if - will be executed whenever an interface goes up (ex: ppp connection is reestablished after a disconnect, goes up first time) and: _after_ firewall stuff, so good ending for custom QoS and stuff - .wanup - should be a better place for disconnect/reconnect events than the ones above
.ipup - is run when ppp connection is reestablished after a disconnect, _after_ firewall stuff.
.ipdown - is run when a ppp connection has been shut down
.sesbutton - is executed when the SES/AOSS/EZ-SETUP button is pressed
피드 구독하기:
글 (Atom)