| Subject: | WriteExcel not processing text formats properly |
Using the above script with the following data set...
Show quoted text
---- Start Data Set ----
newbook
newsheet ARM-01
setdelim \t
saveas ARM-01.xls
formats @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @
writerow 01 000001 TRANSCONTINENTAL PRTG G.T. INC PO BOX 925 SAINT-LAURENT, QC CANADA H4L 4W3 5143351466 TRANSCONTI 05/13/1998 5143392210 Y SUSAN KNOTT
writerow 01 000002 FENTON PRESS 1544 WRIGHTWOOD CT. ADDISON, IL 60101 6305438114 0128 FENTON PRE 05/26/1998 0000000000 Y JULIE
--- End Data Set ----
You will notice that the first column comes up in excel as a '1' and not a '01' that it should because we defined it as a text field.
This is running under RedHat 7.2, perl 5.6.0. To test the information, I am using the following command:
cat dataset.txt | ./excel.pl
If you have any further questions, please send me an email.
- Andy
#!/usr/bin/perl
#
# Script to Create Excel Files
#
# Will duplicate SDOFFICE function calls for functionality.
#
#
use Spreadsheet::WriteExcel::Big;
$debug=0;
my $workbook;
my $format;
while (<>) {
/^newbook/ && do {
if ($workbook) { # If a new book then close previous if necess
print "Closed Workbook\n" if $debug;
$workbook->close();
}
print "Opening New Spreadsheet\n" if $debug;
$workbook = Spreadsheet::WriteExcel::Big->new('/tmp/temp.xls');
$format = $workbook->addformat();
$format->set_num_format('@');
$row=0;
next;
};
/^newsheet (.*)/ && do {
print "WorkSheet: $1\n" if $debug;
$worksheet = $workbook->addworksheet($1);
next;
};
/^setdelim (.*)/ && do {
print "Delim character: $1\n" if $debug;
next;
};
/^saveas (.*)/ && do {
print "Save as file: $1\n" if $debug;
$fname = $1;
next;
};
/^closebook/ && do {
print "Closing workbook\n" if $debug;
$workbook->close;
system "mv /tmp/temp.xls /clients/thermalcare/develop/aon/scripts/$fname";
$workbook='';
next;
};
/^formats (.*)/ && do {
@output = split /\t/, $1;
$col=0;
foreach (@output) {
system "echo formats $col >> /tmp/fmt.log";
$worksheet->set_column($col,$col,undef,$format);
$col=$col+1;
};
next;
};
/^writerow (.*)/ && do {
@output = split /\t/, $1;
$output_ref = \@output;
print "writerow $1\n" if $debug;
system "echo $output[0] $output[1] >>/tmp/out.log";
$worksheet->write($row, 0, $output_ref, $format);
$row=$row+1;
next;
};
print "Skipping ref: $_" if $debug;
}
if ($workbook) {
print "Close workbook\n" if $debug;
$workbook->close;
system "mv /tmp/temp.xls /clients/thermalcare/develop/aon/scripts/$fname";
}