Great monks and others, hi
I'm trying to "merge" several single-worksheeded Excel files into one multi-worksheeded huge file.
I do use Spreadsheet::ParseXLSX, Spreadsheet::ParseExcel::Format and Excel::Writer::XLSX to acheive it and it do work corectly. For data only.
I can't do anything with the images!
Somebody can help?
Here is the wonderfull but not completed code:
# Pour une meilleure programmation
use strict;
use warnings;
$|++;
use Spreadsheet::ParseXLSX;
use Spreadsheet::ParseExcel::Format;
use Excel::Writer::XLSX;
my @files=("file1.xlsx","file2.xlsx","file3.xlsx");
my $fichier_sortie="out_merged.xlsx";
my $out_workbook = Excel::Writer::XLSX->new($fichier_sortie);
my $parser = Spreadsheet::ParseXLSX->new;
for my $f (0..$#files) {
print "=== ".$files[$f]." =======================\n";
my $in_workbook = $parser->parse($files[$f]);
if ( !defined $in_workbook ) { die $parser->error(), ".\n"; }
my $in_worksheet = $in_workbook->worksheet(0);
my $sheet = $out_workbook->add_worksheet($in_worksheet->get_name()
+);
my ( $row_min, $row_max ) = $in_worksheet->row_range();
my ( $col_min, $col_max ) = $in_worksheet->col_range();
for my $row ($row_min..$row_max) {
for my $col ($col_min..$col_max) {
my $cell = $in_worksheet->get_cell( $row, $col );
next unless $cell;
my $in_format = $cell->get_format();
my $out_format= $out_workbook->add_format();
CopyFormat($in_format, $out_format);
$sheet->write($row, $col, $cell->value(),$out_format);
}
}
if(defined($in_worksheet->get_merged_areas())) {
my $merged_areas = $in_worksheet->get_merged_areas();
my $cnt=0;
while (defined($merged_areas->[$cnt])) {
my $first_row=$merged_areas->[$cnt]->[0];
my $first_col=$merged_areas->[$cnt]->[1];
my $last_row=$merged_areas->[$cnt]->[2];
my $last_col=$merged_areas->[$cnt]->[3];
my $cell = $in_worksheet->get_cell( $first_row, $first_col
+ );
my $in_format = $cell->get_format();
my $out_format= $out_workbook->add_format();
CopyFormat($in_format, $out_format);
$sheet->merge_range( $first_row, $first_col, $last_row, $l
+ast_col, $cell->value(), $out_format );
$cnt++;
}
}
}
$out_workbook->close();
## SUBS ##############################################################
+#########
sub CopyFormat() {
use Switch;
my $in_format=shift;
my $out_format=shift;
# Font
my $font=$in_format->{Font};
$out_format->set_font($font->{Name});
$out_format->set_bold($font->{Bold});
$out_format->set_italic($font->{Italic});
$out_format->set_size($font->{Height});
$out_format->set_underline($font->{UnderlineStyle});
$out_format->set_color($font->{Color});
$out_format->set_font_strikeout($font->{Strikeout});
$out_format->set_font_script($font->{Super});
#Format
my $align;
switch( $in_format->{AlignH}) {
#case 0 { $align='No alignment';}
case 1 { $align='left';}
case 2 { $align='center';}
case 3 { $align='right';}
case 4 { $align='fill';}
case 5 { $align='justify';}
case 6 { $align='center_across';}
#case 7 { $align='Distributed/Equal spaced';}
else { $align='';}
}
$out_format->set_align($align);
switch( $in_format->{AlignV}) {
case 0 { $align='top';}
case 1 { $align='vcenter';}
case 2 { $align='bottom';}
case 3 { $align='vjustify';}
#case 4 { $align='Distributed/Equal spaced';}
else { $align='';}
}
$out_format->set_align($align);
$out_format->set_indent($in_format->{Indent});
$out_format->set_text_wrap($in_format->{Wrap});
$out_format->set_shrink($in_format->{Shrink});
my $rotation=$in_format->{Rotate};
if (!defined($rotation)) {
$rotation=0;
} elsif ($rotation==255) {
$rotation=270;
}
$out_format->set_rotation($rotation);
$out_format->set_text_justlast($in_format->{JustLast});
# $in_format->{ReadDir});
my $border=$in_format->{BdrStyle};
$out_format->set_bottom($border->[3]);
$out_format->set_top($border->[2]);
$out_format->set_left($border->[0]);
$out_format->set_right($border->[1]);
my $border_color=$in_format->{BdrColor};
if(defined($border_color->[3])) {$out_format->set_bottom_color($bo
+rder_color->[3]);}
if(defined($border_color->[2])) {$out_format->set_top_color($borde
+r_color->[2]);}
if(defined($border_color->[0])) {$out_format->set_left_color($bord
+er_color->[0]);}
if(defined($border_color->[1])) {$out_format->set_right_color($bor
+der_color->[1]);}
# (my$kind, my$style, my$color)=$in_format->{BdrDiag};
# $out_format->set_diag_type($kind);
# $out_format->set_diag_border($style);
# $out_format->set_diag_color($color);
my $fill=$in_format->{Fill};
$out_format->set_pattern($fill->[0]);
if ($fill->[0]!=0) {
$out_format->set_fg_color($fill->[2]);
$out_format->set_bg_color($fill->[1]);
}
# $in_format->{Lock});
# $in_format->{Hidden});
# $in_format->{Style});
}
The_Hawk_1 - an ignorant monk to be...