Hey, thanks! I got it working now. Works great. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Darrell Gammill Sent: Sunday, May 09, 2004 10:04 PM To: Gary Nielson; [EMAIL PROTECTED] Subject: RE: Help with Spreadsheet::WriteExcel
http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel-0.43/WriteExcel .pm#DATES_IN_EXCEL If you haven't read this portion of the perldoc on Spreadsheet::WrtiteExcel, you better. If you have read it, you will understand why I usually don't deal with dates in Excel. The gist of it is that dates are stored as the number of days since 31 December, 1899. I haven't seen your data. But, I assume you are using some sort of common date format for input. This will need to be converted to Excel's internal format before saving the data. http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel-0.43/WriteExcel /Utility.pm#TIME_AND_DATE_FUNCTIONS This module is available to provide utilities for doing the conversion. It requires you also have the Date::Manip and Data::Calc modules. Once all the modules are loaded, the beginning of your main loop looks something like this: while (<HISTFILE>) { chomp; # Split on single tab my @Fld = split('\t', $_); # We now handle the first column as a special case my $date = shift @Fld; $worksheet->write($row, 0, xl_parse_date($date)); my $col = 1; foreach (@Fld) { (and so on) -----Original Message----- From: Gary Nielson [mailto:[EMAIL PROTECTED] Sent: Sunday, May 09, 2004 10:14 AM To: Darrell Gammill; [EMAIL PROTECTED] Subject: RE: Help with Spreadsheet::WriteExcel Thanks for your help. I am making progress. The ASCII characters have disappeared thanks to removing trailing spaces. And the date field is no longer in General format. However, it is now in custom format. How do I set it in date format? I had to play with your code some to get it to work, but here's what I have now... Changes marked NEW... Any help greatly appreciated: # 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; # Add and define a format NEW: $format = $workbook->add_format(); # Add a format $format->set_num_format('mm/dd/yyyy'); $worksheet->set_column(0, 0, undef, $format); # to apply it to the spreadsheet. ENDNEW: while (<HISTFILE>) { chomp; # Split on single tab my @Fld = split('\t', $_); my $col = 0; NEW: foreach (@Fld) { s/\s+$//; # Strip white space from the end. $worksheet->write($row, $col, $_); $col++; ENDNEW: } $row++; } -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Darrell Gammill Sent: Saturday, May 08, 2004 8:20 PM To: Gary Nielson; [EMAIL PROTECTED] Subject: RE: Help with Spreadsheet::WriteExcel 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 _______________________________________________ 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
