Trying to write a cell with multiple lines in it

[Hadil Sabbagh]

I am using xlwt and python 2.7.1. I am trying to write a cell that has
mutliple lines in it, as if I entered the information in the cell with
Alt+Enter. I welcome any advice, suggestions, etc.

Thanks,
Hadil

[John Machin]

Tell us what you've tried. Suggestion: try starting the Excel/OOo Calc/Gnumeric UI and typing in something like line1AEline2AEline3 (where AE represents Alt+Enter), saving it as a csv file, and inspecting the result with Python ... print repr(open('alt_enter.csv', 'rb').read()) ... or with a hex editor. Or save it as an xls file, and use xlrd to inspect it: import xlrd; print repr(xlrd.open_workbook('alt_enter.xls').sheet_by_index(0).cell_value(0,0))

[Hadil Sabbagh]

I've tried that and isolated the character to \x0a. But I can't seem to embed it in the xls file correctly. It goes in but the lines are delimited with a strange character. It could be a Unicode issue possibly. I have tried \x0d and a combination of the two without luck. 

--
You received this message because you are subscribed to the Google Groups "python-excel" group.

To view this discussion on the web visit https://groups.google.com/d/msg/python-excel/-/PH6D18El-9kJ.

To post to this group, send an email to python...@googlegroups.com.
To unsubscribe from this group, send email to python-excel...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/python-excel?hl=en-GB.

[John Machin]

On Thursday, 9 June 2011 09:28:26 UTC+10, Hadil Sabbagh wrote:

I've tried that and isolated the character to \x0a. But I can't seem to embed it in the xls file correctly. It goes in but the lines are delimited with a strange character. It could be a Unicode issue possibly. I have tried \x0d and a combination of the two without luck. 

Save typing; use \n instead of \x0a

WHAT "strange character"? Do you see

line1S
line2S
line3

or

line1Sline2Sline3

where S = "strange character"?

What version of what spreadsheet UI are you using to view the results? Have you used xlrd to inspect exactly what is being written?

Here is minimal code that should demonstrate it working:

import xlwt
book = xlwt.Workbook()
sheet = book.add_sheet('x')
sheet.write(0, 0, u'line1\nline2\nline3')
book.save('alt_enter.xls')

Please show the minimal code that reproduces your "strange character" problem.

[Hadil Sabbagh]

I executed the instructions exactly as you wrote down and got

line1$line2$line3

Where $=strange character.

Note: I am executing on a Solaris machine and using sftp to tranfer the xls file in binary mode. I also tested it directly on my Windows XP laptop. I get the same result.

Thanks,

Hadil

--
You received this message because you are subscribed to the Google Groups "python-excel" group.

To view this discussion on the web visit https://groups.google.com/d/msg/python-excel/-/0colI_odHkcJ.

[David Avraamides]

You just need to enable the wrap on the style's alignment for the cell:

>>> import xlrd

>>> book = xlrd.open_workbook('test.xls')

>>> sheet = book.sheets()[0]

>>> import xlwt

>>> book = xlwt.Workbook()

>>> sheet = book.add_sheet('Test')

>>> sheet.write(0, 0, 'Hello\nWorld')

>>> style = xlwt.XFStyle()

>>> style.alignment.wrap = 1

>>> sheet.write(0, 1, 'Hello\nWorld', style)

>>> book.save('test.xls')

A1 (0,0) will show all in one line as "HelloWorld" in Excel but B1 (0,1) will show wrapped in two lines.

-Dave

[Hadil Sabbagh]

That works! Thanks so much for your help.

Hadil

[John Machin]

On Friday, 10 June 2011 02:41:05 UTC+10, DavidA wrote:

You just need to enable the wrap on the style's alignment for the cell:

Thanks for that. I was tricked by Excel 2003 which displays line1line2line3 in the cell, but because that A1 cell was the current cell, displays it on 3 lines in the toolbar above (Excel 2007 displays just line1).
 

>>> import xlrd

>>> book = xlrd.open_workbook('test.xls')

>>> sheet = book.sheets()[0]

The above 3 lines are redundant.
 

>>> import xlwt

>>> book = xlwt.Workbook()

>>> sheet = book.add_sheet('Test')

>>> sheet.write(0, 0, 'Hello\nWorld')

>>> style = xlwt.XFStyle()

>>> style.alignment.wrap = 1

or use easyxf:

style = xlwt.easyxf("align: wrap on")
 

[John Machin]

On Friday, 10 June 2011 02:04:48 UTC+10, Hadil Sabbagh wrote:

I executed the instructions exactly as you wrote down and got

line1$line2$line3

Where $=strange character.

Note: I am executing on a Solaris machine and using sftp to tranfer the xls file in binary mode. I also tested it directly on my Windows XP laptop. I get the same result.

Note that neither Dave nor I see a "strange character" . What does this strange character look like? What version of Excel or whatever are you using to view the file? What are your locale settings on the laptop?

[DavidA]

On Thursday, June 9, 2011 4:46:24 PM UTC-4, John Machin wrote:

>>> import xlrd

>>> book = xlrd.open_workbook('test.xls')

>>> sheet = book.sheets()[0]

The above 3 lines are redundant.

Copy-paste error.

[Ian Kelly]

On Thu, Jun 9, 2011 at 2:50 PM, John Machin <sjma...@lexicon.net> wrote:
> Note that neither Dave nor I see a "strange character" . What does this
> strange character look like? What version of Excel or whatever are you using
> to view the file? What are your locale settings on the laptop?

I see the "strange character", which is just the little square that
indicates the font does not include a glyph for that character. I'm
using Excel 2010 on Windows XP. I also notice that if I attempt to
edit the cell, it automatically wraps it and the glyph goes away.
Perhaps xlwt could similarly perform this transformation
automagically?

Cheers,
Ian

[John Machin]

On Friday, 10 June 2011 07:34:51 UTC+10, Ian Kelly wrote:

On Thu, Jun 9, 2011 at 2:50 PM, John Machin <sjma...@lexicon.net> wrote:
> Note that neither Dave nor I see a "strange character" . What does this
> strange character look like? What version of Excel or whatever are you using
> to view the file? What are your locale settings on the laptop?

I see the "strange character", which is just the little square that
indicates the font does not include a glyph for that character.  I'm
using Excel 2010 on Windows XP.

Looks like a bug in Excel 2010.

 I also notice that if I attempt to
edit the cell, it automatically wraps it and the glyph goes away.
Perhaps xlwt could similarly perform this transformation
automagically?

Automagically: each sheet.write(rowx, colx, payload, style) (or equivalent Row method) with a text payload would need to execute code like this (OTTOMH, untested):

if '\n' in payload and not style.alignment.wrap:
    style = copy.deepcopy(style)
    style.alignment.wrap = 1

Alternatively: Putting hard linebreaks in a cell is usually accompanied by setting horizontal and/or vertical alignment. The extra effort in adding ", wrap on" in the easyxf() call seems no great burden.

Cheers,
John