It looks like 'use workbook...' was a poor chose of words. You still
managed to get what I meant.
As for the error: You might try replacing your inner most loop with the
following:
foreach (@Fld) {
s/\s+$// # Strip white space from the end.
if ( $_ ) {
$worksheet->write($row, $col, $_);
}
$col++;
}
This should only write to the cell if there is a value to put in it.
Excel is probably looking a blank space differently the an un-used cell.
BTW: There is no functional difference in you using '$token' verses my
using '$_'. I just read somewhere that using '$_' was a little more
efficient.
-----Original Message-----
From: Gary Nielson [mailto:[EMAIL PROTECTED]
Sent: Saturday, May 08, 2004 6:05 PM
To: Darrell Gammill; [EMAIL PROTECTED]
Subject: RE: Help with Spreadsheet::WriteExcel
Thanks so much for the reply. Here's what I did to change my script,
after
reading your suggestion and the perldoc for set_column. Col 1 is the
date
column. Still not working... See "NEW":
my @lines =`ls -1 $tc2000`;
print @lines;
foreach $file (@lines) {
chomp($file);
print "$file...\n";
open (HISTFILE, "$tc2000$file") or die "$tc2000$file: $!";
$excel = $file;
$excel =~ s/\.txt/\.xls/;
# Create a new Excel workbook
my $workbook = Spreadsheet::WriteExcel->new("$tc2000$excel");
my $worksheet = $workbook->addworksheet();
# Row and column are zero indexed
my $row = 0;
NEW:
my $date = $workbook->addformat();
$date->set_num_format('m/dd/yyyy');
NOTE: The suggestion of use $workbook... Produced a perl syntax error so
I
commented out
and tried another approach, right above.
#use $workbook->addformat($date); # to setup the format. Then
$worksheet->set_column(0, 0, undef, $format); # to apply
it
to the spreadsheet.
END NEW:
while (<HISTFILE>) {
chomp;
# Split on single tab
my @Fld = split('\t', $_);
my $col = 0;
foreach my $token (@Fld) {
$worksheet->write($row, $col, $token);
$col++;
}
$row++;
}
}
close HISTFILE;
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Darrell
Gammill
Sent: Saturday, May 08, 2004 5:46 PM
To: Gary Nielson; [EMAIL PROTECTED]
Subject: RE: Help with Spreadsheet::WriteExcel
use $workbook->addformat() to setup the format. Then
$worksheet->set_column() to apply it to the spreadsheet.
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Gary
Nielson
Sent: Saturday, May 08, 2004 4:36 PM
To: 'Gary Nielson'; [EMAIL PROTECTED]
Subject: RE: Help with Spreadsheet::WriteExcel
Here's an update... I have been playing around with the spreadsheet
converted by Spreadsheet::WriteExcel from an ASCII tab-delimited file.
The
first column is a date, yet I notice when checking cell formatting that
it
is in general format. I think this is my problem, but I do not know how
to
dictate in my script that the first column of every row be in date
format as
in 3/14/2004. Any help appreciated.
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Gary
Nielson
Sent: Saturday, May 08, 2004 11:09 AM
To: [EMAIL PROTECTED]
Subject: Help with Spreadsheet::WriteExcel
I am using Spreadsheet::WriteExcel to get all files in a directory and
convert them from tab-delimited text to xls format. When I import them
manually, my subsequent formula works fine. However, when I use the same
formula on the converted file, I get a #DIV/0!. I notice that in the
last
cell on each line (not used in the formula) contains an "empty box"
ASCII
character, so I am wondering, do I have hidden ASCII characters in these
converted files that are interfering with the formula? Or is there
another
problem that is causing the problem? How do I get around this?
my @lines =`ls -1 $tc2000`;
print @lines;
foreach $file (@lines) {
chomp($file);
print "$file...\n";
# Open the tab-delimited file
open (HISTFILE, "$tc2000$file") or die "$tc2000$file: $!";
$excel = $file;
$excel =~ s/\.txt/\.xls/;
# Create a new Excel workbook
my $workbook = Spreadsheet::WriteExcel->new("$tc2000$excel");
my $worksheet = $workbook->addworksheet();
# Row and column are zero indexed
my $row = 0;
while (<HISTFILE>) {
chomp;
# Split on single tab
my @Fld = split('\t', $_);
my $col = 0;
foreach my $token (@Fld) {
$worksheet->write($row, $col, $token);
$col++;
}
$row++;
}
}
close HISTFILE;
_______________________________________________
ActivePerl mailing list
[EMAIL PROTECTED]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
_______________________________________________
ActivePerl mailing list
[EMAIL PROTECTED]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
_______________________________________________
ActivePerl mailing list
[EMAIL PROTECTED]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
_______________________________________________
ActivePerl mailing list
[EMAIL PROTECTED]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
