使⽤openpyxl追加写⼊Excel的成功案例，以及遇到的坑1. 想实现的功能
程序循环多次，每次向指定的excel中追加写⼊数据，
想看成功⽅案，可以直接跳转到：Try-3
2. 尝试⽅法
Try-1：
使⽤其他⽹友提供的openpyxl代码：, 代码如下：
# pandas 不覆盖写⼊ excel 同⼀张 Sheet 表
import pandas as pd
from openpyxl import load_workbook
def append_df_to_excel(filename, df, sheet_name='Sheet1', startrow=None,
truncate_sheet=False,
**to_excel_kwargs):
"""
Append a DataFrame [df] to existing Excel file [filename]
into [sheet_name] Sheet.
If [filename] doesn't exist, then this function will create it.
Parameters:
filename : File path or existing ExcelWriter
(Example: '/path/to/file.xlsx')
df : dataframe to save to workbook
sheet_name : Name of sheet which will contain DataFrame.
(default: 'Sheet1')
startrow : upper left cell row to dump data frame.
Per default (startrow=None) calculate the last row
in the existing DF and write to the
truncate_sheet : truncate (remove and recreate) [sheet_name]
before writing DataFrame to Excel file
to_excel_kwargs : arguments which will be passed to `_excel()`
[can be dictionary]
Returns: None
"""
# from openpyxl import load_workbook
# import pandas as pd
# ignore [engine] parameter if it was passed
if'engine'in to_excel_kwargs:
to_excel_kwargs.pop('engine')
writer = pd.ExcelWriter(filename, engine='openpyxl')
# Python 2.x: define [FileNotFoundError] exception if it doesn't exist
try:
FileNotFoundError
except NameError:
FileNotFoundError = IOError
try:
# try to open an existing workbook
writer.book = load_workbook(filename)
# get the last row in the existing Excel sheet
# if it was not specified explicitly
if startrow is None and sheet_name in writer.book.sheetnames:
startrow = writer.book[sheet_name].max_row
# truncate sheet
if truncate_sheet and sheet_name in writer.book.sheetnames:
# index of [sheet_name] sheet
idx = writer.book.sheetnames.index(sheet_name)
# remove [sheet_name]
ve(writer.book.worksheets[idx])
# create an empty sheet [sheet_name] using old index
ate_sheet(sheet_name, idx)
# copy existing sheets
writer.sheets ={ws.title:ws for ws in writer.book.worksheets}
except FileNotFoundError:
# file does not exist yet, we will create it
pass
if startrow is None:
startrow =0
# write out the new sheet
<_excel(writer, sheet_name,**to_excel_kwargs)
# save the workbook
writer.save()
出现问题：
⼀开始运⾏是OK的，但是后来总是出现两个错误：
控制台报错： BadZipFile: File is not a zip file
打开excel报错：Excel⽆法打开⽂件xxx.xlsx，因为⽂件格式或⽂件扩展名⽆效。请确定⽂件未损坏
解决办法：
了N多种⽅法都没解决，所以放弃了
Try-2：
读取：xlrd
写⼊：xlwt
修改（追加写⼊）：xlutils
同样参照其他⽹友提供的代码：， 代码如下：
import xlwt
py import copy
def write_excel_xls(path, sheet_name, value):
index =len(value)# 获取需要写⼊数据的⾏数
workbook = xlwt.Workbook()# 新建⼀个⼯作簿
sheet = workbook.add_sheet(sheet_name)# 在⼯作簿中新建⼀个表格
for i in range(0, index):
for j in range(0,len(value[i])):
sheet.write(i, j, value[i][j])# 像表格中写⼊数据（对应的⾏和列）
workbook.save(path)# 保存⼯作簿
print("xls格式表格写⼊数据成功！")
def write_excel_xls_append(path, value):
index =len(value)# 获取需要写⼊数据的⾏数
workbook = xlrd.open_workbook(path)# 打开⼯作簿
sheets = workbook.sheet_names()# 获取⼯作簿中的所有表格
worksheet = workbook.sheet_by_name(sheets[0])# 获取⼯作簿中所有表格中的的第⼀个表格
rows_old = ws  # 获取表格中已存在的数据的⾏数
new_workbook = copy(workbook)# 将xlrd对象拷贝转化为xlwt对象
new_worksheet = _sheet(0)# 获取转化后⼯作簿中的第⼀个表格
for i in range(0, index):
for j in range(0,len(value[i])):
new_worksheet.write(i+rows_old, j, value[i][j])# 追加写⼊数据，注意是从i+rows_old⾏开始写⼊    new_workbook.save(path)# 保存⼯作簿
print("xls格式表格【追加】写⼊数据成功！")
def read_excel_xls(path):
workbook = xlrd.open_workbook(path)# 打开⼯作簿
sheets = workbook.sheet_names()# 获取⼯作簿中的所有表格
worksheet = workbook.sheet_by_name(sheets[0])# 获取⼯作簿中所有表格中的的第⼀个表格
for i in range(0, ws):
for j in range(0, ls):
ll_value(i, j),"\t", end="")# 逐⾏逐列读取数据
print()
book_name_xls ='xls格式测试⼯作簿.xls'getsavefilename
sheet_name_xls ='xls格式测试表'
value_title =[["姓名","性别","年龄","城市","职业"],]
value1 =[["张三","男","19","杭州","研发⼯程师"],
["李四","男","22","北京","医⽣"],
["王五","⼥","33","珠海","出租车司机"],]
value2 =[["Tom","男","21","西安","测试⼯程师"],
["Jones","⼥","34","上海","产品经理"],
["Cat","⼥","56","上海","教师"],]
write_excel_xls(book_name_xls, sheet_name_xls, value_title)
write_excel_xls_append(book_name_xls, value1)
write_excel_xls_append(book_name_xls, value2)
read_excel_xls(book_name_xls)
出现问题：
xlwt最多只能写⼊65535⾏，255列，满⾜不了我的需求
还是回到openpyxl
Try-3：
# 使⽤openpyxl追加写⼊数据到Excel
import openpyxl
import os
def create_excel_xlsx(path, sheet_name):
workbook = openpyxl.Workbook()
sheet = workbook.active
sheet.title = sheet_name
workbook.save(path)
def write_excel_xlsx_append(path, value, truncate_sheet=False):
# 如果不存在就创建该excel
if not ists(path):
create_excel_xlsx(path,'Sheet1')
value = value.values #将dataframe转为array
data = openpyxl.load_workbook(path)
# 取第⼀张表
sheetnames = data.sheetnames
sheet = data[sheetnames[0]]
sheet = data.active
if(truncate_sheet):#truncate_sheet为True，覆盖原表中的数据
startrows =0
else:
# print(sheet.title)  # 输出表名
startrows = sheet.max_row  # 获得⾏数
index =len(value)
for i in range(0, index):
for j in range(0,len(value[i])):
data.save(path)
print("xlsx格式表格追加写⼊数据成功！")
def read_excel_xlsx(path, sheet_name):
workbook = openpyxl.load_workbook(path)
# sheet = wb.get_sheet_by_name(sheet_name)这种⽅式已经弃⽤，不建议使⽤    sheet = workbook[sheet_name]
for row ws:
for cell in row:
print(cell.value,"\t", end="")
print()
book_name_xlsx ='xlsx格式测试⼯作簿.xlsx'
sheet_name_xlsx ='xlsx格式测试表'
value3 =[["111","⼥","66","⽯家庄","运维⼯程师"],
["222","男","55","南京","饭店⽼板"],
["333","⼥","27","苏州","保安"],]
write_excel_xlsx_append(book_name_xlsx, sheet_name_xlsx, value3)
read_excel_xlsx(book_name_xlsx, sheet_name_xlsx)
⾄此成功！

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。