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)