#!/usr/bin/python
# Author:Liu Jin Dong
# Date:2019/5/13
import os
import sys
import time
import xlwings as xw
class SplitExcel(object):
def __init__(self):
self.path = os.path.abspath(os.path.dirname(__file__))
self.time = time.strftime("%Y%m%d", time.localtime(time.time()))
def get_site_list(self):
split_excel=input("\033[32;1mExcel Name>>>:\033[0m").strip()
excel_name=os.path.join(self.path,split_excel)
if len(excel_name)==0 or os.path.isfile(excel_name) is None:
print("\033[31;1mExcel File not found...\033[0m")
sys.exit(0)
site_list=[]
app=xw.App(visible=False,add_book=False)
app.display_alerts=False
app.screen_updating=False
wb=app.books.open(excel_name)
rng=wb.sheets["sheet1"].range("a2").expand("down")
rng_count=rng.count + 2
for obj in rng:
obj_value=obj.value
if obj_value not in site_list:
site_list.append(obj_value)
wb.save()
wb.close()
app.quit()
return site_list,rng_count,excel_name
def run(self):
app = xw.App(visible=False, add_book=False)
app.display_alerts = False
app.screen_updating = False
site_list,rng_count,excel_name=self.get_site_list()
print(site_list)
wb=app.books.open(excel_name)
for site in site_list:
file_x = os.path.join(self.path, "%s_%s.xlsx" % (str(site), str(self.time)))
wb_x = app.books.add()
n = 2
m = 2
wb_x.sheets["sheet1"].range("a1").value = "接入来源"
wb_x.sheets["sheet1"].range("b1").value = "点位名称"
wb_x.sheets["sheet1"].range("c1").value = "设备国标ID"
wb_x.sheets["sheet1"].range("d1").value = "最后人像上传日期"
while n < rng_count:
col_a = wb.sheets["sheet1"].range("a%d" % n).value
if str(site) in str(col_a):
col_d = wb.sheets["sheet1"].range("d%d" % n).value
col_e = wb.sheets["sheet1"].range("e%d" % n).value
col_r = wb.sheets["sheet1"].range("r%d" % n).value
wb_x.sheets["sheet1"].range("a%d" % m).value = str(col_a)
wb_x.sheets["sheet1"].range("b%d" % m).value = str(col_d)
wb_x.sheets["sheet1"].range("c%d" % m).value = "\'" + str(col_e)
wb_x.sheets["sheet1"].range("d%d" % m).value = str(col_r)
print(wb_x.sheets["sheet1"].range("a%d" % m).value)
print(wb_x.sheets["sheet1"].range("b%d" % m).value)
print(wb_x.sheets["sheet1"].range("c%d" % m).value)
print(wb_x.sheets["sheet1"].range("d%d" % m).value)
print(n)
print(m)
m += 1
n += 1
wb_x.save(file_x)
wb_x.close()
else:
print("\033[32;1mJob is Done...\033[0m")
wb.save()
wb.close()
app.quit()
if __name__ == "__main__":
split_excel = SplitExcel()
split_excel.run()
