Hey Folks,
I am working on a script using spreadsheet::parseExcel to eventually build out an XML file. A fellow PM helped me with the spreadsheet::parseEcel script and I've created the script to work out the XML. I know that using template would be have been a better option, but this "works" and is WAY overdue per time. Is there a whay to "merge" these two scripts in to one -- if so how? It can't be as simple as appending them? Getting this to one file is the critical goal here. Thank you all for your help, I've been running in circles!
UPDATE: Note that
arg[1] is an excel spreadsheet (of course)
#!/usr/bin/perl -w
use warnings;
use strict;
use Spreadsheet::ParseExcel;
my $oExcel = new Spreadsheet::ParseExcel;
my $dir = $ARGV[1];
chomp $dir;
unless (-d $dir) {
print "Can't find directory $dir";
exit;
}
my $filename = "$dir/$ARGV[0]";
chomp $filename;
unless (-e $filename) {
print "Can't find file $filename";
exit ;
}
my $fullfilename = "$filename.txt";
## start work
print "Converting...";
#1.1 Normal Excel97
open E2T, ">", $fullfilename or die $!;
my $oBook = Spreadsheet::ParseExcel::Workbook->Parse($filename);
my($iR, $iC, $oWkS, $oWkC);
foreach my $oWkS (@{$oBook->{Worksheet}}) {
print "--------- SHEET:", $oWkS->{Name}, "\n";
print E2T $oWkS->{Name}, "|";
next unless defined $oWkS->{MinRow} and defined $oWkS->{MaxRow};
for my $iR ($oWkS->{MinRow} .. $oWkS->{MaxRow}) {
print E2T "\n";
for my $iC ($oWkS->{MinCol} .. $oWkS->{MaxCol}) {
$oWkC = $oWkS->{Cells}[$iR][$iC];
unless (!defined $oWkC){
print "( $iR , $iC ) =>", $oWkC->Value, "\n" if($oWkC);
+
print E2T $oWkC->Value;
}
print E2T "|";
}
}
print E2T "\n";
}
close E2T;
print "Finished!";
exit;
I also need to make sure that in my flatfile (pipe delimited) I replace empty || with |0| (that was explained
here with great feedback.)
here is the "second" script:
#!/usr/bin/perl
#this is the out from the previous script
$IN = "someFileName.txt";
$OUT = "someOutFile.xml";
open (OUT, ">$OUT");
open(IN) or die ("Cannot open file ($IN)");
print OUT <<TOP;
<?xml version="1.0" encoding="ISO-8859-1"?>
<xtags:some-node xml:lang="en">
TOP
$id = 0;
@columns = (category,id,code,title,summary,prereq,group,subgroup,seque
+nce,rolemandatory,rolerecommended,roleoptional,url,modality,"length")
+;
foreach $row (<IN>){
($category,$code,$title,$summary,$prereq,$group,$subgroup,$sequence,$r
+olemandatory,$rolerecommended,$roleoptional,$url,$modality,$length) =
+ split ('\|', $row);
print OUT "<star:course>\n";
foreach $_ (@columns){ &xmlrow($_); }
print OUT "</xtag:course>\n";
$id++;
}
print OUT "</star:learning-paths>\n";
sub xmlrow{
my $colname = shift(@_);
my $ostar="<xtag:";
my $estar="</xtag:";
print OUT "<xtag:$colname>${$colname}</xtag:$colname>\n";
}
