Have you considered Spreadsheet::ParseExcel?

Thank you very much.

Hello stavelot,

I am not expert of Excel files, in fact this is my second attempt to play around with them, but it seems that the desired output of your task can be done with a few simple steps.

I am using Unix OS so I am not able to use your modules Win32::OLE qw(in with) etc. but my solution is generic so it also work on Windows OS.

Explanation, I am using the Excel::Writer::XLSX::Shape as a module to create the shape insert the data and then extract them. Take a look it has many interesting functions on how to modify the shape, I think you will find it interesting.

You do not need to use the module to create any shapes since you already have the shape on your excel sheet, but it was necessary for me to replicate your working environment.

I simply use Data::Dumper a very useful module that can print arrays, hashes, ref hashes etc.

So I just dump the output to see the location of the text and all the settings of the shape. Then as a second step I just print the hash ref with the desired information and Voila.

Sample of working code:

#!/usr/bin/perl
use strict;
use warnings;
use Data::Dumper;
use Excel::Writer::XLSX;

my $workbook  = Excel::Writer::XLSX->new( 'shape.xlsx' );
my $worksheet = $workbook->add_worksheet( 'Test-1' );

# Add an ellipse with centered text.
my $rect = $workbook->add_shape(
    type => 'rect',
    text => "Hello\nWorld"
    );

# Insert the shapes in the worksheet.
$worksheet->insert_shape( 'B3', $rect );

print Dumper($rect);

print $rect->{_text} . "\n"
[download]

Here is the output:

