Manoj Manoj - 1 year ago 129
Python Question

Xlsx Writer: Writing stings with multiple format in a single cell

I have a string with multiple semicolons.

'firstline: abc \n secondline: bcd \n thirdline: efg'

I want to write this strings in excel cell using Xlsx Writer like below.

firstline: abc

secondline: bcd

thirdline: efg

This is what I did.

description_combined =
firstline: abc
secondline: bcd
thirdline: efg
spanInserted = []
spanInserted = description_combined.split("\n")
result = ''

for spans in spanInserted:
strr1 = '{}:'.format(spans.split(":")[0])
strr2 = spans.split(":")[1]
result += '''bold , "{}" , "{}" ,'''.format(str(strr1),str(strr2))

result = result[:-1]
# print result

worksheet.write_rich_string('A1', result)

This is the result I got in excel cell:

bold , "firstline:" , "abc" ,bold , "secondline:" , "bcd" ,bold , "thirdline:" , "efg"

Answer Source

The write_string() method takes a list as an argument but you are passing a string.

You should use something like this to pass your list of strings and formats:

result = [bold, 'firstline: ',  'abc',
          bold, 'secondline: ', 'bcd',
          bold, 'thirdline: ',  'efg']

worksheet.write_rich_string('A1', *result)

However, if you also want the text to be wrapped you will need to add a text_wrap cell format at the end of the list and add newlines for where you want the wrap to occur. Something like this:

import xlsxwriter

workbook = xlsxwriter.Workbook('rich_strings.xlsx')
worksheet = workbook.add_worksheet()

worksheet.set_column('A:A', 20)
worksheet.set_row(0, 60)

bold = workbook.add_format({'bold': True})
text_wrap = workbook.add_format({'text_wrap': True, 'valign': 'top'})

result = [bold, 'firstline: ',  'abc\n',
          bold, 'secondline: ', 'bcd\n',
          bold, 'thirdline: ',  'efg']


worksheet.write_rich_string('A1', *result)



enter image description here

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download