Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

Re: Need help getting format along with the value from excel using Workdheet::ParseExcel

by jmcnamara (Monsignor)
on Oct 27, 2011 at 11:42 UTC ( #934122=note: print w/ replies, xml ) Need Help??


in reply to Need help getting format along with the value from excel using Workdheet::ParseExcel

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.


Comment on Re: Need help getting format along with the value from excel using Workdheet::ParseExcel
Download Code
Re^2: Need help getting format along with the value from excel using Workdheet::ParseExcel
by jmcnamara (Monsignor) on Oct 27, 2011 at 12:35 UTC

    If anyone was inclined they could take this code and roll it into a Spreadsheet::ReWriteExcel module.

    The majority of the code is already there. It would just need a small amount of documentation and test cases. I think it would be genuinely useful.

    If anyone is interested let me know.

    --
    John.

      I am new to computer developing and perl is the first language I have tried, but I love it. I will try and make the module, as you suggested because I haven't made one yet for the CPAN. I wanna take this opportunity to thank you. I will implement this with my code and complete my task easily now. You are amazing.

      Thanks Again John!

      I am interested in making the module you have suggested, just letting you know!

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://934122]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (13)
As of 2014-07-11 12:45 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (224 votes), past polls