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)
