CODEWITHSUNDEEP
pythonexcelxlwtxlsxwriter
user4048937
user4048937

Reputation:

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

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

Upvotes: 5

Views: 26793

Answers (3)

rafello
rafello

Reputation: 128

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

Upvotes: 4

jmcnamara
jmcnamara

Reputation: 41614

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

Upvotes: 3

sk11
sk11

Reputation: 1824

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

Upvotes: 2

Related Questions