from openpyxl importWorkbookfrom openpyxl.styles importFont, Fill, Alignment, Border, Side, PatternFillfrom handlers.boss_accountant importPbOrderManageBasefrom handlers.base.pub_func importConfigFuncfrom dal.models importShopfrom dal.db_configs importDBSessiondefexport_excel(filename, sheetname, content_body):"""Excel表格导出
:param filename: 表格名称
:param sheetname: 工作表名称
:param content_body: 内容体
:return: None"""workbook=Workbook()if notfilename:
filename= "导出表格.xlsx"workbook_sheet=workbook.activeif notsheetname:
sheetname= "工作表"workbook_sheet.title=sheetname
merge_dict, sheet_row_len, sheet_column_len=merge_content(content_body)print(merge_dict)#数据写入
for row incontent_body:
workbook_sheet.append(row)#合并处理
for key inmerge_dict.keys():
merge_data=merge_dict.get(key)if key == "title":
workbook_sheet.merge_cells(start_row=merge_data[0], start_column=merge_data[1],
end_row=merge_data[2], end_column=merge_data[3])
workbook_sheet.merge_cells(start_row=2, start_column=merge_data[1],
end_row=2, end_column=merge_data[3])
workbook_sheet["A1"].font = Font(size=20, bold=True)
workbook_sheet["A1"].alignment = Alignment(horizontal="center", vertical="center")else:#使用sum求值
workbook_sheet.cell(row=merge_data[0] + 3, column=12).value = "=SUM({}:{})".format(
format_value(str(merge_data[0]+ 3), 10), format_value(str(merge_data[1] + 3), 10))
workbook_sheet.cell(row=merge_data[0] + 3, column=14).value = "=SUM({}:{})".format(
format_value(str(merge_data[0]+ 3), 11), format_value(str(merge_data[1] + 3), 11))
workbook_sheet.cell(row=merge_data[0] + 3, column=13).value = "=({}-{})".format(
format_value(str(merge_data[0]+ 3), 12), format_value(str(merge_data[0] + 3), 14))for i in [2,12, 13, 14]:
workbook_sheet.merge_cells(start_row=merge_data[0]+3, start_column=i,
end_row=merge_data[1]+3, end_column=i)#合计求和
for i in [12, 13, 14]:
workbook_sheet.cell(row=sheet_row_len, column=i).value = "=SUM({}:{})".format(
format_value(3, i), format_value(sheet_row_len - 1, i))#单元格底色
last_row =workbook_sheet[sheet_row_len]for each_cell inlast_row:
each_cell.fill= PatternFill("solid", fgColor="00CDCD")#边框设置
for each_common_row in workbook_sheet.iter_rows("A1:{}".format(format_value(sheet_row_len, sheet_column_len))):for each_cell ineach_common_row:
each_cell.border= Border(left=Side(style="thin", color="000000"),
right=Side(style="thin", color="000000"),
top=Side(style="thin", color="000000"),
bottom=Side(style="thin", color="000000")
)
workbook_sheet.column_dimensions["B"].width = 15workbook_sheet.column_dimensions["C"].width = 20workbook.save(filename)defmerge_content(content_body):"""合并统计
:param content_body: 数据体
:return: 合并字典"""sheet_column_len= len(content_body[3])
sheet_row_len=len(content_body)
merge_dict={}
data_content= content_body[3:-1]
merge_dict["title"] = (1, 1, 1, sheet_column_len)
current_data= data_content[0][1]
current_row=0
start_row= 1end_row=0for data indata_content:
current_row+= 1x= data[1]if data[1] ==current_data:
merge_dict[data[1]] =(start_row, current_row)else:
merge_dict[data[1]] =(current_row, current_row)
current_data= data[1]
start_row=current_rowreturnmerge_dict, sheet_row_len, sheet_column_lendefformat_value(row, column):"""数字转ABC"""change_dict={1: "A", 2: "B", 3: "C", 4: "D", 5: "E", 6: "F", 7: "G", 8: "H", 9: "I", 10: "J",11: "K", 12: "L", 13: "M", 14: "N", 15: "O", 16: "P", 17: "Q", 18: "R", 19: "S", 20: "T",21: "U", 22: "V", 23: "W", 24: "X", 25: "Y", 26: "Z",
}
column=change_dict.get(column)return str(column)+str(row)defexport_func_new(args, session, shop_id):#check_time = 0
#debtor_id = 2884
#debtor_name: 肖小菜
#end_date:
#start_date: 2019 - 07
#statistic_date: 3
#data_type: 1
data_content =[]
check_time=0
from_date= "2019-07"to_date= ""debtor_name= "肖小菜"if_success, query_data,*_ = PbOrderManageBase.common_get_credit_stream(args, session, shop_id, export=True,
need_sum=False, check_time=check_time
)if notif_success:raiseValueError(query_data)
fee_text=ConfigFunc.get_fee_text(session, shop_id)
get_weight_unit_text=ConfigFunc.get_weight_unit_text(session, shop_id)#表店铺、客户名称
shop_name = session.query(Shop.shop_name).filter_by(id=shop_id).first()
data_content.append([shop_name[0]])
data_content.append(["客户:{}".format(debtor_name)])#表头
fee_text_total = "{}小计".format(fee_text)
header_content=["序号", "日期", "货品名", "数量", "重量/{}".format(get_weight_unit_text), "单价", "货品小记", "押金小计", fee_text_total,"赊账金额","待还款", "赊账小记", "已还款", "待还款小计"]
file_name_begin= "客户还款"data_content.append(header_content)#还款数据
index_num =0for single_data inquery_data:
index_num+= 1sales_time= single_data.get("sales_time", "")ifsales_time:
sales_time= sales_time.split(" ")[0]
_payback_money= single_data["unpayback_money"]
single_content=[index_num,
sales_time,
single_data["only_goods_name"],
single_data["commission_mul"],
single_data["sales_num"],"%s元/%s" % (single_data["fact_price"],
single_data["goods_unit"]),
single_data["goods_total"],
single_data["commission_mul"],
single_data["deposit_total"],
single_data["credit_cent"],
_payback_money,
0,
0,
0]
data_content.append(single_content)#表尾合计
data_content.append(["合计"])
config=ConfigFunc.get_config(session, shop_id)if notconfig.enable_deposit:
index_deposit_total= data_content[0].index("押金小计")for data indata_content:
data.pop(index_deposit_total)if notconfig.enable_commission:
index_commission_total=data_content[0].index(fee_text_total)for data indata_content:
data.pop(index_commission_total)
file_name= "{}流水记录导出_{}~{}.xlsx".format(file_name_begin, from_date, to_date)returnfile_name, data_contentif __name__ == "__main__":
filename= "测试打印表格.xlsx"sheetname= "工作表2"session=DBSession()
args={"check_time": 0,"debtor_id": 2884,"debtor_name": "肖小菜","start_date": "2019-07","statistic_date": 3,"data_type": 1}
filename, content_body= export_func_new(args, session, 104)#filename = "测试打印表格.xlsx"
#sheetname = "工作表2"
#content_body = []
#content_body.append(["打印表格表头"])
#content_body.append(["客户:肖某某"])
#content_body.append(["日期", "货品销售", "自营销售", "代卖销售", "联营销售", "总价"])
#content_body.append(["1", "2", "3.1", "4.1", "5.1", "5.1"])
#content_body.append(["1", "2", "3.1", "4.1", "5.1", "5.1"])
#content_body.append(["1", "2", "3.1", "4.1", "5.1", "5.1"])