Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

Re^5: (code) text2xls.pl (was csv2xls.pl)

by adowen (Initiate)
on Nov 09, 2011 at 16:13 UTC ( #937108=note: print w/ replies, xml ) Need Help??


in reply to Re^4: (code) text2xls.pl (was csv2xls.pl)
in thread (code) text2xls.pl (was csv2xls.pl)

I realize this code has been stagnate for a while, but added a few options (zerolead & forcetext) & wanted to share....

#!/usr/bin/perl -w # text2xls.pl # pod at tail use strict; use Spreadsheet::WriteExcel; use Getopt::Long; # Crank it up print("\nStarting $0\n"); # Get command line switches and arguments my %option = ('defaultdelim' => ',', 'defaultencoding' => 'cp1252', ); GetOptions( 'infile=s' => \$option{csvin}, 'outfile=s' => \$option{xlsout}, 'delim=s' => \$option{delim}, 'header!' => \$option{header}, 'encoding=s' => \$option{csvenc}, 'zerolead!' => \$option{zerolead}, 'forcetext=s' => \$option{forcetext}, ); unless (defined ($option{csvin} && $option{xlsout})) { &USAGE(); exit; } unless (defined $option{delim}) { $option{delim} = $option{defaultdelim}; } unless (defined $option{header}) { $option{header} = 0; } unless (defined $option{csvenc}) { $option{csvenc} = $option{defaultencoding}; } unless (defined $option{zerolead}) { $option{zerolead} = 0; } # Do the dirty work open CSVFILE, "<:encoding(".$option{csvenc}.")", $option{csvin} or die + "Error opening $option{csvin} +: $!"; my $workbook = Spreadsheet::WriteExcel -> new($option{xlsout}); my $worksheet = $workbook -> addworksheet(); if($option{zerolead} == 1) { $worksheet -> keep_leading_zeros(); } my @textcols = split ',', $option{forcetext}; # Load the columns into the hash for search later... my %textcols_h; foreach (@textcols) { $textcols_h{$_} = 1; }; my $row = 0; while (<CSVFILE>) { chomp; my @field = split("$option{delim}", $_); my $column = 0; foreach my $token (@field) { if (exists $textcols_h{ $column+1 }) { $worksheet -> write_string($row, $column, $token); } else { $worksheet -> write($row, $column, $token); } $column++; } $row++; } # Prettify row as header if($option{header} == 1) { my $header = $workbook -> addformat(); $header -> set_bold(); $header -> set_align('center'); $header -> set_bg_color('tan'); $header -> set_border(); $worksheet -> set_row(0, undef, $header); } # Optional, unless doing some external action against xls file $workbook -> close() or die "Error closing $workbook: $!"; # Go tell it on the mountain print(" infile = $option{csvin}\n"); print(" outfile = $option{xlsout}\n"); print("Finished $0\n\n"); ######################################################## sub USAGE { print <<EOF Required switches: -i or --infile csv_infilename -o or --output xls_outfilename Optional switches: -d or --delim alt_delimiter default delimiter is ',' -e or --encoding encoding_format default encoding is 'cp1252' -h or --header Accepts no arguments. Bolds and background colors contents of topmost row. -z or --zerolead Accepts no arguments. Leading zeros in values are preserved. -f or --forcetext Accepts comma separated list of columns ie: '1,3,7'. Each column listed will be forced to be text. Example: text2xls.pl -i file1.csv -o file2.xls -d * -h -z EOF ; } ######################################################## =head1 Name text2xls.pl =head1 Description Convert text file in csv format to Excel binary format =head1 Usage Required switches: -i or --infile csv_infilename -o or --output xls_outfilename Optional switches: -d or --delim alt_delimiter default delimiter is ',' -e or --encoding encoding_format default delimiter is 'cp1252' -h or --header Accepts no arguments. Bolds and background colors contents of topmost row. -z or --zerolead Accepts no arguments. Leading zeros in values are preserved. -f or --forcetext Accepts comma separated list of columns ie: '1,3,7'. Each column listed will be forced to be text. Example: text2xls.pl -i file1.csv -o file2.xls -d * -h -z -f '8' =head1 Tested Spreadsheet::WriteExcel 0.31 Getopt::Long 2.19 Perl 5.00503 Debian 2.2r2 "Espy" Excel 9.0.3821 SR-1 97 SR-2 Win32 5.0.2195 SP-1 4.0 SP-6a =head1 Updated 2011-11-09 10:10 Added support for --zerolead & --forcetext Corrected the default encoding Corrected the Usage text 2010-05-15 18:56 Added support of encoding (FVC) 2001-04-17 10:45 Renamed as text2xls - avoid conflict with existin +g csv2xls. Tweaked todos re feedback from jmcnamara (S::WE a +uthor). 2001-04-16 Initial working code & posted to PerlMonks =head1 Todos Use Text::xSV by tilly or Text::CSV_XS for parsing text infile Review and fix "prettify row header" which somewhat works, but not qu +ite right. Support for "freeze pane" anticipated in future S::WE Test with different delimiters. Use Pod::Usage to instead of &USAGE(). But appears to only be in Perl + 5.6+ 8^( Use @headerparms and foreach loop in headers section to reduce duplic +ation. Accept multiple infiles. Save as "file.xls" (provided infile is "file") with -r switch. Add support for "page delimiter" character for multiple worksheets. Add support for $VERSION. =head1 Author ybiC =head1 Credits Thanks to jmcnamara, Petruchio and zdog for suggestions, and to jmcnamara for excellent pod in Spreadsheet::WriteExcel, and vroom, of course for PM. =cut


Comment on Re^5: (code) text2xls.pl (was csv2xls.pl)
Download Code

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (12)
As of 2015-07-30 17:07 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 (273 votes), past polls