1 # -*- coding:utf-8 -*-
2 import pandas as pd
3 import openpyxl
4 import numpy as np
5 # 有以下三种格式的数据，将它们添加进新sheet中以及添加进已有的sheet中
6 list1 = [['张三', '男', '未婚', 20], ['李四', '男', '已婚', 28], ['小红', '女', '未婚', 18], ['小芳', '女', '已婚', 25]]
7 list2 = {'name': ['a', 'b'], 'age': [18, 20]}
8 list3 = [{'name': 'tf', 'age': 10}, {'name': 'tom', 'age': 18}]
9
10 list4 = [['易', '男', '未婚', 20], ['顾', '男', '已婚', 25]]
11 list5 = {'name': ['m', 'h'], 'age': [10, 50]}
12 list6 = [{'name': 'jerry', 'age': 25}, {'name': 'kk', 'age': 18}]
13
14 # --若没使用to_excel可不使用pd.ExcelWriter打开文件，直接用openpyxl.load_workbook
15 # 一、 list1-3如何添加进新sheet中且不覆盖原数据
16 # （1）list1类型数据添加进新sheet中
17 excel_read = pd.ExcelWriter(r'C:UsersAdministratorDesktop est4.xlsx', engine='openpyxl')
18 excel_book = openpyxl.load_workbook(excel_read.path)
19 work_sheet = excel_book.create_sheet(title='new_list1') # 增加新sheet名
20 data1 = ['姓名', '性别', '婚姻', '年龄'] # 表头放入
21 work_sheet.append(data1)
22 for i in list1:
23 work_sheet.append(i)
24 excel_book.save(excel_read)
25
26 # （2）list2类型数据添加进新sheet中
27 # 第一种写法:使用to_excel--标题是有边框的
28 excel_read = pd.ExcelWriter(r'C:UsersAdministratorDesktop est4.xlsx', engine='openpyxl')
29 # 若报错：AttributeError: ‘Workbook’ object has no attribute ‘add_worksheet’
30 # 修改pd.ExcelWriter(xxx, engine=‘openpyxl’)下述其他代码类似
31 excel_book = openpyxl.load_workbook(excel_read.path)
32 excel_read.book = excel_book # 有to_excel时这段代码才需要且后面的保存需为excel_read.save()
33 data2 = pd.DataFrame(list2)
34 data2.to_excel(excel_read, sheet_name='new_list2', index=False)
35 excel_read.save()
36
37 # 第二种写法:利用numpy拆开字典数据，np.array(..)拆标题、..to_numpy()拆数据，然后分别append进sheet--但是这种拆分是没有格式
38 # (但可以设置格式)
39 excel_read = pd.ExcelWriter(r'C:UsersAdministratorDesktop est4.xlsx', engine='openpyxl')
40 excel_book = openpyxl.load_workbook(excel_read.path)
41 new_sheet = excel_book.create_sheet('new_list2')
42 data2 = pd.DataFrame(list2)
43 column = np.array([data2.columns]) # 将行标题拿出[['name' 'age']]
44 new_sheet.append(list(column[0])) # 将标题写进sheet中
45 value = data2.to_numpy() # 将值拿出[['a' 18] ['b' 20]]
46 for i in range(len(value)):
47 new_sheet.append(list(value[i])) # 值写入sheet中
48 excel_book.save(excel_read)
49
50 # （3）list3类型数据添加进新sheet中
51 # 第一种写法：手动拆字典，将标题和数据append进新sheet中
52 excel_read = pd.ExcelWriter(r'C:UsersAdministratorDesktop est4.xlsx', engine='openpyxl')
53 excel_book = openpyxl.load_workbook(excel_read.path)
54 work_sheet3 = excel_book.create_sheet(title='new_list3') # 创建新sheet
55 data3 = [j for j, k in list3[1].items()] # 插入行标题
56 work_sheet3.append(data3)
57 for i in list3:
58 list_list = []
59 for j, k in i.items():
60 list_list.append(k)
61 work_sheet3.append(list_list) # 转化成列表直接添加到sheet中
62 excel_book.save(excel_read)
63
64 # 第二种写法：使用pd.DataFrame、to_excel
65 excel_read.book = excel_book
66 data3_1 = pd.DataFrame(list3)
67 data3_1.to_excel(excel_read, sheet_name='new_list3', index=False)
68 excel_read.save()
69 # 第三种写法：利用numpy拆开字典数据，np.array(..)拆标题、..to_numpy()拆数据，然后分别append进sheet，与list2的第二种写法一样
70
71 # 二、list4-6如何添加进已存在的sheet中且不覆盖原数据
72 # 以下追加新的数据到已有sheet中，没有使用to_excel，因此也可以不使用pd.ExcelWriter，直接openpyxl.load_workbook(....)
73 # （1）list4类型数据添加进已有sheet中
74 excel_read = pd.ExcelWriter(r'C:UsersAdministratorDesktop est4.xlsx', engine='openpyxl')
75 excel_book = openpyxl.load_workbook(excel_read.path)
76 work_sheet = excel_book['new_list1']
77 for i in list4:
78 work_sheet.append(i) # 自动加入到最后一行的下面
79 excel_book.save(excel_read)
80
81 # （2）list5类型数据添加进已有sheet中
82 excel_read = pd.ExcelWriter(r'C:UsersAdministratorDesktop est4.xlsx', engine='openpyxl')
83 excel_book = openpyxl.load_workbook(excel_read.path)
84 # excel_read.book = excel_book # 有to_excel时这段代码才需要，且后面的保存需为excel_read.save()，
85 # 若是没使用to_excel且后面的保存又为excel_read.save()时，这段代码不写上则excel会失去内容并打不开,
86 # 所以若不写这段代码，直接将保存信息改为excel_book.save(excel_read)即可
87 work_sheet = excel_book['new_list2']
88 data5 = pd.DataFrame(list5)
89 value = data5.to_numpy() # [['m' 10] ['h' 50]]
90 for i in range(len(value)):
91 work_sheet.append(list(value[i])) # append的内容必须是可迭代对象，里面的value必须是list类型
92 # 也可使用下面的代码
93 """
94 rows = work_sheet.max_row
95 for i in range(len(value)):
96 for j in range(len(value[i])):
97 work_sheet.cell(i+1+rows, j+1, value[i][j])
98 """
99 excel_book.save(excel_read)
100 # （3）list6类型数据添加进已有sheet中与list5类型一样的方法
101
102
103 # 三、使用openpyxl在已有excel中写入及追加数据
104 def write_xlsx(path, sheetname, value):
105 index = len(value)
106 # 下面的三条注释代码是创建新的excel及sheet，若原excel存在数据则被覆盖，不是在已有excel中新建sheet
107 # workbook = openpyxl.Workbook()
108 # sheet = workbook.active
109 # sheet.title = sheetname
110 workbook = openpyxl.load_workbook(path) # 打开已存在的excel
111 sheet = workbook.create_sheet(sheetname) # 在excel中新建sheet
112 for i in range(index):
113 for j in range(len(value[i])):
114 sheet.cell(i+1, j+1, str(value[i][j])) # 因为这里是标题，所以要str字符转换
115 workbook.save(path)
116 print("xlsx格式表格数据写入成功！")
117
118
119 def append_xlsx(path, sheetname, value):
120 index = len(value)
121 workbook = openpyxl.load_workbook(path)
122 sheet = workbook[sheetname]
123 """
124 rows = sheet.max_row
125 for i in range(index):
126 for j in range(len(value[i])):
127 sheet.cell(i+1+rows, j+1, value[i][j]) # 保持数据的原型
128 """
129 # 也可直接使用下面的代码追加数据
130 for i in range(index):
131 sheet.append(list(value[i])) # append的内容必须是可迭代对象，里面的value必须是list类型
132 workbook.save(path)
133 print("xlsx格式表格数据追加成！")
134
135
136 df = pd.DataFrame({
137 'name': ['Jack', 'kate', 'Jim', 'Steve', 'Mike', 'Lucy'],
138 'Gender': ['M', 'F', 'M', 'M', 'M', 'F'],
139 'Age': [17, 15, 22, 34, 18, 23],
140 'Height': [173, 168, 164, 180, 182, 190],
141 'Hobby': ['Reading', 'Music', 'Football', 'Reading', 'Cooking', 'Running']})
142 df1 = df.copy()
143 column_name = np.array([df1.columns])
144 write_xlsx(path=r'C:UsersAdministratorDesktop est5.xlsx', sheetname='newsheet', value=column_name)
145 append_xlsx(path=r'C:UsersAdministratorDesktop est5.xlsx', sheetname='newsheet', value=df1.to_numpy())
146
147 # 若是不使用def定义函数,直接写成一串代码，可直接写成下面的形式
148 excel_book = openpyxl.load_workbook(r'C:UsersAdministratorDesktop est5.xlsx')
149 new_sheet = excel_book.create_sheet('new_sheet')
150 data = pd.DataFrame(df)
151 column = np.array([data.columns]) # 将行标题拿出[['name' 'Gender' ....]]
152 new_sheet.append(list(column[0])) # 将标题写进sheet中
153 value = data.to_numpy() # 将值拿出[['Jack' 'M' 17 173 'Reading']['kate' 'F' 15 168 'Music'].....]
154 for i in range(len(value)):
155 new_sheet.append(list(value[i])) # 值写入sheet中
156 excel_book.save(r'C:UsersAdministratorDesktop est5.xlsx')
157
158 # 四、使用xlrd/xlwt在已有excel中写入和追加数据
159 import xlrd # 读取
160 import xlwt # 写入
161 from xlutils.copy import copy
162 import pandas as pd
163 import numpy as np
164
165
166 def write_xls(path, sheetname, value):
167 index = len(value) # 获取需要写入数据的行数
168 # 下面注释的两条代码是直接新建excel及sheet，若原excel存在数据则被覆盖，不是在已有excel中新建sheet
169 # workbook = xlwt.Workbook() # 创建一个工作簿
170 # sheet = workbook.add_sheet(sheetname) # 在工作簿中新建一个表格
171 workbook = xlrd.open_workbook(path, formatting_info=True) # 打开工作簿
172 new_workbook = copy(wb=workbook) # 复制原excel中数据
173 sheet = new_workbook.add_sheet(sheetname, cell_overwrite_ok=True) # 在工作簿中新建一个表格
174 for i in range(index):
175 for j in range(len(value[i])):
176 sheet.write(i, j, value[i][j]) # 向表格中写入数据（对应的行和列）
177 new_workbook.save(path)
178 print("xls格式表格写入成功！")
179
180
181 def append_xls(path, sheetname, value):
182 index = len(value) # 获取需要写入的数据的行数
183 workbook = xlrd.open_workbook(path, formatting_info=True) # 打开工作簿
184 # sheets = workbook.sheet_names() # 获取工作簿里的所有表格
185 worksheet = workbook.sheet_by_name(sheetname) # 获取工作簿中sheetname的sheet
186 rows_old = worksheet.nrows # 获取表格中已存在的数据的行数
187 new_workbook = copy(wb=workbook) # 将xlrd对象拷贝转化为xlwt对象
188 new_worksheet = new_workbook.get_sheet(sheetname) # 获取转化后的sheet，xlwt获取sheet信息：workbook.get_sheet(索引号或者'sheet名')
189 for i in range(index):
190 for j in range(len(value[i])):
191 new_worksheet.write(i + rows_old, j, value[i][j]) # 追加写入数据，注意从 i+rows_old 行开始
192 new_workbook.save(path)
193 print("xls格式表格追加写入成功！")
194
195
196 df = pd.DataFrame({
197 'name': ['Jack', 'kate', 'Jim', 'Steve', 'Mike', 'Lucy'],
198 'Gender': ['M', 'F', 'M', 'M', 'M', 'F'],
199 'Age': [17, 15, 22, 34, 18, 23],
200 'Height': [173, 168, 164, 180, 182, 190],
201 'Hobby': ['Reading', 'Music', 'Football', 'Reading', 'Cooking', 'Running']})
202 df1 = df.copy()
203 column_name = np.array([df1.columns])
204 write_xls(path=r'C:UsersAdministratorDesktop est2_xlwt.xls', sheetname='newsheet', value=column_name)
205 append_xls(path=r'C:UsersAdministratorDesktop est2_xlwt.xls', sheetname='newsheet', value=df1.to_numpy())
206 print(column_name) # [['name' 'Gender' 'Age' 'Height' 'Hobby']] 列标题
207 print(df1.to_numpy()) # 所有数据以列表形式[['Jack' 'M' 17 173 'Reading']['kate' 'F' 15 168 'Music'].....]
208 # 由于xlwt没有append属性，因此不能直接new_sheet.append(list(column_name[0])) 还需要new_sheet.write(...)一步步来,即按照上面的函数步骤走