Excel single cell line breaks

SamCG has asked for the wisdom of the Perl Monks concerning the following question:

Okay, I have an issue I've worked on for a bit. Essentially I'm producing an excel spreadsheet using Spreadsheet::WriteExcel. Several columns include long text fields, and I need to break these into separate lines. I've split the text into lines using Text::Wrap. The formula bar shows separate lines, but the cell itself doesn't unless I go in and select it and then tab out of it. It appears all as one line, completely ignoring the newline.

Here's a minimal code sample (certain variable names have been changed to protect those not provably guilty):

   $Text::Wrap::columns = 35;
   my $q = wrap ('','',$qa{$isin}) if defined $qa{$_};
   my $i = wrap ('','',$ib{$isin}) if defined $ib{$isin};
   my $j = wrap ('','',$jk{$isin}) if defined $jk{$isin};
   my $d = wrap ('','',$da{$isin}) if defined $da{$isin};
                                   
   push @data, [$q, $i, $j, $d];      
   $row++;
   ## $dd is a "Delta_Days" value from Date::Calc
   $worksheet->set_row($row-1, undef, $new) if $dd <= 7;
   $worksheet->set_row($row-1, 60, $notnew) if $dd > 7 and $dd <=14;
   $worksheet->set_row($row-1, 60, $stale) if $dd > 14;
   $worksheet->write('A'.$row, \@data);
[download]

Does anyone know how to get these values to come out on separate lines, when first opening the spreadsheet? I'm just about ready to go and write this whole thing in html, then slap an xls extension on it -- except that file sizes seem to balloon ridiculously when that's done.

Comment on Excel single cell line breaks Download Code

Replies are listed 'Best First'.
Re: Excel single cell line breaks by jmcnamara (Monsignor) on Dec 14, 2005 at 21:16 UTC
You can wrap a string across multiple lines by setting the text_wrap property of the cell format and by using `\n` to delimit the lines. `my $format = $workbook->add_format(text_wrap => 1); $worksheet->write(0, 0, "It's\na bum\nwrap", $format);` [download] See the set_text_wrap() section of the documentation and the text_wrap.pl program in the examples directory of the distro -- John.	[reply] [d/l]
Re: Excel single cell line breaks by dragonchild (Archbishop) on Dec 14, 2005 at 22:14 UTC
Also, if you converted to use Excel::Template (which wraps Spreadsheet::WriteExcel), you could set the text_wrap attribute in the <format> tag and it will do the rest. My criteria for good software: Does it work? Can someone else come in, make a change, and be reasonably certain no bugs were introduced?	[reply]