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.

Answer 1

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/

Answer 2

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.