Nooae's Blog

物料批量查询

2024-07-21 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
import os
import time
import openpyxl
from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.common.action_chains import ActionChains


# 进入登陆界面
driver = webdriver.Edge()
mes =  'MES网址'
driver.get(mes)
time.sleep(1)

# 输入用户名
driver.find_element(By.ID,"userDto_loginName").send_keys("MES账号")
time.sleep(1)

# 输入密码
driver.find_element(By.ID,"keeper").send_keys("MES密码")
time.sleep(1)

# 选择账套
driver.find_element(By.ID,'mcDataAuthB').click()

# 空点
ActionChains(driver).move_by_offset(200,100).click().perform()
time.sleep(1)

# 点击登陆按钮
driver.find_element(By.ID, "loginButton").click()
time.sleep(3)

# 点击快捷方式
driver.find_element(By.ID, "infoDiv").click()
time.sleep(1)

# 点击库存查询
driver.find_element(By.XPATH, "/html/body/div[1]/div[1]/div/div[2]/div[3]/div/div[1]").click()
time.sleep(5)

# 进入库存查询框架
iframe = driver.find_element(By.ID, "iframe2")
driver.switch_to.frame(iframe)


# 查找当前文件夹.xlsx文件
path = os.getcwd()
files = os.listdir(path)
excelfiles = [f for f in files if not f.startswith(("~$")) and f.endswith((".xlsx"))]
for file in excelfiles:
    fullpath = os.path.join(path,file)

# 打开.xlsx文件默认sheet
wb = openpyxl.load_workbook(fullpath)
sheet = wb.active


# 定义查找函数
def mes_clik(num):
    cell = sheet["B" + str(num)].value
    v = driver.find_element(By.XPATH, '/html/body/div[1]/div/div[2]/div/form/div/div[1]/div[1]/div/div[2]/div[3]/input[1]')
    v.clear()
    v.send_keys(cell) 
    time.sleep(1) 

    # 点击查询按钮  
    driver.find_element(By.XPATH, "/html/body/div[1]/div/div[2]/div/form/div/div[1]/div[2]/div[1]/a[1]").click()
    name = sheet["A" + str(num)].value
    num = sheet["C" + str(num)].value
    print(str(cell) + '需要' + str(num))
    

# 循环查找物料信息
for i in range(2,100):
    mes_clik(i)
    input("按Enter键继续\n")

站位表制作

2024-07-20 
1
=IF(ISNUMBER(VALUE(LEFT(INDIRECT("C"&ROW()),1))),LEFT(INDIRECT("C"&ROW()),1),INDIRECT("A"&ROW()-1))

判断C列内容左边第1个字符是否为数字；如果是，显示该数字；否则显示A列上一单元格内容。

Selenium模拟点击

2024-06-08 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
from selenium import webdriver
from selenium.webdriver.common.by import By
import time

driver = webdriver.Chrome()  # 打开谷歌浏览器
url = 'https://www.baidu.com/'
driver.get(url)  # 输入网址
 
# 输入搜索关键词并点击搜索按钮
v = driver.find_element(By.ID, 'kw')  # 查找搜索输入框
v.send_keys('不想搬砖怎么办？')  # 输入关键词
time.sleep(3)  # 等待页面加载完成

btn = driver.find_element(By.ID, 'su')  # 查找搜索按钮
btn.click()  # 点击搜索按钮

time.sleep(60)  # 等待搜索结果页面加载完成

driver.quit()  # 关闭浏览器

Win组合快捷键

2024-06-02
快捷键 作用
Win+E 打开我的电脑
Win+D 返回桌面
Win+L 锁定计算机
Win+R 开启运行对话框
Win+M 最小化所有窗口
Win+U 打开设置页面
Win+TAB 窗口切换
Win+Break 打开电脑配置窗口
Win+数字键 切换任务窗口
Win+方向键 应用分屏

提取指定符号后字符串

2024-05-19 
1
=IFERROR(TRIM(MID(SUBSTITUTE(B2,"|",REPT(" ",100)),1,100)),"")

REPT函数用来重复空格100次

SUBSTITUTE将字符替换为指定内容

MID函数用来提取字符串中间部分的字符

TRIM函数去除空格

IFERROR函数将错误转换空文本

视频剪辑

2024-05-11 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
from moviepy.editor import *

# 打开视频文件
video = VideoFileClip('dark.mp4')

# 剪辑视频
video2 = video.subclip(0,1)

# 添加文字、位置、显示时间
text = (TextClip("WPS",fontsize =20,color ="white",font ="黑体")
        .set_position('center')
        .set_duration(0.5))
        
# 在视频上加载文本
video_with_text = CompositeVideoClip([video2,text])

