WGET

-recursive: download the entire Web site.

--domains website.org: don't follow links outside website.org.

--no-parent: don't follow links outside the directory tutorials/html/.

--page-requisites: get all the elements that compose the page (images, CSS and so on).

--html-extension: save files with the .html extension.

--convert-links: convert links so that they work locally, off-line.

--restrict-file-names=windows: modify filenames so that they will work in Windows as well.

--no-clobber: don't overwrite any existing files (used in case the download is interrupted and
resumed).

--limit-rate=200k: Limit download to 200 Kb /sec

--random-wait: Random waits between download - websites dont like their websites downloaded

-r: Recursive - downloads full website

-p: downloads everything even pictures (same as --page-requsites, downloads the images, css stuff and so on)

-E: gets the right extension of the file, without most html and other files have no extension

-e robots=off: act like we are not a robot - not like a crawler - websites dont like robots/crawlers unless they are google/or other famous search engine

-U mozilla: pretends to be just like a browser Mozilla is looking at a page instead of a crawler like wget

-o=/websitedl/wget1.txt: log everything to wget_log.txt

-b: runs it in background and cant see progress

-O
--restrict-file-names=windows:  modify filenames so that they will work in Windows as well. Seems to work good without it

Perl win32 ole Excel

PerlからExcelを扱う

• PerlからExcelを扱う
• Excelのカラーリング
• マクロの見かた

PerlからExcelを扱う

  use Win32::OLE を使うと Perl から Excel を使うことができます。 これは OLE という技巧で別に Excel だけでなく Wordなどでも使用することができます。

#!/usr/bin/perl
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

# エクセルオブジェクトを取得
my $excel = Win32::OLE->GetActiveObject('Excel.Application')
    || die "cannot get active excel!";

# ファイルを開く
#my $book = $excel->Workbooks->Open( $filename );

# ブックを追加する
my $book = $excel->Workbooks->add() ;

#シートを取得する
my $sheet = $book->ActiveSheet;

# モノを書き込む
$sheet->Range("B1")->{Value} = "OLE Sample" ; 

#データを取得する(配列)
my $data = $sheet->Range("B1")->{Value};
print "(" . $data . ")\r\n";

#データを取得する(配列)
my $array = $sheet->Range("A1:C1")->{Value};
print "(" . $$array[0][1] . ")\r\n"; # @@$arrayの２次元配列

#セルの色を指定する(単体)
$sheet->Range("A1")->Interior->{ColorIndex} = 6;

#セルの色を指定する(範囲)
$sheet->Range("A8:G8")->Interior->{ColorIndex} = 6;


#線を引く
#Const xlContinuous = 1;
$sheet->Range("C2")->Borders(xlDiagonalUp)->{LineStyle} = 1;
$sheet->Range("C3")->Borders(xlDiagonalDown)->{LineStyle} = 1;
$sheet->Range("C4")->Borders(xlEdgeLeft)->{LineStyle} = 1;
$sheet->Range("C5")->Borders(xlEdgeRight)->{LineStyle} = 1;
$sheet->Range("C6")->Borders(xlEdgeTop)->{LineStyle} = 1;
$sheet->Range("C7")->Borders(xlEdgeBottom)->{LineStyle} = 1;

#縦列の幅を変える
$sheet->Columns("A:A")->{ColumnWidth} = 4.00;

#折り返しを有効にする
$sheet->Range("A1")->{WrapText} = 1;

#ブックを閉じる
#$book->Close() ;

#エクセルを閉じる
#$excel->quit() ; 

#

  上記のスクリプトの結果は以下のような感じです。

Excelのカラーリング

  上記で出てくるColorIndex は以下のところによると、以下のサイトのような感じになります。

http://www.relief.jp/itnote/archives/000482.php 

  簡単にこっちでもつくってみました。

#!/usr/bin/perl
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

# エクセルオブジェクトを取得
$excel = Win32::OLE->GetActiveObject('Excel.Application')
    || die "cannot get active excel!";
# ブックを追加する
$book = $excel->Workbooks->add() ;

#シートを取得する
$sheet = $book->ActiveSheet;

@work = ("A","B","C","D","E","F","G","H","I","J");
$i = 1;
for ($k = 1 ; $k <= 7 ; $k++ ) {
    for ($j = 0 ; $j < 8 ; $j++) {
        $cell = $work[$j] . $k;
        $sheet->Range($cell)->{Value} = $i;
        $sheet->Range($cell)->Interior->{ColorIndex} = $i;
        $i++;
    }
}
#

マクロの見かた

  PerlでExcelのコマンドで細かい動作を どうやって知るかですが、簡単なのはExcelのマクロを作って それのVB編集スクリプトを眺めてみることです。
  Excelのメニューバーから、ツール→マクロ→ 新しいマクロの記録、と選択してマクロの記録を開始します。


  ここでいろいろ操作を行い、 マクロを停止させるとマクロが作成されます。


  そのマクロをExcelのメニューバーから ツール→マクロ→マクロ、と選択しVBマクロの編集を選択すれば、 なにをやれば良いかだいたいわかります。


起源|  ソース |Source : http://park.ruru.ne.jp/ando/work/perlTips/excel_ja.html

Perl Win32::OLE Excel color index

Auto=>0
Black=>1
Blue=>5
BrightPink=>35
ChalkBlue=>24
Color_001=>32
Color_002=>34
Color_003=>37
Color_005=>39
Color_006=>40
DarkBlue=>12
DarkCoral=>22
DarkGreen=>4
DarkRed=>9
DarkYellow=>11
ForestGreen=>10
Gold=>44
Green=>50
Grey_25=>15
Grey_50=>16
Lavender=>38
LightBlue=>20
LightOrange=>45
LightYellow=>36
Lime=>43
MotorolaBlue=>23
PaleBlue=>17
PaleYellow=>19
Pink=>7
Plum=>18
Purple=>21
Red=>3
Rose=>46
RoyalBlue=>25
SkyBlue=>33
Teal=>14
Turquiose=>8
Violet=>13
White=>2
Yellow=>6

Perl Excel ( Win32::OLE Excel 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;

source : http://www.tek-tips.com/faqs.cfm?fid=6715

VI VIM GVIM

new line
http://stackoverflow.com/questions/71323/how-to-replace-a-character-for-a-newline-in-vim