openpyxl错误：从/xl/worksheets/sheet1.xml中删除的记录：Formula
openpyxl错误：从/xl/worksheets/sheet1.xml中删除的记录：Formula
首先，我是一个编程新手。
我正在尝试使用Python3将MySQL数据库导出为Excel格式。我正在使用openpyxl来实现这个目标。现在我在Excel中遇到了一个有趣的错误。如果我运行的代码中的SQL查询较小（大约1000行），那么在打开Excel时不会出现错误，但如果查询较大（> 30k行），当我尝试打开Excel时就会出现错误：
错误135840_01.xml文件中检测到错误:'C:\Users\id022504\PycharmProjects\GetMySQLdata\Interface planning _mau.xlsx'中的Removed Records: Formula from /xl/worksheets/sheet1.xml部分
有趣的是，当我使用Open XML SDK打开Excel文件时，它指出问题在于颜色：
以下是代码：
import mysql.connector
import openpyxl
from openpyxl import Workbook
from openpyxl.styles import Border, Side, Font, Alignment
import datetime
import os
from openpyxl.worksheet import Worksheet
mydb = mysql.connector.connect(
host="10.10.10.10",
user="user",
passwd="password"
)
#用于自动调整列宽的函数
def auto_column_resize(worksheet):
for col in worksheet.columns:
max_length = 0
column = col[0].column # 获取列名
for cell in col:
try: # 避免空单元格报错
if len(str(cell.value)) > max_length:
max_length = len(cell.value)
except:
pass
adjusted_width = (max_length + 2) * 1.2
if adjusted_width <= 95:
worksheet.column_dimensions[column].width = adjusted_width
else:
worksheet.column_dimensions[column].width =95
return worksheet
# 定义路径
path='C:/Users/'
# 定义文件路径
path_arquivo='C:/Users/Arquivo/'
#定义文件大小
arquivo_file_size = 26
# 打开当前文件并保存到文件夹
current_wb = openpyxl.load_workbook(path+"Interface planning.xlsx")
current_ws = current_wb["Ports allocation"]
if int(datetime.datetime.now().isocalendar()[1]) >9:
current_wb.save("{0}/Interface planning_{1}{2}.xlsx".format(path_arquivo, str(int(datetime.datetime.now().isocalendar()[0])),str(int(datetime.datetime.now().isocalendar()[1]))))
else:
#在周数前加上0，以便稍后计算哪个文件是最旧的
current_wb.save("{0}/Interface planning_{1}0{2}.xlsx".format(path_arquivo, str(int(datetime.datetime.now().isocalendar()[0])),str(int(datetime.datetime.now().isocalendar()[1]))))
#打开SQL和命令
mycursor = mydb.cursor()
mycursor.execute("SELECT hostname, hardware, port_label, ifHighSpeed, ifAdminStatus, ifOperStatus, ifAlias FROM `observium`.`ports` JOIN `observium`.`devices` ON `observium`.`devices`.device_id = `observium`.`ports`.device_id WHERE (port_label LIKE 'xe-%' or port_label LIKE 'et-%' or port_label LIKE 'ge-%' or port_label LIKE '%Ethernet%') and port_label NOT RLIKE '[.][1-9]' ORDER BY hostname, port_label;")
#将从数据库接收到的数据转换为元组
myresult = mycursor.fetchall()
header = mycursor.column_names
#创建工作簿
new_wb = Workbook()
#创建工作表
new_ws = new_wb.active
new_ws.title = "Ports allocation"
############################################## 将SQL数据添加到Excel ##########################################
#添加标题信息和格式
new_ws.append(header)
new_ws["H1"].value = "Person assigned"
for format_row in new_ws:
for i in range(8):
format_row[i].font = Font(bold=True)
#将SQL中的内容添加到Excel
for row in myresult:
new_ws.append(row)
new_ws.auto_filter.ref = "A:H"
#检查接口状态，并根据接口的管理和操作状态设置责任人
for current_ws_row in current_ws:
if current_ws_row[7].value is not None:
for new_ws_row in new_ws:
if (new_ws_row[4].value != "up" or new_ws_row[5].value != "up") and current_ws_row[0].value == new_ws_row[0].value and current_ws_row[2].value == new_ws_row[2].value :
new_ws_row[7].value= current_ws_row[7].value
new_ws_row[6].value = current_ws_row[6].value
for format_row in new_ws:
for i in range(8):
format_row[i].border = Border(right=Side(style='thin'),)
#美化工作表
new_ws = auto_column_resize(new_ws)
new_ws.sheet_view.zoomScale = 85
c=new_ws['D2']
new_ws.freeze_panes = c
wrap_alignment = Alignment(wrap_text=True)
for row in new_ws.iter_rows():
for cell in row:
cell.alignment = Alignment(shrink_to_fit=True)
#保存工作簿
new_wb.save(path+"Interface planning.xlsx")
#从文件夹中删除文件
count_files=0
#无限循环
file_to_delete = '299952'
for directory in os.walk(path_arquivo):
for file in directory[2]:
count_files = count_files+1
if str(file)[-11:-5] < file_to_delete:
file_to_delete = str(file)[-11:-5]
if count_files > arquivo_file_size:
os.remove(path_arquivo+'Interface planning_'+file_to_delete+'.xlsx')