openpyxl.utils.exceptions.IllegalCharacterError报错原因及解决办法

df.to_excel(base_path + 'result.xlsx', index=False)
Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "D:\ProgramData\Anaconda3\envs\gpu\Lib\site-packages\pandas\core\generic.py", line 2032, in to_excel
    engine=engine,
  File "D:\ProgramData\Anaconda3\envs\gpu\Lib\site-packages\pandas\io\formats\excel.py", line 739, in write
    freeze_panes=freeze_panes,
  File "D:\ProgramData\Anaconda3\envs\gpu\Lib\site-packages\pandas\io\excel\_openpyxl.py", line 425, in write_cells
    xcell.value, fmt = self._value_with_fmt(cell.val)
  File "D:\ProgramData\Anaconda3\envs\gpu\Lib\site-packages\openpyxl\cell\cell.py", line 215, in value
    self._bind_value(value)
  File "D:\ProgramData\Anaconda3\envs\gpu\Lib\site-packages\openpyxl\cell\cell.py", line 194, in _bind_value
    value = self.check_string(value)
  File "D:\ProgramData\Anaconda3\envs\gpu\Lib\site-packages\openpyxl\cell\cell.py", line 162, in check_string
    raise IllegalCharacterError
openpyxl.utils.exceptions.IllegalCharacterError
Excel表中有非法字符，这些字符都是八进制的，需要进行清洗

解决办法一：
import re
def data_clean(text):
    # 清洗excel中的非法字符，都是不常见的不可显示字符，例如退格，响铃等
    ILLEGAL_CHARACTERS_RE = re.compile(r'[\000-\010]|[\013-\014]|[\016-\037]')
    text = ILLEGAL_CHARACTERS_RE.sub(r'', text)
    return text
df = df.fillna('').astype(str)
for col in df.columns:
    df[col] = df[col].apply(lambda x: data_clean(x))
df.to_excel(base_path + 'result.xlsx', index=False)
解决办法二：
import xlsxwriter
df.to_excel(base_path + 'result.xlsx', engine='xlsxwriter', index=False, encoding='utf-8')
https://blog.csdn.net/javajiawei/article/details/97147219