gongcw has asked for the wisdom of the Perl Monks concerning the following question:

Updated


My idea is to get each picture( or shape ) in excel firstly, then copy it to clipboard. The Win32::Clipboard can save picture object from clipboard.
I think the detailed code for all steps can show them clearly: 


#####################################################
###########      open the excel file for parsing   
#####################################################
my $srcExcelName = "test.xlsx";
my $excelObject=Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');
$excelObject->{DisplayAlerts} = 0;
my $xlsBook=$excelObject->WorkBooks->Open($srcExcelName);
my $xlsSheet = $xlsBook->Worksheets("Sheet1");
###########      open the excel file for parsing   


#####################################################
###########      open the word  file for saving
#####################################################
my $wordBasicName=$excelFileName;
$wordBasicName =~s/\.xlsx?$//;
my $saveWordName=$wordBasicName.".docx";
my $wordObject = CreateObject Win32::OLE 'Word.Application' or die $!;
$wordObject->{'Visible'} = 0;
my $document = $wordObject->Documents->Add;
my $selection = $wordObject->Selection;
###########      open the word  file for saving


#####################################################
###########      get the picture in sheets and save them in workdir   
#####################################################
my $iCount=0;
my $picFileType="BMP";
my @picFileArray;

foreach my $picObj ( in $xlsSheet->Pictures){
    my $savePicName = $workdir."aa".$iCount++.".".$picFileType;
    $picObj->Copy();
    my $clip=Win32::Clipboard();
    if( $clip->IsBitmap()){
       open CLIPPIC, ">$savePicName";
       binmode CLIPPIC;
       print CLIPPIC $clip->GetBitmap;
       close CLIPPIC;
    }
     push @picFileArray, $savePicName;
}

foreach ( @picFileArray ){
  $selection -> TypeParagraph;
  $selection -> InlineShapes->AddPicture({
    FileName => $_
  });
  $selection -> TypeParagraph;
}

[download]



The original problem:

Dear All,
I met some questions about the issuse in title recently.
I tried to parse excel by win32::ole module, but some pictures exist in one sheet. I need to get these picture objects and insert them into one word file.
My idea is save these pictures from excel file, and insert them into the word file. But problems appeared when I tried to save these picture from excel.
Actually, I'm not sure my idea is a correct method. Maybe some better ways can be employed for this issue. So could someone can give me some advices about how to use win32::ole to get the picture in excel, and insert them into word file?
And actually, I'm not familiar with the API of win32::ole very well. :(
Thanks very much for all friends.
  • Comment on How to get picture in MS excel and insert them into MS word file by the module win32::OLE?
  • Download Code

Replies are listed 'Best First'.
Re: How to get picture in MS excel and insert them into MS word file by the module win32::OLE?
by roboticus (Chancellor) on Jan 05, 2010 at 12:26 UTC

    gongcw:

    Go into excel and start recording a macro to save the picture. Then look at the generated macro (VB code) to see what commands to issue to the Win32::OLE module. I've not done it before, so I have no specific advice. But if you search around the site, you'll find plenty of various examples and discussions on the subject.

    ...roboticus

    Update: Fixed broken search link

[reply]
      Thanks very much for you reminder.
      I tried to search some resources about these issues. And I found something about the ChartObject, which can be exported by the followed method. 
      foreach my $ChartObj (in $xlsSheet->ChartObjects) { 
    $ChartObj->Chart->Export({
        FileName    => $savename,
        FilterName  => $picFileType,
        Interactive => 0});
     push @picFileArray, $savename;
}

      [download]

      I also found some modules like $sheet->Shapes, $sheet->Pictures, which are associate with parsing pictures in excel. But I cannot implement them on my issue.
      Could you kindly help to give me some advices about this?
      Thanks very much.
[reply]
[d/l]

        gongcw:

        First of all, you might want to brush up on your terminology so you can write your questions more clearly. (Example: Win32::OLE doesn't parse spreadsheets, $sheet->Shapes et. al. aren't modules, ...)

        Anyway, one way to handle this manually is to go to Excel, select the picture and copy it. Then switch over to Word, select a location for the picture, and then paste it. So I suggest reading Using Win32::OLE and Excel - Tips and Tricks and Automating Windows Apps with Win32::OLE. Then, open up your Excel document, and start the macro recorder, and copy the picture to the clipboard. Then view the macro and using your newly-won knowledge of Win32::OLE, you'll be able to get the picture to the clipboard. For the next step, start word, start the macro recorder, and paste a picture from the clipboard into your word document. Then use the clues in your macro to get the picture from the clipboard into your word document.

        If you have any trouble, post the code you're having trouble with, and we'll try to help you out a bit further.

        ...roboticus

[reply]

Back to Seekers of Perl Wisdom