Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

Using Win32::OLE and Excel - Tips and Tricks

by cacharbe (Curate)
on Mar 22, 2002 at 04:56 UTC ( [id://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
Domain Nodelet?
Node Status?
node history
Node Type: perltutorial [id://153486]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others studying the Monastery: (5)
As of 2024-03-28 16:53 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found