def writeDataToSheet2(wb, listDeptDetail, listDept): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month sheet2 = wb.add_sheet("(月)负库存课组明细日报", cell_overwrite_ok=True) sheet3 = wb.add_sheet("(月)负库存课组汇总日报", cell_overwrite_ok=True) titlesSheet2 = [[("(%s月)负库存课组明细日报" % month, 0, 1, 7)], [("报表日期", 3, 1, 1), ("%s年-%s月-%s日" % (year, month, date.day), 4, 1, 3)], [("门店编号", 0, 1, 1), ("门店名称", 1, 1, 1), ("课组编码", 2, 1, 1), ("课组名称", 3, 1, 1), ("课组汇总", 4, 1, 1), ("负库存数", 3, 1, 1), ("占比", 3, 1, 1)]] titlesSheet3 = [[("(%s月)负库存课组汇总日报" % month, 0, 1, 5)], [("报表日期", 2, 1, 1), ("%s年-%s月-%s日" % (year, month, date.day), 3, 1, 2)], [("课组编码", 0, 1, 1), ("课组名称", 1, 1, 1), ("课组汇总", 2, 1, 1), ("负库存数", 3, 1, 1), ("占比", 4, 1, 1)]] keylistSheet2 = [ 'shopid', 'shopname', 'deptid', 'deptidname', 'qtyz', 'qtyl', 'zhonbi' ] keylistSheet3 = ['deptid', 'deptidname', 'qtyz', 'qtyl', 'zhonbi'] widthList = [600, 1100, 600, 600, 600, 600] mtu.insertTitle2(sheet2, titlesSheet2, keylistSheet2, widthList) mtu.insertTitle2(sheet3, titlesSheet3, keylistSheet3, widthList) mtu.insertCell2(sheet2, 3, listDeptDetail, keylistSheet2, None) mtu.insertCell2(sheet3, 3, listDept, keylistSheet3, None)
def writeDataToSheet2(wb, resList, title): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month sheet2 = wb.add_sheet("%s负库存商品报告" % title, cell_overwrite_ok=True) titlesSheet2 = [[("(%s月)%s负库存商品报告" % (month, title), 0, 1, 15)], [("报表日期", 11, 1, 1), ("%s年-%s月-%s日" % (year, month, date.day), 12, 1, 3)], [("门店编号", 0, 1, 1), ("门店名称", 1, 1, 1), ("管理类别码", 2, 1, 1), ("管理类别名称", 3, 1, 1), ("小类编码", 4, 1, 1), ("小类名称", 5, 1, 1), ("商品编码", 6, 1, 1), ("商品名称", 7, 1, 1), ("商品规格", 8, 1, 1), ("销售单位", 9, 1, 1), ("负库存数量", 10, 1, 1), ("负库存金额", 11, 1, 1), ("解释原因", 12, 1, 1), ("解决方案", 13, 1, 1), ("解决时间", 14, 1, 1)]] keylistSheet2 = [ 'shopid', 'shopname', 'sgroupid', 'sgroupname', 'deptid', 'deptname', 'goodsid', 'goodsname', 'spec', 'unitname', 'qty', 'costvalue', 'reason1', 'reason2', 'reason3' ] widthList = [ 600, 1400, 600, 600, 600, 800, 600, 1400, 600, 600, 600, 600, 2000, 2000, 2000 ] mtu.insertTitle2(sheet2, titlesSheet2, keylistSheet2, widthList) mtu.insertCell2(sheet2, 3, resList, keylistSheet2, None)
def writeDataToSheet1(wb, code, tbody, thead, total): sheet = wb.add_sheet("往来单据额调整单明细", cell_overwrite_ok=True) titles = [[("往来单据额调整单明细", 0, 1, 8)], [("单位:承德宽广", 5, 1, 3)], [("单号:" + code, 0, 1, 2), ("审核日期:" + thead['chdate'].strftime('%Y-%m-%d'), 2, 1, 3), ("生成日期:" + thead['edate'].strftime('%Y-%m-%d'), 5, 1, 3)], [("门店:" + thead['shopname'], 0, 1, 2), ("供应商代码:" + thead['spercode'], 2, 1, 3), ("供应商名称:" + thead['spername'], 5, 1, 3)], [("供应商编码", 0, 1, 1), ("批次号", 1, 1, 1), ("商品条码", 2, 1, 1), ('品名', 3, 1, 1), ('调整数量', 4, 1, 1), ('原含税单价', 5, 1, 1), ('新含税单价', 6, 1, 1), ('含税调整金额', 7, 1, 1)]] keylist = [ 'adbatchseq', 'pcode', 'barcode', 'pname', 'anum', 'cprice_notax', 'dqhsjj', 'sum4' ] widthList = [1000, 1000, 1000, 2500, 1000, 1000, 1000, 1000] #日销售报表 mtu.insertTitle2(sheet, titles, keylist, widthList) mtu.insertCell2(sheet, 6, tbody, keylist, None) titlesLen = len(titles) listTopLen = len(tbody) mtu.insertSum2(sheet, keylist, titlesLen + listTopLen + 1, total, 4)
def writeDataToSheet11(wb, lis4): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month lastDay = calendar.monthrange(year, month)[1] sheet4 = wb.add_sheet("4家电", cell_overwrite_ok=True) titles = [ [("(%s月%s日)各店日销售排名日报(家电)" % (month, date.day), 0, 1, 13)], [("门店", 0, 2, 1), ("排名", 1, 2, 1), ("商品编码", 2, 2, 1), ("商品名称", 3, 2, 1), ("销售数量", 4, 2, 1), ("销售金额", 5, 2, 1), ("成本金额", 6, 2, 1), ("毛利", 7, 2, 1), ("毛利率%", 8, 2, 1), ("当前库存数量", 9, 2, 1), ("当前库存金额", 10, 2, 1), ("成本价", 11, 2, 1), ("平均售价", 12, 2, 1)], ] keylist = ['shopid', 'paiming', 'goodsid', 'goodsname', 'SaleQty', 'SaleValue', 'SaleCost', 'gpvalue', 'gprate', \ 'qty', 'costvalue', 'cprice', 'price'] widthList = [600, 300, 600, 600, 600, 600, 600, 600, 600, 600, 600, 600] # 日销售报表 mtu.insertTitle2(sheet4, titles, keylist, widthList) mtu.insertCell2(sheet4, 3, lis4, keylist, None) titlesLen = len(titles) listTopLen = len(lis4)
def writeDataToSheet2(wb,listShops,listShopTotal,listDept): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month titlesSheetShop = [[("(%s月)零库存课组明细日报" % month,0,1,7)], [("报表日期",3,1,1),("%s年-%s月-%s日"%(year,month,date.day),4,1,3)], [("门店编号",0,1,1),("门店名称",1,1,1),("课组编码",2,1,1),("课组名称",3,1,1),("课组汇总",4,1,1),("零库存数",3,1,1),("占比",3,1,1)] ] titlesSheetLast = [[("(%s月)零库存课组汇总日报" % month,0,1,5)], [("报表日期",2,1,1),("%s年-%s月-%s日"%(year,month,date.day),3,1,2)], [("课组编码",0,1,1),("课组名称",1,1,1),("课组汇总",2,1,1),("零库存数",3,1,1),("占比",4,1,1)] ] keylistSheet2 = ['ShopID','shopname','deptid','deptidname','qtyz','qtyl','zhonbi'] keylistSheet3 = ['deptid','deptidname','qtyz','qtyl','zhonbi'] widthList = [600,1100,600,600,600,600] sheets = [] for i in range(0,len(listShops)): sheets.append(wb.add_sheet(listShops[i][0]['shopname'],cell_overwrite_ok=True)) sheets.append(wb.add_sheet("(月)零库存课组汇总日报",cell_overwrite_ok=True)) for j in range(0,len(sheets)-1): mtu.insertTitle2(sheets[j],titlesSheetShop,keylistSheet2,widthList) mtu.insertCell2(sheets[j],3,listShops[j-2],keylistSheet2,None) titlesLen = len(titlesSheetShop) listShopsLen = len(listShops) totalDict = {'dict':listShopTotal[j-2]} mtu.insertSum2(sheets[j],keylistSheet2,titlesLen+listShopsLen,totalDict,2) mtu.insertTitle2(sheets[len(sheets)-1],titlesSheetLast,keylistSheet3,widthList) mtu.insertCell2(sheets[len(sheets)-1],3,listDept,keylistSheet3,None)
def writeDataToSheet2(wb, retdetail): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month lastDay = calendar.monthrange(year, month)[1] sheet2 = wb.add_sheet("退货明细", cell_overwrite_ok=True) titles = [ [("(%s月%s日)门店顾客退货明细" % (month, date.day), 0, 1, 12)], [("门店编码", 0, 2, 1), ("门店名称", 1, 2, 1), ("销售日期", 2, 2, 1), ("销售时间", 3, 2, 1), ("退货小票", 4, 2, 1), ("posid", 5, 2, 1), ("收银员工号", 6, 2, 1), ("商品编码", 7, 2, 1), ("商品名称", 8, 2, 1), ("类别编码", 9, 2, 1), ("退货数量", 10, 2, 1), ("退货金额", 11, 2, 1)], ] keylist = [ 'shopid', 'shopname', 'sdate', 'stime', 'listno', 'posid', 'cashierid', 'goodsid', 'goodsname', 'deptid', 'amount', 'sale' ] widthList = [600, 300, 600, 600, 600, 600, 600, 600, 600, 600, 600, 600] # 日销售报表 mtu.insertTitle2(sheet2, titles, keylist, widthList) mtu.insertCell2(sheet2, 3, retdetail, keylist, None) titlesLen = len(titles) listTopLen = len(retdetail)
def writeDataToSheet2(wb, retdetail): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month lastDay = calendar.monthrange(year, month)[1] sheet2 = wb.add_sheet("退货明细", cell_overwrite_ok=True) titles = [ [("(%s月%s日)门店顾客退货明细" % (month, date.day), 0, 1, 12)], [("门店编码", 0, 2, 1), ("门店名称", 1, 2, 1), ("销售日期", 2, 2, 1), ("销售时间", 3, 2, 1), ("退货小票", 4, 2, 1), ("posid", 5, 2, 1), ("收银员工号", 6, 2, 1), ("商品编码", 7, 2, 1), ("商品名称", 8, 2, 1), ("类别编码", 9, 2, 1), ("退货数量", 10, 2, 1), ("退货金额", 11, 2, 1)], ] keylist = ['shopid', 'shopname', 'sdate', 'stime', 'listno', 'posid', 'cashierid', 'goodsid', 'goodsname', 'deptid', 'amount', 'sale'] widthList = [600, 300, 600, 600, 600, 600, 600, 600, 600, 600, 600, 600] # 日销售报表 mtu.insertTitle2(sheet2, titles, keylist, widthList) mtu.insertCell2(sheet2, 3, retdetail, keylist, None) titlesLen = len(titles) listTopLen = len(retdetail)
def writeDataToSheet2(wb,listDeptDetail,listDept): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month sheet2 = wb.add_sheet("(月)负库存课组明细日报",cell_overwrite_ok=True) sheet3 = wb.add_sheet("(月)负库存课组汇总日报",cell_overwrite_ok=True) titlesSheet2 = [[("(%s月)负库存课组明细日报" % month,0,1,7)], [("报表日期",3,1,1),("%s年-%s月-%s日"%(year,month,date.day),4,1,3)], [("门店编号",0,1,1),("门店名称",1,1,1),("课组编码",2,1,1),("课组名称",3,1,1),("课组汇总",4,1,1),("负库存数",3,1,1),("占比",3,1,1)] ] titlesSheet3 = [[("(%s月)负库存课组汇总日报" % month,0,1,5)], [("报表日期",2,1,1),("%s年-%s月-%s日"%(year,month,date.day),3,1,2)], [("课组编码",0,1,1),("课组名称",1,1,1),("课组汇总",2,1,1),("负库存数",3,1,1),("占比",4,1,1)] ] keylistSheet2 = ['shopid','shopname','deptid','deptidname','qtyz','qtyl','zhonbi'] keylistSheet3 = ['deptid','deptidname','qtyz','qtyl','zhonbi'] widthList = [600,1100,600,600,600,600] mtu.insertTitle2(sheet2,titlesSheet2,keylistSheet2,widthList) mtu.insertTitle2(sheet3,titlesSheet3,keylistSheet3,widthList) mtu.insertCell2(sheet2,3,listDeptDetail,keylistSheet2,None) mtu.insertCell2(sheet3,3,listDept,keylistSheet3,None)
def writeDataToSheet1(wb, rlist): sheet = wb.add_sheet("单张小票退货超300", cell_overwrite_ok=True) titles = [[("单张小票退货超300", 0, 1, 10)], [("门店编码", 0, 1, 1), ("销售日期", 1, 1, 1), ("时间", 2, 1, 1), ("小票单号", 3, 1, 1), ("pos机号", 4, 1, 1), ("收银员号", 5, 1, 1), ("收银员名", 6, 1, 1), ("支付原因", 7, 1, 1), ("支付类型", 8, 1, 1), ("支付金额", 9, 1, 1)], ] keylist = ['shopid', 'sdate', 'stime', 'listno', 'posid', 'cashierid', 'name', 'payreson', 'paytype', 'payvalue'] widthlist = [800, 800, 800, 800, 800, 800, 800, 800, 800, 800] mtu.insertTitle2(sheet, titles, keylist, widthlist) mtu.insertCell2(sheet, 2, rlist, keylist, None)
def writeDataToSheet1(wb, listTop, TotalDict): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month lastDay = calendar.monthrange(year, month)[1] sheet = wb.add_sheet("(月)负库存排名日报", cell_overwrite_ok=True) titles = [[("(%s月)负库存排名日报" % month, 0, 1, 15)], [("门店编号", 0, 2, 1), ("门店名称", 1, 2, 1), ("月累计排名(不含联营)", 2, 1, 4)], [('有效商品数', 2, 1, 1), ('合计', 3, 1, 1), ('占比', 4, 1, 1), ('名次', 5, 1, 1)]] keylist = [ 'ShopID', 'shopname', 'qtyzSum', 'qtylSum', 'zhonbiSum', 'mingciSum' ] widthList = [600, 1100, 600, 600, 600, 600] trow1 = titles[1] trow2 = titles[2] n = 6 for d in range(1, lastDay + 1): trow1.append((str(d) + '日排名(不含联营)', n, 1, 4)) trow2.append(('有效商品数', n, 1, 1)) trow2.append(('合计', n + 1, 1, 1)) trow2.append(('占比', n + 2, 1, 1)) trow2.append(('名次', n + 3, 1, 1)) widthList.append(600) widthList.append(400) widthList.append(400) widthList.append(400) n += 4 if d < 10: keylist.append('qtyz_0' + str(d)) keylist.append('qtyl_0' + str(d)) keylist.append('zhonbi_0' + str(d)) keylist.append('mingci_0' + str(d)) else: keylist.append('qtyz_' + str(d)) keylist.append('qtyl_' + str(d)) keylist.append('zhonbi_' + str(d)) keylist.append('mingci_' + str(d)) #日销售报表 mtu.insertTitle2(sheet, titles, keylist, widthList) mtu.insertCell2(sheet, 3, listTop, keylist, None) titlesLen = len(titles) listTopLen = len(listTop) mtu.insertSum2(sheet, keylist, titlesLen + listTopLen, TotalDict, 2)
def writeDataToSheet1(wb,data): sheet = wb.add_sheet("负毛利大于200",cell_overwrite_ok=True) titles = [[("负毛利大于200" ,0,1,15)], [("机构编码",0,1,1),("机构名称",1,1,1),("销售日期",2,1,1),("商品编码",3,1,1),("商品名称",4,1,1), ("管理类别编码",5,1,1),("管理类别名称",6,1,1),("销售金额",7,1,1),("销售数量",8,1,1),("成本金额",9,1,1), ("亏损金额",10,1,1),("库存数量",11,1,1),("解释原因",12,1,1),("解决方案",13,1,1),("解决时间",14,1,1)], ] keylist = ['shopid','shopname','sdate','goodsid','goodsname','deptid','deptname','truevalue','qty', 'costvalue','profit','stockqty','solvereason','solvesolution','solvetime'] widthlist = [800,1000,800,800,2000,800,800,800,800,800,800,800,800,800,800] mtu.insertTitle2(sheet,titles,keylist,widthlist) mtu.insertCell2(sheet,2,data,keylist,None)
def writeDataToSheet1(wb,listTop,TotalDict): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month lastDay = calendar.monthrange(year,month)[1] sheet = wb.add_sheet("(月)零库存排名日报",cell_overwrite_ok=True) titles = [[("(%s月)零库存排名日报" % month,0,1,15)], [("门店编号",0,2,1),("门店名称",1,2,1),("月累计排名(不含联营)",2,1,4)], [('有效商品数',2,1,1),('合计',3,1,1),('占比',4,1,1),('名次',5,1,1)] ] keylist = ['ShopID','shopname','qtyzSum','qtylSum','zhonbiSum','mingciSum'] widthList = [600,1100,600,600,600,600] trow1 = titles[1] trow2 = titles[2] n = 6 for d in range(1,lastDay+1): trow1.append((str(d)+'日排名(不含联营)',n,1,4)) trow2.append(('有效商品数',n,1,1)) trow2.append(('合计',n+1,1,1)) trow2.append(('占比',n+2,1,1)) trow2.append(('名次',n+3,1,1)) widthList.append(600) widthList.append(400) widthList.append(400) widthList.append(400) n += 4 if d<10: keylist.append('qtyz_0'+str(d)) keylist.append('qtyl_0'+str(d)) keylist.append('zhonbi_0'+str(d)) keylist.append('mingci_0'+str(d)) else: keylist.append('qtyz_'+str(d)) keylist.append('qtyl_'+str(d)) keylist.append('zhonbi_'+str(d)) keylist.append('mingci_'+str(d)) #日销售报表 mtu.insertTitle2(sheet,titles,keylist,widthList) mtu.insertCell2(sheet,3,listTop,keylist,None) titlesLen = len(titles) listTopLen = len(listTop) mtu.insertSum2(sheet,keylist,titlesLen+listTopLen,TotalDict,2)
def writeDataToSheet1(wb, listtop, TotalDict): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month lastDay = calendar.monthrange(year, month)[1] sheet1 = wb.add_sheet("门店顾客退货率", cell_overwrite_ok=True) titles = [[("(%s月)门店顾客退货率" % month, 0, 1, 13)], [("序号", 0, 2, 1), ("门店编码", 1, 2, 1), ("门店名称", 2, 2, 1), ('月累计退货', 3, 1, 3)], [('销售金额', 3, 1, 1), ('退货金额', 4, 1, 1), ('退货率', 5, 1, 1)]] keylist = [ 'sequenceNumber', 'shopid', 'shopname', 'shopsalesum', 'retsum', 'retrate' ] widthList = [600, 300, 600, 600, 600, 600, 600, 600, 600, 600, 600, 600] trow1 = titles[1] trow2 = titles[2] n = 6 for d in range(1, lastDay + 1): trow1.append((str(month) + '月' + str(d) + '日', n, 1, 3)) trow2.append(('销售金额', n, 1, 1)) trow2.append(('退货金额', n + 1, 1, 1)) trow2.append(('退货率', n + 2, 1, 1)) widthList.append(600) widthList.append(400) widthList.append(400) n += 3 if d < 10: keylist.append('retsum_0' + str(d)) keylist.append('shopsalesum_0' + str(d)) keylist.append('retrate_0' + str(d)) else: keylist.append('retsum_' + str(d)) keylist.append('shopsalesum_' + str(d)) keylist.append('retrate_' + str(d)) # 日销售报表 mtu.insertTitle2(sheet1, titles, keylist, widthList) mtu.insertCell2(sheet1, 3, listtop, keylist, None) titlesLen = len(titles) listTopLen = len(listtop) mtu.insertSum2(sheet1, keylist, titlesLen + listTopLen, TotalDict, 3)
def writeDataToSheet2(wb, resList, title): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month sheet2 = wb.add_sheet("%s负库存商品报告" % title, cell_overwrite_ok=True) titlesSheet2 = [ [("(%s月)%s负库存商品报告" % (month, title), 0, 1, 15)], [("报表日期", 11, 1, 1), ("%s年-%s月-%s日" % (year, month, date.day), 12, 1, 3)], [ ("门店编号", 0, 1, 1), ("门店名称", 1, 1, 1), ("管理类别码", 2, 1, 1), ("管理类别名称", 3, 1, 1), ("小类编码", 4, 1, 1), ("小类名称", 5, 1, 1), ("商品编码", 6, 1, 1), ("商品名称", 7, 1, 1), ("商品规格", 8, 1, 1), ("销售单位", 9, 1, 1), ("负库存数量", 10, 1, 1), ("负库存金额", 11, 1, 1), ("解释原因", 12, 1, 1), ("解决方案", 13, 1, 1), ("解决时间", 14, 1, 1), ], ] keylistSheet2 = [ "shopid", "shopname", "sgroupid", "sgroupname", "deptid", "deptname", "goodsid", "goodsname", "spec", "unitname", "qty", "costvalue", "reason1", "reason2", "reason3", ] widthList = [600, 1400, 600, 600, 600, 800, 600, 1400, 600, 600, 600, 600, 2000, 2000, 2000] mtu.insertTitle2(sheet2, titlesSheet2, keylistSheet2, widthList) mtu.insertCell2(sheet2, 3, resList, keylistSheet2, None)
def writeDataToSheet2(wb, rlist): sheet = wb.add_sheet("商品退货明细", cell_overwrite_ok=True) titles = [[("商品退货明细", 0, 1, 16)], [("门店编码", 0, 1, 1), ("销售日期", 1, 1, 1), ("时间", 2, 1, 1), ("小票单号", 3, 1, 1), ("pos机号", 4, 1, 1), ("收银员号", 5, 1, 1), ("收银员名", 6, 1, 1), ("商品名称", 7, 1, 1), ("商品编码", 8, 1, 1), ("销售数量", 9, 1, 1), ("销售金额", 10, 1, 1), ("折扣金额", 11, 1, 1), ("实际销售", 12, 1, 1), ("销售类型", 13, 1, 1), ("售价", 14, 1, 1), ("解释原因", 15, 1, 1)], ] keylist = ['shopid', 'sdate', 'stime', 'listno', 'posid', 'cashierid', 'name', 'goodsname', 'deptid', 'xamount', \ 'salevalue', 'discvalue', 'truevalue', 'saletype', 'price'] widthlist = [800, 800, 800, 800, 800, 800, 800, 800, 800, 800, 800, 800, 800, 800, 800, 800] mtu.insertTitle2(sheet, titles, keylist, widthlist) mtu.insertCell2(sheet, 2, rlist, keylist, None)
def writeDataToSheet1(wb, shopTop, TotalDict): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month lastDay = calendar.monthrange(year, month)[1] sheet = wb.add_sheet("(月)水果报损率日报", cell_overwrite_ok=True) titles = [[("(%s月)水果报损率日报" % month, 0, 1, 15)], [("门店编号", 0, 2, 1), ("门店名称", 1, 2, 1), ("月累计报损", 2, 1, 3)], [ ('成本金额', 2, 1, 1), ('销售成本金额', 3, 1, 1), ('报损率', 4, 1, 1), ]] keylist = [ 'shopid', 'shopname', 'lostvalueSum', 'costvalueSum', 'lrateSum' ] widthList = [600, 1100, 600, 600, 600, 600] trow1 = titles[1] trow2 = titles[2] n = 5 #1日单元格开始位置 for d in range(1, lastDay + 1): trow1.append((str(d) + '日', n, 1, 3)) trow2.append(('成本金额', n, 1, 1)) trow2.append(('销售成本金额', n + 1, 1, 1)) trow2.append(('报损率', n + 2, 1, 1)) widthList.append(600) widthList.append(400) widthList.append(400) widthList.append(400) n += 3 #每日单元格数量 dStr = '0' + str(d) if d < 10 else str(d) keylist.append('lostvalue_' + dStr) keylist.append('costvalue_' + dStr) keylist.append('lrate_' + dStr) #日销售报表 mtu.insertTitle2(sheet, titles, keylist, widthList) mtu.insertCell2(sheet, 3, shopTop, keylist, None) titlesLen = len(titles) listTopLen = len(shopTop) mtu.insertSum2(sheet, keylist, titlesLen + listTopLen, TotalDict, 2)
def writeDataToSheet1(wb, listtop, TotalDict): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month lastDay = calendar.monthrange(year, month)[1] sheet1 = wb.add_sheet("门店顾客退货率", cell_overwrite_ok=True) titles = [ [("(%s月)门店顾客退货率" % month, 0, 1, 13)], [("序号", 0, 2, 1), ("门店编码", 1, 2, 1), ("门店名称", 2, 2, 1), ('月累计退货', 3, 1, 3)], [('销售金额', 3, 1, 1), ('退货金额', 4, 1, 1), ('退货率', 5, 1, 1)] ] keylist = ['sequenceNumber', 'shopid', 'shopname', 'shopsalesum', 'retsum', 'retrate'] widthList = [600, 300, 600, 600, 600, 600, 600, 600, 600, 600, 600, 600] trow1 = titles[1] trow2 = titles[2] n = 6 for d in range(1, lastDay + 1): trow1.append((str(month) + '月' + str(d) + '日', n, 1, 3)) trow2.append(('销售金额', n, 1, 1)) trow2.append(('退货金额', n + 1, 1, 1)) trow2.append(('退货率', n + 2, 1, 1)) widthList.append(600) widthList.append(400) widthList.append(400) n += 3 if d < 10: keylist.append('retsum_0' + str(d)) keylist.append('shopsalesum_0' + str(d)) keylist.append('retrate_0' + str(d)) else: keylist.append('retsum_' + str(d)) keylist.append('shopsalesum_' + str(d)) keylist.append('retrate_' + str(d)) # 日销售报表 mtu.insertTitle2(sheet1, titles, keylist, widthList) mtu.insertCell2(sheet1, 3, listtop, keylist, None) titlesLen = len(titles) listTopLen = len(listtop) mtu.insertSum2(sheet1, keylist, titlesLen + listTopLen, TotalDict, 3)
def writeDataToSheet2(wb, rlist, date): year = date.year month = date.month sheet = wb.add_sheet("类别", cell_overwrite_ok=True) titles = [[("%s年%s月各店类别每日业绩指标分解达成表(单位:万元)" % (year, month), 0, 1, 15)], [("类别编码", 0, 3, 1), ("日期", 1, 1, 5)], [("%s月总指标" % month, 1, 2, 1), ("调整后销售指标", 2, 2, 1), ("累计计划销售达标率", 3, 2, 1), ("调整后毛利指标", 4, 2, 1), ("累计计划毛利达标率", 5, 2, 1)], []] keylist = ['idname', 'codelable', 'm_all_sale', 'm_daily_sale', 'm_all_gain', 'm_daily_gain'] widthlist = [1200, 800, 800, 800, 800, 800] initExport(titles, keylist, widthlist, date) mtu.insertTitle2(sheet, titles, keylist, widthlist) mtu.insertCell2(sheet, 4, rlist, keylist, None)
def writeDataToSheet1(wb, rlist): sheet = wb.add_sheet("单张小票退货超300", cell_overwrite_ok=True) titles = [ [("单张小票退货超300", 0, 1, 10)], [("门店编码", 0, 1, 1), ("销售日期", 1, 1, 1), ("时间", 2, 1, 1), ("小票单号", 3, 1, 1), ("pos机号", 4, 1, 1), ("收银员号", 5, 1, 1), ("收银员名", 6, 1, 1), ("支付原因", 7, 1, 1), ("支付类型", 8, 1, 1), ("支付金额", 9, 1, 1)], ] keylist = [ 'shopid', 'sdate', 'stime', 'listno', 'posid', 'cashierid', 'name', 'payreson', 'paytype', 'payvalue' ] widthlist = [800, 800, 800, 800, 800, 800, 800, 800, 800, 800] mtu.insertTitle2(sheet, titles, keylist, widthlist) mtu.insertCell2(sheet, 2, rlist, keylist, None)
def writeDataToSheet1(wb,shopTop,TotalDict): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month lastDay = calendar.monthrange(year,month)[1] sheet = wb.add_sheet("(月)水果报损率日报",cell_overwrite_ok=True) titles = [[("(%s月)水果报损率日报" % month,0,1,15)], [("门店编号",0,2,1),("门店名称",1,2,1),("月累计报损",2,1,3)], [('成本金额',2,1,1),('销售成本金额',3,1,1),('报损率',4,1,1),] ] keylist = ['shopid','shopname','lostvalueSum','costvalueSum','lrateSum'] widthList = [600,1100,600,600,600,600] trow1 = titles[1] trow2 = titles[2] n = 5 #1日单元格开始位置 for d in range(1,lastDay+1): trow1.append((str(d)+'日',n,1,3)) trow2.append(('成本金额',n,1,1)) trow2.append(('销售成本金额',n+1,1,1)) trow2.append(('报损率',n+2,1,1)) widthList.append(600) widthList.append(400) widthList.append(400) widthList.append(400) n += 3 #每日单元格数量 dStr = '0'+str(d) if d<10 else str(d) keylist.append('lostvalue_'+dStr) keylist.append('costvalue_'+dStr) keylist.append('lrate_'+dStr) #日销售报表 mtu.insertTitle2(sheet,titles,keylist,widthList) mtu.insertCell2(sheet,3,shopTop,keylist,None) titlesLen = len(titles) listTopLen = len(shopTop) mtu.insertSum2(sheet,keylist,titlesLen+listTopLen,TotalDict,2)
def writeDataToSheet2(wb, rlist, date): year = date.year month = date.month sheet = wb.add_sheet("类别", cell_overwrite_ok=True) titles = [[("%s年%s月各店类别每日业绩指标分解达成表(单位:万元)" % (year, month), 0, 1, 15)], [("类别编码", 0, 3, 1), ("日期", 1, 1, 5)], [("%s月总指标" % month, 1, 2, 1), ("调整后销售指标", 2, 2, 1), ("累计计划销售达标率", 3, 2, 1), ("调整后毛利指标", 4, 2, 1), ("累计计划毛利达标率", 5, 2, 1)], []] keylist = [ 'idname', 'codelable', 'm_all_sale', 'm_daily_sale', 'm_all_gain', 'm_daily_gain' ] widthlist = [1200, 800, 800, 800, 800, 800] initExport(titles, keylist, widthlist, date) mtu.insertTitle2(sheet, titles, keylist, widthlist) mtu.insertCell2(sheet, 4, rlist, keylist, None)
def writeDataToSheetN(wb, shoplist, rlist, date): year = date.year month = date.month for i in range(len(shoplist)): shop = shoplist[i] sheetname = "%s%s" % (shop["shopid"][2:4], shop["shopname"]) sheet = wb.add_sheet(sheetname, cell_overwrite_ok=True) titles = [[("%s年%s月各店类别每日业绩指标分解达成表(单位:万元)" % (year, month), 0, 1, 15)], [("类别编码", 0, 3, 1), ("日期", 1, 1, 5)], [("%s月总指标" % month, 1, 2, 1), ("调整后销售指标", 2, 2, 1), ("累计计划销售达标率", 3, 2, 1), ("调整后毛利指标", 4, 2, 1), ("累计计划毛利达标率", 5, 2, 1)], []] keylist = ['idname', 'codelable', 'm_all_sale', 'm_daily_sale', 'm_all_gain', 'm_daily_gain'] widthlist = [1200, 800, 800, 800, 800, 800] initExport(titles, keylist, widthlist, date) mtu.insertTitle2(sheet, titles, keylist, widthlist) mtu.insertCell2(sheet, 4, rlist[i], keylist, None)
def writeDataToSheet2(wb, rlist): sheet = wb.add_sheet("商品退货明细", cell_overwrite_ok=True) titles = [ [("商品退货明细", 0, 1, 16)], [("门店编码", 0, 1, 1), ("销售日期", 1, 1, 1), ("时间", 2, 1, 1), ("小票单号", 3, 1, 1), ("pos机号", 4, 1, 1), ("收银员号", 5, 1, 1), ("收银员名", 6, 1, 1), ("商品名称", 7, 1, 1), ("商品编码", 8, 1, 1), ("销售数量", 9, 1, 1), ("销售金额", 10, 1, 1), ("折扣金额", 11, 1, 1), ("实际销售", 12, 1, 1), ("销售类型", 13, 1, 1), ("售价", 14, 1, 1), ("解释原因", 15, 1, 1)], ] keylist = ['shopid', 'sdate', 'stime', 'listno', 'posid', 'cashierid', 'name', 'goodsname', 'deptid', 'xamount', \ 'salevalue', 'discvalue', 'truevalue', 'saletype', 'price'] widthlist = [ 800, 800, 800, 800, 800, 800, 800, 800, 800, 800, 800, 800, 800, 800, 800, 800 ] mtu.insertTitle2(sheet, titles, keylist, widthlist) mtu.insertCell2(sheet, 2, rlist, keylist, None)
def writeDataToSheet2(wb, listShops, listShopTotal, listDept): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month titlesSheetShop = [[("(%s月)零库存课组明细日报" % month, 0, 1, 7)], [("报表日期", 3, 1, 1), ("%s年-%s月-%s日" % (year, month, date.day), 4, 1, 3)], [("门店编号", 0, 1, 1), ("门店名称", 1, 1, 1), ("课组编码", 2, 1, 1), ("课组名称", 3, 1, 1), ("课组汇总", 4, 1, 1), ("零库存数", 3, 1, 1), ("占比", 3, 1, 1)]] titlesSheetLast = [[("(%s月)零库存课组汇总日报" % month, 0, 1, 5)], [("报表日期", 2, 1, 1), ("%s年-%s月-%s日" % (year, month, date.day), 3, 1, 2)], [("课组编码", 0, 1, 1), ("课组名称", 1, 1, 1), ("课组汇总", 2, 1, 1), ("零库存数", 3, 1, 1), ("占比", 4, 1, 1)]] keylistSheet2 = [ 'ShopID', 'shopname', 'deptid', 'deptidname', 'qtyz', 'qtyl', 'zhonbi' ] keylistSheet3 = ['deptid', 'deptidname', 'qtyz', 'qtyl', 'zhonbi'] widthList = [600, 1100, 600, 600, 600, 600] sheets = [] for i in range(0, len(listShops)): sheets.append( wb.add_sheet(listShops[i][0]['shopname'], cell_overwrite_ok=True)) sheets.append(wb.add_sheet("(月)零库存课组汇总日报", cell_overwrite_ok=True)) for j in range(0, len(sheets) - 1): mtu.insertTitle2(sheets[j], titlesSheetShop, keylistSheet2, widthList) mtu.insertCell2(sheets[j], 3, listShops[j - 2], keylistSheet2, None) titlesLen = len(titlesSheetShop) listShopsLen = len(listShops) totalDict = {'dict': listShopTotal[j - 2]} mtu.insertSum2(sheets[j], keylistSheet2, titlesLen + listShopsLen, totalDict, 2) mtu.insertTitle2(sheets[len(sheets) - 1], titlesSheetLast, keylistSheet3, widthList) mtu.insertCell2(sheets[len(sheets) - 1], 3, listDept, keylistSheet3, None)
def writeDataToSheet2(wb, data): date = DateUtil.get_day_of_day(-1) year = date.year month = date.month sheet2 = wb.add_sheet("单品报损超100", cell_overwrite_ok=True) titlesSheet2 = [[("单品报损超100", 0, 1, 12)], [("报表日期", 8, 1, 1), ("%s年-%s月-%s日" % (year, month, date.day), 9, 1, 3)], [("机构编号", 0, 1, 1), ("机构名称", 1, 1, 1), ("单据编号", 2, 1, 1), ("商品名称", 3, 1, 1), ("商品编码", 4, 1, 1), ("类别编码", 5, 1, 1), ("类别名称", 6, 1, 1), ("申请数量", 7, 1, 1), ("审批数量", 8, 1, 1), ("实际报损数", 9, 1, 1), ("成本金额", 10, 1, 1), ("解释原因", 11, 1, 1)]] keylistSheet2 = [ 'shopid', 'shopname', 'sheetid', 'goodsid', 'goodsname', 'deptid', 'deptname', 'askqty', 'checkqty', 'qty', 'costvalue' 'reason' ] widthList = [600, 600, 600, 600, 1000, 600, 600, 600, 600, 600, 600, 1000] mth.insertTitle2(sheet2, titlesSheet2, keylistSheet2, widthList) mth.insertCell2(sheet2, 3, data, keylistSheet2, None)
def writeDataToSheet1(wb,code,tbody, thead,total): sheet = wb.add_sheet("往来单据额调整单明细",cell_overwrite_ok=True) titles = [ [("往来单据额调整单明细",0,1,8)], [("单位:承德宽广",5,1,3)], [("单号:" + code,0,1,2), ("审核日期:" + thead['chdate'].strftime('%Y-%m-%d'),2,1,3), ("生成日期:" + thead['edate'].strftime('%Y-%m-%d'),5,1,3)], [("门店:" + thead['shopname'],0,1,2), ("供应商代码:" + thead['spercode'],2,1,3),("供应商名称:" + thead['spername'],5,1,3)], [("供应商编码",0,1,1),("批次号",1,1,1),("商品条码",2,1,1),('品名',3,1,1),('调整数量',4,1,1),('原含税单价',5,1,1),('新含税单价',6,1,1),('含税调整金额',7,1,1)] ] keylist = ['adbatchseq','pcode','barcode','pname','anum','cprice_notax','dqhsjj','sum4'] widthList = [1000,1000,1000,2500,1000,1000,1000,1000] #日销售报表 mtu.insertTitle2(sheet,titles,keylist,widthList) mtu.insertCell2(sheet,6,tbody,keylist,None) titlesLen = len(titles) listTopLen = len(tbody) mtu.insertSum2(sheet,keylist,titlesLen+listTopLen+1,total,4)
def writeDataToSheetN(wb, shoplist, rlist, date): year = date.year month = date.month for i in range(len(shoplist)): shop = shoplist[i] sheetname = "%s%s" % (shop["shopid"][2:4], shop["shopname"]) sheet = wb.add_sheet(sheetname, cell_overwrite_ok=True) titles = [[("%s年%s月各店类别每日业绩指标分解达成表(单位:万元)" % (year, month), 0, 1, 15)], [("类别编码", 0, 3, 1), ("日期", 1, 1, 5)], [("%s月总指标" % month, 1, 2, 1), ("调整后销售指标", 2, 2, 1), ("累计计划销售达标率", 3, 2, 1), ("调整后毛利指标", 4, 2, 1), ("累计计划毛利达标率", 5, 2, 1)], []] keylist = [ 'idname', 'codelable', 'm_all_sale', 'm_daily_sale', 'm_all_gain', 'm_daily_gain' ] widthlist = [1200, 800, 800, 800, 800, 800] initExport(titles, keylist, widthlist, date) mtu.insertTitle2(sheet, titles, keylist, widthlist) mtu.insertCell2(sheet, 4, rlist[i], keylist, None)
def writeDataToSheet1(wb,rlist,sumDict): date = DateUtil.get_day_of_day(-1) yesterday = date.strftime("%Y-%m-%d") sheet = wb.add_sheet("宽广集团销售日报表",cell_overwrite_ok=True) titles = [[("宽广集团销售日报表",2,1,13)], [("数据日期:",0,1,2),(yesterday,2,1,1),("单位:元",4,1,1)], [("机构编码",0,2,1),("机构名称",1,2,1),("POS销售数据",3,1,9),("批发销售数据",4,1,4)], [("总客流量",2,1,1),("平均客单价",3,1,1),("销售金额",4,1,1),("折扣金额",5,1,1),("实际销售",6,1,1),("销售成本",7,1,1), ("毛利",8,1,1),("毛利率",9,1,1),("优惠占比",10,1,1),("实际销售",11,1,1),("销售成本",12,1,1),("毛利",13,1,1),("毛利率",14,1,1)], ] keylist = ['shopid','shopnm','tradenumber','tradeprice','salevalue','discvalue','sale','costvalue', 'salegain','gaintx','yhzhanbi','wsalevalue','wcostvalue','wsalegain', 'wgaintx'] widthList = [600,400,1000,800,400,800,800,800,800,800,800,800,800,800,800] mtu.insertTitle2(sheet,titles,keylist,widthList) count = mtu.insertCell2(sheet,4,rlist,keylist,None) mtu.insertSum2(sheet,keylist,count,sumDict,2)