CODEWITHSUNDEEP
pythonexcelencodingsqlalchemyopenpyxl
milka1117
milka1117

Reputation: 521

How to save an xlsx with openpyxl and get right encoding? [trash characters]

I getting results from MYSQL DB and saving the data to an XLSX using openpyxl library. After saving and opening the file, I all names with any special characters full of bad characters. I believe this is some encoding problem, but I just can not find a way to set the encoding when opening or saving a workbook.

I have already tried to use:

wb =Workbook(encoding='utf8')

But then I get an error about unexpected keyword argument and I have no idea what else I can try.

The part of the code that deals with writing query result data to excel is here:

wb = Workbook()
ws = wb.create_sheet(0)
ws.append(res.keys())
x = res.fetchall()
xlength = len(x)
ylength = len(x[0])
for i in range(xlength):
    for k in range(ylength):
        ws.cell(row=(i+2), column=(k+1)).value = x[i][k]    
wb.save('Documents/python pigu_lt/reports/cache/{}'.format(query_type) + ".xlsx")

My actual result is:

'Vaikams ir kūdikiams'

And my expected result is:

'Vaikams ir kūdikiams'

Does anyone know how to get this right?

Upvotes: 0

Views: 7042

Answers (1)

Charlie Clark
Charlie Clark

Reputation: 19557

This has little to do with openpyxl and everything to do with MySQL and how you connect to it. XLSX stores data in XML which is UTF8 by default, and so capable of representing all unicode characters. You should check the settings for the database and make sure that it is returning either unicode or utf8.

Upvotes: 1

Related Questions