Multi Line cell entries / Simulate alt-enter in a cell Options

[applecore]

I am wanting to simultate an "alt-enter" within a cell. The row of
data is coming from a file and NOT from within the perl script
itself. I am on a UNIX (sun) platform.

Here is how I am trying to create the row that I want wrapped:

From a shell script:
echo 'Project \n Name' > /tmp/input.txt

Executing the perl script:
perl excel_report_writer.pl /tmp/output.xls < /tmp/input.txt

Here is the format I am using:
my $fmtHeader = $workbook->add_format() ;
$fmtHeader->set_bg_color( 22 ) ;
$fmtHeader->set_color( 'black' ) ;
$fmtHeader->set_border( 2 ) ;
$fmtHeader->set_bold() ;
$fmtHeader->set_align( 'center' ) ;
$fmtHeader->set_align( 'bottom' ) ;
$fmtHeader->set_text_wrap() ;

The row of data appears in the spreadsheet as "Project" in the
formatted cell and "Name" goes to the next line in the left-most cell
(ie: it breaks out of the intended target cell). I am hoping somehow
to get the data wrapped in the same cell.

Is ths possible to have the data wrapped when the source of data is
coming from a file as oppossed to with the perl script itself ?

Thanks for any/all help.

[jmcnamara]

On Feb 12, 3:18 pm, applecore <ladybug91_soc...@yahoo.com> wrote:
> I am wanting to simultate an "alt-enter" within a cell. The row of
> data is coming from a file and NOT from within the perl script
> itself. I am on a UNIX (sun) platform.
>
> Here is how I am trying to create the row that I want wrapped:
>
> From a shell script:
> echo 'Project \n Name' > /tmp/input.txt

Hi,

I'm a little confused by the input that you describe and the output
you see. They seem to contradict each other a little. So the following
might not apply exactly but it should put you on the correct path.

The file /tmp/input.txt will contain 1 line with the string 'Project
\n Name'. When perl reads that string it will treat '\n' as 2
characters '\' and 'n' and not as the single character "\n" (the
quotes are used here in the Perl sense. See the perlop manpage for
further information http://perldoc.perl.org/perlop.html#Quote-Like-Operators
).

So the text_wrap format doesn't work since the string doesn't contain
a newline. In order to have the text wrap you need to convert '\n' to
"\n". Here is an example program based on your format:


#!/usr/bin/perl -w

use strict;
use Spreadsheet::WriteExcel;


my $workbook = Spreadsheet::WriteExcel->new('wrap.xls');
my $worksheet = $workbook->add_worksheet();

my $fmtHeader = $workbook->add_format() ;
$fmtHeader->set_bg_color( 22 ) ;
$fmtHeader->set_color( 'black' ) ;
$fmtHeader->set_border( 2 ) ;
$fmtHeader->set_bold() ;
$fmtHeader->set_align( 'center' ) ;
$fmtHeader->set_align( 'bottom' ) ;
$fmtHeader->set_text_wrap() ;

my $row = 0;

# Simulate reading from an external source.
while (my $line = <DATA>) {

chomp $line;

my @data = split ':', $line;
my $col = 0;

for my $data (@data) {

if ($row == 0) {
$data =~ s{\\n}{\n}g;
$worksheet->write($row, $col, $data, $fmtHeader);
}
else {
$worksheet->write($row, $col, $data);
}
$col++
}
$row++;
}

__DATA__
Project\nName:Status:Location
Foo:Active:Paris
Bar:Stalled:London


If this doesn't clarify the problem or a solution for you then modify
the above program to illustrate your case and post it here.

John.
--

[applecore]

John:

Thank you very much for helping me see the light ! I was taking for
granted that everyone (including my perl script - ha!) knew that the
'\n' sequence was one (1) character and not realizing it was
interpreted as 2. I did as you suggested and everything worked
perfect. I actually used the sequence of "\@" in my external data
file to indicate that a new-line needed to be inserted. And then
within the perl script I inspected and inserted as:

${col_data} =~ s{\\@}{\n}g ;

Worked perfect ! Thank you very much for your help. Another
satisfied customer.

On Feb 12, 5:45 pm, jmcnamara <jmcnam...@cpan.org> wrote:
> On Feb 12, 3:18 pm, applecore <ladybug91_soc...@yahoo.com> wrote:
>
> > I am wanting to simultate an "alt-enter" within a cell.  The row of
> > data is coming from a file and NOT from within the perl script
> > itself.  I am on a UNIX (sun) platform.
>
> > Here is how I am trying to create the row that I want wrapped:
>
> > From a shell script:
> > echo 'Project \n Name' > /tmp/input.txt
>
> Hi,
>
> I'm a little confused by the input that you describe and the output
> you see. They seem to contradict each other a little. So the following
> might not apply exactly but it should put you on the correct path.
>
> The file /tmp/input.txt will contain 1 line with the string 'Project
> \n Name'. When perl reads that string it will treat '\n' as 2
> characters '\' and 'n' and not as the single character "\n" (the
> quotes are used here in the Perl sense. See the perlop manpage for

> further informationhttp://perldoc.perl.org/perlop.html#Quote-Like-Operators