writing a line break into an excel-cell

[Harald Armin Massa]

hello,

I am triyng to write out text into an excel-worksheet.

w = xlwt.Workbook()
ws=w.add_sheet(sheetname)

ws.write(20,20,"this is line1 \n this is line 2 \n this is line 3)


when opening the file in excel, there IS some symbol indicating a line
break, and after F2 and return on every cell, it gets formated
correctly...

but as the table has around 2000 cells with this kind of data,
pressing 2000 times F2 + return is not really an option :)

so: how can I put a line break into the data in one cell?

Harald

[John Machin]

Welcome, Harald.

This problem had me stumped for a while. Using Excel 2003, type
foo\nbar\nzot (where \n is effected by Alt-Enter), save the file,
inspect it with xlrd, sure enough the cell contains 'foo\nbar\nzot', use
xlwt to write that, open the new file with Excel: same problem as you
noticed. Whether Excel displays a symbol or not may depend on which
version and which "service pack" you have.

Now try this:
Select an empty cell in Excel, do Format > Cells > Alignment, notice
nothing unusual, change nothing, exit, type foo\nbar\nzot (where \n is
effected by Alt-Enter) then Enter, then do Format > Cells > Alignment,
notice that the "Wrap" box is now ticked!

So, short answer to your question: use a style (aka XF) with the
Alignment wrap set to 1.

import xlwt
algn1 = xlwt.Alignment()
algn1.wrap = 1
style1 = xlwt.XFStyle()
style1.alignment = algn1
wb = xlwt.Workbook()
ws = wb.add_sheet('LineBreakDemo')
ws.write(10, 0, 'Hard\nLine\nBreak', style1)
ws.write(10, 1, 'Bad\nLine\nBreak')
ws.write(10, 2, 'Soft Line Break', style1)
ws.write(10, 3, 'No Line Break')
wb.save('LineBreakDemo.xls')

HTH,
John

[Harald Armin Massa]

John,

> So, short answer to your question: use a style (aka XF) with the
> Alignment wrap set to 1.
>
> import xlwt
> algn1 = xlwt.Alignment()
> algn1.wrap = 1
> style1 = xlwt.XFStyle()
> style1.alignment = algn1
> wb = xlwt.Workbook()
> ws = wb.add_sheet('LineBreakDemo')
> ws.write(10, 0, 'Hard\nLine\nBreak', style1)
> ws.write(10, 1, 'Bad\nLine\nBreak')
> ws.write(10, 2, 'Soft Line Break', style1)
> ws.write(10, 3, 'No Line Break')
> wb.save('LineBreakDemo.xls')

Thank you very much for explanation and example. This works. All my
trials of "\n\r", "\r", "\n\r\n\r" etc. where in vain :)

best wishes,

Harald