def sfcx_batch_query(filename):
with open_file(filename) as f:
rows = f.get_sheet_by_index(0).get_rows()
for row in rows:
account = row['账号']
begindate = row['起始日期']
enddate = row['结束日期']
sfcx(account, begindate, enddate)
time.sleep(30)
from excel_magic.dataset import open_file
from urllib.request import urlretrieve
excel = open_file('图片库.xlsx')
sheet = excel.get_sheet_by_index(0)
rows = sheet.get_rows()
for row in rows:
name = row['人物名称'].value
url = row['下载链接'].value
path = f'result/{name}.jpeg'
urlretrieve(url, path)
print(f'{name} 已下载 ✔️')
import os
from excel_magic.dataset import open_file
# print(os.curdir())
now = os.curdir
print(now)
file_name = '招福银行客户信息总表.xlsx'
excel_file = open_file(file_name)
for f in os.listdir():
if '支行' in f:
excel_file.merge_file(f)
excel_file.save()
from excel_magic.dataset import Dataset, open_file
ex_1 = open_file(path="example.xlsx")
ex_1.save()
ex_2 = Dataset(path="example-2.xlsx")
ex_2.save()
with Dataset("example-3.xlsx") as ex_3:
# Open file without worrying close file
# Do stuff here
pass
from excel_magic.dataset import open_file
file_a = open_file('显示器数据-电商.xlsx')
file_b = open_file('显示器数据-维修部.xlsx')
file_c = open_file("显示器数据-合并.xlsx")
sheet_a = file_a.get_sheet_by_index(0)
sheet_b = file_b.get_sheet_by_index(0)
sheet_c = file_c.get_sheet_by_index(0)
rows_a = sheet_a.get_rows()
rows_b = sheet_b.get_rows()
for row_a, row_b in zip(rows_a, rows_b):
join_row = row_a + row_b
filtered_row = join_row.filter_fields(sheet_c.fields)
sheet_c.append_row(filtered_row)
file_c.save()
from excel_magic.dataset import open_file,Style
from excel_magic.diff import diff
total_file = open_file('库存信息总表.xlsx')
local_file = open_file('库存信息子表.xlsx')
check_file = local_file.duplicate("错误检查.xlsx")
sheet_a = local_file.get_sheet_by_index(0)
sheet_b = total_file.get_sheet_by_index(0)
sheet_c = check_file.get_sheet_by_index(0)
diff_result = diff(sheet_a, sheet_b)
diff_sheet = diff_result.not_found_in_b
diff_rows = diff_result.not_found_in_b.get_rows() # 找到有问题的 row
highlight_style = Style(fill_color='#FFC7CD', font_color='#C65F60')
sheet_c.highlight(diff_rows, highlight_style) # 在 check_file 中给这些问题 row 标红
check_file.sheets.append(diff_sheet) # 同时为问题 row 新建一个 sheet ，添加到 check_file 中
check_file.save()
from excel_magic.dataset import open_file
from word_spell import Document
with open_file('应聘面试信息.xlsx') as excel_file:
sheet = excel_file.get_sheet_by_index(0)
rows = sheet.get_rows()
for row in rows:
name = row['面试者姓名'].value
job = row['投递职位'].value
date = row['面试日期'].value
time = row['面试时间'].value
doc = Document('面试通知模板.docx')
doc.render_from_template(面试者姓名=name,
投递职位=job,
面试日期=date,
面试时间=time,
out="./{}.docx".format(name))
from excel_magic.dataset import open_file
from word_spell import Document
excel_file = open_file("租赁数据.xlsx")
sheet = excel_file.get_sheet_by_index(0)
for row in sheet.get_rows():
doc = Document("租赁合同模板.docx")
doc.render_from_template(
out=f"{row['合同编号']}_{row['承租方姓名']}.docx",
合同编号=row['合同编号'],
出租方姓名=row['出租方姓名'],
出租方身份证=row['出租方身份证'],
出租方电话=row['出租方电话'],
承租方姓名=row['承租方姓名'],
承租方身份证=row['承租方身份证'],
承租方电话=row['承租方电话'],
房屋地址=row['房屋地址'],
租房起始日期=row['租房起始日期'],
租房终止日期=row['租房终止日期'],
月租金=row['月租金'],
保证金=row['保证金'],
)
from excel_magic.dataset import open_file
import pyautogui as auto
import time
time.sleep(10)
with open_file('财务报告.xlsx') as f:
sheet = f.get_sheet_by_index(0)
rows = sheet.get_rows()
for row in rows:
auto.keyDown("tab")
auto.keyDown("enter")
# find ADD
auto.keyDown("tab")
auto.keyDown("tab")
auto.typewrite(row['指标名称'].value)
auto.keyDown("tab")
auto.typewrite(str(row['第一季度'].value))
auto.keyDown("tab")
auto.typewrite(str(row['第二季度'].value))
auto.keyDown("tab")
auto.typewrite(str(row['第三季度'].value))
auto.keyDown("tab")
auto.typewrite(str(row['第四季度'].value))
auto.keyDown("tab")
auto.keyDown("enter")
auto.keyDown("tab")
auto.keyDown("tab")
auto.keyDown("enter")
from excel_magic.dataset import open_file
file = open_file('fans.xlsx')
sheet = file.get_sheet_by_index(0)
rows = sheet.get_rows()
fans_num = len(rows)
group_size = 30
num = 0
for i in range(0, fans_num, group_size):
num += 1
group_rows = rows[i:i + group_size]
new_file = open_file(f"result/「初音ミク 粉丝群」群{num}.xlsx")
new_sheet = new_file.add_sheet("sheet1", sheet.fields)
new_sheet.append_rows(group_rows)
new_file.save(backup=False)
import os
from excel_magic.dataset import open_file
file_list = os.listdir()
excel_file = open_file('./招福银行客户信息总表.xlsx')
for f in file_list:
if ".xlsx" in f:
excel_file.merge_file(f)
excel_file.save()
亲爱的{}同事，您好：
根据《国务院办公厅公布2020年放假安排》，结合公司的实际情况，端午节期间放假安排如下：
2020年端午节放假，2020年06月25日至06月27日放假三天。
注意事项：
1、请各部门负责人提前组织好放假前安全检查，并做好防火、防盗排查;
2、放假期间需要外出的员工请注意安全防护，避免意外事故发生。
3、公司今年的端午礼品是六芳斋粽子礼盒，每人一盒。
祝您节日愉快，身体健康!
'''
client = QQMail(address=ADDRESS, access_token=TOKEN)
excel_file = open_file('员工表.xlsx')
sheet = excel_file.get_sheet_by_index(0)
for row in sheet.get_rows():
name = row['姓名']
mail = row['邮箱']
msg = Message(from_name=ADDRESS, to_name=mail.value, subject=subject)
msg.add_text(mail_text.format(name))
msg.add_attachment('端午放假时间.png')
client.send(to_address=mail.value, msg=msg)
print(f'{name} {mail} ✔️')
import time
from excel_magic.dataset import open_file
from helium import * # 导入 helium 库中的全部函数
path = 'file:///D:/project/muggleRpa/day13/库存提交系统.html' # 本地 HTML 文件的路径，可以用浏览器打开 HTML，复制地址栏即可
start_chrome(path)
all_text = find_all(Text())
id_list = [i.web_element.text for i in all_text if i.web_element.text.isnumeric()]
sh = open_file("库存信息.xlsx").get_sheet_by_index(0)
result = sh.filter(lambda row: str(row["商品编号"]) in id_list)
for row in result:
order_id = str(row["商品编号"].value)
ipt = TextField(to_right_of=order_id)
write(row['存量'].value, into=ipt)
cb = CheckBox(to_right_of=ipt)
if int(row['存量'].value) < 100:
click(cb)
time.sleep(1)
click(Button("提交"))
time.sleep(2)
kill_browser()
