Excel读取日期和时间格式的问题

beyondlee1

使用Win32::OLE读取.xls文件

当单元格内容为：

2016-6-1 7:56

print $Sheet->Cells(1,1)->{'Value'};
内容为：

1. Win32::OLE::Variant=SCALAR(0x2e5a568)

复制代码

请问，怎么转换格式?

日期和时间分别怎么读取？

谢谢~！

beyondlee1

自问自答

1. #!/usr/bin/perl
   
2. use strict;
   
3. 
   
4. use Win32::OLE qw(in with);
   
5. use Win32::OLE::Const 'Microsoft Excel';
   
6. use Win32::OLE::Variant;
   
7. use Win32::OLE::NLS qw(:LOCALE :DATE);
   
8. 
   
9. $Win32::OLE::Warn = 3; # Die on Errors.
   
10. 
    
11. 
    
12. # get already active Excel application or open new
    
13. my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    
14.     || Win32::OLE->new('Excel.Application', 'Quit');  
    
15. $Excel->{DisplayAlerts}=0;
    
16. 
    
17. # open Excel file
    
18. use Cwd;
    
19. my $dir=getcwd;
    
20. $dir=$dir."/test_excel.xls";
    
21. 
    
22. #$dir = encode("euc-cn",$dir);
    
23. my $Book = $Excel->Workbooks->Open($dir);
    
24. 
    
25. my $Sheet = $Book->Worksheets(1);
    
26. 
    
27. #找到最后一行 最后一列方法  
    
28. my $LastRow = $Sheet->UsedRange->Find({What=>"*",  
    
29.     SearchDirection=>xlPrevious,  
    
30.     SearchOrder=>xlByRows})->{Row};  
    
31.   
    
32. my $LastCol = $Sheet->UsedRange->Find({What=>"*",  
    
33.                   SearchDirection=>xlPrevious,  
    
34.                   SearchOrder=>xlByColumns})->{Column};  
    
35. 
    
36. 
    
37. # ::Warn = 2; throws the errors, but #
    
38. # expects that the programmer deals  #
    
39. 
    
40. my $dt = Variant(VT_DATE, $Sheet->Range("a8")->{Value});
    
41. print "$dt\n";
    
42. print $dt->Date(DATE_LONGDATE), "\n";
    
43. 
    
44. $Sheet->Range("a2")->{Value} =   $dt->Date(DATE_LONGDATE);
    
45. 
    
46. print $dt->Date("ddd',' MMM dd yy"), "\n";
    
47. $Sheet->Range("a3")->{Value}= $dt->Date("ddd',' MMM dd yy");  
    
48. 
    
49. #Some Properties Take Variant ints or longs -
    
50. #in this case the Zoom property MUST be set for the
    
51. #FitToPages(Wide|Tall) to be acknowledged.
    
52. 
    
53. my $vtfalse =  Variant(VT_BOOL, 0);  
    
54. my $vtpages =  Variant(VT_I4, 1);  
    
55. with ($Sheet->PageSetup,
    
56.     'FitToPagesWide'=>$vtpages,
    
57.     'FitToPagesTall'=>$vtpages,
    
58.     'Zoom'=>$vtfalse,     
    
59.     'PrintGridlines'=>0,
    
60.     'LeftHeader'=> "Using Perl and Excel",
    
61.     'CenterHeader' => "My First Script",
    
62.     'RightHeader' => "Test",   
    
63.     'LeftFooter' => "Done",
    
64.     'CenterFooter' => $dt->Date("ddd',' MMM dd yy"),
    
65.     'Orientation' => xlLandscape,
    
66.     'RightFooter' => "",
    
67.     'PrintHeadings'=>0,
    
68.     'FirstPageNumber'=> xlAutomatic,
    
69.     'PrintTitleRows' => "1:1");  
    
70. 
    
71.    #$Book->Save();
    
72.    #$Book = $Excel->Workbooks->Close();

复制代码