m8ta
You are not authenticated, login. |
|
{641} | ||
A friend has many Excel files that he converts to labels for affixing to packages to be shipped. To print the proper number of labels (rather than one label with Qty=20), he needs to duplicate rows in the source excel file based on the Qty column. If you export the excel file to XML, this script should do the trick (you'll have to import the resultant XML): #!/usr/bin/perl $narg = $#ARGV + 1; if( $narg ne 2 ){ print "please specify the file to read followed by the file to write on the command line\n"; }else{ $source = $ARGV[0]; $dest = $ARGV[1]; local( $/) ; $/ = ""; open(FH, "< $source"); open(FHO, "> $dest"); $j = <FH>; #slurp the entire file into one string. # look for the header - if( $j =~ s/(.*?)<Sheet1>/<Sheet1>/s){ print FHO $1 ; } while ($j =~ /(<Sheet1>.*?<\/Sheet1>)/gs ){ $newl = $1; if( $newl =~ /<Qty>(\d+)<\/Qty>/ ){ $qty = $1; $newl =~ s/<Qty>\d+<\/Qty>/<Qty>1<\/Qty>/ ; for( $g=0; $g<$qty; $g++){ print FHO $newl ; } } } print FHO "</dataroot>" ; # assume that the footer is always this close FH; close FHO; }not very complicated, but worth posting, I guess. More examples on the internet = better ;-) Note that I hard-coded to split on <Sheet1> -- check your XML files!! |