How to handle the exception ‘IllegalCharacterError’ in openpyxl

The python module openpyxl may sometimes raise the exception IllegalCharacterError (see: https://openpyxl.readthedocs.io/en/stable/api/openpyxl.utils.exceptions.html for further details).

The raised exception however, does not hint at a way of handling the exception, nor does the documentation at the moment.

I’ve written the function below, that escapes all of the necessary characters. You can for instance use it when inserting a string into a cell, like: wb['A1'].value = escape_xlsx_string('Your string with a char \x00 that would normally raise an exception').

The illegal character \x00 is then escaped, so that the inserted string becomes: ‘Your string with a char \x00 that would normally raise an exception' – note that the \x00 is “spelled out” as and not inserted as the NULL character, that would otherwise cause the exception to be raised.

The list of illegal characters was derived by testing every single unicode codepoint, so it should be complete. Interrestingly the illegal characters are all ASCII control characters > 32. But the other unicode control characters seems to be allowed.

def escape_xlsx_char(ch):
	illegal_xlsx_chars = {
	'\x00':'\\x00',	#	NULL
	'\x01':'\\x01',	#	SOH
	'\x02':'\\x02',	#	STX
	'\x03':'\\x03',	#	ETX
	'\x04':'\\x04',	#	EOT
	'\x05':'\\x05',	#	ENQ
	'\x06':'\\x06',	#	ACK
	'\x07':'\\x07',	#	BELL
	'\x08':'\\x08',	#	BS
	'\x0b':'\\x0b',	#	VT
	'\x0c':'\\x0c',	#	FF
	'\x0e':'\\x0e',	#	SO
	'\x0f':'\\x0f',	#	SI
	'\x10':'\\x10',	#	DLE
	'\x11':'\\x11',	#	DC1
	'\x12':'\\x12',	#	DC2
	'\x13':'\\x13',	#	DC3
	'\x14':'\\x14',	#	DC4
	'\x15':'\\x15',	#	NAK
	'\x16':'\\x16',	#	SYN
	'\x17':'\\x17',	#	ETB
	'\x18':'\\x18',	#	CAN
	'\x19':'\\x19',	#	EM
	'\x1a':'\\x1a',	#	SUB
	'\x1b':'\\x1b',	#	ESC
	'\x1c':'\\x1c',	#	FS
	'\x1d':'\\x1d',	#	GS
	'\x1e':'\\x1e',	#	RS
	'\x1f':'\\x1f'}	#	US
	
	if ch in illegal_xlsx_chars:
		return illegal_xlsx_chars[ch]
	
	return ch
	
#
# Wraps the function escape_xlsx_char(ch).
def escape_xlsx_string(st):
	
	return ''.join([escape_xlsx_char(ch) for ch in st])

I am aware that the title says ‘handle the exception’ – you could do that by inserting strings into the cell and handling the exception, when it occours, using the function above.
Another method could be just to use the function every time.

How to stop hanging print jobs in Windows 8.1

From time to time, some print jobs may be hanging for no apparent reason. This may some times be resolved by restarting or deleting the print job in the print job list. This however is not always possible, but another fix may be handy:

Right click on the start button and select “Command prompt (Admin) “.

In the command promt, restart the print spooler, by writing

net stop spooler

and then:

 net stop spooler