Openpyxl.utils.exceptions.IllegalcharacterError

Question

I have the following python code to write processed words into excel file. The words are about 7729

From openpyxl import *
book=Workbook ()
sheet=book.active
sheet.title="test"
for x in range (7729):
    sheet.cell (row=1,column=x+1).value=x
book.save ('test.xlsx')

This is the what the code I used looks like, but when I run it, it gives me an error that says

openpyxl.utils.exceptions.IllegalCharacterError

This is my first time using this module, I would appreciate any kind of help.

can you share the outcome of this `print (sys.getdefaultencoding())` ? — toheedNiaz, Apr 15 '18 at 18:03
please add your python version as well it will help people to reproduce the issue. — toheedNiaz, Apr 15 '18 at 18:24
`exception openpyxl.utils.exceptions.IllegalCharacterError` Reason : The data submitted which cannot be used directly in Excel files. It must be removed or escaped. so the issue is some how with your data i.e ur `x` value — toheedNiaz, Apr 15 '18 at 18:30
The data is a string, specifically a bunch of english letters. — EHM, Apr 15 '18 at 18:37
that must be the issue then, you need to either escape data or try to forcefully encode it with utf-8 and ignore errors if you can live without unprintable chars in your data — toheedNiaz, Apr 15 '18 at 18:40

score 16 · Answer 1 · answered Jul 29 '21 at 15:16

openpyxl comes with an illegal characters regular expression, ready for you to use. Presuming you're happy to simply remove these characters, you can do:

import re
from openpyxl.cell.cell import ILLEGAL_CHARACTERS_RE
from openpyxl import *

book=Workbook ()
sheet=book.active
sheet.title="test"
for x in range (7729):
   sheet.cell (row=1,column=x+1).value = ILLEGAL_CHARACTERS_RE.sub(r'',x)
book.save ('test.xlsx')

To speed it up, you could put the original cell value assignment inside a try/except and only run the re substitution when an openpyxl.utils.exceptions.IllegalCharacterError is caught.

Source: https://www.programmersought.com/article/43315246046/

score 8 · Answer 2 · answered Jul 20 '20 at 15:22

I faced similar issue and found out that it is because of \xa1 character which is hex value of ascii 26 (SUB). Openpyxl is not allowing to write such characters (ascii code < 32). I tried xlsxwriter library without any issue it worte this character in xlsx file.

toheedNiaz · Accepted Answer · 2018-04-15T20:05:13.577

1

Try this : This code works for me .

from openpyxl import *
book=Workbook ()
sheet=book.active
sheet.title="test"
x = 0
with open("temp.txt") as myfile :
    text = myfile.readline()
    while text !="":
            sheet.cell (row=1,column=x+1).value=str(text).encode("ascii",errors="ignore")
            x+=1
            text = myfile.readline()

book.save ('test.xlsx')

edited Apr 15 '18 at 20:05

answered Apr 15 '18 at 18:37

toheedNiaz

1,435
1
10
14

In the real program x is a string what should I do for that, should I remove the int function and use the above – EHM Apr 15 '18 at 18:40
Thank you so much but it seems this din't work. If this helps when I do like sheet ['A1']=1 or sheet ['A1']='hi' works. – EHM Apr 15 '18 at 18:50
can i see your sample data please ? and they way you are reading that data from the source ? – toheedNiaz Apr 15 '18 at 19:26
'Kitaabota', 'seenaa', 'yesuus',and yes i am reading it from a source and the source is a txt file. – EHM Apr 15 '18 at 19:34
can i see the code you are using to read the text file and exact one line for text file. that will help me to reproduce the issue – toheedNiaz Apr 15 '18 at 19:38
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/169038/discussion-between-hilea-and-toheedniaz). – EHM Apr 15 '18 at 19:48

score 0 · Answer 4 · answered Apr 15 '18 at 17:42

You missed to add the value for cell sheet.cell (row=1,column=x+1).value =

Try like this

from openpyxl import *
book = Workbook ()
sheet = book.active
sheet.title = "test"
for x in range (7):
    sheet.cell (row=1,column=x+1).value = "Hello"
book.save ('test.xlsx')

score 0 · Answer 5 · answered Jul 06 '23 at 09:21

0

The best easy solution is to install Xlwriter.

pip install XlsxWriter

answered Jul 06 '23 at 09:21

Tejas Sutar

71
1
4

Openpyxl.utils.exceptions.IllegalcharacterError

5 Answers5

Linked