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"
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
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.
Seeking for Perl wisdom...on the process of learning...not there...yet!
| 