一、xlrd、xlwt读写Excel
1、读操作
import xlrd# 1、打开工作本workbookxlsx = xlrd.open_workbook(r'.\7月下旬入库表.xlsx')# 2、打开需要操作的表sheettable = xlsx.sheet_by_index(0)# table = xlsx.sheet_by_name('7月下旬入库表')# 3、读取指定单元格的数据print(table.cell_value(1,1))print(table.cell(1,1).value)print(table.row(1)[1].value)
2、写操作
import xlwt# 1、新建一个工作本new_workbook = xlwt.Workbook()# 2、为这个工作本中添加一个工作表worksheet = new_workbook.add_sheet('new_test')# 3、向指定单元格写入内容worksheet.write(0,0,'test')# 4、保存new_workbook.save('./test.xls')
3、带文字格式的写入操作
from xlutils.copy import copyimport xlrdimport xlwt# 1、打开需要进行操作的工作本，并将其进行复制操作# 注意：使用读取xls文件的时候都是使用的xlrd库，但是这个库只能操作 .xls格式，对于后来的 .xlsx的版本支持不算太好# formatting_info 该参数默认为False，这可以节省内存；当取值为True时，会读取各种格式的信息tem_excel = xlrd.open_workbook(r'.\日统计.xls',formatting_info=True)# 2、选择需要操作的工作表tem_sheet = tem_excel.sheet_by_index(0)# 3、新建一个工作本，通过复制模板工作本的方式new_excel = copy(tem_excel)# 4、选择新的工作本中需要操作的工作表new_sheet = new_excel.get_sheet(0)# 初始化样式style = xlwt.XFStyle()# 初始化字体font = xlwt.Font()font.name = '微软雅黑'font.bold = Truefont.height = 18 * 20style.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 = alignment# 5、向已选择的工作表中写入数据new_sheet.write(2,1,12,style)new_sheet.write(3,1,18,style)new_sheet.write(4,1,19,style)new_sheet.write(5,1,15,style)# 6、保存文件new_excel.save('./填写.xls')
4、综合案例：
import xlrdimport xlwtfrom xlutils.copy import copyxlsx = xlrd.open_workbook(r'.\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(r'.\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 = 18 * 20style.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 = alignmentnew_sheet.write(2,1,len(a_weight),style)new_sheet.write(2,2,round(sum(a_weight)),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)),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)),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)),style)new_sheet.write(5,3,round(sum(d_total_price),2),style)new_excel.save('./7月下旬统计表.xls')
