WIN32::OLE操作之excel
工作关系,近期有用perl来处理大量.c/.h里特殊字串,并统计到一张excel中。也着实体验了一把perl处理文本的高效,4-5days的工作量在5-6mins就给做好了。
因为是新手,以下的demo code难免一些抄袭的痕迹(heh),至此向那些share这块经验的高手致敬。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 use strict; 2 use Cwd; 3 4 #引入OLE模块 5 use Win32::OLE qw(in with); 6 use Win32::OLE::Const 'Microsoft Excel'; 7 8 9 # use existing instance if Excel is already running 10 #About how to use the Win32::OLE, please read the help document. 11 #the first part you can get is from perldoc win32::OLE 12 #another is the manual, a part of the manual of office ,you can find it in your system if you installed the Windows Family Office software 13 #the path location is C:/Program Files/Microsoft Office/OFFICE11/1033,the file name is VBAXL10.CHM 14 #In advance, I find there are many manuals about the Win32::OLE, such as VBAWD10.CHM is for "Word Object Model"; 15 #VBAPP10.chm is for "PowerPoint Object Model" 16 #VBAPB10.CHM is for "Publisher Object Model " 17 #VBAOL11.CHM is for "Outlook Object Model" 18 #XMLSDK5.CHM is for "MSXML 5.0 SDK" 19 #and so on. 20 21 #create an instance 22 my $ex; 23 $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;}) or die "Oops, cannot start Excel"; 24 25 # set property 26 $ex->{DisplayAlerts} = 'True';#打开excel提供,比如是否保存修改之类的 ???但是设置为False,貌似同样有提示,不过不影响大局,暂不关注~_~ 27 28 #add a workbook 29 my $book = $ex->Workbooks->Add; 30 31 #get the current absolute path,and set the filename 32 my $dir = getcwd; 33 my $file_name = "\\test1.xls"; 34 35 #save as,will be saved as test1.xls in current folder 36 $book->SaveAs($dir.$file_name); 37 38 # select the sheet want to modify 39 my $sheet = $book->Worksheets("Sheet1"); #sheet对象 40 41 #set the column width 42 $sheet->Columns("A")->{ColumnWidth} = 30; 43 $sheet->Columns("B")->{ColumnWidth} = 10; 44 $sheet->Columns("C")->{ColumnWidth} = 30; 45 #$sheet->Columns("A:C")->{ColumnWidth} = 10; #set A B C columnwidth to 10 46 47 #rename sheet 48 $sheet->{Name}= "Demo"; 49 #delete sheet2 50 $book->Worksheets("Sheet2")->Delete; 51 52 # init cells 53 my $rng = $sheet->Range('A1:C1'); 54 55 my $exTitle; 56 $exTitle->[0][0] = "Title A"; 57 $exTitle->[0][1] = "Title B"; 58 $exTitle->[0][2] = "Title C"; 59 60 # let the cell as txt format' 61 $rng->{'NumberFormat'}="/@"; 62 63 #init the cells in the range 64 $rng->{Value} = $exTitle; 65 66 #set autofilter property 67 $rng->{AutoFilter} = 1; 68 69 #start parse 70 print "start parse files...\n"; 71 72 73 my$i = 2; 74 while(1) 75 { 76 my$rangeA = "A".$i; 77 my$rangeB = "B".$i; 78 my$rangeC = "C".$i; 79 80 my$parseStr; 81 $parseStr->[0][0] = "Macro Name".$i; 82 $parseStr->[0][1] = "rate".$i; 83 $parseStr->[0][2] = "Filename".$i; 84 85 $sheet->Range($rangeA)->{Value} = $parseStr->[0][0]; 86 $sheet->Range($rangeB)->{Value} = $parseStr->[0][1]; 87 $sheet->Range($rangeC)->{Value} = $parseStr->[0][2]; 88 89 $rng = $sheet->Range($rangeA.':'.$rangeC); 90 91 # let the cell as txt format' 92 $rng->{'NumberFormat'}="/@"; 93 94 $rng->{Value} = $parseStr; 95 96 $i++; 97 if($i>30) 98 { 99 last; 100 } 101 } 102 103 104 $rng->{value} = "the last cell"; 105 106 107 #read data from cell 108 my $val = $sheet->Range('A1')->{value}; 109 print "Get data from excel cell A1:$val/n"; 110 111 112 113 # save and exit 114 print "finish"; 115 $book->Save(); 116 $ex->Quit; 117 undef $book; 118 undef $ex;