Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"

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
    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
  4. or download this
    my $Book = $Excel->Workbooks->Add();
       $Book->SaveAs($excelfile); #Good habit when working with OLE, save 
  5. or download this
     my $Book = $Excel->Workbooks->Open($excelfile);     
  6. or download this
        my $Sheet = $Book->Worksheets("Sheet1");
           $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 = $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=>"*",
    my $LastCol = $Sheet->UsedRange->Find({What=>"*", 
  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
  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?

What's my password?
Create A New User
Node Status?
node history
Node Type: perltutorial [id://153486]
[Discipulus]: sometimes the client dies for extra content </chatter><span class="error" style="color: #cc0000">Server Error (Error ID 9162
[Discipulus]: the span is considered extra content by the parser
[Corion]: Mhmm - that would be some internal error, but I can't look into that right now as I don't have access to the logs from here ;)
[Discipulus]: ah, but it can be skipped by the client (?)
[Corion]: I think so, but I wonder what error might be there...
[Discipulus]: raining in germany? ;=)

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (13)
As of 2017-07-25 08:11 GMT
Find Nodes?
    Voting Booth?
    I came, I saw, I ...

    Results (366 votes). Check out past polls.