|
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 xlrdxlsx =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 xlwtnew_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 xlrdimport xlwtfrom xlutils.copy import copy#设置样式style =xlwt.XFStyle()#字体font =xlwt.Font()font.name='微软雅黑'font.bold = Truefont.height =180style.font= font#边界borders = xlwt.Borders()borders.top = xlwt.Borders.THINborders.bottom= xlwt.Borders.THINborders.left = xlwt.Borders.THINborders.right = xlwt.Borders.THINstyle.borders=borders#对齐alignment =xlwt.Alignment()alignment.horz = xlwt.Alignment.HORZ_CENTERalignment.vert = xlwt.Alignment.VERT_CENTERstyle.alignment=alignmenttem_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 xlrdimport xlwtfrom xlutils.copy import copyxlsx = 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 = Truefont.height = 360style.font = fontborders = xlwt.Borders()borders.top = xlwt.Borders.THINborders.bottom = xlwt.Borders.THINborders.left = xlwt.Borders.THINborders.right = xlwt.Borders.THINstyle.borders = bordersalignment = xlwt.Alignment()alignment.horz = xlwt.Alignment.HORZ_CENTERalignment.vert = xlwt.Alignment.VERT_CENTERstyle.alignment = alignmentnew_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、 import pymysqldatabase = 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 zxmimport xlrdimport xlwtfrom xlutils.copy import copyimport pymysqldatabase = 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 = Truefont.height = 360style.font = fontborders = xlwt.Borders()borders.top = xlwt.Borders.THINborders.bottom = xlwt.Borders.THINborders.left = xlwt.Borders.THINborders.right = xlwt.Borders.THINstyle.borders = bordersalignment = xlwt.Alignment()alignment.horz = xlwt.Alignment.HORZ_CENTERalignment.vert = xlwt.Alignment.VERT_CENTERstyle.alignment = alignmentnew_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') 总结： 先思路再行动 |
|
|
目标增加后昵称如图所示：
