def create(url, file_name, school, year, type): raw_wb = workbook.Workbook() excel.get_comments(url, raw_wb) excel.classify(raw_wb) excel.collate(raw_wb) try: db_wb = load_workbook(file_name) except FileNotFoundError: db_wb = workbook.Workbook() create_headers(db_wb) raw_to_db(raw_wb, db_wb, school, year, type) db_wb.save(file_name)
def clean(wb): old_wb = wb old_ws = old_wb.active new_wb = workbook.Workbook() new_ws = new_wb.active max_row = old_ws.max_row max_column = old_ws.max_column for i in range(max_row): # Check if line is missing 3 elements row = i + 1 if old_ws.cell(column=classifier_lists.headers.index('SAT I:') + 1, row=row).value is None: if old_ws.cell( column=classifier_lists.headers.index('SAT II:') + 1, row=row).value is None: if old_ws.cell( column=classifier_lists.headers.index('GPA:') + 1, row=row).value is None: continue for i in range(max_column): column = i + 1 new_ws.cell(row=row, column=column).value = old_ws.cell(row=row, column=column).value print('Clean done') return new_wb
def save_excel(self): # QModelIndex() # for i in range(self.list_model.rowCount()): # print(self.list_model.item(, i).data()) self.tableView.selectAll() indexs = self.tableView.selectionModel().selection().indexes() if len(indexs) > 0: ls0 = [] for i in range(0, len(indexs), 13): ls = [] for j in range(13): ls.append(indexs[i + j].data()) ls0.append(ls) wb = workbook.Workbook() s = [ "用户名", "姓名", "一级单位", "二级单位", "三级单位", "试卷名称", "考试时间", "考试时长", "用时", "题目数量", "总分", "得分", "考试类别" ] ls0.insert(0, s) for x in ls0: wb.worksheets[0].append(x) filepath, _ = QFileDialog.getSaveFileName(self, "导出题库到Excel文件", "./", "excel(*.xlsx)") if len(filepath) > 0: wb.save(filepath)
def getFriendsAndSaveAsEcxel(): friends = itchat.get_friends(update=True)[:] print(friends) total = len(friends) - 1 male = female = other = 0 wb = workbook.Workbook() # 创建Excel对象 ws = wb.active # 获取当前正在操作的表对象 ws.append(['昵称', '备注', '性别', '省份', '城市', '个性签名']) for friend in friends[1:]: Sex = friend["Sex"] NickName = friend["NickName"] Province = friend["Province"] City = friend["City"] Signature = friend["Signature"] RemarkName = friend["RemarkName"] #绘制地图用 cities.append(City + '市') if Sex == 1: male += 1 Sex = '男' elif Sex == 2: Sex = '女' female += 1 else: other += 1 Sex = '不男不女' # 往表中写入标题行,以列表形式写入! ws.append([NickName, RemarkName, Sex, Province, City, Signature]) #wb.save('wechat.xlsx') # 存入所有信息后,保存为filename.xlsx print("男性好友:%.2f%%" % (float(male) / total * 100)) print("女性好友:%.2f%%" % (float(female) / total * 100)) print("其他:%.2f%%" % (float(other) / total * 100))
def depositedInExcel(self,path,ocrSwitch,sku_content=None): wb = workbook.Workbook() sheet1 = wb.active cellCount = 2 sheet1["A1"] = "商品标题" sheet1["A2"] = sku_content[0]["NameCommodity"] sheet1["B1"] = "重要卖点" sheet1["B2"] = sku_content[0]["keySellingPoint"] sheet1["C1"] = "商品类目" sheet1["C2"] = sku_content[0]["category"] sheet1["D1"] = "店铺名称" sheet1["D2"] = sku_content[0]["Shop"] sheet1["G1"] = "商品ID" sheet1["G2"] = sku_content[0]["ID"] sheet1["H1"] = "商品参数" sheet1["H2"] = sku_content[0]["parameter"] sheet1["I1"] = "月销量" sheet1["I2"] = sku_content[0]["Monthly_sales"] sheet1["J1"] = "累计评价" sheet1["J2"] = sku_content[0]["Cumulative_comments"] # 这个图有的没有 try: sheet1["K1"] = "主图1" sheet1["K2"] = sku_content[0]["mainFigure1"] sheet1["L1"] = "主图2" sheet1["L2"] = sku_content[0]["mainFigure2"] sheet1["M1"] = "主图3" sheet1["M2"] = sku_content[0]["mainFigure3"] sheet1["N1"] = "主图4" sheet1["N2"] = sku_content[0]["mainFigure4"] sheet1["O1"] = "主图5" sheet1["O2"] = sku_content[0]["mainFigure5"] if ocrSwitch!=None: sheet1["P1"] = "主图1_OCR" sheet1["P2"] = sku_content[0]["mainFigure1_ocr"] sheet1["Q1"] = "主图2_OCR" sheet1["Q2"] = sku_content[0]["mainFigure2_ocr"] sheet1["R1"] = "主图3_OCR" sheet1["R2"] = sku_content[0]["mainFigure3_ocr"] sheet1["S1"] = "主图4_OCR" sheet1["S2"] = sku_content[0]["mainFigure4_ocr"] sheet1["T1"] = "主图5_OCR" sheet1["T2"] = sku_content[0]["mainFigure5_ocr"] except:pass # SKU需要单独处理 sheet1["E1"] = "SKU" sheet1["F1"] = "SKU价格" for i in sku_content[1:]: # 解决SKU列表不等于显示SKU列表的问题 try: sheet1["E{}".format(cellCount)] = sku_content[0]["ID"]+"_"+i["SKU"] sheet1["F{}".format(cellCount)] = i["Price"] cellCount+=1 except:pass path = os.path.join(path,"SKU_{0}_ID{1}_{2}.xlsx".format(time.strftime('%Y-%m-%d'),sku_content[0]["ID"],sku_content[0]["Shop"])) # 完成一个表 print(sku_content[0]["Shop"],sku_content[0]["ID"]) wb.save(filename=path)
def onClickOutput(self): if self.y_predict is None: return #输出表格中结果 file_path, self.save = QFileDialog.getSaveFileName( self, '保存文件', '../result', 'ALL Files(*);;xlsx(*.xlsx);;xls(*.xls);;csv(*.csv)') if file_path == '': return wb = workbook.Workbook() wb.encoding = 'utf-8' wa = wb.active # 文件中写入数据 try: rows = self.model.rowCount() columns = self.model.columnCount() for i in range(rows): item = [] for j in range(columns): item.append(self.model.index(i, j).data()) wa.append(item) wb.save(file_path) except Exception as e: print(e) return try: self.plt.savefig('{}_img.svg'.format( file_path.split('.', maxsplit=1)[0])) except: pass self.status_bar.showMessage('导出完毕!', 5000) print('导出完毕!')
def write_case(self, row, col, value): '''在指定的单元格写入指定的数据,并保存到当前Excel''' wb = workbook.Workbook(self.file_name) #打开要写入的数据的工作簿 sheet = wb[self.sheet_name] #定位表单 sheet.cell(row, col).value = value #定位单元格,或者第二种方法sheet.cell(row,col,value) wb.save(self.file_name) #保存工作簿 wb.close() #每次操作完必须关闭掉
def input_data(msg): wb = workbook.Workbook() #新建 wb.save('tk_question1.xlsx') #保存 ---修改或者新建之后必须先保存 wb_1 = load_workbook('tk_question1.xlsx') #打开工作簿 st = wb_1['Sheet'] #定位表单 st.cell(row=1, column=1).value = msg # 修改之前必须手动关闭文件 wb_1.save('tk_question1.xlsx') res = st.cell(1, 1).value print(res)
def create_excel(self, file): ''' 新建一个Excel文件 :param file: 文件名 :return: ''' wb = workbook.Workbook(file) wb.save(file) log.info('新建一个文件,文件名为:{}'.format(file)) return wb
def write_excel(file_path, data_list): try: wb = workbook.Workbook() wb.encoding = 'utf-8' wa = wb.active for item in data_list: wa.append(item) wb.save(file_path) except Exception as e: print(e)
def __init__(self, saved_file_path): self.saved_file_path = saved_file_path self.workbook = workbook.Workbook() self.sheet_no = 0 self.start_column = 1 # all the start_column is 1 self.start_rows = [1] self.block_nos = [0] self.worksheet_level_set = [False]
def __init__(self): self.douban_url = "https://book.douban.com" self.start_url = "https://book.douban.com/tag/?view=type&icn=index-sorttags-hot" self.headers = { "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:60.0) Gecko/20100101 Firefox/60.0" } self.wb = workbook.Workbook() self.ws = self.wb.active self.ws.append(['书名', '评分', '评价人数'])
def __init__(self): # self.url = 'https://wuhan.anjuke.com/sale/p{}-rd1/?kw={}&direct=yes#filtersort' self.url = 'https://cs.anjuke.com/sale/p{}-rd1/?kw={}&direct=yes#filtersort' self.headers = { 'User-Agent':'Mozilla/5.0(Windows NT 6.1;WOW64)AppleWebKit/537.36(KHTML, likeGecko)Chrome/74.0.3729.169Safari / 537.36' } self.proxy = {'http':'111.231.94.44:8888'} self.num = 0 self.wb = workbook.Workbook() # 创建Excel对象 self.ws = self.wb.active # 获取单签正在操作的表对象
def __init__(self,ex,sh): wb=workbook.Workbook() wb.create_sheet(sh,index=0) wb.save(ex) self.data1='白日依山尽' self.data2="{'name':'邢麦苗','age':25}" self.data3='[0,2,3]' self.data4='黄河入海流' self.data5=25 self.ex = ex self.sh = sh
def index(): wb = workbook.Workbook() ws = wb.active ws.append(['姓名', '时间', '篮板', '助攻', '得分']) threads = [Thread(target=get, args=(page, ws)) for page in range(1, 200)] threads.start() threads.join() for t in threads: t.start() for t in threads: t.join()
def output_excel(): wb = workbook.Workbook() #创建Excel对象 CourseInfoExcel = wb.active #获取当前正在操作的表对象 #往表中写入标题行,以列表形式 CourseInfoExcel.append(['课程号','课程名','学分','教师名','职称','授课学院','领域','学年','学期','学时安排','备注']) CourseInfo = [] parser(CourseInfo) for i in range(len(CourseInfo)): CourseInfoExcel.append(CourseInfo[i]) wb.save('CourseInfoExcel.xlsx')
def create_excel(filename="filename.xlsx", recreate=False, add_sheet=None): # 创建excel if add_sheet is None: add_sheet = [] path = os.path.join(os.getcwd() + '\\' + filename + ".xlsx") p1 = os.path.exists(path) if p1: if recreate: os.remove(path) wk = workbook.Workbook() sheet_method(wk, add_sheet) wk.save(path) messagebox.showinfo('提示', '文件已存在') else: pass else: wk = workbook.Workbook() sheet_method(wk, add_sheet) wk.save(path) messagebox.showinfo('提示', '创建完成' + filename + '.xlsx')
def add_excel(self,file_name,sheet_name): '''新建表格函数 file_name 文件名 sheet_name 表单名''' try : wb=workbook.Workbook()#新建表格 wb.create_sheet(sheet_name)#新建表单 wb.save(file_name) log.info('新建成功') except Exception as e : log.error('新建失败'.format(e)) raise e
def to_table(url): table = workbook.Workbook() data = table.active data.append(['学校名称', '官方网站']) for i, j in zip(url, range(len(url))): if 'baike' in i: pass else: data1 = re.findall(r'href="(.*?)"', i) data2 = re.findall(r'>(.*?)<\/a>', i) data.append([data2[0], data1[0]]) table.save('College.xlsx')
def excel_write(self, str, row, column): try: wb = load_workbook(self.filename) if self.sheet_name not in wb.sheetnames: wb.create_sheet(self.sheet_name) except FileNotFoundError as e: wb = workbook.Workbook() wb.create_sheet(self.sheet_name) finally: sheet = wb[self.sheet_name] sheet.cell(row, column).value = str wb.save(self.filename) wb.close()
def run(self): self.start_signal.emit('导出准备中...') try: dtl = DataFrameListMTF.DataFrameListMTF() data_list = dtl.model_to_list(self.model) wb = workbook.Workbook() wb.encoding = 'utf-8' wa = wb.active cnt = len(data_list) for i, item in enumerate(data_list): wa.append(item) self.start_signal.emit('导出进度:{}%'.format(int(i / cnt * 100))) wb.save(self.file_path) self.start_signal.emit('导出进度:100%') self.end_signal.emit() except Exception as e: print(e)
def toGetImagePath(path): wb = workbook.Workbook() sheet1 = wb.active # 第一个表头:文件ID+主图名称 sheet1["A1"] = "图片信息" sheet1["B1"] = "图片" sheet1["C1"] = "OCR" # 拿到图片数量 filecount = 2 for root,dirs,files in os.walk(path): filecount+=len(files) # 拿到图片路径 for root,dirs,files in os.walk(path): # 拿到图片路径 for name in files: # 拿到图片路径 imagePath = os.path.join(root,name) #print(imagePath) # 文件id标识+文件名=商品信息 commodityInformation = root.split("\\")[-1].split("_")[0] + os.path.splitext(name)[0] #print(commodityInformation) # 第一列插入商品信息 sheet1["A{}".format(filecount)] = commodityInformation # 第二列插入图片,首先调整行高和列宽 sheet1.column_dimensions['B'].width = 80 sheet1.row_dimensions[filecount].height = 80 img = Image(imagePath) # 生成新的图片的宽和高 newsize = (160,160) img.width,img.height = newsize # 将图片添加到excel中 sheet1.add_image(img,'B{}'.format(filecount)) # 第三列为OCR结果 sheet1["C{}".format(filecount)] = ocr.picture_Path(imagePath) filecount+=1 wb.save(filename=os.path.join(r"D:\atnight\蓝禾科技","result.xlsx"))
def initialize_form(): wb = workbook.Workbook() ws = wb.active ws.title = "Sheet1" ws["A1"] = '''"null_$head,main_m_pk_glorgbook,main_m_pk_vouchertype,main_m_no,main_pk_prepared,main_m_prepareddate,m_explanation,m_accsubjcode,m_pk_currtype,m_debitamount,m_localdebitamount,m_price,m_debitquantity,m_creditquantity,m_creditamount,m_localcreditamount,m_fracdebitamount,m_fraccreditamount,m_checkno,m_checkdate,m_free1,m_free2,m_bankaccount,m_free3,m_checkstyle,m_excrate2,m_excrate1,ass_1,ass_2,ass_3,ass_4,ass_5,ass_6,ass_7,ass_8,ass_9"''' ws["B1"] = "主体账簿" ws["C1"] = "凭证类别" ws["D1"] = "凭证号" ws["E1"] = "制单人" ws["F1"] = "制单日期" ws["G1"] = "摘要" ws["H1"] = "科目编码" ws["I1"] = "币种" ws["J1"] = "原币借方金额" ws["K1"] = "本币借方金额" ws["L1"] = "单价" ws["M1"] = "借方数量" ws["N1"] = "贷方数量" ws["O1"] = "原币贷方金额" ws["P1"] = "本币贷方金额" ws["Q1"] = "辅币借方金额" ws["R1"] = "辅币贷方金额" ws["S1"] = "结算号" ws["T1"] = "结算日期" ws["U1"] = "核销号" ws["V1"] = "核销日期" ws["W1"] = "银行账户" ws["X1"] = "票据类型" ws["Y1"] = "结算方式" ws["Z1"] = "折本汇率" ws["AA1"] = "折辅汇率" ws["AB1"] = "辅助核算1" ws["AC1"] = "辅助核算2" ws["AD1"] = "辅助核算3" ws["AE1"] = "辅助核算4" ws["AF1"] = "辅助核算5" ws["AG1"] = "辅助核算6" ws["AH1"] = "辅助核算7" ws["AI1"] = "辅助核算8" ws["AJ1"] = "辅助核算9" return ws, wb
def exportExcel(): global resultList wb = workbook.Workbook() sheet = wb.active sheet.title = "好评统计" sheet['A1'] = 'title' sheet['B1'] = 'content' i = 2 for index in resultList: keyA = "A" + str(i) keyB = "B" + str(i) sheet[keyA] = index['title'] sheet[keyB] = index['content'] i = i + 1 wb.save('./result1.xlsx') messagebox.showinfo(title='导出完成', message='导出完成')
def __init__(self,sheetname,excelname): self.sheetname = sheetname self.excelname = excelname wb = workbook.Workbook() wb.create_sheet(self.sheetname,index=0) wb.save(self.excelname) self.op = load_workbook(self.excelname) self.sheet = self.op[self.sheetname] self.data1='{"mobilephone":"18688773467","pwd":"123456"}' self.data2='登录成功' self.data3='{"mobilephone":"18688773467","pwd":"1234567"}' self.data4="用户名或密码错误" self.data5='{"mobilephone":"18688773467","pwd":None}' self.data6="用户名或密码不能为空" self.data7='{"mobilephone":None,"pwd":"123456"}' self.data8="用户名或密码不能为空" self.data9 = 1 self.data10 = 2 self.data11= 3 self.data12= 4
def register(username, password): db_file_path = os.path.join(BASE_DIR, FILE_NAME) if os.path.exists(db_file_path): wb = load_workbook(db_file_path) sheet = wb.worksheets[0] next_row_position = sheet.max_row + 1 else: wb = workbook.Workbook() sheet = wb.worksheets[0] next_row_position = 1 user = sheet.cell(next_row_position, 1) user.value = username pwd = sheet.cell(next_row_position, 2) pwd.value = md5(password) ctime = sheet.cell(next_row_position, 3) ctime.value = datetime.now().strftime("%Y-%m-%d %H:%M:%S") wb.save(db_file_path)
def run(self): self.start_signal.emit('导出准备中...') try: dtl = DTL() data_list = dtl.model_to_list(self.model) wb = workbook.Workbook() wb.encoding = 'utf-8' wa = wb.active cnt = len(data_list) for i, item in enumerate(data_list): wa.append(item) self.start_signal.emit('导出进度:{}%'.format(int(i / cnt * 100))) wb.save(self.file_path) try: self.plt.savefig('{}_img.svg'.format( self.file_path.rsplit('.', maxsplit=1)[0])) except: pass self.start_signal.emit('导出进度:100%') self.end_signal.emit() except Exception as e: print(e)
def save_balance_to_xls(self, header: list, data: list): # open workbook date = time.strftime('%Y-%m-%d') dir_name = settings.join(settings.sd_path, 'balance_data') if not os.path.exists(dir_name): os.makedirs(dir_name) file_name = settings.join(dir_name, f'account_balance_%s.xlsx' % date) if os.path.exists(file_name): wb = load_workbook(filename=file_name) else: wb = workbook.Workbook() # open worksheet sheet_names = wb.sheetnames if self.platform in sheet_names: ws = wb[self.platform] else: ws = wb.create_sheet(title=self.platform) if 'Sheet' in sheet_names: del wb['Sheet'] # write header if not ws['A1'].value: for col, i in enumerate(header, 1): ws.cell(row=1, column=col, value=i) # resort read_header = [x.value for x in next(ws.rows)] max_col = ws.max_column for c in header: if c not in read_header: max_col += 1 ws.cell(1, max_col, value=c) for k, v in enumerate(data.copy()): data[k] = [v.get(x, 0) for x in read_header] # write for i in data: ws.append(i) # save wb.save(file_name) return
def triggeredSave(self): self.status_bar.showMessage('保存文件', 5000) file_path, _ = QFileDialog.getSaveFileName(self, '保存文件', '../data', 'ALL Files(*);;xlsx(*.xlsx);;xls(*.xls);;csv(*.csv)') if file_path == '': return # 文件中写入数据 try: wb = workbook.Workbook() wb.encoding = 'utf-8' wa = wb.active for item in self.data: # 过滤无效数据 try: if ''.join(item) == '': continue except: pass wa.append(item) wb.save(file_path) self.status_bar.showMessage('保存完毕!') except Exception as e: print(e)
def run(): print("选择爬取的网址:") # url = 'https://cucc.tazzfdc.com/reisPub/pub/preSaleBuildingStatist' # url = 'https://cucc.tazzfdc.com/reisPub/pub/saleBuildingStatist' print("1.爬取预售楼盘公示 2.爬取现售楼盘公示") size = input() if (int(size) == 1): url = 'https://cucc.tazzfdc.com/reisPub/pub/preSaleBuildingStatist' print(url) elif (int(size) == 2): url = 'https://cucc.tazzfdc.com/reisPub/pub/saleBuildingStatist' print(url) else: print("错误!!!") print("请输入总页数:") page = input() print("请输入导出的文件名称:") wjmc = input() wb = workbook.Workbook() # 创建Excel对象 ws = wb.active # 获取当前正在操作的表对象 # 往表中写入标题行,以列表形式写入! ws.append(['区域', '项目名称', '地址', '预售证号', '开发企业', '批准时间']) for _pgn in range(1, int(page) + 1): craw(url, _pgn, wjmc, ws, wb)