def write_histories(sheet, data, rowx): detail_head = [u'步骤', u'意见', '审批人', '审批时间'] head_width = [8000, 8000, 8000, 8000] merge_col = [1, 1, 1, 1] kinds = 'text text text text' style = [ ezxf( 'font:bold on, height 240; align: wrap on, vert centre, horiz left' ), ezxf( 'font:bold on, height 240; align: wrap on, vert centre, horiz left' ), ezxf( 'font:bold on, height 240; align: wrap on, vert centre, horiz left' ), ezxf( 'font:bold on, height 240; align: wrap on, vert centre, horiz left' ) ] kinds = kinds.split() heading_xf = {'style': style, 'width': head_width, 'merge_col': merge_col} data_format = 'font: height 240; align: wrap on, vert centre, horiz left' kind_to_xf_map = { 'date': ezxf(data_format, num_format_str='yyyy-mm-dd'), 'int': ezxf(data_format, num_format_str='#,##0'), 'money': ezxf(data_format, num_format_str='#,##0.00'), 'price': ezxf(data_format, num_format_str='#0.00'), 'text': ezxf(data_format), } data_xfs = [kind_to_xf_map[k] for k in kinds] write_details(sheet, rowx, detail_head, data, heading_xf, data_xfs)
def write_histories(sheet, data, rowx): detail_head = [u'步骤', u'意见', '审批人', '审批时间'] head_width = [8000, 8000, 8000, 8000] merge_col = [1, 1, 1, 1] kinds = 'text text text text' style= [ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz left'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz left'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz left'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz left')] kinds = kinds.split() heading_xf = {'style' : style, 'width' : head_width, 'merge_col' : merge_col} data_format = 'font: height 240; align: wrap on, vert centre, horiz left' kind_to_xf_map = { 'date': ezxf(data_format ,num_format_str='yyyy-mm-dd'), 'int': ezxf(data_format, num_format_str='#,##0'), 'money': ezxf(data_format ,num_format_str='#,##0.00'), 'price': ezxf(data_format,num_format_str='#0.00'), 'text': ezxf(data_format), } data_xfs = [kind_to_xf_map[k] for k in kinds] write_details(sheet, rowx, detail_head, data, heading_xf, data_xfs)
def write_receiving_list(sheet, result, rowx): detail_head = [u'序号', u'材料名称', u'品牌', u'规格', u'单位', u'数量', u'单价', u'金额', u'送货日期', u'送货单位', u'备注'] head_width = [0x0d00, 5000, 5000, 5000, 0x0d00, 0x0d00, 0x0d00, 0x0d00, 4000, 8000, 8000] merge_col = [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1] kinds = 'int text text text text int price money date text text'.split() style = [ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz left'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz left'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz left'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz left'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz left'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz left'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz left')] heading_xf = {'style' : style, 'width' : head_width, 'merge_col' : merge_col} data_format = 'font: height 240; align: wrap on, vert centre, horiz left' number_format = 'font: height 240; align: wrap on, vert centre, horiz right' kind_to_xf_map = { 'date': ezxf(data_format ,num_format_str='yyyy-mm-dd'), 'int': ezxf(number_format, num_format_str='#,##0'), 'money': ezxf(number_format ,num_format_str='#,##0.00'), 'price': ezxf(number_format,num_format_str='#0.00'), 'text': ezxf(data_format), } data_xfs = [kind_to_xf_map[k] for k in kinds] data = [] index = 1 for line in result['lines']: row = [] row.append(index) row.append(line['projectMaterialName']) row.append(line['brand']) row.append(line['specification']) row.append(line['unit']) row.append(line['receiving_quantity']) row.append(line['price']) row.append(line['totalPrice']) row.append(line['receiving_date']) row.append(line['vendor']) row.append(line['comments'] or '') data.append(row) index = index + 1 write_details(sheet, rowx, detail_head, data, heading_xf, data_xfs)
def write_total_amounts(sheet, result, rowx, hasBrandName): detail_head = [u'本期供货金额', u'本期付款金额', u'上期欠款金额', u'累计应付款', u'本期开票金额', u'上期欠发票', '累计应开票'] head_width = [2500, 5000, 5000, 2500, 2500, 2500, 5000] merge_col = [2, 1, 1, 2, 2, 2, 1] style = [ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz right')] if hasBrandName is False: head_width = [2500, 5000, 5000, 5000, 2500, 2500, 5000] merge_col = [2, 1, 1, 1, 2, 2, 1] kinds = 'money money money money money money money'.split() heading_xf = {'style' : style, 'width' : head_width, 'merge_col' : merge_col} data_format = 'font: height 240; align: wrap on, vert centre, horiz left' number_format = 'font: height 240; align: wrap on, vert centre, horiz right' kind_to_xf_map = { 'date': ezxf(data_format , num_format_str='yyyy-mm-dd'), 'int': ezxf(number_format, num_format_str='#,##0'), 'money': ezxf(number_format , num_format_str='#,##0.00'), 'price': ezxf(number_format, num_format_str='#0.00'), 'text': ezxf(data_format), } data_xfs = [kind_to_xf_map[k] for k in kinds] data = [] row = [] row.append(result['receiving_unchecked_account_sum']) row.append(result['payments_sum']) row.append(result['last_term_owed_amount']) row.append(result['total_owed_amount']) row.append(result['invoices_sum']) row.append(result['last_term_owed_invoices']) row.append(result['total_owed_invoices']) data.append(row) write_details(sheet, rowx, detail_head, data, heading_xf, data_xfs, False)
def write_payments_list(sheet, result, rowx): detail_head = [u'申请付款日期', u'实际付款日期', u'付款方式', u'公司', u'金额'] head_width = [2000, 2000, 3500, 5000, 5000] merge_col = [2, 2, 1, 4, 1] kinds = 'date date text text money'.split() style = [ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz left'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz left'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz left'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz left'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz right'), ] heading_xf = {'style' : style, 'width' : head_width, 'merge_col' : merge_col} data_format = 'font: height 240; align: wrap on, vert centre, horiz left' number_format = 'font: height 240; align: wrap on, vert centre, horiz right' kind_to_xf_map = { 'date': ezxf(data_format , num_format_str='yyyy-mm-dd'), 'int': ezxf(number_format, num_format_str='#,##0'), 'money': ezxf(number_format , num_format_str='#,##0.00'), 'price': ezxf(number_format, num_format_str='#0.00'), 'text': ezxf(data_format), } data_xfs = [kind_to_xf_map[k] for k in kinds] data = [] for line in result['payments']: row = [] row.append(line.create_time) row.append(line.payment_date) row.append(line.paymentType.name) row.append(line.company.name) row.append(line.applied_amount if line.applied_amount else line.payment_amount) data.append(row) write_details(sheet, rowx, detail_head, data, heading_xf, data_xfs, False)
def write_order_lines(sheet, data, rowx, has_brand_name, has_comments): # detail_head = [u'序号', u'品名', u'品牌', u'规格要求', u'单位', u'数量', u'含税单价', u'金额', u'交货日期', u'备注'] # head_width = [2000, 6000, 0x0d00, 0x0d00, 0x0d00, 0x0d00, 0x0d00, 0x0d00, 0x0d00, 0x0d00] # merge_col = [1, 1, 1, 1, 1, 1, 1, 1, 1, 1] # kinds = 'int text text text text int price money date text'.split() detail_head = [u"序号", u"品名"] head_width = [2000, 6000] merge_col = [1, 1] kinds = "int text" style = [ ezxf("font: bold on, height 240; align: wrap on, vert centre, horiz right"), ezxf("font: bold on, height 240; align: wrap on, vert centre, horiz left"), ] if has_brand_name: detail_head.append(u"品牌") head_width.append(0x0D00) merge_col.append(1) kinds += " text" style.append(ezxf("font: bold on, height 240; align: wrap on, vert centre, horiz left")) else: # 移掉 品牌不用显示 for row in data: del row[2] detail_head += (u"规格要求", u"单位", u"数量", u"含税单价", u"金额", u"交货日期") head_width += (5000, 0x0D00, 0x0D00, 0x0D00, 0x0D00, 0x0D00) merge_col += (1, 1, 1, 1, 1, 1) kinds += " text text int price money date" style += [ ezxf("font: bold on, height 240; align: wrap on, vert centre, horiz left"), ezxf("font: bold on, height 240; align: wrap on, vert centre, horiz left"), ezxf("font: bold on, height 240; align: wrap on, vert centre, horiz right"), ezxf("font: bold on, height 240; align: wrap on, vert centre, horiz right"), ezxf("font: bold on, height 240; align: wrap on, vert centre, horiz right"), ezxf("font: bold on, height 240; align: wrap on, vert centre, horiz left"), ] if has_comments: detail_head.append(u"备注") head_width.append(0x0D00) merge_col.append(1) kinds += " text" style.append(ezxf("font: bold on, height 240; align: wrap on, vert centre, horiz left")) else: # 移掉 备注不用显示 for row in data: del row[-1] kinds = kinds.split() heading_xf = {"style": style, "width": head_width, "merge_col": merge_col} data_format = "font: height 240; align: wrap on, vert centre, horiz left" number_format = "font: height 240; align: wrap on, vert centre, horiz right" kind_to_xf_map = { "date": ezxf(data_format, num_format_str="yyyy-mm-dd"), "int": ezxf(number_format, num_format_str="#,##0"), "money": ezxf(number_format, num_format_str="#,##0.00"), "price": ezxf(number_format, num_format_str="#0.00"), "text": ezxf(data_format), } data_xfs = [kind_to_xf_map[k] for k in kinds] write_details(sheet, rowx, detail_head, data, heading_xf, data_xfs, frozen_head=False)
def write_order_lines(sheet, data, rowx, has_brand_name, has_comments): # detail_head = [u'序号', u'品名', u'品牌', u'规格要求', u'单位', u'数量', u'含税单价', u'金额', u'交货日期', u'备注'] # head_width = [2000, 6000, 0x0d00, 0x0d00, 0x0d00, 0x0d00, 0x0d00, 0x0d00, 0x0d00, 0x0d00] # merge_col = [1, 1, 1, 1, 1, 1, 1, 1, 1, 1] # kinds = 'int text text text text int price money date text'.split() detail_head = [u'序号', u'品名'] head_width = [2000, 6000] merge_col = [1, 1] kinds = 'int text' style = [ ezxf( 'font: bold on, height 240; align: wrap on, vert centre, horiz right' ), ezxf( 'font: bold on, height 240; align: wrap on, vert centre, horiz left' ) ] if has_brand_name: detail_head.append(u'品牌') head_width.append(0x0d00) merge_col.append(1) kinds += ' text' style.append( ezxf( 'font: bold on, height 240; align: wrap on, vert centre, horiz left' )) else: # 移掉 品牌不用显示 for row in data: del row[2] detail_head += (u'规格要求', u'单位', u'数量', u'含税单价', u'金额', u'交货日期') head_width += (5000, 0x0d00, 0x0d00, 0x0d00, 0x0d00, 0x0d00) merge_col += (1, 1, 1, 1, 1, 1) kinds += ' text text int price money date' style += [ ezxf( 'font: bold on, height 240; align: wrap on, vert centre, horiz left' ), ezxf( 'font: bold on, height 240; align: wrap on, vert centre, horiz left' ), ezxf( 'font: bold on, height 240; align: wrap on, vert centre, horiz right' ), ezxf( 'font: bold on, height 240; align: wrap on, vert centre, horiz right' ), ezxf( 'font: bold on, height 240; align: wrap on, vert centre, horiz right' ), ezxf( 'font: bold on, height 240; align: wrap on, vert centre, horiz left' ) ] if has_comments: detail_head.append(u'备注') head_width.append(0x0d00) merge_col.append(1) kinds += ' text' style.append( ezxf( 'font: bold on, height 240; align: wrap on, vert centre, horiz left' )) else: # 移掉 备注不用显示 for row in data: del row[-1] kinds = kinds.split() heading_xf = {'style': style, 'width': head_width, 'merge_col': merge_col} data_format = 'font: height 240; align: wrap on, vert centre, horiz left' number_format = 'font: height 240; align: wrap on, vert centre, horiz right' kind_to_xf_map = { 'date': ezxf(data_format, num_format_str='yyyy-mm-dd'), 'int': ezxf(number_format, num_format_str='#,##0'), 'money': ezxf(number_format, num_format_str='#,##0.00'), 'price': ezxf(number_format, num_format_str='#0.00'), 'text': ezxf(data_format), } data_xfs = [kind_to_xf_map[k] for k in kinds] write_details(sheet, rowx, detail_head, data, heading_xf, data_xfs, frozen_head=False)
def write_receiving_list(sheet, result, rowx): detail_head = [ u'序号', u'材料名称', u'品牌', u'规格', u'单位', u'数量', u'单价', u'金额', u'送货日期', u'送货单位', u'备注' ] head_width = [ 0x0d00, 5000, 5000, 5000, 0x0d00, 0x0d00, 0x0d00, 0x0d00, 4000, 8000, 8000 ] merge_col = [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1] kinds = 'int text text text text int price money date text text'.split( ) style = [ ezxf( 'font: bold on, height 240; align: wrap on, vert centre, horiz right' ), ezxf( 'font: bold on, height 240; align: wrap on, vert centre, horiz left' ), ezxf( 'font: bold on, height 240; align: wrap on, vert centre, horiz left' ), ezxf( 'font: bold on, height 240; align: wrap on, vert centre, horiz left' ), ezxf( 'font: bold on, height 240; align: wrap on, vert centre, horiz left' ), ezxf( 'font: bold on, height 240; align: wrap on, vert centre, horiz right' ), ezxf( 'font: bold on, height 240; align: wrap on, vert centre, horiz right' ), ezxf( 'font: bold on, height 240; align: wrap on, vert centre, horiz right' ), ezxf( 'font: bold on, height 240; align: wrap on, vert centre, horiz left' ), ezxf( 'font: bold on, height 240; align: wrap on, vert centre, horiz left' ), ezxf( 'font: bold on, height 240; align: wrap on, vert centre, horiz left' ) ] heading_xf = {'style': style, 'width': head_width, 'merge_col': merge_col} data_format = 'font: height 240; align: wrap on, vert centre, horiz left' number_format = 'font: height 240; align: wrap on, vert centre, horiz right' kind_to_xf_map = { 'date': ezxf(data_format, num_format_str='yyyy-mm-dd'), 'int': ezxf(number_format, num_format_str='#,##0'), 'money': ezxf(number_format, num_format_str='#,##0.00'), 'price': ezxf(number_format, num_format_str='#0.00'), 'text': ezxf(data_format), } data_xfs = [kind_to_xf_map[k] for k in kinds] data = [] index = 1 for line in result['lines']: row = [] row.append(index) row.append(line['projectMaterialName']) row.append(line['brand']) row.append(line['specification']) row.append(line['unit']) row.append(line['receiving_quantity']) row.append(line['price']) row.append(line['totalPrice']) row.append(line['receiving_date']) row.append(line['vendor']) row.append(line['comments'] or '') data.append(row) index = index + 1 write_details(sheet, rowx, detail_head, data, heading_xf, data_xfs)
def write_payment_summary_list(sheet, result, rowx, exportDetail): year = result['year'] month = result['month'] owed_amount_before_year_lable = str(year - 1) + '年底欠款' owed_invoice_before_year_lable = str(year - 1) + '年底欠发票' receiving_total_lable = str(year) + '年发生额' if month == 1: owed_amount_before_month_lable = '截止' + str(year-1) + '年' + '底欠款' else: owed_amount_before_month_lable = '截止' + str(year) + '年' + str(month - 1) +'月底欠款' detail_head = [u'序号', u'类别', u'供应商', owed_amount_before_year_lable, owed_invoice_before_year_lable, receiving_total_lable, u'实际未开发票', owed_amount_before_month_lable, u'实际应付款', u'送货', u'开票', u'付款'] head_width = [0x0d00, 0x0d00, 6000, 5000, 5000, 5000, 5000, 7000, 4000, 0x0d00, 0x0d00, 0x0d00] merge_col = [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1] kinds = 'int text text price price price price price price price price price'.split() style= [ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz left'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz left'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ] heading_xf = {'style' : style, 'width' : head_width, 'merge_col' : merge_col} if exportDetail : detail_head.insert(3, '公司') head_width.insert(3, 0x0d00) merge_col.insert(3, 1) kinds.insert(3, 'text') style.insert(3, ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz left')) data_format = 'font: height 240; align: wrap on, vert centre, horiz left' number_format = 'font: height 240; align: wrap on, vert centre, horiz right' kind_to_xf_map = { 'date': ezxf(data_format ,num_format_str='yyyy-mm-dd'), 'int': ezxf(number_format, num_format_str='#,##0'), 'money': ezxf(number_format ,num_format_str='#,##0.00'), 'price': ezxf(number_format,num_format_str='#0.00'), 'text': ezxf(data_format), } data_xfs = [kind_to_xf_map[k] for k in kinds] data = [] index = 1 #只导出汇总 if not exportDetail: for line in result['parent_lines']: #只导出合计 if line['index'] == -2: row = [] row.append(index) row.append(line['vendor_category'].name if line['vendor_category'] else '') vendor_name = line['vendor'].short_name if line['vendor'].short_name else line['vendor'].name row.append(vendor_name) row.append(line['owed_amount_before_year']) row.append(line['owed_invoice_before_year']) row.append(line['receiving_total']) row.append(line['unreceiving_invoice']) row.append(line['owed_amount_before_month']) row.append(line['owed_amount']) row.append(line['month_receiving']) row.append(line['month_invoice']) row.append(line['month_payment']) data.append(row) index = index + 1 else:#导出明细 for line in result['lines']: row = [] if line['index'] == -1 or line['index'] == -2: row.append('') row.append('') else: row.append(line['index']) row.append(line['vendor_category'].name if line['vendor_category'] else '') vendor_name = line['vendor'].short_name if line['vendor'].short_name else line['vendor'].name row.append(vendor_name) if line['index'] == -1 or line['index'] == -2: row.append('总计') else: row.append(line['company']) row.append(line['owed_amount_before_year']) row.append(line['owed_invoice_before_year']) row.append(line['receiving_total']) row.append(line['unreceiving_invoice']) row.append(line['owed_amount_before_month']) row.append(line['owed_amount']) row.append(line['month_receiving']) row.append(line['month_invoice']) row.append(line['month_payment']) data.append(row) index = index + 1 write_details(sheet, rowx, detail_head, data, heading_xf, data_xfs) return index
def write_project_used_list(sheet, result, rowx): detail_head = [u'序号', u'工程名称', u'预算成本', u'上线之前已用量', u'1月', u'2月', u'3月', u'4月', u'5月', u'6月', u'7月', u'8月', u'9月', u'10月', u'11月', u'12月', u'合计', u'用量百分比'] head_width = [0x0d00, 0x0d00, 5000, 5000, 4000, 4000, 4000, 4000, 4000, 4000, 4000, 4000, 4000, 4000, 4000, 4000, 5000, 4000,] merge_col = [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1] kinds = 'int text price price price price price price price price price price price price price price price price'.split() style= [ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz left'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font:bold on, height 240; align: wrap on, vert centre, horiz right') ] heading_xf = {'style' : style, 'width' : head_width, 'merge_col' : merge_col} data_format = 'font: height 240; align: wrap on, vert centre, horiz left' number_format = 'font: height 240; align: wrap on, vert centre, horiz right' kind_to_xf_map = { 'date': ezxf(data_format ,num_format_str='yyyy-mm-dd'), 'int': ezxf(number_format, num_format_str='#,##0'), 'money': ezxf(number_format ,num_format_str='#,##0.00'), 'price': ezxf(number_format,num_format_str='#0.00'), 'text': ezxf(data_format), } data_xfs = [kind_to_xf_map[k] for k in kinds] data = [] for line in result['lines']: row = [] row.append(line['index']) row.append(line['project_name']) row.append(line['estimate_total']) row.append(line['online_before_amount']) row.append(line['one_month']) row.append(line['two_month']) row.append(line['three_month']) row.append(line['four_month']) row.append(line['five_month']) row.append(line['six_month']) row.append(line['seven_month']) row.append(line['eight_month']) row.append(line['night_month']) row.append(line['ten_month']) row.append(line['eleven_month']) row.append(line['twelve_month']) row.append(line['total']) percent = str(line['percent']) + '%' row.append(percent) data.append(row) write_details(sheet, rowx, detail_head, data, heading_xf, data_xfs)
def write_receiving_list(sheet, result, rowx, has_brand_name): detail_head = [u'序号', u'送货日期', u'工程项目', u'材料名称'] head_width = [1500, 5000, 8000, 5000] merge_col = [1, 1, 1, 1] kinds = 'int date text text' style = [ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz left'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz left'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz left') ] if has_brand_name: detail_head.append(u'品牌') head_width.append(0x0d00) merge_col.append(1) kinds += ' text' style += [ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz left')] detail_head += [u'规格', u'单位', u'数量', u'单价', u'金额', u'备注'] head_width += [5000, 2500, 2500, 5000, 5000, 5000] merge_col += [1, 1, 1, 1, 1, 1] kinds += ' text text float money money text' style += [ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz left'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz left'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz right'), ezxf('font: bold on, height 240; align: wrap on, vert centre, horiz center'), ] kinds = kinds.split() heading_xf = {'style' : style, 'width' : head_width, 'merge_col' : merge_col} data_format = 'font: height 240; align: wrap on, vert centre, horiz left' number_format = 'font: height 240; align: wrap on, vert centre, horiz right' kind_to_xf_map = { 'date': ezxf(data_format , num_format_str='yyyy-mm-dd'), 'int': ezxf(number_format, num_format_str='#,##0'), 'float': ezxf(number_format, num_format_str='#0.00'), 'money': ezxf(number_format , num_format_str='#,##0.00'), 'price': ezxf(number_format, num_format_str='#0.00'), 'text': ezxf(data_format), } data_xfs = [kind_to_xf_map[k] for k in kinds] data = [] index = 1 for line in result['receiving_list']: row = [] row.append(index) row.append(line['receiving_date']) row.append(line['project']) row.append(line['projectMaterialName']) row.append(line['brand']) row.append(line['specification']) row.append(line['unit']) row.append(line['receiving_quantity']) row.append(line['price']) row.append(line['totalPrice']) row.append(line['comments']) data.append(row) index = index + 1 # 移掉 品牌不用显示 if has_brand_name is False: for row in data: del row[4] write_details(sheet, rowx, detail_head, data, heading_xf, data_xfs, False)
def write_payment_summary_list(sheet, result, rowx, exportDetail): year = result['year'] month = result['month'] owed_amount_before_year_lable = str(year - 1) + '年底欠款' owed_invoice_before_year_lable = str(year - 1) + '年底欠发票' receiving_total_lable = str(year) + '年发生额' if month == 1: owed_amount_before_month_lable = '截止' + str(year - 1) + '年' + '底欠款' else: owed_amount_before_month_lable = '截止' + str(year) + '年' + str( month - 1) + '月底欠款' detail_head = [ u'序号', u'类别', u'供应商', owed_amount_before_year_lable, owed_invoice_before_year_lable, receiving_total_lable, u'实际未开发票', owed_amount_before_month_lable, u'实际应付款', u'送货', u'开票', u'付款' ] head_width = [ 0x0d00, 0x0d00, 6000, 5000, 5000, 5000, 5000, 7000, 4000, 0x0d00, 0x0d00, 0x0d00 ] merge_col = [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1] kinds = 'int text text price price price price price price price price price'.split( ) style = [ ezxf( 'font:bold on, height 240; align: wrap on, vert centre, horiz right' ), ezxf( 'font:bold on, height 240; align: wrap on, vert centre, horiz left' ), ezxf( 'font:bold on, height 240; align: wrap on, vert centre, horiz left' ), ezxf( 'font:bold on, height 240; align: wrap on, vert centre, horiz right' ), ezxf( 'font:bold on, height 240; align: wrap on, vert centre, horiz right' ), ezxf( 'font:bold on, height 240; align: wrap on, vert centre, horiz right' ), ezxf( 'font:bold on, height 240; align: wrap on, vert centre, horiz right' ), ezxf( 'font:bold on, height 240; align: wrap on, vert centre, horiz right' ), ezxf( 'font:bold on, height 240; align: wrap on, vert centre, horiz right' ), ezxf( 'font:bold on, height 240; align: wrap on, vert centre, horiz right' ), ezxf( 'font:bold on, height 240; align: wrap on, vert centre, horiz right' ), ezxf( 'font:bold on, height 240; align: wrap on, vert centre, horiz right' ), ] heading_xf = {'style': style, 'width': head_width, 'merge_col': merge_col} if exportDetail: detail_head.insert(3, '公司') head_width.insert(3, 0x0d00) merge_col.insert(3, 1) kinds.insert(3, 'text') style.insert( 3, ezxf( 'font:bold on, height 240; align: wrap on, vert centre, horiz left' )) data_format = 'font: height 240; align: wrap on, vert centre, horiz left' number_format = 'font: height 240; align: wrap on, vert centre, horiz right' kind_to_xf_map = { 'date': ezxf(data_format, num_format_str='yyyy-mm-dd'), 'int': ezxf(number_format, num_format_str='#,##0'), 'money': ezxf(number_format, num_format_str='#,##0.00'), 'price': ezxf(number_format, num_format_str='#0.00'), 'text': ezxf(data_format), } data_xfs = [kind_to_xf_map[k] for k in kinds] data = [] index = 1 #只导出汇总 if not exportDetail: for line in result['parent_lines']: #只导出合计 if line['index'] == -2: row = [] row.append(index) row.append(line['vendor_category']. name if line['vendor_category'] else '') vendor_name = line['vendor'].short_name if line[ 'vendor'].short_name else line['vendor'].name row.append(vendor_name) row.append(line['owed_amount_before_year']) row.append(line['owed_invoice_before_year']) row.append(line['receiving_total']) row.append(line['unreceiving_invoice']) row.append(line['owed_amount_before_month']) row.append(line['owed_amount']) row.append(line['month_receiving']) row.append(line['month_invoice']) row.append(line['month_payment']) data.append(row) index = index + 1 else: #导出明细 for line in result['lines']: row = [] if line['index'] == -1 or line['index'] == -2: row.append('') row.append('') else: row.append(line['index']) row.append(line['vendor_category']. name if line['vendor_category'] else '') vendor_name = line['vendor'].short_name if line[ 'vendor'].short_name else line['vendor'].name row.append(vendor_name) if line['index'] == -1 or line['index'] == -2: row.append('总计') else: row.append(line['company']) row.append(line['owed_amount_before_year']) row.append(line['owed_invoice_before_year']) row.append(line['receiving_total']) row.append(line['unreceiving_invoice']) row.append(line['owed_amount_before_month']) row.append(line['owed_amount']) row.append(line['month_receiving']) row.append(line['month_invoice']) row.append(line['month_payment']) data.append(row) index = index + 1 write_details(sheet, rowx, detail_head, data, heading_xf, data_xfs) return index