首先，我是编程新手。
我正在尝试用python3将MySQL数据库导出为excel格式。我正在使用openpyxl。现在我在excel中出现了一个有趣的错误。如果我运行代码，但SQL查询很小(大约1000行(，那么我在操作excel时不会出错，但如果它更大(>30000行(，当我试图打开excel时，我会出错：
error135840_01.xml在文件"C:\Users\id022504\PycharmProjects\GetMySQLdata\Interface planning _mau.xlsx"中检测到错误"已删除记录：来自/xl/worksheets/sheet1.xml的公式"部分
有趣的是，当我使用Open XML SDK打开excel文件时，它指出问题在于颜色：
在此处输入图像描述
在此处输入图像描述
Bellow是代码：
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"
)
#Funcao para por as colunas com auto size
def auto_column_resize(worksheet):
for col in worksheet.columns:
max_length = 0
column = col[0].column # Get the column name
for cell in col:
try: # Necessary to avoid error on empty cells
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
# definir path
path='C:/Users/'
# definir path para arquivo
path_arquivo='C:/Users/Arquivo/'
#definir tamanho do arquivo
arquivo_file_size = 26
# Abrir ficheiro actual e guardar nos arquivos
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:
#introduce 0 in the week so it can calculate later which file is the oldest
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]))))
#Abrir SQL e comando
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;")
#Meter em tupel os dados recebidos pela base de dados
myresult = mycursor.fetchall()
header = mycursor.column_names
#Criar workbook
new_wb = Workbook()
#Criar worksheet
new_ws = new_wb.active
new_ws.title = "Ports allocation"
############################################## Meter dados SQL em excel ##########################################
#Add header information and formating
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)
#Add content from SQL to excel
for row in myresult:
new_ws.append(row)
new_ws.auto_filter.ref = "A:H"
#Verificar o estado da interface e colocar a pessoa responsavel se a interface esta administrativamente e operacionalmente em baixo
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'),)
#Meter o worksheet bonitinho
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)
#Salvar workbook
new_wb.save(path+"Interface planning.xlsx")
#remover ficheiro no arquivo
count_files=0
#infinite time
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')
我发现了这个问题，在其中一个单元格中有"="符号，不知何故excel将其识别为公式。为了解决这个问题，我只是清理了"="。
import re
try:
new_ws_row[7].value = re.sub("=", "", new_ws_row[7].value)
except:
pass
扩展Pedro于2018年12月12日提供的精彩答案。
当尝试使用Openpyxl 时，我发现以下是解决包含以等号开头的值的故障列的有效方法
for cell in ws['R']:
if "=" in str(cell.value):
cell.value = re.sub('^=', ' =', cell.value)
Excel公式的长度限制为255个字符。注意这一点。