New line in cells.

[hochl]

Dear all,

I've written a Python program using xlwt to create an Excel file. I'm
pretty happy so far, but one thing seems complicated and I couldn't
find out how to achieve it. So far I've searched for an answer using
Google and this group, and I'm sure the Problem can be solved easily.

I'm writing cells to an Excel file using the following program
(simplified for this posting):

---------------------
import xlwt

def excel_add_row(sheet, row_number, values):
for column, value in enumerate(values):
sheet.write(row_number, column, value)

def excel_save(filename, *args):
workbook=xlwt.Workbook("UTF-8")
sheet=workbook.add_sheet("sheet 1")
for n, values in enumerate(args):
excel_add_row(sheet, n, values)
workbook.save(filename)

rows=[]
for row in range(0, 5):
rows.append(["row %d\ncolumn %d" % (row, col) for col in range
(0, 5)])

excel_save("test.xls", *rows)
---------------------

The problem arises when I open the resulting file, because all newline
characters are displayed as a black box. When I double-click a cell
the newline is expanded and I have two lines (f.e. line #1: row 0,
line #2: column 0). Now the question is, can I write the file in a way
that I get a new line instead of the boxes when I open the file?

Cheers,
Thomas.

[John Machin]

Hi Thomas, welcome to the group.

From almost exactly a year ago (2008-05-06) Harald Armin Massa was
wondering the same thing:

=== start of extract from the archives ===

Harald Armin Massa wrote:
> > 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)

Thomas, that's what a simplified program looks like :-)

> > 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?
> >

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')

=== end of extract from the archives ===

Nowadays, we'd use easyxf instead to create style1:

style1 = xlwt.easyxf("alignment: wrap on") # plus whatever other things
you need

HTH,
John

[hochl]

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

[...]

> HTH,
> John

Thanks for the welcome and fast reply John!

The output now looks completely different, unfortunately it has
changed in a peculiar way:

- if you double-click on a column separator the column doesn't auto-
adjust it's width anymore.
- the fields seem to have a fixed width and lines are separated
*somewhere*, not necessarily at the `black boxes'.
- there are still cells that contains black boxes, and those are
sometimes wrapped at other positions.

I'm using Excel 2003, maybe this is a bug or maybe I have to set other
properties together with wrap? If you like I can send you the result,
unfortunately I'm not very familiar with Excel, I only need to write
an exporter for our data!

Cheers,
Thomas.

[John Machin]

On 7/05/2009 10:55 PM, hochl wrote:
>> So, short answer to your question: use a style (aka XF) with the
>> Alignment wrap set to 1.
>>
>> import xlwt
>> algn1 = xlwt.Alignment()
>
> [...]
>
>> HTH,
>> John
>
> Thanks for the welcome and fast reply John!
>
> The output now looks completely different, unfortunately it has
> changed in a peculiar way:
>
> - if you double-click on a column separator the column doesn't auto-
> adjust it's width anymore.
> - the fields seem to have a fixed width and lines are separated
> *somewhere*, not necessarily at the `black boxes'.
> - there are still cells that contains black boxes, and those are
> sometimes wrapped at other positions.

weird * 3

>
> I'm using Excel 2003, maybe this is a bug or maybe I have to set other
> properties together with wrap?

or maybe the planets are not aligned ... speculation is futile.

If you like I can send you the result,

send result PLUS the code that created it

> unfortunately I'm not very familiar with Excel, I only need to write
> an exporter for our data!

Cheers,
John

[hochl]

Hi,

After further tests it seems the resulting files work for OpenOffice
and Gnumeric, but not for my German Excel version 11.8237.8221 SP 3.

Cheers,
Thomas.

[John Machin]

And they are also display as expected by:
* English Excel 2003 (11.8302.82210 SP3)
* English Excel 2007
* Excel Viewer

[grap3_ap3]

Hello, 

Forgive me if reviving an old post is not the preferred method within this group, but the post below outlines the basic problem I'm facing. I have a column within my worksheet which has data in the following format: 

"Problem:\nSome Problem Text Here.\nSolution:\nSolution to the Problem Here\n\n"

I want the \n to be observed as the "newline"|Alt+Enter character, and it just is not. I continue to see the line printed as above rather than observing the newlines. I have experimented with the wrap on/off and this makes no difference. I have also experimented with both XFStyle AND easyxf- neither of which makes a difference. 

Is there some other method by which I can enforce the newline character within a cell or column? 

[John Machin]

On Wednesday, July 25, 2012 12:53:09 PM UTC+10, grap3_ap3 wrote:

Forgive me if reviving an old post is not the preferred method within this group, but the post below outlines the basic problem I'm facing.

I would rather rely on YOU outlining what your problem is, together with the minimal Python code that demonstrates the problem -- in this case, about 10 lines.
 

I have a column within my worksheet which has data in the following format: 

"Problem:\nSome Problem Text Here.\nSolution:\nSolution to the Problem Here\n\n"

Column?? Do you mean "cell"? Please show the Python code.

I want the \n to be observed as the "newline"|Alt+Enter character, and it just is not. I continue to see the line printed as above rather than observing the newlines.

Printed? By what software on what device? If you mean that you can actually see the backslash and the n when looking at the Excel screen, then you must have doubled up your backslashes in your script somehow e.g. "foo\\nbar" or r"foo\nbar" instead of "foo\nbar"
 

I have experimented with the wrap on/off and this makes no difference. I have also experimented with both XFStyle AND easyxf- neither of which makes a difference. 

Is there some other method by which I can enforce the newline character within a cell or column? 

There is no method other than as outlined in the earlier posts.

[grap3_ap3]

On Wednesday, July 25, 2012 7:59:58 AM UTC-4, John Machin wrote:

On Wednesday, July 25, 2012 12:53:09 PM UTC+10, grap3_ap3 wrote:

Forgive me if reviving an old post is not the preferred method within this group, but the post below outlines the basic problem I'm facing.

I would rather rely on YOU outlining what your problem is, together with the minimal Python code that demonstrates the problem -- in this case, about 10 lines.

Thanks once again for the reply- just what I needed to get where I need to go! In the future I will just post my own problem. I know some forums/groups have different preferences. 

 

 

I have a column within my worksheet which has data in the following format: 

"Problem:\nSome Problem Text Here.\nSolution:\nSolution to the Problem Here\n\n"

Column?? Do you mean "cell"? Please show the Python code.

 

The whole column ends up with data of the example format. But another point you make gave me what I need to fix the problem. 

I want the \n to be observed as the "newline"|Alt+Enter character, and it just is not. I continue to see the line printed as above rather than observing the newlines.

Printed? By what software on what device? If you mean that you can actually see the backslash and the n when looking at the Excel screen, then you must have doubled up your backslashes in your script somehow e.g. "foo\\nbar" or r"foo\nbar" instead of "foo\nbar"

Viewing the output spreadsheet in both libreOffice  and Excel, I was seeing the backslash and the n- I didn't realize the data had the double backslashes. After reading this, I debugged my way to success- thank you!

 

I have experimented with the wrap on/off and this makes no difference. I have also experimented with both XFStyle AND easyxf- neither of which makes a difference. 

Is there some other method by which I can enforce the newline character within a cell or column? 

There is no method other than as outlined in the earlier posts.

 

Thanks for the help- this group is great. I was able to muddle my way to a solution here by implementing a function that will format any "\\n" to "\n" which solves the issue.