Openpyxl 插入数据添加数据

pip install xlwt
 

程序循环多次，每次向指定的excel中追加写入数据

# 使用openpyxl追加写入数据到Excel
import openpyxl
import os
from openpyxl.styles import Alignmentdef create_excel_xlsx(path, sheet_name):workbook = openpyxl.Workbook()sheet = workbook.activesheet.title = sheet_nameheader = ['id','sex','age','provience','job']sheet.append(header)alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)sheet.alignment = alignmentworkbook.save(path)def write_excel_xlsx_append(path, value, truncate_sheet=False):# 如果不存在就创建该excelif not os.path.exists(path):create_excel_xlsx(path, 'Sheet1')#value = value.values #将dataframe转为arraydata = openpyxl.load_workbook(path)# 取第一张表sheetnames = data.sheetnamessheet = data[sheetnames[0]]sheet = data.activeif(truncate_sheet): #truncate_sheet为True，覆盖原表中的数据startrows = 0else:# print(sheet.title)  # 输出表名startrows = sheet.max_row  # 获得行数index = len(value)for i in range(0, index):for j in range(0, len(value[i])):sheet.cell(row=startrows + i + 1, column=j + 1, value=str(value[i][j]))#调整对齐alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)data.alignment = alignmentdata.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 in sheet.rows:for cell in row:print(cell.value, "\t", end="")print()book_name_xlsx = 'xlsx格式测试工作簿.xlsx'
sheet_name_xlsx = 'xlsx格式测试表'
value3 = [["1111", "女", "25", "石家庄", "运维工程师"],["222", "男", "55", "南京", "饭店老板"],["333", "女", "27", "苏州", "保安"], ]write_excel_xlsx_append(book_name_xlsx, value3, False)
read_excel_xlsx(book_name_xlsx, 'Sheet1')

参考：使用openpyxl追加写入Excel的成功案例，以及遇到的坑_openpyxl 写入没报错,但是文件损坏-CSDN博客