python+excel 自动化,从此做表不加班
用python来操作excel
1、读写excel,自动化从此开始
2、套用模板,一键完成漂流样式
3、整合项目:用excel和python自动生成统计报表
配合数据库自动生成报表
1、mysql简易安装
2、创建mysql数据库
3、数据库的增删改查
整合项目:自动生成
1、mysql+python自动生成统计报表,让报表震惊
准备:
安装xlrd,xlwt
pip install xlrd #用来读excel
pip install xlwt #用来写excel
读顺序: 路径-》工作簿(表格)-》工作表(sheet1...)-》行和列
已有表格及路径:D:\autoOffice\autoExcel.xlsx
表格中的数据如图:
importxlrd
xlsx=xlrd.open_workbook('D:/autoOffice/autoExcel.xlsx')#table =xlsx.sheet_by_index(0) #通过索引查表
table =xlsx.sheet_by_name('学生表') #通过表名查表#查单元格3种方式(任选一种)
print(table.cell_value(1,1))print(table.cell(1,1).value)print(table.row(1)[1].value)
结果:
================ RESTART: D:/Python/Python37/zxm/autoExcel.py ================赵大
赵大
赵大
写顺序: 路径-》工作簿(表格)-》增加工作表(sheet1...)-》行和列写入值-》保存工作簿
importxlwt
new_workbook=xlwt.Workbook()
worksheet=new_workbook.add_sheet('new_test')
worksheet.write(1,1,'赵一')
new_workbook.save('D:/autoOffice/autoExcel.xlsx')
项目实践一:
查看表格,新增一列昵称,原始情况和目标查看图片,C列需建立样式
目标增加后昵称如图所示:
完成顺序: 复制模板-》设置格式-》写入内容-》保存工作簿
复制模板:tem_excel =xlrd.open_workbook('D:/autoOffice/autoExcel.xlsx',formatting_info=True)
设置样式:style =xlwt.XFStyle()
写入内容:new_sheet.write(i+1,2,small_name[i],style)
保存工作簿:new_excel.save('D:/autoOffice/autoExcel2.xls') PS:包使用的xlutils 保存为xls支持较好
importxlrdimportxlwtfrom xlutils.copy importcopy#设置样式
style =xlwt.XFStyle()#字体
font =xlwt.Font()
font.name='微软雅黑'font.bold=True
font.height=180style.font=font#边界
borders =xlwt.Borders()
borders.top=xlwt.Borders.THIN
borders.bottom=xlwt.Borders.THIN
borders.left=xlwt.Borders.THIN
borders.right=xlwt.Borders.THIN
style.borders=borders#对齐
alignment =xlwt.Alignment()
alignment.horz=xlwt.Alignment.HORZ_CENTER
alignment.vert=xlwt.Alignment.VERT_CENTER
style.alignment=alignment
tem_excel=xlrd.open_workbook('D:/autoOffice/autoExcel.xlsx',formatting_info=True)
tem_sheet=tem_excel.sheet_by_index(0)
new_excel=copy(tem_excel)
new_sheet=new_excel.get_sheet(0)
small_name=['大大','老二','老三','老四','老五']for i in range(5):
new_sheet.write(i+1,2,small_name[i],style)
new_excel.save('D:/autoOffice/autoExcel2.xls')
样式写入表格
项目实践二:统计表格
原始表格
完成目标表格:
importxlrdimportxlwtfrom xlutils.copy importcopy
xlsx= xlrd.open_workbook('d:/7月下旬入库表.xlsx')
table=xlsx.sheet_by_index(0)
all_data=[]for n in range(1, table.nrows):
company= table.cell(n, 1).value
price= table.cell(n, 3).value
weight= table.cell(n, 4).value
data= {'company': company, 'weight': weight, 'price': price}
all_data.append(data)#以下内容可以用pandas的groupby轻易实现,这里不引入新知识,使用一个笨办法
a_weight =[]
a_total_price=[]
b_weight=[]
b_total_price=[]
c_weight=[]
c_total_price=[]
d_weight=[]
d_total_price=[]for i inall_data:if i['company'] == '张三粮配':
a_weight.append(i['weight'])
a_total_price.append(i['weight'] * i['price'])if i['company'] == '李四粮食':
b_weight.append(i['weight'])
b_total_price.append(i['weight'] * i['price'])if i['company'] == '王五小麦':
c_weight.append(i['weight'])
c_total_price.append(i['weight'] * i['price'])if i['company'] == '赵六麦子专营':
d_weight.append(i['weight'])
d_total_price.append(i['weight'] * i['price'])
tem_excel= xlrd.open_workbook('D:/统计表_模板.xls', formatting_info=True)
tem_sheet=tem_excel.sheet_by_index(0)
new_excel=copy(tem_excel)
new_sheet=new_excel.get_sheet(0)
style=xlwt.XFStyle()
font=xlwt.Font()
font.name= '微软雅黑'font.bold=True
font.height= 360style.font=font
borders=xlwt.Borders()
borders.top=xlwt.Borders.THIN
borders.bottom=xlwt.Borders.THIN
borders.left=xlwt.Borders.THIN
borders.right=xlwt.Borders.THIN
style.borders=borders
alignment=xlwt.Alignment()
alignment.horz=xlwt.Alignment.HORZ_CENTER
alignment.vert=xlwt.Alignment.VERT_CENTER
style.alignment=alignment
new_sheet.write(2, 1, len(a_weight), style)
new_sheet.write(2, 2, round(sum(a_weight), 2), style)
new_sheet.write(2, 3, round(sum(a_total_price), 2), style)
new_sheet.write(3, 1, len(b_weight), style)
new_sheet.write(3, 2, round(sum(b_weight), 2), style)
new_sheet.write(3, 3, round(sum(b_total_price), 2), style)
new_sheet.write(4, 1, len(c_weight), style)
new_sheet.write(4, 2, round(sum(c_weight), 2), style)
new_sheet.write(4, 3, round(sum(c_total_price), 2), style)
new_sheet.write(5, 1, len(d_weight), style)
new_sheet.write(5, 2, round(sum(d_weight), 2), style)
new_sheet.write(5, 3, round(sum(d_total_price), 2), style)
new_excel.save('d:/7月下旬统计表.xls')
python完成统计表格
项目三:根据项目二 利用mysql完成以上项目
完成顺序:安装mysql-》将表格导入数据库-》用python完成统计并写入统计表
用python完成统计-》连接数据库 -》处理数据
数据库表设计
手动导入表格到数据库
1、
2、
3、
4、直接下一步
5、
除id外一一对应数据库表中的值,导入后检查数值是否一致
importpymysql
database= pymysql.connect("127.0.0.1", "root", "root", "autooffice", charset='utf8')
cursor=database.cursor()
sql= "SELECT company ,COUNT(company),SUM(weight),SUM(weight*price) FROM data GROUP BY company"cursor.execute(sql)
result=cursor.fetchall()print(result)#查看数据是否显示
连接数据库
#Author zxm
importxlrdimportxlwtfrom xlutils.copy importcopyimportpymysql
database= pymysql.connect("127.0.0.1", "root", "root", "autooffice", charset='utf8')
cursor=database.cursor()
sql= "SELECT company ,COUNT(company),SUM(weight),SUM(weight*price) FROM data GROUP BY company"cursor.execute(sql)
result=cursor.fetchall()#print(result)
for i inresult:if i[0] == '张三粮配':
a_num= i[1]
a_weight= i[2]
a_total_price= i[3]elif i[0] == '李四粮食':
b_num= i[1]
b_weight= i[2]
b_total_price= i[3]elif i[0] == '王五小麦':
c_num= i[1]
c_weight= i[2]
c_total_price= i[3]elif i[0] == '赵六麦子专营':
d_num= i[1]
d_weight= i[2]
d_total_price= i[3]
tem_excel= xlrd.open_workbook('D:/autoOffice/7月下旬统计表.xls', formatting_info=True)
tem_sheet=tem_excel.sheet_by_index(0)
new_excel=copy(tem_excel)
new_sheet=new_excel.get_sheet(0)
style=xlwt.XFStyle()
font=xlwt.Font()
font.name= '微软雅黑'font.bold=True
font.height= 360style.font=font
borders=xlwt.Borders()
borders.top=xlwt.Borders.THIN
borders.bottom=xlwt.Borders.THIN
borders.left=xlwt.Borders.THIN
borders.right=xlwt.Borders.THIN
style.borders=borders
alignment=xlwt.Alignment()
alignment.horz=xlwt.Alignment.HORZ_CENTER
alignment.vert=xlwt.Alignment.VERT_CENTER
style.alignment=alignment
new_sheet.write(2, 1, a_num, style)
new_sheet.write(2, 2, a_weight, style)
new_sheet.write(2, 3, a_total_price, style)
new_sheet.write(3, 1, b_num, style)
new_sheet.write(3, 2, b_weight, style)
new_sheet.write(3, 3, b_total_price, style)
new_sheet.write(4, 1, c_num, style)
new_sheet.write(4, 2, c_weight, style)
new_sheet.write(4, 3, c_total_price, style)
new_sheet.write(5, 1, d_num, style)
new_sheet.write(5, 2, d_weight, style)
new_sheet.write(5, 3, d_total_price, style)
new_excel.save('d:/autoOffice/7月下旬统计表.xls')
所有代码
总结:
先思路再行动