另外一篇操作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; 
}