This is something that should be relatively easy to do but isn't. Partially this is my fault because after I took over the maintenance of Spreadsheet::ParseExcel I didn't improve SaveParser in any way apart from fixing a few bugs.
Anyway, in recompense here is some working code that should help you and anyone else with a similar problem. The rewrite_workbook() code is based on SaveAs() in Spreadsheet::ParseExcel::SaveParser. It just has slightly saner variable names and formatting to make modification easier.
#!/usr/bin/perl
use strict;
use warnings;
use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel;
my $in_xls_file = 'test1.xls';
my $out_xls_file = 'test2.xls';
my $row_offset = 1;
# Parse the input xls file.
my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse( $in_xls_file );
if ( !defined $workbook ) {
die $parser->error(), ".\n";
}
# Convert the existing xls file to a new WriteExcel object.
my $output_workbook = rewrite_workbook( $workbook, $out_xls_file, $row
+_offset );
# Get the first WriteExcel worksheet.
my $output_worksheet1 = $output_workbook->sheets( 0 );
# Write some new text to it.
$output_worksheet1->write( 'A1', 'Some new text in the first row' );
# Do anything else you need to do.
######################################################################
+#########
#
# rewrite_workbook()
#
# Take a parsed Spreadsheet::ParseExcel workbook object and convert it
+ to a new
# Spreadsheet::WriteExcel workbook object. Returns the workbook object
+.
# Optionally shifts the rows down by $row_offset.
#
# Based on SaveAs() in Spreadsheet::ParseExcel::SaveParser.
#
sub rewrite_workbook {
my $parsed_workbook = shift;
my $out_xls_file = shift;
my $row_offset = shift || 0;
# Create a new Excel workbook
my $output_workbook = Spreadsheet::WriteExcel->new( $out_xls_file
+);
$output_workbook->compatibility_mode();
my %parsed_formats;
my $format_number = 0;
my @horiz_alignments = (
'left', 'left', 'center', 'right',
'fill', 'justify', 'merge', 'equal_space'
);
my @vert_alignments =
( 'top', 'vcenter', 'bottom', 'vjustify', 'vequal_space' );
foreach my $parsed_format ( @{ $parsed_workbook->{Format} } ) {
my $output_format = $output_workbook->addformat();
if ( !$parsed_format->{Style} ) {
$parsed_formats{$format_number} = $output_format;
my $parsed_font = $parsed_format->{Font};
$output_format->set_font( $parsed_font->{Name} );
$output_format->set_size( $parsed_font->{Height} );
$output_format->set_color( $parsed_font->{Color} );
$output_format->set_bold( $parsed_font->{Bold} );
$output_format->set_italic( $parsed_font->{Italic} );
$output_format->set_underline( $parsed_font->{Underline} )
+;
$output_format->set_font_strikeout( $parsed_font->{Strikeo
+ut} );
$output_format->set_font_script( $parsed_font->{Super} );
$output_format->set_hidden( $parsed_font->{Hidden} );
$output_format->set_locked( $parsed_format->{Lock} );
$output_format->set_align(
$horiz_alignments[ $parsed_format->{AlignH} ] );
$output_format->set_align(
$vert_alignments[ $parsed_format->{AlignV} ] );
$output_format->set_rotation( $parsed_format->{Rotate} );
$output_format->set_num_format( $parsed_workbook->{FmtClas
+s}
->FmtStringDef( $parsed_format->{FmtIdx}, $parsed_wo
+rkbook )
);
$output_format->set_text_wrap( $parsed_format->{Wrap} );
$output_format->set_pattern( $parsed_format->{Fill}->[0] )
+;
$output_format->set_fg_color( $parsed_format->{Fill}->[1]
+)
if ( ( $parsed_format->{Fill}->[1] >= 8 )
&& ( $parsed_format->{Fill}->[1] <= 63 ) );
$output_format->set_bg_color( $parsed_format->{Fill}->[2]
+)
if ( ( $parsed_format->{Fill}->[2] >= 8 )
&& ( $parsed_format->{Fill}->[2] <= 63 ) );
$output_format->set_left(
( $parsed_format->{BdrStyle}->[0] > 7 )
? 3
: $parsed_format->{BdrStyle}->[0]
);
$output_format->set_right(
( $parsed_format->{BdrStyle}->[1] > 7 )
? 3
: $parsed_format->{BdrStyle}->[1]
);
$output_format->set_top(
( $parsed_format->{BdrStyle}->[2] > 7 )
? 3
: $parsed_format->{BdrStyle}->[2]
);
$output_format->set_bottom(
( $parsed_format->{BdrStyle}->[3] > 7 )
? 3
: $parsed_format->{BdrStyle}->[3]
);
$output_format->set_left_color( $parsed_format->{BdrColor}
+->[0] )
if ( ( $parsed_format->{BdrColor}->[0] >= 8 )
&& ( $parsed_format->{BdrColor}->[0] <= 63 ) );
$output_format->set_right_color( $parsed_format->{BdrColor
+}->[1] )
if ( ( $parsed_format->{BdrColor}->[1] >= 8 )
&& ( $parsed_format->{BdrColor}->[1] <= 63 ) );
$output_format->set_top_color( $parsed_format->{BdrColor}-
+>[2] )
if ( ( $parsed_format->{BdrColor}->[2] >= 8 )
&& ( $parsed_format->{BdrColor}->[2] <= 63 ) );
$output_format->set_bottom_color( $parsed_format->{BdrColo
+r}->[3] )
if ( ( $parsed_format->{BdrColor}->[3] >= 8 )
&& ( $parsed_format->{BdrColor}->[3] <= 63 ) );
}
$format_number++;
}
for (
my $sheet_index = 0 ;
$sheet_index < $parsed_workbook->{SheetCount} ;
$sheet_index++
)
{
my $parsed_worksheet = $parsed_workbook->{Worksheet}[$sheet_in
+dex];
my $output_worksheet =
$output_workbook->addworksheet( $parsed_worksheet->{Name} );
if ( !$parsed_worksheet->{Landscape} ) {
$output_worksheet->set_landscape();
}
else {
$output_worksheet->set_portrait();
}
if ( defined $parsed_worksheet->{Protect} ) {
if ( $parsed_worksheet->{Protect} ne '' ) {
$output_worksheet->protect( $parsed_worksheet->{Protec
+t} );
}
else {
$output_worksheet->protect();
}
}
if ( ( $parsed_worksheet->{FitWidth} == 1 )
and ( $parsed_worksheet->{FitHeight} == 1 ) )
{
# Pages on fit with width and Heigt
$output_worksheet->fit_to_pages( $parsed_worksheet->{FitWi
+dth},
$parsed_worksheet->{FitHeight} );
#Print Scale
$output_worksheet->set_print_scale( $parsed_worksheet->{Sc
+ale} );
}
else {
#Print Scale
$output_worksheet->set_print_scale( $parsed_worksheet->{Sc
+ale} );
# Pages on fit with width and Heigt
$output_worksheet->fit_to_pages( $parsed_worksheet->{FitWi
+dth},
$parsed_worksheet->{FitHeight} );
}
# Paper Size
$output_worksheet->set_paper( $parsed_worksheet->{PaperSize} )
+;
# Margin
$output_worksheet->set_margin_left( $parsed_worksheet->{LeftMa
+rgin} );
$output_worksheet->set_margin_right( $parsed_worksheet->{Right
+Margin} );
$output_worksheet->set_margin_top( $parsed_worksheet->{TopMarg
+in} );
$output_worksheet->set_margin_bottom(
$parsed_worksheet->{BottomMargin} );
# HCenter
$output_worksheet->center_horizontally()
if ( $parsed_worksheet->{HCenter} );
# VCenter
$output_worksheet->center_vertically()
if ( $parsed_worksheet->{VCenter} );
# Header, Footer
$output_worksheet->set_header( $parsed_worksheet->{Header},
$parsed_worksheet->{HeaderMargin} );
$output_worksheet->set_footer( $parsed_worksheet->{Footer},
$parsed_worksheet->{FooterMargin} );
# Print Area
if ( ref( $parsed_workbook->{PrintArea}[$sheet_index] ) eq 'AR
+RAY' ) {
my $parsed_print_area;
for $parsed_print_area (
@{ $parsed_workbook->{PrintArea}[$sheet_index] } )
{
$output_worksheet->print_area( @$parsed_print_area );
}
}
# Print Title
for my $parsed_row_print_title (
@{ $parsed_workbook->{PrintTitle}[$sheet_index]->{Row} } )
{
$output_worksheet->repeat_rows( @$parsed_row_print_title )
+;
}
for my $parsed_col_print_title (
@{ $parsed_workbook->{PrintTitle}[$sheet_index]->{Column}
+} )
{
$output_worksheet->repeat_columns( @$parsed_col_print_titl
+e );
}
# Print Gridlines
if ( $parsed_worksheet->{PrintGrid} == 1 ) {
$output_worksheet->hide_gridlines( 0 );
}
else {
$output_worksheet->hide_gridlines( 1 );
}
# Print Headings
if ( $parsed_worksheet->{PrintHeaders} ) {
$output_worksheet->print_row_col_headers();
}
# Horizontal Page Breaks
$output_worksheet->set_h_pagebreaks(
@{ $parsed_worksheet->{HPageBreak} } );
# Veritical Page Breaks
$output_worksheet->set_v_pagebreaks(
@{ $parsed_worksheet->{VPageBreak} } );
for (
my $col_num = $parsed_worksheet->{MinCol} ;
defined $parsed_worksheet->{MaxCol}
&& $col_num <= $parsed_worksheet->{MaxCol} ;
$col_num++
)
{
if ( defined $parsed_worksheet->{ColWidth}[$col_num] ) {
if ( $parsed_worksheet->{ColWidth}[$col_num] > 0 ) {
$output_worksheet->set_column( $col_num, $col_num,
$parsed_worksheet->{ColWidth}[$col_num] );
}
else {
$output_worksheet->set_column( $col_num, $col_num,
+ 0, undef,
1 );
}
}
}
for (
my $rowl_num = $parsed_worksheet->{MinRow} ;
defined $parsed_worksheet->{MaxRow}
&& $rowl_num <= $parsed_worksheet->{MaxRow} ;
$rowl_num++
)
{
$output_worksheet->set_row( $rowl_num + $row_offset,
$parsed_worksheet->{RowHeight}[$rowl_num] );
for (
my $col_num = $parsed_worksheet->{MinCol} ;
defined $parsed_worksheet->{MaxCol}
&& $col_num <= $parsed_worksheet->{MaxCol} ;
$col_num++
)
{
my $parsed_cell =
$parsed_worksheet->{Cells}[$rowl_num][$col_num];
if ( $parsed_cell ) {
if ( $parsed_cell->{Merged} ) {
# There is probably a bug here.
# See the GitHub repo if required.
my $output_format = $output_workbook->addforma
+t();
$output_format->copy(
$parsed_formats{ $parsed_cell->{FormatNo}
+} );
$output_format->set_merge( 1 );
$output_worksheet->write(
$rowl_num + $row_offset,
$col_num,
$parsed_workbook->{FmtClass}->TextFmt(
$parsed_cell->{Val}, $parsed_cell->{Co
+de}
),
$output_format
);
}
else {
$output_worksheet->write(
$rowl_num + $row_offset,
$col_num,
$parsed_workbook->{FmtClass}->TextFmt(
$parsed_cell->{Val}, $parsed_cell->{Co
+de}
),
$parsed_formats{ $parsed_cell->{FormatNo}
+}
);
}
}
}
}
}
return $output_workbook;
}
--
John.