#!/usr/bin/env python
import openpyxl
bj='D:/地市縣公司/西藏臺賬數據分析-設備臺帳分析.xlsx'
wb = openpyxl.load_workbook (bj)
get_sheets = wb.sheetnames
#print(get_sheets)
TA01='TA01'
TA02='TA02'
TA03='TA03'
TE01='TE01'
YG201='YG201'
YG202='YG202'
YG203='YG203'
YG204='YG204'
YG205='YG205'
a=0
max=0
for  name in get_sheets:ws = wb[name]sheet = wb.activea = 0num=1for row in ws:if not all([cell.value == None for cell in row]):num += 1i=0b = []total=0while i < num:if TE01 == name:#  b.insert(i, ws.cell(i+5,2).value)b.append(ws.cell(i+5,2).value)total = num-5i = i+1#breakelif TA01 == name:#b.insert(i, ws.cell(i + 4, 2).value)b.append(ws.cell(i + 4, 2).value)total = num - 4i = i + 1elif TA02 == name:# b.insert(i, ws.cell(i + 5, 2).value)b.append(ws.cell(i + 5, 2).value)total = num - 5i = i + 1#breakelif TA03 == name:# b.insert(i, ws.cell(i + 4, 2).value)b.append(ws.cell(i + 4, 2).value)total = num - 4i = i + 1# breakelif YG201 == name:#b.insert(i, ws.cell(i + 7, 2).value)b.append(ws.cell(i + 7, 2).value)total = num - 7i = i + 1#breakelif YG202 == name:#  b.insert(i, ws.cell(i + 7, 2).value)b.append(ws.cell(i + 7, 2).value)total = num - 7i = i + 1#breakelif YG203 == name:#b.insert(i, ws.cell(i + 7, 2).value)b.append(ws.cell(i + 7, 2).value)total = num - 7i = i + 1#breakelif YG204 == name:# b.insert(i, ws.cell(i + 7, 2).value)b.append(ws.cell(i + 7, 2).value)total = num - 7i = i + 1#breakelif YG205==name:# b.insert(i, ws.cell(i + 7, 2).value)b.append(ws.cell(i + 7, 2).value)total = num - 7i = i + 1#break#print(b)c= [ii for ii in b if ii is not None]#print(c)num1=1if TE01 == name:wbtest = openpyxl.load_workbook('D:/定員測試數據收集表/1-1客戶服務臺賬數據.xlsx')ws = wbtest['供電公司-客戶服務臺賬數據']sheet = wb.activefor row1 in ws:if not all([cell1.value == None for cell1 in row1]):num1 += 1#breakelif TA01==name:wbtest = openpyxl.load_workbook('D:/定員測試數據收集表/1-2電力調度臺賬數據.xlsx')ws = wbtest['供電公司-電力調度臺賬數據']sheet = wb.activefor row1 in ws:if not all([cell1.value == None for cell1 in row1]):num1 += 1elif TA02 == name:wbtest = openpyxl.load_workbook('D:/定員測試數據收集表/1-3輸電運檢臺賬數據.xlsx')ws = wbtest['供電公司-輸電運檢臺賬數據']sheet = wb.activefor row1 in ws:if not all([cell1.value == None for cell1 in row1]):num1 += 1#breakelif TA03 == name:wbtest = openpyxl.load_workbook('D:/定員測試數據收集表/1-4變電運檢臺賬數據.xlsx')ws = wbtest['供電公司-變電運檢臺賬數據']sheet = wb.activefor row1 in ws:if not all([cell1.value == None for cell1 in row1]):num1 += 1# breakelif YG201 == name:wbtest = openpyxl.load_workbook('D:/定員測試數據收集表/2-1前端供電服務實際用工數據.xlsx')ws = wbtest['供電公司-前端供電服務-實際用工']sheet = wb.activefor row1 in ws:if not all([cell1.value == None for cell1 in row1]):num1 += 1elif YG202 == name:wbtest = openpyxl.load_workbook('D:/定員測試數據收集表/2-2供電服務支撐實際用工數據.xlsx')ws = wbtest['供電公司-供電服務支撐-實際用工']sheet = wb.activefor row1 in ws:if not all([cell1.value == None for cell1 in row1]):num1 += 1elif YG203 == name:wbtest = openpyxl.load_workbook('D:/定員測試數據收集表/2-3電力調度實際用工數據.xlsx')ws = wbtest['供電公司-電力調度-實際用工']sheet = wb.activefor row1 in ws:if not all([cell1.value == None for cell1 in row1]):num1 += 1elif YG204 == name:wbtest = openpyxl.load_workbook('D:/定員測試數據收集表/2-4輸電運檢實際用工數據.xlsx')ws = wbtest['供電公司-輸電運檢-實際用工']sheet = wb.activefor row1 in ws:if not all([cell1.value == None for cell1 in row1]):num1 += 1elif YG205 == name:wbtest = openpyxl.load_workbook('D:/定員測試數據收集表/2-5變電運檢實際用工數據.xlsx')ws = wbtest['供電公司-變電運檢-實際用工']sheet = wb.activeprint(c)for row1 in ws:if not all([cell1.value == None for cell1 in row1]):num1 += 1iii=0while iii < len(c):ws.cell(row=num1+iii, column=2).value = c[iii]#  print( c[iii])#print(ws.cell(row=num1+iii, column=2).value)#ws.cell(row=num1+iii, column=1).value = 1+iiiiii = iii+1# print(c)#print(num1)#print(TA01)if TE01 == name:#  print(num1)wbtest.save('D:/定員測試數據收集表/1-1客戶服務臺賬數據.xlsx')elif TA01 == name:# print(name)wbtest.save('D:/定員測試數據收集表/1-2電力調度臺賬數據.xlsx')elif TA02 == name:wbtest.save('D:/定員測試數據收集表/1-3輸電運檢臺賬數據.xlsx')elif TA03 == name:wbtest.save('D:/定員測試數據收集表/1-4變電運檢臺賬數據.xlsx')elif YG201 == name:wbtest.save('D:/定員測試數據收集表/2-1前端供電服務實際用工數據.xlsx')elif YG202 == name:wbtest.save('D:/定員測試數據收集表/2-2供電服務支撐實際用工數據.xlsx')elif YG203 == name:wbtest.save('D:/定員測試數據收集表/2-3電力調度實際用工數據.xlsx')elif YG204 == name:wbtest.save('D:/定員測試數據收集表/2-4輸電運檢實際用工數據.xlsx')elif YG205 == name:wbtest.save('D:/定員測試數據收集表/2-5變電運檢實際用工數據.xlsx')

?