Openpyxl.utils.exceptions.IllegalcharacterError

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.

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/

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