Writing to Excel using openpyxl results in a different character symbol

Question

Following is my code that is used to insert a row to excel

from openpyxl import Workbook  

wb = Workbook()  
sheet = wb.active  
i = ['an', '_xa100_list']
sheet.append(i)  
wb.save('appending_values.xlsx') 

The problem is , after writing to the above excel, when I opened the file , i can see the second column's value as '__ꄀlist__' instead of '_xa100_list'.

' \_xa100\_' got internally substituted by '__ꄀ__' .

why is it happening and how can i print the exact string to excel rather than this internal converted form?

Answer 1

Discussion

The box shows because you aren't using a font that has the YI SYLLABLE DIT character. This is the unicode character encoded as A100 in UTF-16. Now, why is Excel converting _xa100_ to YI SYLLABLE DIT? It turns out that _x[bytes]_ is an escape sequence in Excel. Give it a try with something that will render:

from openpyxl import Workbook  

wb = Workbook()  
sheet = wb.active  
i = ['an', '_x0040_list']
sheet.append(i)  
wb.save('appending_values.xlsx')

You should see the table below in Excel. This is because 0040 is the encoded @.

A	B
an	@list

Solution

There exists a hacky workaround: escape the first character of the escape sequence _, 005F.

from openpyxl import Workbook  

wb = Workbook()  
sheet = wb.active  
i = ['an', '_x005f_xa100_list']
sheet.append(i)  
wb.save('appending_values.xlsx')

More Background

This isn't unique to Excel, this is part of the design of OOXML, the underlying format of XLSX files. Here's a bug report from 2014 related to your issue. And the relevant part of the specification:

22.2.2.21 Properties (Application Specific File Properties)
This element specifies the application properties of a document. For properties of type string, NCR escape
format (_xHHHH_) is used for any invalid XML characters.
[Note: The W3C XML Schema definition of this element’s content model (CT_Properties) is located in §A.6.2. end
note]