def execAddWorksheetInvalidRPT01StoreInv(objSheet, listInvalidRPT01StoreInv, objColumnFormat, objRowCenterFormat, objRowLeftFormat, objRowRightFormat): objSheet.set_default_row(20) objSheet.set_row(0, 40) objSheet.set_column(0, 0, 8) objSheet.set_column(1, 1, 10) objSheet.set_column(2, 2, 15) objSheet.set_column(3, 3, 30) objSheet.set_column(4, 4, 15) objSheet.set_column(5, 5, 15) intRowIndex = 0 objSheet.write(intRowIndex, 0, "系统代号", objColumnFormat) objSheet.write(intRowIndex, 1, "系统名称", objColumnFormat) objSheet.write(intRowIndex, 2, "系统门店代号", objColumnFormat) objSheet.write(intRowIndex, 3, "系统门店名称", objColumnFormat) objSheet.write(intRowIndex, 4, "最早未匹配数据\n库存日期", objColumnFormat) objSheet.write(intRowIndex, 5, "最晚未匹配数据\n库存日期", objColumnFormat) intRowIndex += 1 for dictInvalidRPT01StoreInv in listInvalidRPT01StoreInv: objSheet.write(intRowIndex, 0, dictInvalidRPT01StoreInv["KA_SYSTEM_CODE"], objRowCenterFormat) objSheet.write(intRowIndex, 1, dictInvalidRPT01StoreInv["KA_SYSTEM_NM"], objRowCenterFormat) objSheet.write(intRowIndex, 2, dictInvalidRPT01StoreInv["SYSTEM_STORE_CODE"], objRowLeftFormat) objSheet.write(intRowIndex, 3, dictInvalidRPT01StoreInv["SYSTEM_STORE_NM"], objRowLeftFormat) objSheet.write(intRowIndex, 4, Common.formatDateString(dictInvalidRPT01StoreInv["INV_DATE_MIN"]), objRowCenterFormat) objSheet.write(intRowIndex, 5, Common.formatDateString(dictInvalidRPT01StoreInv["INV_DATE_MAX"]), objRowCenterFormat) intRowIndex += 1 return True
def execAddWorksheetInvalidRPT01ProdPos(objSheet, listInvalidRPT01ProdPos, objColumnFormat, objRowCenterFormat, objRowLeftFormat, objRowRightFormat): objSheet.set_default_row(20) objSheet.set_row(0, 40) objSheet.set_column(0, 0, 8) objSheet.set_column(1, 1, 10) objSheet.set_column(2, 2, 15) objSheet.set_column(3, 3, 30) objSheet.set_column(4, 4, 15) objSheet.set_column(5, 5, 15) intRowIndex = 0 objSheet.write(intRowIndex, 0, "系统代号", objColumnFormat) objSheet.write(intRowIndex, 1, "系统名称", objColumnFormat) objSheet.write(intRowIndex, 2, "系统产品销售编码", objColumnFormat) objSheet.write(intRowIndex, 3, "系统产品名称", objColumnFormat) objSheet.write(intRowIndex, 4, "最早未匹配数据\n销售日期", objColumnFormat) objSheet.write(intRowIndex, 5, "最晚未匹配数据\n销售日期", objColumnFormat) intRowIndex += 1 for dictInvalidRPT01ProdPos in listInvalidRPT01ProdPos: objSheet.write(intRowIndex, 0, dictInvalidRPT01ProdPos["KA_SYSTEM_CODE"], objRowCenterFormat) objSheet.write(intRowIndex, 1, dictInvalidRPT01ProdPos["KA_SYSTEM_NM"], objRowCenterFormat) objSheet.write(intRowIndex, 2, dictInvalidRPT01ProdPos["SYSTEM_PROD_CODE"], objRowLeftFormat) objSheet.write(intRowIndex, 3, dictInvalidRPT01ProdPos["SYSTEM_PROD_NM"], objRowLeftFormat) objSheet.write(intRowIndex, 4, Common.formatDateString(dictInvalidRPT01ProdPos["POS_DATE_MIN"]), objRowCenterFormat) objSheet.write(intRowIndex, 5, Common.formatDateString(dictInvalidRPT01ProdPos["POS_DATE_MAX"]), objRowCenterFormat) intRowIndex += 1 return True
def execWriteWorkbook(strJobPathRES, listWeekNum, listPOSRPT01, listPOSRPT01System, listPOSRPT01SystemPCS, listPOSRPT01SystemSKU, listPOSRPT01ProdH1PCS, listPOSRPT01ProdH1SKU): if len(listWeekNum) == 0 or len(listPOSRPT01) == 0 or len(listPOSRPT01ProdH1PCS) != len(listPOSRPT01ProdH1SKU) or len(listPOSRPT01SystemPCS) != len(listPOSRPT01SystemSKU): return False else: objExcelWorkbook = None objExcelWorksheet = None objExcelRowCenterFormat = None objExcelRowLeftFormat = None objExcelRowRightFormat = None strKA_SYSTEM_ACT_NM = None strKA_SYSTEM_CODE = None strKA_SYSTEM_NM = None intPOSRPT01SystemIndex = 0 intPOSRPT01ProdH1Index = 0 intExcelRow = 0 intNowDates = (Common.getDate(listPOSRPT01[0]["POS_DATE"]) - Common.getDate(listWeekNum[0]["WEEK_FIRST_DATE"])).days + 1 while intPOSRPT01ProdH1Index < len(listPOSRPT01ProdH1PCS): if strKA_SYSTEM_CODE != listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["KA_SYSTEM_CODE"]: strKA_SYSTEM_ACT_NM = listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["KA_SYSTEM_ACT_NM"] strKA_SYSTEM_CODE = listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["KA_SYSTEM_CODE"] strKA_SYSTEM_NM = listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["KA_SYSTEM_NM"] if objExcelWorksheet != None: objExcelWorksheet.freeze_panes("K8") objExcelWorksheet.autofilter("A7:CF" + str(intExcelRow)) objExcelWorksheet.set_column('F:G', None, None, {'hidden': True}) if objExcelWorkbook != None: objExcelWorkbook.close() objExcelWorkbook = xlsxwriter.Workbook(strJobPathRES.replace(".dat", "-" + strKA_SYSTEM_CODE + ".dat")) objExcelTitleFormat = objExcelWorkbook.add_format({ "bold": 1, "align": "left", "valign": "vcenter", "font_size": 16, "font_color": "#305496", "fg_color": "#D6DCE4", "border": 0}) objExcelSubTitleFormat = objExcelWorkbook.add_format({ "bold": 1, "align": "left", "valign": "vcenter", "font_size": 11, "font_color": "#305496", "fg_color": "#D6DCE4", "border": 0}) objExcelColumnFormat = objExcelWorkbook.add_format({ "text_wrap": 1, "bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#FFFFFF", "fg_color": "#305496", "border": 0}) objExcelWeekFormat = objExcelWorkbook.add_format({ "bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#FFFFFF", "fg_color": "#305496", "border": 1, "top_color": "#305496", "bottom_color": "#FFFFFF", "left_color": "#305496", "right_color": "#305496"}) objExcelRowFilterFormat = objExcelWorkbook.add_format({ "bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#000000", "fg_color": "#BFBEBF", "border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#BFBEBF", "right_color": "#BFBEBF"}) objExcelRowCenter1Format = objExcelWorkbook.add_format({ "bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#000000", "fg_color": "#FFFFFF", "border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#305496", "right_color": "#305496"}) objExcelRowCenter2Format = objExcelWorkbook.add_format({ "bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#000000", "fg_color": "#D9E1F2", "border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#305496", "right_color": "#305496"}) objExcelRowLeft1Format = objExcelWorkbook.add_format({ "bold": 1, "align": "left", "valign": "vcenter", "font_size": 11, "font_color": "#000000", "fg_color": "#FFFFFF", "border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#305496", "right_color": "#305496"}) objExcelRowLeft2Format = objExcelWorkbook.add_format({ "bold": 1, "align": "left", "valign": "vcenter", "font_size": 11, "font_color": "#000000", "fg_color": "#D9E1F2", "border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#305496", "right_color": "#305496"}) objExcelRowRight1Format = objExcelWorkbook.add_format({ "num_format": "#,##0", "bold": 1, "align": "right", "valign": "vcenter", "font_size": 11, "font_color": "#000000", "fg_color": "#FFFFFF", "border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#FFFFFF", "right_color": "#FFFFFF"}) objExcelRowRight2Format = objExcelWorkbook.add_format({ "num_format": "#,##0", "bold": 1, "align": "right", "valign": "vcenter", "font_size": 11, "font_color": "#000000", "fg_color": "#D9E1F2", "border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#D9E1F2", "right_color": "#D9E1F2"}) objExcelRowIcon1Format = objExcelWorkbook.add_format({ "bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#000000", "fg_color": "#FFFFFF", "border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#FFFFFF", "right_color": "#305496"}) objExcelRowIcon1UpFormat = objExcelWorkbook.add_format({ "bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#B02317", "fg_color": "#FFFFFF", "border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#FFFFFF", "right_color": "#305496"}) objExcelRowIcon1DownFormat = objExcelWorkbook.add_format({ "bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#4EAD5B", "fg_color": "#FFFFFF", "border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#FFFFFF", "right_color": "#305496"}) objExcelRowIcon1FlatFormat = objExcelWorkbook.add_format({ "bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#EBA064", "fg_color": "#FFFFFF", "border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#FFFFFF", "right_color": "#305496"}) objExcelRowIcon2Format = objExcelWorkbook.add_format({ "bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#000000", "fg_color": "#D9E1F2", "border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#D9E1F2", "right_color": "#305496"}) objExcelRowIcon2UpFormat = objExcelWorkbook.add_format({ "bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#B02317", "fg_color": "#D9E1F2", "border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#D9E1F2", "right_color": "#305496"}) objExcelRowIcon2DownFormat = objExcelWorkbook.add_format({ "bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#4EAD5B", "fg_color": "#D9E1F2", "border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#D9E1F2", "right_color": "#305496"}) objExcelRowIcon2FlatFormat = objExcelWorkbook.add_format({ "bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#EBA064", "fg_color": "#D9E1F2", "border": 1, "top_color": "#F2F2F2", "bottom_color": "#F2F2F2", "left_color": "#D9E1F2", "right_color": "#305496"}) objExcelWorksheet = objExcelWorkbook.add_worksheet(strKA_SYSTEM_CODE + "-" + strKA_SYSTEM_NM) objExcelWorksheet.set_default_row(20) objExcelWorksheet.set_row(3, 30) objExcelWorksheet.set_column(0, 6, 8) objExcelWorksheet.set_column(7, 7, 10) objExcelWorksheet.set_column(8, 8, 20) objExcelWorksheet.set_column(9, 9, 11) objExcelRowCenterFormat = objExcelRowCenter1Format objExcelRowLeftFormat = objExcelRowLeft1Format objExcelRowRightFormat = objExcelRowRight1Format objExcelRowIconFormat = objExcelRowIcon1Format intExcelRow = 0 intTemp = 0 for dictWeekNum in listWeekNum: objExcelWorksheet.set_column(10 + intTemp * 2, 10 + intTemp * 2, 11) objExcelWorksheet.set_column(10 + intTemp * 2 + 1, 10 + intTemp * 2 + 1, 2.5) intTemp += 1 objExcelWorksheet.merge_range("A1:I1", "现代渠道发展营业部业绩-通路系统品类周别POS推移", objExcelTitleFormat) objExcelWorksheet.write(intExcelRow, 8, "", objExcelTitleFormat) objExcelWorksheet.write(intExcelRow, 9, "", objExcelTitleFormat) intTemp = 0 for dictWeekNum in listWeekNum: objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2, "", objExcelTitleFormat) objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2 + 1, "", objExcelTitleFormat) intTemp += 1 intExcelRow += 1 objExcelWorksheet.merge_range("A2:H2", Common.formatDateString(listPOSRPT01[0]["POS_DATE"]) + " 销售数量为最小单位加总", objExcelSubTitleFormat) objExcelWorksheet.write(intExcelRow, 8, "", objExcelTitleFormat) objExcelWorksheet.write(intExcelRow, 9, "", objExcelTitleFormat) intTemp = 0 for dictWeekNum in listWeekNum: objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2, "", objExcelTitleFormat) objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2 + 1, "", objExcelTitleFormat) intTemp += 1 intExcelRow += 1 objExcelWorksheet.merge_range("A3:A4", "通路", objExcelColumnFormat) objExcelWorksheet.merge_range("B3:B4", "系统编码", objExcelColumnFormat) objExcelWorksheet.merge_range("C3:C4", "系统名称", objExcelColumnFormat) objExcelWorksheet.merge_range("D3:D4", "分公司\n编码", objExcelColumnFormat) objExcelWorksheet.merge_range("E3:E4", "分公司\n名称", objExcelColumnFormat) objExcelWorksheet.merge_range("F3:F4", "统仓地\n编码", objExcelColumnFormat) objExcelWorksheet.merge_range("G3:G4", "统仓地\n名称", objExcelColumnFormat) objExcelWorksheet.merge_range("H3:H4", "品类编码", objExcelColumnFormat) objExcelWorksheet.merge_range("I3:I4", "品类名称", objExcelColumnFormat) objExcelWorksheet.write(intExcelRow, 9, "周数", objExcelColumnFormat) intTemp = 0 for dictWeekNum in listWeekNum: objExcelWorksheet.merge_range(getExcelColumnName(65 + 10 + intTemp * 2) + "3:" + getExcelColumnName(65 + 10 + intTemp * 2 + 1) + "3", int(dictWeekNum["WEEK_NUM"][-2:]), objExcelWeekFormat) intTemp += 1 intExcelRow += 1 objExcelWorksheet.write(intExcelRow, 9, "KPI", objExcelColumnFormat) intTemp = 0 for dictWeekNum in listWeekNum: objExcelWorksheet.merge_range(getExcelColumnName(65 + 10 + intTemp * 2) + "4:" + getExcelColumnName(65 + 10 + intTemp * 2 + 1) + "4", Common.formatDateString(dictWeekNum["WEEK_FIRST_DATE"]), objExcelColumnFormat) intTemp += 1 intExcelRow += 1 objExcelWorksheet.write(intExcelRow, 0, strKA_SYSTEM_ACT_NM, objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 1, strKA_SYSTEM_CODE, objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 2, strKA_SYSTEM_NM, objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 3, "", objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 4, "", objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 5, "", objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 6, "", objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 7, "", objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 8, "合计", objExcelRowLeftFormat) objExcelWorksheet.write(intExcelRow, 9, "销售数量", objExcelRowCenterFormat) intIndexCount = 0 intIndexTemp = 0 intTemp = 0 for dictWeekNum in listWeekNum: intIndexTemp = intPOSRPT01SystemIndex + intIndexCount if intIndexTemp < len(listPOSRPT01SystemPCS) and \ listPOSRPT01SystemPCS[intIndexTemp]["KA_SYSTEM_CODE"] == strKA_SYSTEM_CODE and \ listPOSRPT01SystemPCS[intIndexTemp]["POS_WEEK_NUM"] == listWeekNum[intTemp]["WEEK_NUM"]: strDirection = "" decTarget = 0 objExcelRowIconDirFormat = objExcelRowIconFormat if (intIndexTemp + 1) < len(listPOSRPT01SystemPCS) and (intTemp + 1) < len(listWeekNum) and \ listPOSRPT01SystemPCS[intIndexTemp + 1]["KA_SYSTEM_CODE"] == strKA_SYSTEM_CODE and \ listPOSRPT01SystemPCS[intIndexTemp + 1]["POS_WEEK_NUM"] == listWeekNum[intTemp + 1]["WEEK_NUM"]: if intTemp == 0: decTarget = round(listPOSRPT01SystemPCS[intIndexTemp + 1]["POS_QTY_PCS"] / 7 * intNowDates, 0) else: decTarget = listPOSRPT01SystemPCS[intIndexTemp + 1]["POS_QTY_PCS"] if listPOSRPT01SystemPCS[intIndexTemp]["POS_QTY_PCS"] > decTarget: strDirection = "▲" if objExcelRowIconFormat == objExcelRowIcon1Format: objExcelRowIconDirFormat = objExcelRowIcon1UpFormat else: objExcelRowIconDirFormat = objExcelRowIcon2UpFormat elif listPOSRPT01SystemPCS[intIndexTemp]["POS_QTY_PCS"] < decTarget: strDirection = "▼" if objExcelRowIconFormat == objExcelRowIcon1Format: objExcelRowIconDirFormat = objExcelRowIcon1DownFormat else: objExcelRowIconDirFormat = objExcelRowIcon2DownFormat else: strDirection = "━" if objExcelRowIconFormat == objExcelRowIcon1Format: objExcelRowIconDirFormat = objExcelRowIcon1FlatFormat else: objExcelRowIconDirFormat = objExcelRowIcon2FlatFormat objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2, listPOSRPT01SystemPCS[intIndexTemp]["POS_QTY_PCS"], objExcelRowRightFormat) objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2 + 1, strDirection, objExcelRowIconDirFormat) intIndexCount += 1 intTemp += 1 else: objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2, "-", objExcelRowRightFormat) objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2 + 1, "", objExcelRowIconFormat) intTemp += 1 intExcelRow += 1 objExcelWorksheet.write(intExcelRow, 0, strKA_SYSTEM_ACT_NM, objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 1, strKA_SYSTEM_CODE, objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 2, strKA_SYSTEM_NM, objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 3, "", objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 4, "", objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 5, "", objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 6, "", objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 7, "", objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 8, "合计", objExcelRowLeftFormat) objExcelWorksheet.write(intExcelRow, 9, "品项数", objExcelRowCenterFormat) intIndexCount = 0 intIndexTemp = 0 intTemp = 0 for dictWeekNum in listWeekNum: intIndexTemp = intPOSRPT01SystemIndex + intIndexCount if intIndexTemp < len(listPOSRPT01SystemSKU) and \ listPOSRPT01SystemSKU[intIndexTemp]["KA_SYSTEM_CODE"] == strKA_SYSTEM_CODE and \ listPOSRPT01SystemSKU[intIndexTemp]["POS_WEEK_NUM"] == listWeekNum[intTemp]["WEEK_NUM"]: strDirection = "" decTarget = 0 objExcelRowIconDirFormat = objExcelRowIconFormat if (intIndexTemp + 1) < len(listPOSRPT01SystemSKU) and (intTemp + 1) < len(listWeekNum) and \ listPOSRPT01SystemSKU[intIndexTemp + 1]["KA_SYSTEM_CODE"] == strKA_SYSTEM_CODE and \ listPOSRPT01SystemSKU[intIndexTemp + 1]["POS_WEEK_NUM"] == listWeekNum[intTemp + 1]["WEEK_NUM"]: decTarget = listPOSRPT01SystemSKU[intIndexTemp + 1]["POS_SKU"] if listPOSRPT01SystemSKU[intIndexTemp]["POS_SKU"] > decTarget: strDirection = "▲" if objExcelRowIconFormat == objExcelRowIcon1Format: objExcelRowIconDirFormat = objExcelRowIcon1UpFormat else: objExcelRowIconDirFormat = objExcelRowIcon2UpFormat elif listPOSRPT01SystemSKU[intIndexTemp]["POS_SKU"] < decTarget: strDirection = "▼" if objExcelRowIconFormat == objExcelRowIcon1Format: objExcelRowIconDirFormat = objExcelRowIcon1DownFormat else: objExcelRowIconDirFormat = objExcelRowIcon2DownFormat else: strDirection = "━" if objExcelRowIconFormat == objExcelRowIcon1Format: objExcelRowIconDirFormat = objExcelRowIcon1FlatFormat else: objExcelRowIconDirFormat = objExcelRowIcon2FlatFormat objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2, listPOSRPT01SystemSKU[intIndexTemp]["POS_SKU"], objExcelRowRightFormat) objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2 + 1, strDirection, objExcelRowIconDirFormat) intIndexCount += 1 intTemp += 1 else: objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2, "-", objExcelRowRightFormat) objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2 + 1, "", objExcelRowIconFormat) intTemp += 1 intExcelRow += 1 intPOSRPT01SystemIndex += intIndexCount objExcelWorksheet.write(intExcelRow, 0, "", objExcelRowFilterFormat) objExcelWorksheet.write(intExcelRow, 1, "", objExcelRowFilterFormat) objExcelWorksheet.write(intExcelRow, 2, "", objExcelRowFilterFormat) objExcelWorksheet.write(intExcelRow, 3, "", objExcelRowFilterFormat) objExcelWorksheet.write(intExcelRow, 4, "", objExcelRowFilterFormat) objExcelWorksheet.write(intExcelRow, 5, "", objExcelRowFilterFormat) objExcelWorksheet.write(intExcelRow, 6, "", objExcelRowFilterFormat) objExcelWorksheet.write(intExcelRow, 7, "", objExcelRowFilterFormat) objExcelWorksheet.write(intExcelRow, 8, "", objExcelRowFilterFormat) objExcelWorksheet.write(intExcelRow, 9, "筛选", objExcelRowFilterFormat) intTemp = 0 for dictWeekNum in listWeekNum: objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2, "", objExcelRowFilterFormat) objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2 + 1, "", objExcelRowFilterFormat) intTemp += 1 intExcelRow += 1 if objExcelRowCenterFormat == objExcelRowCenter2Format: objExcelRowCenterFormat = objExcelRowCenter1Format objExcelRowLeftFormat = objExcelRowLeft1Format objExcelRowRightFormat = objExcelRowRight1Format objExcelRowIconFormat = objExcelRowIcon1Format else: objExcelRowCenterFormat = objExcelRowCenter2Format objExcelRowLeftFormat = objExcelRowLeft2Format objExcelRowRightFormat = objExcelRowRight2Format objExcelRowIconFormat = objExcelRowIcon2Format objExcelWorksheet.write(intExcelRow, 0, listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["KA_SYSTEM_ACT_NM"], objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 1, listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["KA_SYSTEM_CODE"], objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 2, listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["KA_SYSTEM_NM"], objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 3, listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["SALES_COM_ID_SA"], objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 4, listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["SALES_COM_ABR_SA"], objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 5, "", objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 6, "", objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 7, listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["PROD_H1_ID"], objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 8, listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["PROD_H1_NM"], objExcelRowLeftFormat) objExcelWorksheet.write(intExcelRow, 9, "销售数量", objExcelRowCenterFormat) intIndexCount = 0 intIndexTemp = 0 intTemp = 0 for dictWeekNum in listWeekNum: intIndexTemp = intPOSRPT01ProdH1Index + intIndexCount if intIndexTemp < len(listPOSRPT01ProdH1PCS) and \ listPOSRPT01ProdH1PCS[intIndexTemp]["KA_SYSTEM_CODE"] == strKA_SYSTEM_CODE and \ listPOSRPT01ProdH1PCS[intIndexTemp]["PROD_H1_ID"] == listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["PROD_H1_ID"] and \ listPOSRPT01ProdH1PCS[intIndexTemp]["POS_WEEK_NUM"] == listWeekNum[intTemp]["WEEK_NUM"]: strDirection = "" decTarget = 0 objExcelRowIconDirFormat = objExcelRowIconFormat if (intIndexTemp + 1) < len(listPOSRPT01ProdH1PCS) and (intTemp + 1) < len(listWeekNum) and \ listPOSRPT01ProdH1PCS[intIndexTemp + 1]["KA_SYSTEM_CODE"] == strKA_SYSTEM_CODE and \ listPOSRPT01ProdH1PCS[intIndexTemp + 1]["PROD_H1_ID"] == listPOSRPT01ProdH1PCS[intPOSRPT01ProdH1Index]["PROD_H1_ID"] and \ listPOSRPT01ProdH1PCS[intIndexTemp + 1]["POS_WEEK_NUM"] == listWeekNum[intTemp + 1]["WEEK_NUM"]: if intTemp == 0: decTarget = round(listPOSRPT01ProdH1PCS[intIndexTemp + 1]["POS_QTY_PCS"] / 7 * intNowDates, 0) else: decTarget = listPOSRPT01ProdH1PCS[intIndexTemp + 1]["POS_QTY_PCS"] if listPOSRPT01ProdH1PCS[intIndexTemp]["POS_QTY_PCS"] > decTarget: strDirection = "▲" if objExcelRowIconFormat == objExcelRowIcon1Format: objExcelRowIconDirFormat = objExcelRowIcon1UpFormat else: objExcelRowIconDirFormat = objExcelRowIcon2UpFormat elif listPOSRPT01ProdH1PCS[intIndexTemp]["POS_QTY_PCS"] < decTarget: strDirection = "▼" if objExcelRowIconFormat == objExcelRowIcon1Format: objExcelRowIconDirFormat = objExcelRowIcon1DownFormat else: objExcelRowIconDirFormat = objExcelRowIcon2DownFormat else: strDirection = "━" if objExcelRowIconFormat == objExcelRowIcon1Format: objExcelRowIconDirFormat = objExcelRowIcon1FlatFormat else: objExcelRowIconDirFormat = objExcelRowIcon2FlatFormat objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2, listPOSRPT01ProdH1PCS[intIndexTemp]["POS_QTY_PCS"], objExcelRowRightFormat) objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2 + 1, strDirection, objExcelRowIconDirFormat) intIndexCount += 1 intTemp += 1 else: objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2, "-", objExcelRowRightFormat) objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2 + 1, "", objExcelRowIconFormat) intTemp += 1 intExcelRow += 1 objExcelWorksheet.write(intExcelRow, 0, listPOSRPT01ProdH1SKU[intPOSRPT01ProdH1Index]["KA_SYSTEM_ACT_NM"], objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 1, listPOSRPT01ProdH1SKU[intPOSRPT01ProdH1Index]["KA_SYSTEM_CODE"], objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 2, listPOSRPT01ProdH1SKU[intPOSRPT01ProdH1Index]["KA_SYSTEM_NM"], objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 3, listPOSRPT01ProdH1SKU[intPOSRPT01ProdH1Index]["SALES_COM_ID_SA"], objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 4, listPOSRPT01ProdH1SKU[intPOSRPT01ProdH1Index]["SALES_COM_ABR_SA"], objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 5, "", objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 6, "", objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 7, listPOSRPT01ProdH1SKU[intPOSRPT01ProdH1Index]["PROD_H1_ID"], objExcelRowCenterFormat) objExcelWorksheet.write(intExcelRow, 8, listPOSRPT01ProdH1SKU[intPOSRPT01ProdH1Index]["PROD_H1_NM"], objExcelRowLeftFormat) objExcelWorksheet.write(intExcelRow, 9, "品项数", objExcelRowCenterFormat) intIndexCount = 0 intIndexTemp = 0 intTemp = 0 for dictWeekNum in listWeekNum: intIndexTemp = intPOSRPT01ProdH1Index + intIndexCount if intIndexTemp < len(listPOSRPT01ProdH1SKU) and \ listPOSRPT01ProdH1SKU[intIndexTemp]["KA_SYSTEM_CODE"] == strKA_SYSTEM_CODE and \ listPOSRPT01ProdH1SKU[intIndexTemp]["PROD_H1_ID"] == listPOSRPT01ProdH1SKU[intPOSRPT01ProdH1Index]["PROD_H1_ID"] and \ listPOSRPT01ProdH1SKU[intIndexTemp]["POS_WEEK_NUM"] == listWeekNum[intTemp]["WEEK_NUM"]: strDirection = "" decTarget = 0 objExcelRowIconDirFormat = objExcelRowIconFormat if (intIndexTemp + 1) < len(listPOSRPT01ProdH1SKU) and (intTemp + 1) < len(listWeekNum) and \ listPOSRPT01ProdH1SKU[intIndexTemp + 1]["KA_SYSTEM_CODE"] == strKA_SYSTEM_CODE and \ listPOSRPT01ProdH1SKU[intIndexTemp + 1]["PROD_H1_ID"] == listPOSRPT01ProdH1SKU[intPOSRPT01ProdH1Index]["PROD_H1_ID"] and \ listPOSRPT01ProdH1SKU[intIndexTemp + 1]["POS_WEEK_NUM"] == listWeekNum[intTemp + 1]["WEEK_NUM"]: decTarget = listPOSRPT01ProdH1SKU[intIndexTemp + 1]["POS_SKU"] if listPOSRPT01ProdH1SKU[intIndexTemp]["POS_SKU"] > decTarget: strDirection = "▲" if objExcelRowIconFormat == objExcelRowIcon1Format: objExcelRowIconDirFormat = objExcelRowIcon1UpFormat else: objExcelRowIconDirFormat = objExcelRowIcon2UpFormat elif listPOSRPT01ProdH1SKU[intIndexTemp]["POS_SKU"] < decTarget: strDirection = "▼" if objExcelRowIconFormat == objExcelRowIcon1Format: objExcelRowIconDirFormat = objExcelRowIcon1DownFormat else: objExcelRowIconDirFormat = objExcelRowIcon2DownFormat else: strDirection = "━" if objExcelRowIconFormat == objExcelRowIcon1Format: objExcelRowIconDirFormat = objExcelRowIcon1FlatFormat else: objExcelRowIconDirFormat = objExcelRowIcon2FlatFormat objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2, listPOSRPT01ProdH1SKU[intIndexTemp]["POS_SKU"], objExcelRowRightFormat) objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2 + 1, strDirection, objExcelRowIconDirFormat) intIndexCount += 1 intTemp += 1 else: objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2, "-", objExcelRowRightFormat) objExcelWorksheet.write(intExcelRow, 10 + intTemp * 2 + 1, "", objExcelRowIconFormat) intTemp += 1 intExcelRow += 1 intPOSRPT01ProdH1Index += intIndexCount if objExcelWorksheet != None: objExcelWorksheet.freeze_panes("K8") objExcelWorksheet.autofilter("A7:CF" + str(intExcelRow)) objExcelWorksheet.set_column('F:G', None, None, {'hidden': True}) if objExcelWorkbook != None: objExcelWorkbook.close() return True
def execWriteWorkbook(strJobPathRES, listStoreMatlD): if len(listStoreMatlD) == 0: return False else: objBook = None strKA_SYSTEM_CODE = None intRowIndex = 0 for dictStoreMatlD in listStoreMatlD: if dictStoreMatlD["KA_SYSTEM_CODE"] != strKA_SYSTEM_CODE: strKA_SYSTEM_CODE = dictStoreMatlD["KA_SYSTEM_CODE"] if objBook != None: objBook.close() objBook = xlsxwriter.Workbook( strJobPathRES.replace( ".dat", "-" + dictStoreMatlD["KA_SYSTEM_CODE"] + ".dat")) objColumnFormat = objBook.add_format({ "text_wrap": 1, "align": "center", "valign": "vcenter", "bold": 1, "font_size": 11, "font_color": "#FFFFFF", "fg_color": "#305496", "border": 0 }) objRowCenterFormat = objBook.add_format({ "align": "center", "valign": "vcenter", "bold": 1, "font_size": 11, "font_color": "#000000", "border": 0 }) objRowLeftFormat = objBook.add_format({ "align": "left", "valign": "vcenter", "bold": 1, "font_size": 11, "font_color": "#000000", "border": 0 }) objRowRightFormat = objBook.add_format({ "align": "right", "valign": "vcenter", "bold": 1, "font_size": 11, "font_color": "#000000", "border": 0 }) objRowRightFormat.set_num_format("0.0000_ ") objSheet = objBook.add_worksheet( dictStoreMatlD["KA_SYSTEM_CODE"] + dictStoreMatlD["KA_SYSTEM_NM"]) objSheet.set_default_row(20) objSheet.set_column(0, 0, 8) objSheet.set_column(1, 1, 10) objSheet.set_column(2, 2, 15) objSheet.set_column(3, 3, 30) objSheet.set_column(4, 4, 10) objSheet.set_column(5, 5, 10) objSheet.set_column(6, 6, 10) objSheet.set_column(7, 7, 10) objSheet.set_column(8, 8, 10) objSheet.set_column(9, 9, 20) objSheet.set_column(10, 10, 40) objSheet.set_column(11, 11, 12) objSheet.set_column(12, 12, 10) objSheet.set_column(13, 13, 10) objSheet.set_column(14, 14, 10) objSheet.set_column(15, 15, 10) objSheet.set_column(16, 16, 10) intRowIndex = 0 objSheet.write(intRowIndex, 0, "系统代号", objColumnFormat) objSheet.write(intRowIndex, 1, "系统名称", objColumnFormat) objSheet.write(intRowIndex, 2, "门店编号", objColumnFormat) objSheet.write(intRowIndex, 3, "门店名称", objColumnFormat) objSheet.write(intRowIndex, 4, "门店大仓别", objColumnFormat) objSheet.write(intRowIndex, 5, "业绩拆分地", objColumnFormat) objSheet.write(intRowIndex, 6, "大仓所在地", objColumnFormat) objSheet.write(intRowIndex, 7, "产品大类", objColumnFormat) objSheet.write(intRowIndex, 8, "PM线", objColumnFormat) objSheet.write(intRowIndex, 9, "物料编号", objColumnFormat) objSheet.write(intRowIndex, 10, "物料名称", objColumnFormat) objSheet.write(intRowIndex, 11, "销售库存日期", objColumnFormat) objSheet.write(intRowIndex, 12, "销售数量", objColumnFormat) objSheet.write(intRowIndex, 13, "销售件数", objColumnFormat) objSheet.write(intRowIndex, 14, "销售金额", objColumnFormat) objSheet.write(intRowIndex, 15, "库存数量", objColumnFormat) objSheet.write(intRowIndex, 16, "库存件数", objColumnFormat) intRowIndex += 1 objSheet.write(intRowIndex, 0, dictStoreMatlD["KA_SYSTEM_CODE"], objRowCenterFormat) objSheet.write(intRowIndex, 1, dictStoreMatlD["KA_SYSTEM_NM"], objRowCenterFormat) objSheet.write(intRowIndex, 2, dictStoreMatlD["KA_STORE_WH_CODE"], objRowCenterFormat) objSheet.write(intRowIndex, 3, dictStoreMatlD["KA_STORE_WH_NM"], objRowLeftFormat) objSheet.write(intRowIndex, 4, dictStoreMatlD["STORE_WH_TYPE_NM"], objRowCenterFormat) objSheet.write(intRowIndex, 5, dictStoreMatlD["SALES_COM_ABR_SA"], objRowCenterFormat) objSheet.write(intRowIndex, 6, dictStoreMatlD["SALES_COM_ABR_WH"], objRowCenterFormat) objSheet.write(intRowIndex, 7, dictStoreMatlD["PROD_H1_NM"], objRowCenterFormat) objSheet.write(intRowIndex, 8, dictStoreMatlD["PROD_H2_NM"], objRowCenterFormat) objSheet.write(intRowIndex, 9, dictStoreMatlD["PROD_MATL_ID"], objRowCenterFormat) objSheet.write(intRowIndex, 10, dictStoreMatlD["PROD_MATL_NM"], objRowLeftFormat) objSheet.write( intRowIndex, 11, Common.formatDateString(dictStoreMatlD["POS_INV_DATE"]), objRowCenterFormat) objSheet.write(intRowIndex, 12, dictStoreMatlD["POS_QTY_PCS"], objRowRightFormat) objSheet.write(intRowIndex, 13, dictStoreMatlD["POS_QTY_PKG"], objRowRightFormat) objSheet.write(intRowIndex, 14, dictStoreMatlD["POS_AMT"], objRowRightFormat) objSheet.write(intRowIndex, 15, dictStoreMatlD["INV_QTY_PCS"], objRowRightFormat) objSheet.write(intRowIndex, 16, dictStoreMatlD["INV_QTY_PKG"], objRowRightFormat) intRowIndex += 1 objBook.close() return True
def execWriteWorkbook(strJobPathRES, listEMPSales): if len(listEMPSales) == 0: return False else: objExcelWorkbook = None objExcelWorksheet = None objExcelWorkbook = xlsxwriter.Workbook(strJobPathRES) objExcelColumnFormat = objExcelWorkbook.add_format({ "bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#FFFFFF", "fg_color": "#305496", "border": 0 }) objExcelRowCenterFormat = objExcelWorkbook.add_format({ "bold": 1, "align": "center", "valign": "vcenter", "font_size": 11, "font_color": "#000000", "border": 0 }) objExcelRowLeftFormat = objExcelWorkbook.add_format({ "bold": 1, "align": "left", "valign": "vcenter", "font_size": 11, "font_color": "#000000", "border": 0 }) objExcelRowRightFormat = objExcelWorkbook.add_format({ "bold": 1, "align": "right", "valign": "vcenter", "font_size": 11, "font_color": "#000000", "border": 0 }) objExcelRowRightFormat.set_num_format("0.00") objExcelRow = 0 objExcelWorksheet = objExcelWorkbook.add_worksheet("业务人员花名册") objExcelWorksheet.set_default_row(20) objExcelWorksheet.set_column(0, 0, 10) objExcelWorksheet.set_column(1, 1, 10) objExcelWorksheet.set_column(2, 2, 6) objExcelWorksheet.set_column(3, 3, 15) objExcelWorksheet.set_column(4, 4, 8) objExcelWorksheet.set_column(5, 5, 15) objExcelWorksheet.set_column(6, 6, 15) objExcelWorksheet.set_column(7, 7, 18) objExcelWorksheet.set_column(8, 8, 18) objExcelWorksheet.set_column(9, 9, 15) objExcelWorksheet.set_column(10, 10, 10) objExcelWorksheet.set_column(11, 11, 15) objExcelWorksheet.set_column(12, 12, 10) objExcelWorksheet.set_column(13, 13, 6) objExcelWorksheet.write(objExcelRow, 0, "人员编号", objExcelColumnFormat) objExcelWorksheet.write(objExcelRow, 1, "人员姓名", objExcelColumnFormat) objExcelWorksheet.write(objExcelRow, 2, "性别", objExcelColumnFormat) objExcelWorksheet.write(objExcelRow, 3, "加入日期", objExcelColumnFormat) objExcelWorksheet.write(objExcelRow, 4, "年资", objExcelColumnFormat) objExcelWorksheet.write(objExcelRow, 5, "所属部门", objExcelColumnFormat) objExcelWorksheet.write(objExcelRow, 6, "分公司", objExcelColumnFormat) objExcelWorksheet.write(objExcelRow, 7, "营业所", objExcelColumnFormat) objExcelWorksheet.write(objExcelRow, 8, "职位", objExcelColumnFormat) objExcelWorksheet.write(objExcelRow, 9, "职位类型", objExcelColumnFormat) objExcelWorksheet.write(objExcelRow, 10, "职位注记", objExcelColumnFormat) objExcelWorksheet.write(objExcelRow, 11, "职务", objExcelColumnFormat) objExcelWorksheet.write(objExcelRow, 12, "职等编号", objExcelColumnFormat) objExcelWorksheet.write(objExcelRow, 13, "职等", objExcelColumnFormat) objExcelRow += 1 for dictEMPSales in listEMPSales: objExcelWorksheet.write(objExcelRow, 0, dictEMPSales["EMP_ID"], objExcelRowCenterFormat) objExcelWorksheet.write(objExcelRow, 1, dictEMPSales["EMP_NM"], objExcelRowLeftFormat) objExcelWorksheet.write(objExcelRow, 2, dictEMPSales["EMP_GENDER_NM"], objExcelRowCenterFormat) objExcelWorksheet.write( objExcelRow, 3, Common.formatDateString(dictEMPSales["ONBOARD_YMD"]), objExcelRowCenterFormat) objExcelWorksheet.write(objExcelRow, 4, dictEMPSales["SENIORITY"], objExcelRowRightFormat) objExcelWorksheet.write(objExcelRow, 5, dictEMPSales["EMP_POS_PROP_NM"], objExcelRowLeftFormat) objExcelWorksheet.write(objExcelRow, 6, dictEMPSales["HR_COM_NM"], objExcelRowLeftFormat) objExcelWorksheet.write(objExcelRow, 7, dictEMPSales["HR_OFF_NM"], objExcelRowLeftFormat) objExcelWorksheet.write(objExcelRow, 8, dictEMPSales["EMP_POS_NM"], objExcelRowLeftFormat) objExcelWorksheet.write(objExcelRow, 9, dictEMPSales["EMP_POS_TYPE_NM"], objExcelRowLeftFormat) objExcelWorksheet.write(objExcelRow, 10, dictEMPSales["EMP_POS_FLAG"], objExcelRowCenterFormat) objExcelWorksheet.write(objExcelRow, 11, dictEMPSales["EMP_POS_TITLE_NM"], objExcelRowLeftFormat) objExcelWorksheet.write(objExcelRow, 12, dictEMPSales["EMP_POS_LEVEL_ID"], objExcelRowCenterFormat) objExcelWorksheet.write(objExcelRow, 13, dictEMPSales["EMP_POS_LEVEL_NM"], objExcelRowCenterFormat) objExcelRow += 1 objExcelWorkbook.close() return True