生成統計報表

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):  # table.nrows傳回Excel的行數    
	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:/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, len(a_weight), style)
new_sheet.write(2, 2, round(sum(a_weight), 2), style)  # 使用round是為了保留後面的兩個小數，sum是求和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:/統計表.xls')