# -*- coding:utf-8 -*-
import pandas as pd
import openpyxl
import numpy as np
# 有以下三种格式的数据，将它们添加进新sheet中以及添加进已有的sheet中
list1 = [['张三', '男', '未婚', 20], ['李四', '男', '已婚', 28], ['小红', '女', '未婚', 18], ['小芳', '女', '已婚', 25]]
list2 = {'name': ['a', 'b'], 'age': [18, 20]}
list3 = [{'name': 'tf', 'age': 10}, {'name': 'tom', 'age': 18}]
list4 = [['易烊千玺', '男', '未婚', 20], ['顾未易', '男', '已婚', 25]]
list5 = {'name': ['m', 'h'], 'age': [10, 50]}
list6 = [{'name': 'jerry', 'age': 25}, {'name': 'kk', 'age': 18}]
# --若没使用to_excel可不使用pd.ExcelWriter打开文件，直接用openpyxl.load_workbook
# 一、 list1-3如何添加进新sheet中且不覆盖原数据
# （1）list1类型数据添加进新sheet中
excel_read = pd.ExcelWriter(r'C:\Users\Administrator\Desktop\test4.xlsx', engine='openpyxl')
excel_book = openpyxl.load_workbook(excel_read.path)
work_sheet = excel_book.create_sheet(title='new_list1') # 增加新sheet名
data1 = ['姓名', '性别', '婚姻', '年龄'] # 表头放入
work_sheet.append(data1)
for i in list1:
work_sheet.append(i)
excel_book.save(excel_read)
# （2）list2类型数据添加进新sheet中
# 第一种写法:使用to_excel--标题是有边框的
excel_read = pd.ExcelWriter(r'C:\Users\Administrator\Desktop\test4.xlsx', engine='openpyxl')
# 若报错：AttributeError: ‘Workbook’ object has no attribute ‘add_worksheet’
# 修改pd.ExcelWriter(xxx, engine=‘openpyxl’)下述其他代码类似
excel_book = openpyxl.load_workbook(excel_read.path)
excel_read.book = excel_book # 有to_excel时这段代码才需要且后面的保存需为excel_read.save()
data2 = pd.DataFrame(list2)
data2.to_excel(excel_read, sheet_name='new_list2', index=False)
excel_read.save()
# 第二种写法:利用numpy拆开字典数据，np.array(..)拆标题、..to_numpy()拆数据，然后分别append进sheet--但是这种拆分是没有格式
# (但可以设置格式)
excel_read = pd.ExcelWriter(r'C:\Users\Administrator\Desktop\test4.xlsx', engine='openpyxl')
excel_book = openpyxl.load_workbook(excel_read.path)
new_sheet = excel_book.create_sheet('new_list2')
data2 = pd.DataFrame(list2)
column = np.array([data2.columns]) # 将行标题拿出[['name' 'age']]
new_sheet.append(list(column[0])) # 将标题写进sheet中
value = data2.to_numpy() # 将值拿出[['a' 18] ['b' 20]]
for i in range(len(value)):
new_sheet.append(list(value[i])) # 值写入sheet中
excel_book.save(excel_read)
# （3）list3类型数据添加进新sheet中
# 第一种写法：手动拆字典，将标题和数据append进新sheet中
excel_read = pd.ExcelWriter(r'C:\Users\Administrator\Desktop\test4.xlsx', engine='openpyxl')
excel_book = openpyxl.load_workbook(excel_read.path)
work_sheet3 = excel_book.create_sheet(title='new_list3') # 创建新sheet
data3 = [j for j, k in list3[1].items()] # 插入行标题
work_sheet3.append(data3)
for i in list3:
list_list = []
for j, k in i.items():
list_list.append(k)
work_sheet3.append(list_list) # 转化成列表直接添加到sheet中
excel_book.save(excel_read)
# 第二种写法：使用pd.DataFrame、to_excel
excel_read.book = excel_book
data3_1 = pd.DataFrame(list3)
data3_1.to_excel(excel_read, sheet_name='new_list3', index=False)
excel_read.save()
# 第三种写法：利用numpy拆开字典数据，np.array(..)拆标题、..to_numpy()拆数据，然后分别append进sheet，与list2的第二种写法一样
# 二、list4-6如何添加进已存在的sheet中且不覆盖原数据
# 以下追加新的数据到已有sheet中，没有使用to_excel，因此也可以不使用pd.ExcelWriter，直接openpyxl.load_workbook(....)
# （1）list4类型数据添加进已有sheet中
excel_read = pd.ExcelWriter(r'C:\Users\Administrator\Desktop\test4.xlsx', engine='openpyxl')
excel_book = openpyxl.load_workbook(excel_read.path)
work_sheet = excel_book['new_list1']
for i in list4:
work_sheet.append(i) # 自动加入到最后一行的下面
excel_book.save(excel_read)
# （2）list5类型数据添加进已有sheet中
excel_read = pd.ExcelWriter(r'C:\Users\Administrator\Desktop\test4.xlsx', engine='openpyxl')
excel_book = openpyxl.load_workbook(excel_read.path)
# excel_read.book = excel_book # 有to_excel时这段代码才需要，且后面的保存需为excel_read.save()，
# 若是没使用to_excel且后面的保存又为excel_read.save()时，这段代码不写上则excel会失去内容并打不开,
# 所以若不写这段代码，直接将保存信息改为excel_book.save(excel_read)即可
work_sheet = excel_book['new_list2']
data5 = pd.DataFrame(list5)
value = data5.to_numpy() # [['m' 10] ['h' 50]]
for i in range(len(value)):
work_sheet.append(list(value[i])) # append的内容必须是可迭代对象，里面的value必须是list类型
# 也可使用下面的代码
"""
rows = work_sheet.max_row
for i in range(len(value)):
for j in range(len(value[i])):
work_sheet.cell(i+1+rows, j+1, value[i][j])
"""
excel_book.save(excel_read)
# （3）list6类型数据添加进已有sheet中与list5类型一样的方法
# 三、使用openpyxl在已有excel中写入及追加数据
def write_xlsx(path, sheetname, value):
index = len(value)
# 下面的三条注释代码是创建新的excel及sheet，若原excel存在数据则被覆盖，不是在已有excel中新建sheet
# workbook = openpyxl.Workbook()
# sheet = workbook.active
# sheet.title = sheetname
workbook = openpyxl.load_workbook(path) # 打开已存在的excel
sheet = workbook.create_sheet(sheetname) # 在excel中新建sheet
for i in range(index):
for j in range(len(value[i])):
sheet.cell(i+1, j+1, str(value[i][j])) # 因为这里是标题，所以要str字符转换
workbook.save(path)
print("xlsx格式表格数据写入成功！")
def append_xlsx(path, sheetname, value):
index = len(value)
workbook = openpyxl.load_workbook(path)
sheet = workbook[sheetname]
"""
rows = sheet.max_row
for i in range(index):
for j in range(len(value[i])):
sheet.cell(i+1+rows, j+1, value[i][j]) # 保持数据的原型
"""
# 也可直接使用下面的代码追加数据
for i in range(index):
sheet.append(list(value[i])) # append的内容必须是可迭代对象，里面的value必须是list类型
workbook.save(path)
print("xlsx格式表格数据追加成！")
df = pd.DataFrame({
'name': ['Jack', 'kate', 'Jim', 'Steve', 'Mike', 'Lucy'],
'Gender': ['M', 'F', 'M', 'M', 'M', 'F'],
'Age': [17, 15, 22, 34, 18, 23],
'Height': [173, 168, 164, 180, 182, 190],
'Hobby': ['Reading', 'Music', 'Football', 'Reading', 'Cooking', 'Running']})
df1 = df.copy()
column_name = np.array([df1.columns])
write_xlsx(path=r'C:\Users\Administrator\Desktop\test5.xlsx', sheetname='newsheet', value=column_name)
append_xlsx(path=r'C:\Users\Administrator\Desktop\test5.xlsx', sheetname='newsheet', value=df1.to_numpy())
# 若是不使用def定义函数,直接写成一串代码，可直接写成下面的形式
excel_book = openpyxl.load_workbook(r'C:\Users\Administrator\Desktop\test5.xlsx')
new_sheet = excel_book.create_sheet('new_sheet')
data = pd.DataFrame(df)
column = np.array([data.columns]) # 将行标题拿出[['name' 'Gender' ....]]
new_sheet.append(list(column[0])) # 将标题写进sheet中
value = data.to_numpy() # 将值拿出[['Jack' 'M' 17 173 'Reading']['kate' 'F' 15 168 'Music'].....]
for i in range(len(value)):
new_sheet.append(list(value[i])) # 值写入sheet中
excel_book.save(r'C:\Users\Administrator\Desktop\test5.xlsx')
# 四、使用xlrd/xlwt在已有excel中写入和追加数据
import xlrd # 读取
import xlwt # 写入
from xlutils.copy import copy
import pandas as pd
import numpy as np
def write_xls(path, sheetname, value):
index = len(value) # 获取需要写入数据的行数
# 下面注释的两条代码是直接新建excel及sheet，若原excel存在数据则被覆盖，不是在已有excel中新建sheet
# workbook = xlwt.Workbook() # 创建一个工作簿
# sheet = workbook.add_sheet(sheetname) # 在工作簿中新建一个表格
workbook = xlrd.open_workbook(path, formatting_info=True) # 打开工作簿
new_workbook = copy(wb=workbook) # 复制原excel中数据
sheet = new_workbook.add_sheet(sheetname, cell_overwrite_ok=True) # 在工作簿中新建一个表格
for i in range(index):
for j in range(len(value[i])):
sheet.write(i, j, value[i][j]) # 向表格中写入数据（对应的行和列）
new_workbook.save(path)
print("xls格式表格写入成功！")
def append_xls(path, sheetname, value):
index = len(value) # 获取需要写入的数据的行数
workbook = xlrd.open_workbook(path, formatting_info=True) # 打开工作簿
# sheets = workbook.sheet_names() # 获取工作簿里的所有表格
worksheet = workbook.sheet_by_name(sheetname) # 获取工作簿中sheetname的sheet
rows_old = worksheet.nrows # 获取表格中已存在的数据的行数
new_workbook = copy(wb=workbook) # 将xlrd对象拷贝转化为xlwt对象
new_worksheet = new_workbook.get_sheet(sheetname) # 获取转化后的sheet，xlwt获取sheet信息：workbook.get_sheet(索引号或者'sheet名')
for i in range(index):
for j in range(len(value[i])):
new_worksheet.write(i + rows_old, j, value[i][j]) # 追加写入数据，注意从 i+rows_old 行开始
new_workbook.save(path)
print("xls格式表格追加写入成功！")
df = pd.DataFrame({
'name': ['Jack', 'kate', 'Jim', 'Steve', 'Mike', 'Lucy'],
'Gender': ['M', 'F', 'M', 'M', 'M', 'F'],
'Age': [17, 15, 22, 34, 18, 23],
'Height': [173, 168, 164, 180, 182, 190],
'Hobby': ['Reading', 'Music', 'Football', 'Reading', 'Cooking', 'Running']})
df1 = df.copy()
column_name = np.array([df1.columns])
write_xls(path=r'C:\Users\Administrator\Desktop\test2_xlwt.xls', sheetname='newsheet', value=column_name)
append_xls(path=r'C:\Users\Administrator\Desktop\test2_xlwt.xls', sheetname='newsheet', value=df1.to_numpy())
print(column_name) # [['name' 'Gender' 'Age' 'Height' 'Hobby']] 列标题
print(df1.to_numpy()) # 所有数据以列表形式[['Jack' 'M' 17 173 'Reading']['kate' 'F' 15 168 'Music'].....]
# 由于xlwt没有append属性，因此不能直接new_sheet.append(list(column_name[0])) 还需要new_sheet.write(...)一步步来,即按照上面的函数步骤走