def writer(self, alias, col):
wd = copy.copy(self.data)
ws = wd.get_sheet(0)
j = 1
for s in alias:
cell = self.table.cell(j, col)
if cell is None:
ws.write(j, col, s)
else:
cell = ""
ws.write(j, col, s)
j += 1
wd.save(self.path)
def __init__(self, path, sheet=0, head_index=0, callback=None, offset=0, limit=None, desc_name="aaa.xls"):
data = xlrd.open_workbook(path)
self.table = data.sheet_by_index(sheet)
self.nrows = self.table.nrows
self.callback = callback
self.headIndex = head_index
self.ncols = self.table.ncols
self.headers = []
self.bodys = []
self.limit = limit
self.descTable = copy(data)
self.desc_name = desc_name
self.offset = offset
self.parse_header()
def WriteExcel(CaseFilePath, value, i, j):
testCaseFile = os.path.join(os.getcwd(), CaseFilePath) #os.getcwd获取当前目录
if not os.path.exists(testCaseFile):
logging.error('需要写入的Excel文件不存在！！！')
sys.exit()
TestCase = xlrd.open_workbook(testCaseFile) #打开Excel文件读取数据
table = TestCase.sheet_by_index(0) #通过索引顺序获取
wb = copy(TestCase)
Sheet = wb.get_sheet(0)
try:
Sheet.write(i, j, value)
wb.save(testCaseFile)
print("写入数据成功")
except:
print("写入数据失败")
def write_value(self, i, row, value):
'''
对Excel表进行写数据
:param i:
:param row:
:param value:
:return:
'''
read_value = self.excel
# 将read_value的值复制到内存中 ，这样可以对这些数据进行操作
write_value = copy(read_value)
write_sheet = write_value.get_sheet(i)
# 将数据写到第8列
write_sheet.write(row, 8, value)
# 将数据存到知道路径的文件夹下
write_sheet.save(self.file_path)
def reserve():
global reserveInfo
reserveInfo.append(comboxText.get())
workbook = xlrd.open_workbook(r'my ticket.xls')
sheets = workbook.sheet_names() # 获取工作簿中的所有表格
worksheet = workbook.sheet_by_name(sheets[0]) # 获取工作簿中所有表格中的的第一个表格
rows_old = worksheet.nrows # 获取表格中已存在的数据的行数
new_workbook = copy(workbook) # 将xlrd对象拷贝转化为xlwt对象
new_worksheet = new_workbook.get_sheet(0) # 获取转化后工作簿中的第一个表格
new_worksheet.write(rows_old, 0, reserveInfo[0])
new_worksheet.write(rows_old, 1, reserveInfo[1])
new_worksheet.write(rows_old, 2, reserveInfo[2])
new_worksheet.write(rows_old, 3, reserveInfo[3])
new_worksheet.write(rows_old, 4, reserveInfo[4])
new_worksheet.write(rows_old, 5, reserveInfo[5])
new_workbook.save("my ticket.xls")
print('写入成功')
def set_value(self,sheetindex,row,col,value):
'''
通过copy修改Excel表某sheet页某行某列值
:param sheetindex:指定要修改的index
:param row:指定行
:param col:列
:param value:修改为
:return:
'''
try:
workbook2=copy(self.workbook)
sheet2=workbook2.get_sheet(sheetindex)
sheet2.write(row,col,value)
#self.workbook.release_resources()
workbook2.save(self.path)#保存为原文件，覆盖掉
self.getlog.info('修改excel数值成功')
except BaseException as e:
self.getlog.error('修改excel数值失败：s%' % e)
def student_ready(self, driver):
self.student_search(driver) # 查找学员
company = driver.get_text(self.L_stu.stu_home_company_name) # 获取学员公司
company_abb = db_select.select_company_abb(company) # 获取公司缩写
employee_number = db_select.get_employe_number(company) # 创建员工号
name = str(company_abb) + "_" + str(employee_number) # 创建学员名字
time.sleep(1)
rd = xlrd.open_workbook(
os_tool.abs_path("./data/data_stu_template/学员导出模板.xlsx"))
wb = copy(rd)
# 通过get_sheet()获取有写入的write()方法
ws = wb.get_sheet(1)
ws.write(1, 0, name) # 向名字列写入名字
ws.write(1, 3, company) # 写入公司名
ws.write(1, 7, company_abb) # 写入公司缩写
ws.write(1, 8, employee_number) # 写入员工号
ws.write(1, 12, "2021-12-31") # 写入关闭时间
wb.save(os_tool.abs_path("./data_stu_template/学员导出模板.xls"))
def import_excel(self, **kw):
path = APP_DIR + '/static/excel/'
# arr_data = kwargs['arr_data']
json_data = json.loads(request.httprequest.data.decode())
arr_data = json_data['exl_data']
# 打开模板excel文件进行读写操作
rdbook = xlrd.open_workbook(path + 'award_collect.xls')
# 复制模板
wtbook = xcopy.copy(rdbook)
worksheet = wtbook.get_sheet(0)
# 表头
row = 1
if len(arr_data) > 0:
for record in arr_data:
worksheet.write(row, 0, record['line_id'])
worksheet.write(row, 1, record['site_id'])
worksheet.write(row, 2, record['jobnumber'])
if record['position']:
worksheet.write(row, 3, record['position'])
else:
worksheet.write(row, 3, "")
worksheet.write(row, 4, record['award_money'])
worksheet.write(row, 5, record['comment_count'])
row += 1
name = str(int(round(time.time() * 1000))) + str(random.randint(1, 1000)) + '.xls'
file = path + name
wtbook.save(file)
with open(file, 'rb') as f:
data = f.read()
# response = request.make_response(data)
# response.headers["Content-Disposition"] = "attachment; filename={}{}". \
# format(year.encode().decode('latin-1'), '年维修计划表.xls'.encode().decode('latin-1'))
os.remove(file)
# 直接返回byte[] 或 返回 str(data)、response
return data
def test_copy_xlrd(self,c,xlwtw):
inwb = object()
outwb = Mock()
xlwtwi = Mock()
xlwtwi.output=[('junk',outwb)]
xlwtw.return_value=xlwtwi
self.failUnless(copy(inwb) is outwb)
self.assertEqual(len(c.call_args_list),1)
args = c.call_args_list[0][0]
self.assertEqual(len(args),2)
r = args[0]
self.failUnless(isinstance(r,XLRDReader))
self.failUnless(r.wb is inwb)
self.assertEqual(r.filename,'unknown.xls')
w = args[1]
self.failUnless(w is xlwtwi)
def load_Table(cls, page):
'''获取报表页面数据'''
# 创建工作簿
driver = cls.driver
wbk = xlwt.Workbook(encoding='utf-8', style_compression=0)
# 创建工作表
sheet = wbk.add_sheet('Web_data', cell_overwrite_ok=True)
excel = r"F:\PyTesting\AutoTest\log\excel\USER_LOG.xls"
# 查找页面数据，xpath为报表数据的路径
table_rows = driver.find_element_by_xpath(
"//table[starts-with(@id, 'EaserUI_EditorTable_Body_')]"
).find_elements_by_tag_name('tr')
# print(table_rows)
row = 11
for i, tr in enumerate(
table_rows): # enumerate()是python的内置函数.enumerate多用于在for循环中得到计数
# 获取页面table中的标题
if i == 0 and page == 0:
table_cols1 = tr.find_elements_by_tag_name('td')
for j, tc in enumerate(table_cols1):
sheet.write(i, j, tc.text)
wbk.save(excel)
# 获取页面table中的数据，进行拼接
else:
table_cols2 = tr.find_elements_by_tag_name('td')
for j, tc in enumerate(table_cols2):
# 老的工作簿，打开excel
oldWb = xlrd.open_workbook(excel, formatting_info=True)
# 新的工作簿,复制老的工作簿
newWb = copy(oldWb)
# 新的工作表
newWs = newWb.get_sheet(0)
newWs.write(i + page * row, j, tc.text)
os.remove(excel)
newWb.save(excel)
# 删除多余行
del_row(excel, excel)
def import_excel(self, **kw):
path = APP_DIR + '/static/excel/'
# 打开模板excel文件进行读写操作
rdbook = xlrd.open_workbook(path + 'award_standard.xls')
print(rdbook)
# 复制模板
wtbook = xcopy.copy(rdbook)
worksheet = wtbook.get_sheet(0)
name = '奖励标准' + str(int(round(time.time() * 1000))) + str(random.randint(1, 1000)) + '.xls'
file = path + 'check_record.xls'
print(file)
wtbook.save(file)
with open(file, 'rb') as f:
data = f.read()
response = request.make_response(data)
response.headers['Content-Type'] = 'application/vnd.ms-excel'
response.headers["Content-Disposition"] = "attachment; filename={}". \
format(name.encode().decode('latin-1'))
# os.remove(file)
return response
# os.remove(wtbook)
# 直接返回byte[] 或 返回 str(data)、response
# return data
def write_sheet(self, row, col, value):
data = xlrd.open_workbook(self.path)
copy_data = copy.copy(data)
table = copy_data.get_sheet(0)
table.write(row, col, value)
copy_data.save(self.path)
def import_excel(self, **kw):
path = APP_DIR + '/static/excel/'
# 打开模板excel文件进行读写操作
rdbook = xlrd.open_workbook(path + 'belong_to_management.xls')
# 复制模板
wtbook = xcopy.copy(rdbook)
worksheet = wtbook.get_sheet(0)
row = 1
site = request.env[
'cdtct_dingtalk.cdtct_dingtalk_department'].get_line_or_def_site()
site_id_self = [sites.get('id') for sites in site.get('site_options')]
records = request.env['funenc_xa_station.belong_to_management'].search(
[('site_id', 'in', site_id_self)])
if len(records) > 0:
for record in records:
if record.line_id.name:
worksheet.write(row, 0, record.line_id.name)
else:
worksheet.write(row, 0, '')
if record.site_id.name:
worksheet.write(row, 1, record.site_id.name)
else:
worksheet.write(row, 1, "")
if record.post_check:
if record.post_check == 'guard':
post_check = '保安'
elif record.post_check == 'check':
post_check = '安检'
elif record.post_check == 'clean':
post_check = '保洁'
worksheet.write(row, 2, post_check)
else:
worksheet.write(row, 2, "")
if record.check_time:
worksheet.write(row, 3, record.check_time)
else:
worksheet.write(row, 3, "")
if record.check_state:
worksheet.write(row, 4, record.check_state)
else:
worksheet.write(row, 4, "")
if record.find_problem:
worksheet.write(row, 5, record.find_problem)
else:
worksheet.write(row, 5, 0)
if record.reference_according:
worksheet.write(row, 6, record.reference_according)
else:
worksheet.write(row, 6, "")
if record.local_image:
worksheet.write(row, 7, record.local_image)
else:
worksheet.write(row, 7, "")
if record.check_score:
worksheet.write(row, 8, record.check_score)
else:
worksheet.write(row, 8, "")
if record.note:
worksheet.write(row, 9, record.note)
else:
worksheet.write(row, 9, "")
if record.write_person:
worksheet.write(row, 10, record.write_person)
else:
worksheet.write(row, 10, "")
row += 1
name = str(int(round(time.time() * 1000))) + str(
random.randint(1, 1000)) + '.xls'
file = path + name
wtbook.save(file)
with open(file, 'rb') as f:
data = f.read()
response = request.make_response(data)
response.headers['Content-Type'] = 'application/vnd.ms-excel'
response.headers["Content-Disposition"] = "attachment; filename={}". \
format(name.encode().decode('latin-1'))
os.remove(file)
return response
def write_value(self,rowx,colx,value):
read_data = xlrd.open_workbook(self.file_name)
write_data = copy.copy(read_data)
sheet_data = write_data.get_sheet(0)
sheet_data.write(int(rowx),int(colx),value)
write_data.save(self.file_name)
store_type = config.get("store_data_setting", "store_type") # 门店类型
store_status = config.get("store_data_setting", "store_status") # 门店状态
store_phone = config.get("store_data_setting", "store_phone") # 门店电话
store_contact = config.get("store_data_setting", "store_contact") # 联系人
father_manage_scope = config.get("store_data_setting", "father_manage_scope") # 父级经营范围
manage_scope = config.get("store_data_setting", "manage_scope") # 子级经营范围
# ====================================================================
# ====================================================================
"""初始化读excel操作"""
data_source_path = os.path.join("./DataSource/" + data_source)
data = xlrd.open_workbook(data_source_path)
table = data.sheet_by_name('account')
copy_data = copy(data)
copy_sheet = copy_data.get_sheet(0)
# ====================================================================
# ====================================================================
"""log文件和截图文件路径变量"""
launch_log_path = os.path.join("./Log/Log_" + time.strftime("%Y%m%d"))
launch_screenshot_path = os.path.join("./Log/Screenshot_" + time.strftime("%Y%m%d") + "/" + time.strftime("%H%M%S"))
launch_result_path = os.path.join("./Result/Result_" + time.strftime("%Y%m%d"))
big_image_name = os.path.join("./Image/" + big_image_name)
small_image_name = os.path.join("./Image/" + small_image_name)
# ====================================================================
def write_unfinished(value): # 判断新建表中哪为空
table = xlrd.open_workbook(file_name, formatting_info=True)
xls = copy(table)
sheet1 = xls.get_sheet('统计')
sheet1.write(value, 2, '未交')
xls.save(file_name)
def write_data(filepath, row, col, value):
data = xlrd.open_workbook(filepath) # 打开指定的excel文件
data_copy = copy(data) # 复制原文件使的文件可以改动
sheet = data_copy.get_sheet(0) # 取得复制文件的sheet对象
sheet.write(row, col, value) # 在某一单元格写入value
data_copy.save(filepath) # 保存文件
def LandChina(EXCEL_NAME):
# 打开表格
wb = xlrd.open_workbook(EXCEL_NAME)
#历史日期
historyDate = datetime.datetime.strptime(
wb.sheet_by_name('historyNum').cell_value(0, 1), '%Y-%m-%d')
oneday = datetime.timedelta(days=1)
start = historyDate.strftime('%Y-%m-%d')
chormedriver = 'C:\Program Files (x86)\Google\Chrome\Application\chromedriver.exe'
chrome_options = Options()
chrome_options.add_argument('--headless')
#browser是自己定义的
browser = webdriver.Chrome(chormedriver, chrome_options=chrome_options)
#日期增加
for i in range(0, 365):
tag = 1 #标记用来循环执行异常
index = 2 #等待指数
while tag:
try:
print("-----开始加载-----")
url = 'http://www.landchina.com/default.aspx?tabid=263&wmguid=75c72564-ffd9-426a-954b-8ac2df0903b7&p=9f2c3acd-0256-4da2-a659-6949c4671a2a%3A' \
+ start + '~' + start
browser.get(url)
WebDriverWait(
browser, 30).until(lambda browser: browser.
find_elements_by_class_name('gridItem'))
if len(browser.find_elements_by_class_name('pager')) != 0:
subTitle = WebDriverWait(
browser,
30).until(lambda browser: browser.
find_elements_by_class_name('pager'))
# 一天中所有公告结果的循环,翻页
pageNum = int(subTitle[1].text.split()[0][1:-1])
print('一共有' + str(pageNum) + '页')
else:
pageNum = 1
print('不满一页')
except Exception as e:
print('加载出错！' + repr(e))
browser.close()
browser = webdriver.Chrome(chormedriver,
chrome_options=chrome_options)
print('等待' + str(index) + 's')
time.sleep(index)
index = index + 10
else:
tag = 0
# 打开表格
wb = xlrd.open_workbook(EXCEL_NAME)
#历史页数
historyPage = int(
wb.sheet_by_name('historyNum').cell_value(0, 2))
print('历史页' + str(historyPage))
for page in range(historyPage, pageNum + 1):
tag = 1
index = 2 # 等待指数
while tag:
try:
if len(browser.find_elements_by_class_name(
'pager')) != 0:
print('-----开始翻页，第' + str(page) + '页-----')
subTitle = WebDriverWait(browser, 30).until(
lambda browser: browser.
find_elements_by_class_name('pager'))
inputPage = subTitle[
2].find_elements_by_tag_name('input')[0]
inputPage.clear()
inputPage.send_keys(str(page))
subTitle[2].find_elements_by_tag_name(
'input')[1].click()
trlList1 = WebDriverWait(browser, 30).until(
lambda browser: browser.
find_elements_by_class_name('gridItem'))
trlList2 = browser.find_elements_by_class_name(
'gridAlternatingItem')
if len(trlList2) != 0:
trlList2 = WebDriverWait(
browser,
30).until(lambda browser: browser.
find_elements_by_class_name(
'gridAlternatingItem'))
trlList1.extend(trlList2) # 合并列表
except Exception as e:
print('翻页出错！' + repr(e))
browser.close()
browser = webdriver.Chrome(
chormedriver, chrome_options=chrome_options)
url = 'http://www.landchina.com/default.aspx?tabid=263&wmguid=75c72564-ffd9-426a-954b-8ac2df0903b7&p=9f2c3acd-0256-4da2-a659-6949c4671a2a%3A' \
+ start + '~' + start
browser.get(url)
print('等待' + str(index) + 's')
time.sleep(index)
index = index + 10
else:
tag = 0
# 打开表格,一页一页的存储
wb = xlrd.open_workbook(EXCEL_NAME)
# 记录历史条数
recordNum = int(
wb.sheet_by_name('historyNum').cell_value(
0, 0))
wb = copy.copy(wb) # 拷贝一份原来的excel
table = wb.get_sheet(0)
print('-----一页有' + str(len(trlList1)) +
'条数据------')
for tr in trlList1:
tag = 1
index = 2 # 等待指数
while tag:
try:
print('-----读取数据-----')
print(start)
url = tr.find_element_by_tag_name(
'a').get_attribute('href')
print(
tr.find_element_by_class_name(
'gridTdNumber').text)
new_windows = 'window.open("' + url + '");'
browser.execute_script(new_windows)
#隐式等待1min
# browser.implicitly_wait(60)
# 获取当前窗口的句柄
origin_windows = browser.window_handles[
0]
current_windows = browser.window_handles[
-1]
browser.switch_to.window(
current_windows)
rowInfo = WebDriverWait(
browser, 30
).until(
lambda browser: browser.
find_elements_by_xpath(
"//table[@id='Table1']//table[@id='mainModuleContainer_1855_1856_ctl00_ctl00_p1_f1']/tbody/tr"
))
except Exception as e:
print('读取数据时出错！' + repr(e))
browser.close()
browser.switch_to.window(
origin_windows)
print('等待' + str(index) + 's')
time.sleep(index)
index = index + 10
else:
tag = 0
#读取表格数据
for row in rowInfo[2:]:
i = 0
spanList = row.find_elements_by_xpath(
'./td/span')
# print(spanList[i].text)
while (i < len(spanList)):
if spanList[
i].text == '分期支付约定:':
subspanList = row.find_elements_by_xpath(
"./td/table//tr[@id='mainModuleContainer_1855_1856_ctl00_ctl00_p1_f3_r2_0']//span"
)
for j in range(
len(subspanList) -
1):
# print(subspanList[j].text)
table.write(
recordNum, 18 + j,
subspanList[j].text
)
i = i + 1
elif spanList[
i].text == '约定容积率:':
subspanList = row.find_elements_by_xpath(
'./td/table//span')
for subspan in subspanList:
if subspan.text in tb_head:
Index = subspanList.index(
subspan)
table.write(
recordNum,
tb_head.index(
subspan.
text),
subspanList[
Index +
1].text)
i = i + 1
elif spanList[
i].text == '土地使用权人:':
if spanList[i +
1].text == '':
Index = rowInfo.index(
row)
table.write(
recordNum,
tb_head.index(
'土地使用权人'),
rowInfo[Index +
1].text)
else:
table.write(
recordNum,
tb_head.index(
'土地使用权人'),
spanList[i +
1].text)
i = i + 1
elif spanList[
i].text in tb_head:
table.write(
recordNum,
tb_head.index(
spanList[i].text),
spanList[i + 1].text)
i = i + 2
else:
i = i + 1
browser.close()
recordNum = recordNum + 1
browser.switch_to.window(
origin_windows)
time.sleep(2)
wb.get_sheet('historyNum').write(0, 0, recordNum)
wb.save(EXCEL_NAME)
print(page)
wb.get_sheet('historyNum').write(0, 2, page + 1)
wb.save(EXCEL_NAME)
wb.get_sheet('historyNum').write(0, 2, 1)
wb.save(EXCEL_NAME)
historyDate = historyDate + oneday
start = historyDate.strftime('%Y-%m-%d')
print('存储日期' + start)
wb.get_sheet('historyNum').write(0, 1, start)
wb.save(EXCEL_NAME)
if __name__ == '__main__':
'''程序默认目的数据表在第一张'''
'''程序直接覆盖目的数据表，务必进行备份以免出现错误不可回退'''
rows = 66 # 源数据行数
yuan = '装机.xlsx' # 源
mudi = 'satationinfo.xlsx' # 目的
start_row = 2 # 合并单元格的起始行数
leap = 9 # 合并单元格的数目
delta = 2 # 合并单元格的变化行数
data = xlrd.open_workbook(yuan)
source = data.sheets()[0]
oldwb = xlrd.open_workbook(mudi, formatting_info=True)
newwb = copy(oldwb)
result = newwb.get_sheet(0)
for i in range(0, rows):
row = source.row_values(i)
result.write_merge(start_row, start_row + leap, 0, 0, i + 1,
set_style())
for j in range(1, 5):
result.write_merge(start_row, start_row + leap, j, j, row[j],
set_style())
start_row += leap + 2
if start_row == 14:
leap -= delta
newwb.save('satationinfo.xlsx')
def testGetProductInfo(self):
"""
test body
:return:
"""
localConfigHttp.set_url(self.Host, self.Request_url)
if self.No != 'No':
#if self.No == "HDP114":
a = test_login()
token = a.test_ho()
time.sleep(2)
headers = {
"content-type": "application/x-www-form-urlencoded",
"Authorization": "Bearer " + token
}
#print(self.Request_data)
Request_data = json.loads(self.Request_data)
#Request_data = json.dumps(self.Request_data)
localConfigHttp.set_headers(headers)
localConfigHttp.set_data(Request_data)
if self.No != 'No':
if self.Method == 'get':
# get http
self.response = localConfigHttp.get()
if self.response.status_code != 201:
httpcode = self.response.status_code
value = "http" + str(httpcode)
result = "http" + str(httpcode)
print(self.response.content.decode(encoding='utf-8'))
#self.info = json.loads(self.json_response)
else:
self.info = self.response.content.decode(
encoding='utf-8')
self.info = json.loads(self.info)
print(self.response.content.decode(encoding='utf-8'))
if self.info["error_code"] == 0:
msg = self.info["msg"]
value = msg
result = 1
else:
msg = self.info["msg"]
code = self.info["error_code"]
self.logger.debug(msg)
print(msg + 'cg')
self.log.build_case_line(self.Api_name, msg)
value = msg
result = code
# check result
"""
if common.check_result(self.Return_data, self.response):
value = 1
result = 1
else:
value = 0
result = 0
"""
else:
# post http
# response.setContentType("charset=utf-8”)
return_data = localConfigHttp.post()
if return_data.status_code != 201:
httpcode = return_data.status_code
result = "http" + str(httpcode)
value = "http" + str(httpcode)
else:
self.response = return_data.json()
if self.response["error_code"] == 0:
msg = self.response["msg"]
value = msg
self.info = self.response
print(self.info)
#self.log.write_result(msg)
result = 1
# check result
# common.check_result(self.return_data, self.info)
#
else:
msg = self.response["msg"]
code = self.response["error_code"]
self.logger.debug(msg)
print(msg + 'cg')
self.log.build_case_line(self.Api_name, msg)
value = msg
result = code
#xlsPath = os.path.join(proDir, "testFile", 'case', 'test_ho.xlsx')
filepath = os.path.join(proDir, "testFile", 'case',
'test_ho_result.xls')
#filepath = os.path.join(proDir, "testFile", 'case', 'test_ho.xlsx')
i, j = common.local(filepath=filepath,
sheet_name='api',
str=self.No) # 定位查询字段在第几行第几列
data = xlrd.open_workbook(filepath) # 打开指定的excel文件
data_copy = copy(data) # 复制原文件使的文件可以改动
sheet = data_copy.get_sheet(0) # 取得复制文件的sheet对象
sheet.write(i, j + 9, result) # 在某一单元格写入value
sheet.write(i, j + 7, value)
#filepath = os.path.join(proDir, "testFile", 'case', 'test_ho_result.xlsx')
data_copy.save(filepath) # 保存文件
else:
self.logger.debug('非json入参')
def import_excel(self, **kw):
path = APP_DIR + '/static/excel/'
# 打开模板excel文件进行读写操作
rdbook = xlrd.open_workbook(path + 'good_deeds.xls')
print(rdbook)
# 复制模板
wtbook = xcopy.copy(rdbook)
worksheet = wtbook.get_sheet(0)
row = 1
site = request.env[
'cdtct_dingtalk.cdtct_dingtalk_department'].get_line_or_def_site()
site_id_self = [sites.get('id') for sites in site.get('site_options')]
records = request.env['fuenc_station.good_deeds'].search([
('site_id', '=', site_id_self)
])
if len(records) > 0:
for record in records:
if record.id:
worksheet.write(row, 0, record.id)
else:
worksheet.write(row, 0, '')
if record.line_id.name:
worksheet.write(row, 1, record.line_id.name)
else:
worksheet.write(row, 1, '')
if record.site_id.name:
worksheet.write(row, 2, record.site_id.name)
else:
worksheet.write(row, 2, "")
if record.type:
worksheet.write(row, 3, record.type.good_type)
else:
worksheet.write(row, 3, "")
if record.open_time:
worksheet.write(row, 4, record.open_time)
else:
worksheet.write(row, 4, "")
if record.write_person:
worksheet.write(row, 5, record.write_person)
else:
worksheet.write(row, 5, "")
if record.write_time:
worksheet.write(row, 6, record.write_time)
else:
worksheet.write(row, 6, "")
if record.audit_state:
if record.audit_state == 'one_audit':
kind = '待初核'
elif record.audit_state == 'two_audit':
kind = '待复核'
elif record.audit_state == 'through':
kind = '通过'
elif record.audit_state == 'rejected':
kind = '驳回'
worksheet.write(row, 7, kind)
else:
worksheet.write(row, 7, "")
row += 1
name = str(int(round(time.time() * 1000))) + str(
random.randint(1, 1000)) + '.xls'
file = path + name
print(file)
wtbook.save(file)
with open(file, 'rb') as f:
data = f.read()
response = request.make_response(data)
response.headers['Content-Type'] = 'application/vnd.ms-excel'
response.headers["Content-Disposition"] = "attachment; filename={}". \
format(name.encode().decode('latin-1'))
os.remove(file)
return response
def import_excel(self, **kw):
path = APP_DIR + '/static/excel/'
# 打开模板excel文件进行读写操作
rdbook = xlrd.open_workbook(path + 'special_money_excel.xls')
print(rdbook)
# 复制模板
wtbook = xcopy.copy(rdbook)
worksheet = wtbook.get_sheet(0)
row = 1
site = request.env[
'cdtct_dingtalk.cdtct_dingtalk_department'].get_line_or_def_site()
site_id_self = [sites.get('id') for sites in site.get('site_options')]
records = request.env['funenc_xa_station.special_money'].search([
('site_id', '=', site_id_self)
])
if len(records) > 0:
for record in records:
if record.id:
worksheet.write(row, 0, record.id)
else:
worksheet.write(row, 0, '')
if record.line_id.name:
worksheet.write(row, 1, record.line_id.name)
else:
worksheet.write(row, 1, '')
if record.site_id.name:
worksheet.write(row, 2, record.site_id.name)
else:
worksheet.write(row, 2, "")
if record.open_time:
d = datetime.datetime.strptime(record.open_time,
'%Y-%m-%d %H:%M:%S')
delta = datetime.timedelta(hours=8)
open_time = d + delta
worksheet.write(row, 3,
open_time.strftime('%Y-%m-%d %H:%M:%S'))
# worksheet.write(row, 3, record.open_time)
else:
worksheet.write(row, 3, "")
if record.event_type:
if record.event_type == 'money':
event_type = '非及时退款'
elif record.event_type == 'deal':
event_type = '事务处理'
worksheet.write(row, 4, event_type)
else:
worksheet.write(row, 4, "")
if record.involving_money:
worksheet.write(row, 5, record.involving_money)
else:
worksheet.write(row, 5, 0)
if record.passengers_name:
worksheet.write(row, 6, record.passengers_name)
else:
worksheet.write(row, 6, "")
if record.webmaster:
worksheet.write(row, 7, record.webmaster)
else:
worksheet.write(row, 7, "")
if record.write_time:
d = datetime.datetime.strptime(record.write_time,
'%Y-%m-%d %H:%M:%S')
delta = datetime.timedelta(hours=8)
open_time = d + delta
worksheet.write(row, 8,
open_time.strftime('%Y-%m-%d %H:%M:%S'))
else:
worksheet.write(row, 8, "")
if record.deal_result:
if record.deal_result == 'one_audit':
kind = '待初核'
elif record.deal_result == 'two_audit':
kind = '待复核'
elif record.deal_result == 'through':
kind = '已通过'
elif record.deal_result == 'rejected':
kind = '已驳回'
worksheet.write(row, 9, kind)
else:
worksheet.write(row, 9, "")
row += 1
name = str(int(round(time.time() * 1000))) + str(
random.randint(1, 1000)) + '.xls'
file = path + name
print(file)
wtbook.save(file)
with open(file, 'rb') as f:
data = f.read()
response = request.make_response(data)
response.headers['Content-Type'] = 'application/vnd.ms-excel'
response.headers["Content-Disposition"] = "attachment; filename={}". \
format(name.encode().decode('latin-1'))
os.remove(file)
return response
def import_excel(self, **kw):
path = APP_DIR + '/static/excel/'
# 打开模板excel文件进行读写操作
rdbook = xlrd.open_workbook(path + 'award_record.xls')
print(rdbook)
# 复制模板
wtbook = xcopy.copy(rdbook)
worksheet = wtbook.get_sheet(0)
row = 1
records = request.env['funenc_xa_station.award_record'].search([])
if len(records) > 0:
for record in records:
if record.line_id.name:
worksheet.write(row, 0, record.line_id.name)
else:
worksheet.write(row, 0, '')
if record.site_id.name:
worksheet.write(row, 1, record.site_id.name)
else:
worksheet.write(row, 1, "")
if record.jobnumber:
worksheet.write(row, 2, record.jobnumber)
else:
worksheet.write(row, 2, "")
if record.staff.name:
worksheet.write(row, 3, record.staff.name)
else:
worksheet.write(row, 3, "")
if record.position:
worksheet.write(row, 4, record.position)
else:
worksheet.write(row, 4, "")
if record.award_money:
worksheet.write(row, 5, record.award_money)
else:
worksheet.write(row, 5, 0)
if record.award_target_kind:
worksheet.write(row, 6, record.award_target_kind.name)
else:
worksheet.write(row, 6, "")
if record.award_project:
worksheet.write(row, 7, record.award_project.name)
else:
worksheet.write(row, 7, "")
if record.check_project:
worksheet.write(row, 8, record.check_project.name)
else:
worksheet.write(row, 8, "")
if record.incident_describe:
worksheet.write(row, 9, record.incident_describe)
else:
worksheet.write(row, 9, "")
if record.check_person:
worksheet.write(row, 10, record.check_person)
else:
worksheet.write(row, 10, "")
if record.check_time:
worksheet.write(row, 11, record.check_time)
else:
worksheet.write(row, 11, "")
row += 1
name = str(int(round(time.time() * 1000))) + str(
random.randint(1, 1000)) + '.xls'
file = path + name
print(file)
wtbook.save(file)
with open(file, 'rb') as f:
data = f.read()
response = request.make_response(data)
response.headers['Content-Type'] = 'application/vnd.ms-excel'
response.headers["Content-Disposition"] = "attachment; filename={}". \
format(name.encode().decode('latin-1'))
os.remove(file)
return response
def import_excel(self, **kw):
path = APP_DIR + '/static/excel/'
# 打开模板excel文件进行读写操作
rdbook = xlrd.open_workbook(path + 'prode_newpaper_exel.xls')
print(rdbook)
# 复制模板
wtbook = xcopy.copy(rdbook)
worksheet = wtbook.get_sheet(0)
row = 1
# site = ding_user.user_property_departments.id
site = request.env[
'cdtct_dingtalk.cdtct_dingtalk_department'].get_line_or_def_site()
site_id_self = [sites.get('id') for sites in site.get('site_options')]
records = request.env['funenc_xa_station.prude_newspaper'].search([
('site_id', 'in', site_id_self)
]) #获取当前线路的日报记录
if len(records) > 0:
for record in records:
if record.line_id.name:
worksheet.write(row, 0, record.line_id.name)
else:
worksheet.write(row, 0, '')
if record.site_id.name:
worksheet.write(row, 1, record.site_id.name)
else:
worksheet.write(row, 1, "")
if record.event_stype:
if record.event_stype == 'enter_come':
event_stype = '边门进出情况'
elif record.event_stype == 'ticket_acf':
event_stype = '票务、AFC故障及异常情况'
elif record.event_stype == 'ticket_sales':
event_stype = '日票、预制单程票售卖情况'
elif record.event_stype == 'other_brenk':
event_stype = '其他设备故障情况'
elif record.event_stype == 'normal':
event_stype = '普通事件'
worksheet.write(row, 2,
record.event_stype.prude_event_type)
else:
worksheet.write(row, 2, "")
if record.event_content:
worksheet.write(row, 3, record.event_content)
else:
worksheet.write(row, 3, "")
if record.open_time:
d = datetime.datetime.strptime(record.open_time,
'%Y-%m-%d %H:%M:%S')
delta = datetime.timedelta(hours=8)
open_time = d + delta
worksheet.write(row, 4,
open_time.strftime('%Y-%m-%d %H:%M:%S'))
else:
worksheet.write(row, 4, "")
if record.write_time:
worksheet.write(row, 5, record.write_time)
else:
worksheet.write(row, 5, '')
if record.write_name:
worksheet.write(row, 6, record.write_name)
else:
worksheet.write(row, 6, "")
if record.iobnumber:
worksheet.write(row, 7, record.iobnumber)
else:
worksheet.write(row, 7, "")
row += 1
name = str(int(round(time.time() * 1000))) + str(
random.randint(1, 1000)) + '.xls'
file = path + name
print(file)
wtbook.save(file)
with open(file, 'rb') as f:
data = f.read()
response = request.make_response(data)
response.headers['Content-Type'] = 'application/vnd.ms-excel'
response.headers["Content-Disposition"] = "attachment; filename={}". \
format(name.encode().decode('latin-1'))
os.remove(file)
return response
def pa():
person_num = int(ID1.get())
# 构造name
name_list = MutiTools.get_faker_data('name', person_num)
data_dic = {}
data_dic[0] = name_list
# 构造性别
sex = ['男', '女']
sex_list = [sex[random.randint(0, 1)] for x in range(person_num)]
data_dic[1] = sex_list
# 构造排序号
sort_list = [str(x + 1) for x in range(0, person_num)]
data_dic[4] = sort_list
# 构造手机号
phone_list = MutiTools.get_faker_data('phone', person_num)
data_dic[5] = phone_list
# 构造邮箱
email_list = MutiTools.get_faker_data('email', person_num)
data_dic[6] = email_list
# # 构造固定电话和传真
tel_list = [
'029-' + str(x + 1).rjust(7, '0') for x in range(0, person_num)
]
data_dic[7] = tel_list
data_dic[8] = tel_list
# # 构造job
job_list = MutiTools.get_faker_data('job', person_num)
data_dic[9] = job_list
data_dic[10] = job_list
# 构造工号
jobNum_list = [str(x + 1).rjust(8, '0') for x in range(0, person_num)]
data_dic[11] = jobNum_list
# # 构造address
address_list = MutiTools.get_faker_data('address', person_num)
data_dic[12] = address_list
# 构造部门
dept_list = []
# 根部门名称
l1 = ID2.get().strip()
dept_list.append(l1)
# 二级部门
l2 = [l2d.strip() for l2d in ID3.get().split(',')]
for x in l2:
dept_list.append(l1 + '>' + x)
# 三级部门
l3 = [l3d.strip() for l3d in ID4.get().split(',')]
for x in l3:
for y in l2:
dept_list.append(l1 + '>' + y + '>' + y + u'的' + x)
# 所有部门名称保存到dept.txt中
with open('dept.txt', 'w') as f:
for x in dept_list:
f.write(x + '\n')
dept_list2 = [dept_list[i % len(dept_list)] for i in range(0, person_num)]
data_dic[2] = dept_list2
# 构造组织
zz_list = [l1 for x in range(0, person_num)]
data_dic[3] = zz_list
import xlrd
from xlutils3.copy import copy
old_excel = xlrd.open_workbook('pt2.xls', formatting_info=True)
new_excel = copy(old_excel)
ws = new_excel.get_sheet(0)
for x in range(0, int(ID1.get()) / 1000):
try:
for key, value in data_dic.items():
now = value[1000 * (x):1000 * (x + 1)]
for y in range(len(now)):
ws.write(3 + y, key, now[y].decode('utf-8'))
except Exception as e:
raise e
new_excel.save('personTemplate' + str(x + 1) + '.xls')
showinfo(title='提示',
message='数据生成完成！\n人员：' + str(person_num) + '人\n部门：' +
str(len(dept_list)) + '个')
def import_excel(self, **kw):
path = APP_DIR + '/static/excel/'
# 打开模板excel文件进行读写操作
rdbook = xlrd.open_workbook(path + 'instruments.xls')
# print(rdbook)
# 复制模板
wtbook = xcopy.copy(rdbook)
worksheet = wtbook.get_sheet(0)
row = 1
records = request.env[
'funenc_xa_station.transient_break_management'].search([])
if len(records) > 0:
for record in records:
if record.transceiver_type:
worksheet.write(row, 0,
record.transceiver_type.consumables_type)
else:
worksheet.write(row, 0, '')
if record.transceive_name:
worksheet.write(row, 1, record.transceive_name)
else:
worksheet.write(row, 1, "")
if record.transceive_number:
worksheet.write(row, 2, record.transceive_number)
else:
worksheet.write(row, 2, "")
if record.line_id:
worksheet.write(row, 3, record.line_id.name)
else:
worksheet.write(row, 3, "")
if record.site_id:
worksheet.write(row, 4, record.site_id.name)
else:
worksheet.write(row, 4, "")
if record.post:
worksheet.write(row, 5, record.post)
else:
worksheet.write(row, 5, '')
if record.apply_time:
d = datetime.datetime.strptime(record.apply_time,
'%Y-%m-%d %H:%M:%S')
delta = datetime.timedelta(hours=8)
open_time = d + delta
worksheet.write(row, 6,
open_time.strftime('%Y-%m-%d %H:%M:%S'))
else:
worksheet.write(row, 6, "")
if record.break_describe:
worksheet.write(row, 7, record.break_describe)
else:
worksheet.write(row, 7, "")
if record.state:
if record.state == 'zero':
kind = '未处理'
elif record.state == 'one':
kind = '已修复'
worksheet.write(row, 8, kind)
else:
worksheet.write(row, 8, "")
if record.repair_time:
d = datetime.datetime.strptime(record.repair_time,
'%Y-%m-%d %H:%M:%S')
delta = datetime.timedelta(hours=8)
open_time = d + delta
worksheet.write(row, 9,
open_time.strftime('%Y-%m-%d %H:%M:%S'))
else:
worksheet.write(row, 9, "")
if record.repair_manufacturer:
worksheet.write(row, 10, record.repair_manufacturer)
else:
worksheet.write(row, 10, "")
row += 1
name = str(int(round(time.time() * 1000))) + str(
random.randint(1, 1000)) + '.xls'
file = path + name
print(file)
wtbook.save(file)
with open(file, 'rb') as f:
data = f.read()
response = request.make_response(data)
response.headers['Content-Type'] = 'application/vnd.ms-excel'
response.headers["Content-Disposition"] = "attachment; filename={}". \
format(name.encode().decode('latin-1'))
os.remove(file)
return response
def write_value(self, row, col, value):
read_data = xlrd.open_workbook(self.file_name)
write_data = copy(read_data)
sheet_data = write_data.get_sheet(0)
sheet_data.write(row, col, value)
write_data.save(self.file_name)
