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
表格中的数据如图：
import xlrd 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...）-》行和列写入值-》保存工作簿
import xlwt 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支持较好
import xlrd import xlwt from xlutils.copy import copy #设置样式 style =xlwt.XFStyle() #字体 font =xlwt.Font() font.name='微软雅黑' font.bold = True font.height =180 style.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')
项目实践二：统计表格
原始表格
完成目标表格：
import xlrd import xlwt from xlutils.copy import copy 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 in all_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 = 360 style.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')
项目三：根据项目二 利用mysql完成以上项目
完成顺序：安装mysql-》将表格导入数据库-》用python完成统计并写入统计表
用python完成统计-》连接数据库 -》处理数据
数据库表设计
手动导入表格到数据库
1、
2、
3、
4、直接下一步
5、
除id外一一对应数据库表中的值，导入后检查数值是否一致
import pymysql 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 import xlrd import xlwt from xlutils.copy import copy import pymysql 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 in result: 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 = 360 style.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')
总结：
先思路再行动
