另外一篇操作excel的参考

1， 和上次那边基本差不多，只不过是个实用的工具，多了些文件操作，模式匹配等，备份供参考

2， 如何打开VBA帮助，office2007里面

新建或打开已有OFFICE文档

ALT+F11
此時出現VBA編程介面,
幫助菜單

打开EXCEL,工具栏中选宏,单击visual basic编辑器,出现了Microsoft visual basic,在其工具栏帮助中找Microsoft visual basic帮助.

#Auther: lancer
#Data: 2009.11.02
#Function: Get name of all muscic from GDD file and process it, then write to another excel file, to be used by code
#Version: 1.00

#Modified
#Version: 1.01 12/17/2009 -- Remove existed files and create a new excel file;

#!usr/bin/perl
use strict;
use warnings;
use Win32::OLE; #import win32 OLE package

my $dir = 'C://iphonegame//ParadiseQuest//iphone//trunk//doc//Production//'; #create two EXCEL files under this dir
my $src_name = $dir."Paradise Quest IPhone GDD"."/.xlsx"; #file to be read and processed
my $dst_name = $dir."test"."/.xlsx"; #file to be written
my $src_sheet_name = 'Sound and iTunes Access'; #sheet number in the source xls
my $dst_sheet_name = 'sheet1'; #sheet number in the destination xls
my $nowstr;
my $sound_name;
my $bgm_count = 0;
my $se_count = 0;
my $align_space = ' ';

print "By lancer/n";
#new a EXCEL application object, then we can operate to excel file
my $app_xls = Win32::OLE->new('Excel.Application', sub{$_[0]->Quit})
or die"Can't install Excel01!";

#open a EXCEL file, 'True' means read only
my $src_book = $app_xls->WorkBooks->Open($src_name, 0, 'True');
my $src_sheet = $src_book->Worksheets($src_sheet_name); #selete a worksheet

unlink $dst_name;
#while(<STDIN>){
#
#}
print("delete primal file/n");

#open(OUTFILE, ">$dst_name")||die "Open file [$dst_name] error!";
#close(OUTFILE); #close excel file handle
##while(<STDIN>){
##
##}
#print("open file/n");
#
#while(<STDIN>){
#
#}
my $dst_book = $app_xls->WorkBooks->Add();

print("create new file/n");
#$dst_book->SaveAs($dst_name);
my $dst_sheet = $dst_book->Worksheets(1);
$app_xls->{DisplayAlerts} = 'False'; #close the alert of excel, such as change or save

for (my $i = 1; $i < 200; $i++) {
$nowstr = $src_sheet->Cells($i,'D')->{Value}; #get data from a cell
if($nowstr){
if($nowstr eq 'bgm'){
$sound_name = $src_sheet->Cells($i,'C')->{Value}; #get data from a cell
process_bgm_name($sound_name);
}elsif($nowstr eq 'se'){
$sound_name = $src_sheet->Cells($i,'C')->{Value}; #get data from a cell
process_se_name($sound_name);
}
}
}

#$dst_book->Save; #save the change to destination xls
$dst_book->SaveAs($dst_name);
print("Finish process, file saved. Press Enter to quit!/n");
$app_xls->{DisplayAlerts} = 'True'; #restore the alert of excel

undef $src_book;
undef $dst_book;
undef $app_xls; #close the opend excel application

my $a;
$a = <>;


sub process_bgm_name{
++$bgm_count;
my $bgm_str = $_[0];

#process enum value
my $enum_str = uc($bgm_str);
$enum_str =~ s//)?.(WAV|M4A)//;
$enum_str =~ s// /(|[() -]/_/g;
if($bgm_count==1){ #first enum
$enum_str .= ' = 0';
}
$enum_str = $align_space . 'MUSIC_BGM_' . $enum_str . ',';


#process char array value
my $char_str = $bgm_str;
$char_str =~ s/.wav/.m4a/;
$char_str = $align_space . '"' . $char_str . '",';

print "$enum_str/n";
print "$char_str/n";
$dst_sheet->Cells($bgm_count,'A')->{Value} = $enum_str; #write a data to a cell
$dst_sheet->Cells($bgm_count,'B')->{Value} = $char_str;
}

sub process_se_name{
++$se_count;
my $se_str = $_[0];

#process enum value
my $enum_str = uc($se_str);
$enum_str =~ s//)?.WAV//;
$enum_str =~ s// /(|[() -]/_/g;
if($se_count==1){ #first enum
$enum_str .= ' = 0';
}
$enum_str =$align_space . 'SOUND_SE_' . $enum_str . ',';

#process char array value
my $char_str = $se_str;
$char_str = $align_space . '"' . $char_str . '",';
print "$enum_str/n";
print "$char_str/n";
$dst_sheet->Cells($se_count,'C')->{Value} = $enum_str; #write a data to a cell
$dst_sheet->Cells($se_count,'D')->{Value} = $char_str;
}