Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Using Win32::OLE and Excel - Tips and Tricks

by cacharbe (Curate)
on Mar 22, 2002 at 04:56 UTC ( #153486=perltutorial: print w/ replies, xml ) Need Help??

Help for this page

Select Code to Download


  1. or download this
    #!c:\perl\bin\
    use strict;
    ...
    # expects that the programmer deals  #
    
    my $excelfile = '<MYPATH>\perltut.xls';
    
  2. or download this
    my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
            || Win32::OLE->new('Excel.Application', 'Quit');
    
  3. or download this
    $Excel->{DisplayAlerts}=0;   
    
  4. or download this
     
    my $Book = $Excel->Workbooks->Add();
       $Book->SaveAs($excelfile); #Good habit when working with OLE, save 
    +often.
    
  5. or download this
                                                     
     my $Book = $Excel->Workbooks->Open($excelfile);     
    
  6. or download this
        my $Sheet = $Book->Worksheets("Sheet1");
           $Sheet->Activate();       
           $Sheet->{Name} = "DidItInPerl";
    
  7. or download this
      
       my ($mday,$mon,$year) = (localtime(time))[3,4,5];
           $year += 1900;
    ...
    
       $Sheet->Range("a1")->{Value} = $str;   
       $Sheet->Range("c1")->{Value} = "This is a long piece of text";
    
  8. or download this
        foreach my $y(1..56){
            my $range = 'b'.$y;
    ...
         }
    
    #$Excel->ActiveSheet->Pictures->Insert(<PATH TO THE PIC>);   
    
  9. or download this
        
    my $dt = Variant(VT_DATE, $Sheet->Range("a1")->{Value});
    ...
    
       #$Book->Save();
       #$Book = $Excel->Workbooks->Close();
    
  10. or download this
    my $sheetcnt = $Book->Worksheets->Count();
    foreach (1..$sheetcnt){
       print "\t" .$Book->Worksheets($_)->{Name} ."\n";
    } 
    
  11. or download this
    foreach my $Sheet(in $Book->{Worksheets}){
       print "\t" .$Sheet->{Name} ."\n";
    }
    
  12. or download this
    my $LastRow = $Sheet->UsedRange->Find({What=>"*",
        SearchDirection=>xlPrevious,
    ...
    my $LastCol = $Sheet->UsedRange->Find({What=>"*", 
                      SearchDirection=>xlPrevious,
                      SearchOrder=>xlByColumns})->{Column};
    
  13. or download this
          # Create the chart by dimension  #
          my $Chart = $Sheet->ChartObjects->Add(200, 200, 200, 200);
    
  14. or download this
          $Chart->Chart->ChartWizard({Source =>$Sheet->Cells(1)});
          $Chart->Chart->SeriesCollection(1)->{Values}= [19,3,24,56,34, 33
    +, 16, 10, 3, 100];
    
  15. or download this
    $Excel->Run($MacroName);
    
  16. or download this
    with ($Sheet->PageSetup,   Zoom           => Variant(VT_BOOL, 0),
                               FitToPagesTall => 1, FitToPagesWide => 1,
                               Orientation    => xlLandscape);
    
  17. or download this
    my $Sheet = $Book->Worksheets->Add({After=>$Book->Worksheets($Book->Wo
    +rksheets->{Count})}) or die Win32::OLE->LastError();
    
  18. or download this
    my $Sheet = $Book->Worksheets->Add({Before=>$Book->Worksheets(1)}) or 
    +die Win32::OLE->LastError();
    
  19. or download this
    my $Sheet = $Book->Worksheets->Add({After=>$Book->Worksheets("Sheet1")
    +}) or die Win32::OLE->LastError();
    

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perltutorial [id://153486]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (10)
As of 2015-07-29 22:42 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (269 votes), past polls