# 保存为新文件
video_with_text.write_videofile('new_1.mp4')

拆分工作薄

2024-05-11

将工作薄中的Excel工作表拆分为单独的工作薄

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
import xlwings as xw
import os

def split_excel(file_path):
    app = xw.App(visible=False, add_book=False)
    
    workbook = app.books.open(file_path)
    worksheet = workbook.sheets
    for i in worksheet:  # 遍历工作簿中所有工作表
        new_workbook = app.books.add()  # 新建工作簿
        new_worksheet = new_workbook.sheets[0]  # 选中新建工作簿中的第1张工作表
        i.copy(before=new_worksheet)  # 将原来工作簿中的当前工作表复制到新建工作簿的第1张工作表之前
        new_workbook.sheets[1].delete()  #删除空白工作表
        new_workbook.save('拆分\{}.xlsx'.format(i.name))  # 保存新工作簿
        print('{}.xlsx'.format(i.name) + "拆分完成")
        new_workbook.close()  # 关闭新工作薄
    workbook.close()   # 关闭旧工作薄

def find_excel():
    path = os.getcwd()
    files = os.listdir(path)
    if not os.path.exists("拆分"):   #检查如果没有“拆分”文件夹
        os.mkdir("拆分")   #创建“拆分”文件夹
    excelfiles = [f for f in files if not f.startswith(("~$")) and f.endswith((".xlsx"))]  #过滤当前所有xlsx文件
    for file in excelfiles:   #遍历所有xlsx文件
        fullpath = os.path.join(path,file) 
        split_excel(fullpath)   #对xlsx文件进行拆分

find_excel()

将工作薄转换为PDF

2024-04-30

将EXCEL中的所有工作表转换为一个PDF文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
import win32com.client,os
  
def excel_to_pdf(excel_path, pdf_path):  
    excel = win32com.client.Dispatch("Excel.Application")  
    excel.Visible = False    
    wb = excel.Workbooks.Open(excel_path)  
  
    wb.ExportAsFixedFormat(  
        Type=0,  
        Filename=pdf_path,  
        Quality=win32com.client.constants.xlQualityStandard,  
        IncludeDocProperties=True,  
        IgnorePrintAreas=False,  
        OpenAfterPublish=False  
    )  
  
    wb.Close(SaveChanges=False)
    excel.Quit()

def excel_file():
    path = os.getcwd()
    files = os.listdir(path)
    if not os.path.exists("pdf"):
        os.mkdir("pdf")
    excelfiles = [f for f in files if not f.startswith(("~$")) and f.endswith((".xlsx"))]
    for file in excelfiles:
        excel_path = os.path.join(path,file)
        pdf_name = os.path.splitext(file)[0]
        pdf_path = path + "\\" + "pdf" + "\\" + pdf_name + ".pdf"
        excel_to_pdf(excel_path, pdf_path)
        print(excel_path+"格式转换完成")
    
excel_file()

批量调整Excel表格格式

2024-04-21 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
import openpyxl
import os

def excel_col(file_name):
    wb = openpyxl.load_workbook(file_name)
    sheet = wb.active

    for row in sheet.iter_rows():
        for cell in row:
            cell.font=openpyxl.styles.Font(name='宋体')
            cell.font=openpyxl.styles.Font(name='Times New Roman',size=12)
            
    sheet.column_dimensions['B'].width = 6
    sheet.column_dimensions['C'].width = 18
    sheet.column_dimensions['D'].width = 25

    sheet['A1'].font=openpyxl.styles.Font(name='宋体',size=20,bold=True)
    sheet['G1'].font=openpyxl.styles.Font(name='宋体',size=12,bold=True)
    sheet['G2'].font=openpyxl.styles.Font(name='宋体',size=12,bold=True)

    wb.save(file_name)

def excel_file():
    path = os.getcwd()
    files = os.listdir(path)
    excelfiles = [f for f in files if not f.startswith(("~$")) and f.endswith((".xlsx"))]
    for file in excelfiles:
        fullpath = os.path.join(path,file)
        excel_col(fullpath)
        print(fullpath + "格式调整完成")

excel_file()

生成词云

2024-01-27 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
import pandas as pd
import matplotlib.pyplot as plt
from wordcloud import WordCloud

# 读取Excel文件
data = pd.read_excel('index.xlsx')
df = pd.DataFrame(data)

# 从DataFrame中提取数据
text = df['Name']
text1 = ' '.join(df['Name'])
print(text)
print(text1)

# 创建词云对象
wordcloud = WordCloud(background_color='white').generate(text1)

# 展示词云
plt.imshow(wordcloud, interpolation='bilinear')
plt.axis('off')
plt.show()