$VAR1 = bless( {
                 '_type' => 'rect',
                 '_x_offset' => 0,
                 '_x2' => 0,
                 '_scale_y' => 1,
                 '_line_type' => '',
                 '_align' => 'ctr',
                 '_flip_h' => 0,
                 '_row_end' => 0,
                 '_start_index' => undef,
                 '_text' => 'Hello
World',
                 '_rotation' => 0,
                 '_flip_v' => 0,
                 '_fill' => 0,
                 '_start' => undef,
                 '_y2' => 0,
                 '_txBox' => 0,
                 '_y1' => 0,
                 '_format' => {},
                 '_editAs' => '',
                 '_width' => 50,
                 '_palette' => [
                                 [
                                   0,
                                   0,
                                   0,
                                   0
                                 ],
                                 [
                                   255,
                                   255,
                                   255,
                                   0
                                 ],
                                 [
                                   255,
                                   0,
                                   0,
                                   0
                                 ],
                                 [
                                   0,
                                   255,
                                   0,
                                   0
                                 ],
                                 [
                                   0,
                                   0,
                                   255,
                                   0
                                 ],
                                 [
                                   255,
                                   255,
                                   0,
                                   0
                                 ],
                                 [
                                   255,
                                   0,
                                   255,
                                   0
                                 ],
                                 [
                                   0,
                                   255,
                                   255,
                                   0
                                 ],
                                 [
                                   128,
                                   0,
                                   0,
                                   0
                                 ],
                                 [
                                   0,
                                   128,
                                   0,
                                   0
                                 ],
                                 [
                                   0,
                                   0,
                                   128,
                                   0
                                 ],
                                 [
                                   128,
                                   128,
                                   0,
                                   0
                                 ],
                                 [
                                   128,
                                   0,
                                   128,
                                   0
                                 ],
                                 [
                                   0,
                                   128,
                                   128,
                                   0
                                 ],
                                 [
                                   192,
                                   192,
                                   192,
                                   0
                                 ],
                                 [
                                   128,
                                   128,
                                   128,
                                   0
                                 ],
                                 [
                                   153,
                                   153,
                                   255,
                                   0
                                 ],
                                 [
                                   153,
                                   51,
                                   102,
                                   0
                                 ],
                                 [
                                   255,
                                   255,
                                   204,
                                   0
                                 ],
                                 [
                                   204,
                                   255,
                                   255,
                                   0
                                 ],
                                 [
                                   102,
                                   0,
                                   102,
                                   0
                                 ],
                                 [
                                   255,
                                   128,
                                   128,
                                   0
                                 ],
                                 [
                                   0,
                                   102,
                                   204,
                                   0
                                 ],
                                 [
                                   204,
                                   204,
                                   255,
                                   0
                                 ],
                                 [
                                   0,
                                   0,
                                   128,
                                   0
                                 ],
                                 [
                                   255,
                                   0,
                                   255,
                                   0
                                 ],
                                 [
                                   255,
                                   255,
                                   0,
                                   0
                                 ],
                                 [
                                   0,
                                   255,
                                   255,
                                   0
                                 ],
                                 [
                                   128,
                                   0,
                                   128,
                                   0
                                 ],
                                 [
                                   128,
                                   0,
                                   0,
                                   0
                                 ],
                                 [
                                   0,
                                   128,
                                   128,
                                   0
                                 ],
                                 [
                                   0,
                                   0,
                                   255,
                                   0
                                 ],
                                 [
                                   0,
                                   204,
                                   255,
                                   0
                                 ],
                                 [
                                   204,
                                   255,
                                   255,
                                   0
                                 ],
                                 [
                                   204,
                                   255,
                                   204,
                                   0
                                 ],
                                 [
                                   255,
                                   255,
                                   153,
                                   0
                                 ],
                                 [
                                   153,
                                   204,
                                   255,
                                   0
                                 ],
                                 [
                                   255,
                                   153,
                                   204,
                                   0
                                 ],
                                 [
                                   204,
                                   153,
                                   255,
                                   0
                                 ],
                                 [
                                   255,
                                   204,
                                   153,
                                   0
                                 ],
                                 [
                                   51,
                                   102,
                                   255,
                                   0
                                 ],
                                 [
                                   51,
                                   204,
                                   204,
                                   0
                                 ],
                                 [
                                   153,
                                   204,
                                   0,
                                   0
                                 ],
                                 [
                                   255,
                                   204,
                                   0,
                                   0
                                 ],
                                 [
                                   255,
                                   153,
                                   0,
                                   0
                                 ],
                                 [
                                   255,
                                   102,
                                   0,
                                   0
                                 ],
                                 [
                                   102,
                                   102,
                                   153,
                                   0
                                 ],
                                 [
                                   150,
                                   150,
                                   150,
                                   0
                                 ],
                                 [
                                   0,
                                   51,
                                   102,
                                   0
                                 ],
                                 [
                                   51,
                                   153,
                                   102,
                                   0
                                 ],
                                 [
                                   0,
                                   51,
                                   0,
                                   0
                                 ],
                                 [
                                   51,
                                   51,
                                   0,
                                   0
                                 ],
                                 [
                                   153,
                                   51,
                                   0,
                                   0
                                 ],
                                 [
                                   153,
                                   51,
                                   102,
                                   0
                                 ],
                                 [
                                   51,
                                   51,
                                   153,
                                   0
                                 ],
                                 [
                                   51,
                                   51,
                                   51,
                                   0
                                 ]
                               ],
                 '_fh' => undef,
                 '_column_start' => 1,
                 '_end_index' => undef,
                 '_end' => undef,
                 '_start_side' => '',
                 '_valign' => 'ctr',
                 '_x1' => 0,
                 '_x_abs' => 0,
                 '_y_offset' => 0,
                 '_end_side' => '',
                 '_y_abs' => 0,
                 '_height' => 50,
                 '_connect' => 0,
                 '_name' => undef,
                 '_column_end' => 0,
                 '_scale_x' => 1,
                 '_stencil' => 1,
                 '_drawing' => 0,
                 '_line_weight' => 1,
                 '_element' => 0,
                 '_id' => 2,
                 '_adjustments' => [],
                 '_row_start' => 2,
                 '_line' => '000000'
               }, 'Excel::Writer::XLSX::Shape' );
Hello
World
[download]

As you can see with the hash we got (Hello World) it is printed in two lines because of the \n new line character.

Hope this solution is what you are looking for.

#!perl
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

$Win32::OLE::Warn = 3; # die on errors...
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
  || Win32::OLE->new('Excel.Application', 'Quit'); 

my $book = $Excel->Workbooks->Open('25.xls');
my $sheet = $book->Worksheets('Test1');

# print all 
for my $shape ( in $sheet->Shapes ){
  print $shape->Name.' = ';
  print $shape->TextFrame2->TextRange->Text."\n";
}

# print one
print $sheet->Shapes('TextBox 1')->TextFrame2->TextRange->Text."\n";
[download]