Write a list to a cell in Excel using Python

Question

I would like to write a list of lists : k = [['1','a'],['2','b'],['3','c']] - to a cell in a Python excel sheet.

My code:

workbook = xlsxwriter.Workbook('names.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write('A1', 'Generic Name')

I have tried: worksheet.write_row('B1', allmajor) which causes the contents the list k to be written in separate cells (B1, C1, D1..).

Doing: worksheet.write('B1', ','.join(k)) causes the cotnents of cell B1 to have strings like - '1','a','2','b','3','c'

Is there any way in which I can write a list to a cell such that cell B1 looks like [['1','a'],['2','b'],['3','c']]? Is there any other module in Python that can facilitate this?

systemjack · Accepted Answer · 2019-02-28 07:26:36Z

2

You can use the built-in function repr which gives a string representation of the list object:

worksheet.write('B1', repr(k))

At python prompt you can see how it works:

>>> k = [['1','a'],['2','b'],['3','c']]
>>> k
[['1', 'a'], ['2', 'b'], ['3', 'c']]
>>> repr(k)
"[['1', 'a'], ['2', 'b'], ['3', 'c']]"
>>> print(repr(k))
[['1', 'a'], ['2', 'b'], ['3', 'c']]

In the case of your simple list structure, you could just cast it to a string as well like str(k) but repr is probably better for this case.

edited Feb 28, 2019 at 7:26

answered Feb 28, 2019 at 7:10

systemjack

3,0051 gold badge21 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adarsa Sivaprasad · Accepted Answer · 2019-02-28 07:09:45Z

1

You can take care of this by converting your list of list to string. For example

>>import pandas as pd
>>list1=[['1','a'],['2','b'],['3','c']]
>>list2=[['4','a'],['5','b'],['6','c']]
>>df=pd.DataFrame({'a':[str(list1), str(list2)]]})

Now when you save it in a comma separated format,each element will be treated as a string. When you read this back from file, you can use ast to convert it back into a list of lists.

>>import ast
>>[ast.literal_eval(i) for i in df['a']]

Hope this answers your question.

answered Feb 28, 2019 at 7:09

Adarsa Sivaprasad

515 bronze badges

Collectives™ on Stack Overflow

Write a list to a cell in Excel using Python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related