CODEWITHSUNDEEP
pythonopenpyxl
EHM
EHM

Reputation: 959

Openpyxl.utils.exceptions.IllegalcharacterError

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.

Upvotes: 25

Views: 47233

Answers (7)

Fledias weh
Fledias weh

Reputation: 381

If I understand your question correctly, you have a string x of len(x)=7729 and while filling parts of the string into cells you encounter the error: openpyxl.utils.exceptions.IllegalCharacterError.

Befor running your string through the loop you could replace all problematic substrings with an empty string ('') in the string, using the ILLEGAL_CHARACTERS_RE pattern from https://stackoverflow.com/a/68578582.

import re    
from openpyxl.cell.cell import ILLEGAL_CHARACTERS_RE
    
x = 'string containing substrings like \x0c or \x0b...'
clean_x= re.sub(ILLEGAL_CHARACTERS_RE, '', x)
print(clean_x)
...'string containing substrings like  or ...'

The resulting clean_x you can just use in your loop as before.

Cleaning the string beforehand in one line, is likely much faster, then doing it in every loop step (doing it once vs doing it 7729 times).

Alternatively, if you need to retain a representation of the problematic substrings, you could use the dictionary (escape_xlsx_char) from this post How to handle the exception ‘IllegalCharacterError’ in openpyxl to get an exscape of the problematic substrings.

Upvotes: 0

fan.li
fan.li

Reputation: 41

import xlsxwriter
df.to_excel('abc.xlsx', engine='xlsxwriter')

Upvotes: 4

Tejas Sutar
Tejas Sutar

Reputation: 89

The best easy solution is to install Xlwriter.

pip install XlsxWriter

Upvotes: 1

otocan
otocan

Reputation: 923

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/

Upvotes: 22

John Prawyn
John Prawyn

Reputation: 1663

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.

Upvotes: 10

toheedNiaz
toheedNiaz

Reputation: 1445

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

Upvotes: 1

user5777975
user5777975

Reputation:

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

Upvotes: 0

Related Questions