将dataframe写入Excel,防止覆盖,及openpyxl写入非法自符时报错

将dataframe中的NULL转为空字符

df.fillna('', inplace=True)

将dataframe写入Excel,防止覆盖,及openpyxl写入非法自符时报错

data = pd.DataFrame(df).applymap(lambda x: re.sub(r'[\x00-\x09]|[\x0B-\x0C]|[\x0E-\x1F]', '此处有非法字符', str(x)))
writer = ExcelWriter(to_excel_path, engine='openpyxl')
if not os.path.exists(to_excel_path):
    data.to_excel(writer, sheet_name=sheet_name, columns=columns, index=False)
else:
    book = load_workbook(writer.path)
    writer.book = book
    data.to_excel(writer, sheet_name=sheet_name, columns=columns, index=False)
    writer.save()
    writer.close()