python+excel 自動(dòng)化，從此做表不加班

用python來(lái)操作excel

1、讀寫excel，自動(dòng)化從此開始

2、套用模板，一鍵完成漂流樣式

3、整合項(xiàng)目：用excel和python自動(dòng)生成統(tǒng)計(jì)報(bào)表

配合數(shù)據(jù)庫(kù)自動(dòng)生成報(bào)表

1、mysql簡(jiǎn)易安裝

2、創(chuàng)建mysql數(shù)據(jù)庫(kù)

3、數(shù)據(jù)庫(kù)的增刪改查

整合項(xiàng)目：自動(dòng)生成

1、mysql+python自動(dòng)生成統(tǒng)計(jì)報(bào)表，讓報(bào)表震驚

準(zhǔn)備：

安裝xlrd，xlwt

pip install xlrd   #用來(lái)讀excel

pip install xlwt   #用來(lái)寫excel

讀順序：  路徑-》工作簿（表格）-》工作表（sheet1...）-》行和列

已有表格及路徑：D:\autoOffice\autoExcel.xlsx

表格中的數(shù)據(jù)如圖：

import xlrdxlsx =xlrd.open_workbook('D:/autoOffice/autoExcel.xlsx')#table =xlsx.sheet_by_index(0) #通過(guò)索引查表table =xlsx.sheet_by_name('學(xué)生表') #通過(guò)表名查表#查單元格3種方式（任選一種）print(table.cell_value(1,1))print(table.cell(1,1).value)print(table.row(1)[1].value)

結(jié)果：

================ 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')

 項(xiàng)目實(shí)踐一：

查看表格，新增一列昵稱，原始情況和目標(biāo)查看圖片，C列需建立樣式

   目標(biāo)增加后昵稱如圖所示：

完成順序：  復(fù)制模板-》設(shè)置格式-》寫入內(nèi)容-》保存工作簿

復(fù)制模板：tem_excel =xlrd.open_workbook('D:/autoOffice/autoExcel.xlsx',formatting_info=True)

設(shè)置樣式：style =xlwt.XFStyle()

寫入內(nèi)容：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#設(shè)置樣式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#對(duì)齊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')

樣式寫入表格

項(xiàng)目實(shí)踐二：統(tǒng)計(jì)表格

原始表格

完成目標(biāo)表格：

import xlrdimport xlwtfrom xlutils.copy import copyxlsx = xlrd.open_workbook('d:/7月下旬入庫(kù)表.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)# 以下內(nèi)容可以用pandas的groupby輕易實(shí)現(xiàn)，這里不引入新知識(shí)，使用一個(gè)笨辦法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'] == '趙六麥子專營(yíng)': d_weight.append(i['weight']) d_total_price.append(i['weight'] * i['price'])tem_excel = xlrd.open_workbook('D:/統(tǒng)計(jì)表_模板.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月下旬統(tǒng)計(jì)表.xls')

python完成統(tǒng)計(jì)表格

 項(xiàng)目三：根據(jù)項(xiàng)目二 利用mysql完成以上項(xiàng)目

完成順序：安裝mysql-》將表格導(dǎo)入數(shù)據(jù)庫(kù)-》用python完成統(tǒng)計(jì)并寫入統(tǒng)計(jì)表

用python完成統(tǒng)計(jì)-》連接數(shù)據(jù)庫(kù) -》處理數(shù)據(jù)

數(shù)據(jù)庫(kù)表設(shè)計(jì)

手動(dòng)導(dǎo)入表格到數(shù)據(jù)庫(kù)

1、

2、

3、

4、直接下一步

5、除id外一一對(duì)應(yīng)數(shù)據(jù)庫(kù)表中的值，導(dǎo)入后檢查數(shù)值是否一致

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)#查看數(shù)據(jù)是否顯示

連接數(shù)據(jù)庫(kù)

# 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] == '趙六麥子專營(yíng)': d_num = i[1] d_weight = i[2] d_total_price = i[3]tem_excel = xlrd.open_workbook('D:/autoOffice/7月下旬統(tǒng)計(jì)表.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月下旬統(tǒng)計(jì)表.xls')

所有代碼

 總結(jié)：

先思路再行動(dòng)