Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
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 taking refuge in the Monastery: (8)
As of 2015-07-03 09:05 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (51 votes), past polls