xlsxwriter and xlwt: Writing a list of strings to a cell

Question

I am currently using xlwt quite successfully to create .xls files. I am also learning xlsxwriter for possible future applications where I'll need some of its features.

xlwt smoothly writes lists of strings into cells.

e.g.

import xlwt
a = ['January\n','February\n','March\n','April\n','May\n','June\n']
book = xlwt.Workbook()
sheet = book.add_sheet('Test')
sheet.write(0,0,a)
book.save('Test.xls')

Open the Test.xls file, enable wrap text, and cell A1 shows:

January
February
March
April
May
June

I tried to do something similar with xlsxwriter

import xlsxwriter
xbook = xlsxwriter.Workbook('Test.xlsx')
xsheet = xbook.add_worksheet('Test')
xsheet.write(0,0,a)

Here, I get a lengthy error message culminating in

...anaconda/lib/python2.7/site-packages/xlsxwriter/worksheet.pyc in write(self, row, col, *args)
    416         # We haven't matched a supported type. Try float.
    417         try:
--> 418             f = float(token)
    419             if not self._isnan(f) and not self._isinf(f):
    420                 return self.write_number(row, col, f, *args[1:])

TypeError: float() argument must be a string or a number

I've tried other xlsxwriter write methods and all give roughly similar errors.

Other research: I've searched this site fairly thoroughly. I've also gone through the excellent xlsxwriter PDF and checked the xlsxwriter Github pages. So far, I have not come across anything that addresses this.

Again, xlwt is fine for now I expect to need to add charts and sparklines as well as to create xlsx files in the near future.

Thanks,

The Old Guy In The Club

Poore

Just use "".join(a) instead of a if isinstance(a, list) == True. – Regime

This is exactly what I need. I am unable to accept the answer as there is no checkbox... – Poore

Answer 1

No, you cannot write a list type using xlsxwrite.write().

Check the docs

If none of the above types are matched the value is evaluated with float() to see if it corresponds to a user defined float type. If it does then it is written using write_number().

That is why you get the error TypeError: float() argument must be a string or a number

To fix this try converting the list to a string, as suggested by Paulo Scardine (in the comments):

import xlsxwriter
a = ['January\n','February\n','March\n','April\n','May\n','June\n']
xbook = xlsxwriter.Workbook('Test.xlsx')
xsheet = xbook.add_worksheet('Test')
xsheet.write(0,0,''.join(a))

Ibnsaud

Answer 2

I've been working through a similar problem.

Was able to achieve the desired result by using enumerate to get the index of each string in the list and then passing that as both the Row reference and the data item to .write:

import xlsxwriter

a = ['January\n','February\n','March\n','April\n','May\n','June\n']

xbook = xlsxwriter.Workbook('Test.xlsx')
xsheet = xbook.add_worksheet('Test')

for idx, month in enumerate(a):
    xsheet.write(idx,0,a[idx])

xbook.close()

Descendent

Answer 3

The write() method in XlsxWriter doesn't support lists.

You can write lists using the worksheet write_row() method:

import xlsxwriter

a = ['January\n','February\n','March\n','April\n','May\n','June\n']

xbook = xlsxwriter.Workbook('Test.xlsx')
xsheet = xbook.add_worksheet('Test')

xsheet.write_row(0, 0, a)

xbook.close()

P.S. That exception isn't very helpful in this case and will be fixed in the next release. Here is the exception from your example program using the version of XlsxWriter on GitHub:

TypeError: Unsupported type <type 'list'> in write()

Trubow

I did try that as part of my initial experiments with each of the write_ methods. This placed each element of the list into a separate cell (as expected from the docs.) The "".join method puts all the strings into one cell which is something I need to do for a current application. Hope its not bad form to acknowledge your efforts in creating this package. – Poore

Whoops, sorry. I missed that you wanted them all in one cell. In that case the join() shown above is a better option. – Trubow

In which case you may also need the wrap_text format. And no, it wasn't bad form. :-) – Trubow