def manage_standard_2G(sheet,pos): """ Draw "2.4G standard" express from template """ s = Range(sheet,pos).value if "\n" in s: standard_rate, stream = s.split("\n") standard_rate = standard_rate.strip() standard, rate = standard_rate.split(" ") stream = stream.split(" ")[0] rate = rate.split("M")[0] ## print (standard,rate,stream) ##************************************************ ## from sheet will get "11gac" but data is "11ac" if standard == "11gac": standard = "11ac" ##************************************************ return (standard,rate,stream) else: ## print s ##************************************************ ## for match data "11g" actually got "11ag if s == "11g": s = "11ag" ##************************************************ return s
def manage_standard_5G(sheet,pos): """ Draw "5G standard" express from template """ s = Range(sheet,pos).value if "\n" in s: standard_rate, stream = s.split("\n") standard_rate = standard_rate.replace(" ","") standard, rate = standard_rate.split("-") ## print standard_rate stream = stream.split(" ")[0] rate = rate.split("T")[-1] ## print (standard,rate,stream) return (standard,rate,stream) else: ## print s ##************************************************ ## for match data "11a" actually got "11ag if s == "11a": s = "11ag" ##************************************************ return s
def execute(Balance_Sheet_Path, Tre0Uno_Sheet_Path, ManHour_Path, ACHour_Path, Pro_Name): # # Balance Sheet wb = Workbook(Balance_Sheet_Path) wb = Workbook.caller() SheetA = u'預算平衡表-Phase Summary A' SheetE = u'預算平衡表-Phase Summary E' SheetP = u'預算平衡表-Phase Summary P' SheetC = u'預算平衡表-Phase Summary C' SheetArr=[SheetA, SheetE, SheetP, SheetC] SheetAlphabetArr = ['A', 'E', 'P','C'] ColumnAlphabet=['D','E','F','G','H','I', 'J', 'K', 'L', 'M', 'N', 'O', 'P'] AllRow = [] for SName in SheetArr: AllRange = Range(SName, 'D10').vertical.get_address() LastRow = AllRange.split(':')[1].split('$')[2] LastRow = int(LastRow) AllRow.append(LastRow) print AllRow for SName in SheetArr: print SheetArr.index(SName) #Create Empty Dataframe for Balance Sheet columns = ['Req No', 'Req Description', 'A', 'W', 'Z', 'Current Budget', 'Exe Budget', 'C', 'PO Num', 'Excepted Budget', 'EAC Now', 'G', 'H'] ArangeLen=(sum(AllRow)+100) index = np.arange(int(ArangeLen)) df_bs = pd.DataFrame(columns=columns, index = index) def BSreadRange(row, SheetIX): SName = SheetArr[SheetIX] SAlphabet = SheetAlphabetArr[SheetIX] Val = Range(SName, row).value if (Val != 0): for j in xrange(0,13): if(row[0] == ColumnAlphabet[j]): rowNum = '%s%d' % (SAlphabet, int(row[1:])) df_bs.loc[rowNum, columns[j]]=Val for SName in SheetArr: SheetIX = SheetArr.index(SName) SheetLastRow = AllRow[SheetIX] for i in xrange(11,(SheetLastRow+1)): CellNameArr = map(lambda x:('%s%d' % (x,i)), ColumnAlphabet) [BSreadRange(x, SheetIX) for x in CellNameArr] df_bs = df_bs[(pd.notnull(df_bs['Req No']))] # # 讀取工時 d = datetime.date.today()- timedelta(days=20) SheetMonth = '{:02d}'.format(d.month) SheetYear = format(d.year)[2:] MH_SheetName=('%s%s' % (SheetYear, SheetMonth)) print MH_SheetName MH_Arr=[] #Pro_Name = ['14A1701A', '14C1701A', '14P1701A', '14E1701A'] #ManHour_Path=r'C:\Python2.7.10\Scripts\notebook\xlwings\MH.xls' wb = Workbook(ManHour_Path) wb = Workbook.caller() AllRange_MH = Range(MH_SheetName, 'A6').vertical.get_address() LastRow_MH = AllRange_MH.split(':')[1].split('$')[2] LastRow_MH = int(LastRow_MH) print LastRow_MH for i in xrange(6,LastRow_MH+1): SearchLocate = ('A%d'% (i)) s = Range(MH_SheetName, SearchLocate).value if s in Pro_Name: TargetLocate = ('B%d'% (i)) MH_Arr.append([s[2], Range(MH_SheetName, TargetLocate).value]) print MH_Arr Workbook(ManHour_Path).close() # # 其他費用 #ACHour_Path=r'C:\Python2.7.10\Scripts\notebook\xlwings\fa.xlsx' ACSheetName='acc303' AC_Arr=[] wb = Workbook(ACHour_Path) wb = Workbook.caller() AllRange_ACC = Range(ACSheetName, 'A2').vertical.get_address() LastRow_ACC = AllRange_ACC.split(':')[1].split('$')[2] LastRow_ACC = int(LastRow_ACC) print LastRow_ACC for i in xrange(1,LastRow_ACC+1): SearchLocate = ('A%d'% (i)) s = Range(ACSheetName, SearchLocate).value if s in Pro_Name: CatLocate = ('D%d'% (i)) TargetLocate = ('H%d'% (i)) AC_Arr.append([s[2], Range(ACSheetName, CatLocate).value, Range(ACSheetName, TargetLocate).value]) print len(AC_Arr) columns_AC = ['Type', 'Amount'] ACrangeLen=(len(AC_Arr)) index_AC = np.arange(int(ACrangeLen)) df_ac = pd.DataFrame(columns=columns_AC, index = index_AC) j=0 for i in AC_Arr: df_ac.loc[j, 'Type']=('%s%d' % (i[0],i[1])) df_ac.loc[j, 'Amount']=i[2] j+=1 #print df_ac AC_Result_df = df_ac.groupby(by=['Type'])['Amount'].sum() for i in range(0,len(AC_Result_df)-1): print AC_Result_df.index[i], AC_Result_df[i] Workbook(ACHour_Path).close() # # 301表 wb = Workbook(Tre0Uno_Sheet_Path) wb = Workbook.caller() ColumnAlphabet301 = ['B', 'C', 'D', 'E', 'F', 'G', 'H'] AllRange301 = Range('D10').vertical.get_address() LastRow301 = AllRange301.split(':')[1].split('$')[2] LastRow301 = int(LastRow301) LastRow301 = LastRow *3 Tre0Uno_columns = ['PO Num', 'Req No', 'Req Description', 'A', 'Current Budget', 'Exe Budget', 'C'] Tre0UnorangeLen=(LastRow301+100) Tre0Uno_index = np.arange(int(Tre0UnorangeLen)) df_T0U = pd.DataFrame(columns=Tre0Uno_columns, index = Tre0Uno_index) # #### 讀取原始301資料 #Read 301 data into the Dataframe function def T0UreadRange(row): Val = Range(row).value if (Val != 0): for j in xrange(0,7): if(row[0] == ColumnAlphabet301[j]): rowNum = int(row[1:]) df_T0U.loc[rowNum, Tre0Uno_columns[j]]=Val df_T0U.loc[rowNum, 'RowID']=rowNum for i in xrange(7,(LastRow301+1)): #Change This month EAC to last month EAC BeforeEACName = ('J%d' % (i)) AfterEACName = ('K%d' % (i)) Range(AfterEACName).value = Range(BeforeEACName).value #Generate all columns name CellNameArr301 = map(lambda x:('%s%d' % (x,i)), ColumnAlphabet301) #Read 301 data into the Dataframe action map(T0UreadRange, CellNameArr301) #drop N/A df_T0U = df_T0U[(pd.notnull(df_T0U['Req Description']))] # #### 寫入Balance Sheet def BSintoT0U(row): XColumnName = ['PO Num_x', 'A_x', 'Current Budget_x', 'Exe Budget_x', 'C_x'] YColumnName = ['PO Num_y', 'A_y', 'Current Budget_y', 'Exe Budget_y', 'C_y'] for checkCol in YColumnName: Col_IX = YColumnName.index(checkCol) bs_Val = row[checkCol] T0U_Val = row[XColumnName[Col_IX]] if (bs_Val != None) and (pd.isnull(bs_Val) != True) and (bs_Val != T0U_Val): #301 Column Name to Column Alphabet ColAnchor = Tre0Uno_columns.index(checkCol[:-2]) Input_Location = ('%s%d' % (ColumnAlphabet301[ColAnchor], row['RowID'])) Range(Input_Location).value = bs_Val Range(Input_Location).color = (136, 153, 238) #Generate the key to merge df_T0U['key']=df_T0U['Req No'] df_bs['key']=df_bs['Req No'] #merge left MergeItem = pd.merge(df_T0U, df_bs, on='key', how='left') #if Balance sheet has diff between 301 diff = MergeItem[(MergeItem['Current Budget_x'] != MergeItem['Current Budget_y']) | (MergeItem['PO Num_x'] != MergeItem['PO Num_y']) | (MergeItem['Exe Budget_x'] != MergeItem['Exe Budget_y']) | (MergeItem['C_x'] != MergeItem['C_y'])] #Merge and Fill in diff['RELSULT'] = diff.apply(BSintoT0U, axis=1) # #### 填入其他費用及自辦工時 FeeArr=[u'其他費用',u'自辦工時', u'自辦工時(MH)', u'間接分攤'] FeeSymbol=['3','4','mh', '5'] TypeAnchor = 0 FillInQuery=[] mhTitle = map(lambda x:('%smh' % (x[0])), MH_Arr) for i in xrange(7,(LastRow301+1)): CheckRName = ('D%d' % (i)) CheckTName = ('A%d' % (i)) InputName = ('H%d' % (i)) CheckRValue = Range(CheckRName).value if CheckRValue in FeeArr: CheckTValue = Range(CheckTName).value if CheckTValue != None: TypeAnchor = CheckTValue[2] SymbolIX = FeeArr.index(CheckRValue) SymbolName = ('%s%s' % (TypeAnchor, FeeSymbol[SymbolIX] )) mhComment = Range(InputName).comment FillInQuery.append([SymbolName,CheckRName,InputName, mhComment]) else: SymbolIX = FeeArr.index(CheckRValue) SymbolName = ('%s%s' % (TypeAnchor, FeeSymbol[SymbolIX] )) mhComment = Range(InputName).comment FillInQuery.append([SymbolName,CheckRName,InputName, mhComment]) print FillInQuery #Fill in MH QueryTitleAnchor = map(lambda x:x[0], FillInQuery) for mhcheck in QueryTitleAnchor: if mhcheck in mhTitle: mhTrueIX = mhTitle.index(mhcheck) mhQueryIX = QueryTitleAnchor.index(mhcheck) FillinTargetRange = FillInQuery[mhQueryIX][2] FillinTargetComment =FillInQuery[mhQueryIX][3] MH_Data = int(MH_Arr[mhTrueIX][1]) print mhcheck, MH_Arr[mhTrueIX][1] ,FillInQuery[mhQueryIX][2] #Check the Comment Month if str(format(d.month)) not in FillinTargetComment: # Update and Change the Comment print "Update" OValue = Range(FillinTargetRange).value Range(FillinTargetRange).value = OValue + MH_Data Range(FillinTargetRange).comment = str(format(d.month)) #fill in other fee for i in range(0,len(AC_Result_df)-1): if AC_Result_df.index[i] in QueryTitleAnchor: QueryFeeIX = QueryTitleAnchor.index(AC_Result_df.index[i]) QueryTargetRange = FillInQuery[QueryFeeIX][2] FeeFillInValue = AC_Result_df[i] Range(QueryTargetRange).value = FeeFillInValue Range(QueryTargetRange).color = (136, 153, 238) print FillInQuery[QueryFeeIX][0], QueryTargetRange, FeeFillInValue
SheetA = u"預算平衡表-Phase Summary A" SheetE = u"預算平衡表-Phase Summary E" SheetP = u"預算平衡表-Phase Summary P" SheetC = u"預算平衡表-Phase Summary C" SheetArr = [SheetA, SheetE, SheetP, SheetC] SheetAlphabetArr = ["A", "E", "P", "C"] ColumnAlphabet = ["D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P"] AllRow = [] for SName in SheetArr: AllRange = Range(SName, "D10").vertical.get_address() LastRow = AllRange.split(":")[1].split("$")[2] LastRow = int(LastRow) AllRow.append(LastRow) print AllRow for SName in SheetArr: print SheetArr.index(SName) # Create Empty Dataframe for Balance Sheet columns = [ "Req No", "Req Description", "A", "W", "Z", "Current Budget", "Exe Budget", "C",