|
Re: Alt + Enter
by wwe (Friar) on Mar 24, 2011 at 14:10 UTC
|
Just surround your value with quotation marks. Maybe you need to define quotation marks as "text quoting character" - not sure about right translation, have only german Excel here. Excel then imports this value into one cell. Look at this example:
spalte1 spalte2
wert11 wert12
wer21 "wer
t22"
this will result in a 2*3 excel sheet with B3 containing a string "wer<newline>t22".
tested with MS Excel 2007 | [reply]
[d/l] |
Re: Alt + Enter
by Ratazong (Monsignor) on Mar 24, 2011 at 13:39 UTC
|
What about accessing Excel directly?. I'm using WIN32::OLE, and the following code gives me a multi-line-entry in an Excel-cell:
$WPsheet->Range("B2")->{'Value'} = "This is\na test.";
Rata | [reply]
[d/l] |
|
|
Not using a module (not my decision). It's a tab-delimited file - i.e. open file handle and print tab-delimited strings to it. It "mainly" gets opened by Excel is all. If there was a way to print alt+enter equivalent or something for Excel it'd be great. The file can have some stuff for Excel in it (can be removed by text editor) but should be formatted in case of import in Excel.
| [reply] |
|
|
You cannot put newlines, or tabs, in a cell, in tab delimited files. It comes with the territory.
You could upgrade to a CSV file (possibly even using a tab as the column delimiter), which allows you to quote cell contents and thereby including newlines.
You could also try to just use a standalone return ("\r") in the tab delimited file. No garantees, it could work, but it's an extremely dangerous hack — you never know if somebody editing the file in a text editor could ruin it for you.
| [reply] |
Re: Alt + Enter
by anonymized user 468275 (Curate) on Mar 24, 2011 at 13:55 UTC
|
By enclosing the multiple lines (carriage return separated) in quotes, Open Office calc will load the multiple lines therein into a single cell (after asking you what delimiter(s) to use and you have to unselect comma and select tab). This should work also on Excel - But I couldn't test it on the Excel 2007 I have on my corporate laptop because that version of Excel appears to have changed so that it makes no effort to find a delimiter and just loads each line of input into a single column - hence the test on OOcalc instead.
| [reply] |
Re: Alt + Enter
by rpike (Scribe) on Mar 24, 2011 at 14:18 UTC
|
Quotes? Hmmmm I'll give it a try and let you know if it works. It'll take a few minutes before I can try but I'll repost my findings. Thanks. | [reply] |
Re: Alt + Enter
by rpike (Scribe) on Mar 24, 2011 at 15:58 UTC
|
Didn't seem to work.
Here's what I had in the text file :
Line 1"\n"Line 2"\n"Line 3......
also tried (and didn't work)
Line 1"\n"Line 2"\n"Line 3......
| [reply]
[d/l]
[select] |
|
|
Try using the test file wwe provided. I also tested it using the following and it works as expected:
foo_a1 foo_b1
foo_a2 fo_b2
foo_a3 "foo_b3
foo_b3_2"
foo_a4 foo_b4
Makes a 4x2 spreadsheet where cell b3 contains 2 lines. | [reply]
[d/l] |
Re: Alt + Enter
by ninja2k (Initiate) on Feb 08, 2013 at 06:23 UTC
|
All, got this sorted out today, looks like years late but ok.
Correct syntax to make this work is as follows:
print "Column 1,\"Column 2\nline 2\nline 3\",Column 3\n";
Print this out to a csv file, open the csv with excel, auto adjust the columns, then auto adjust the row height and you will see the results.
This works every time for Perl v5.10.1 and Excel 2007
| [reply]
[d/l] |
Re: Alt + Enter
by tmaly (Monk) on Mar 25, 2011 at 13:22 UTC
|
the problem I have found in trying to add Alt+Enter into my worksheets from perl is that when you open the worksheet, Excel does not always auto expand the character by default
| [reply] |