我写了三个.py文件,想让他们依次执行,在执行前,我已经手动测试了一下,没有问题,但用自动程序跑了以后,就提示错误,请大家帮忙看一下问题在哪
顺序执行的文件
import os
import subprocess
# ret1 = os.system('python 进程1.py')
subprocess.run('python 进程1.py', shell=True)
print("程序1结束")
# ret2 = os.system('python 进程2.py')
subprocess.run('python 进程2.py', shell=True)
print("程序2结束")
# ret3 = os.system('python 进程3.py')
subprocess.run('python 进程3.py', shell=True)
print("程序3结束")
程序1,手动测试时没有问题,正常运行
import openpyxl
import pandas as pd
from win32com.client import Dispatch
import os
import gc
# 文件路径
path = r"D:\python课件\数据分析\python练习集\应收账款-2023\应收账款-测试\测试-全部 - 副本 (7).xlsx"
region_path = r"C:\Users\eric\Desktop\应收账款\区域标准库.xlsx"
output_path = r"D:\python课件\数据分析\python练习集\应收账款-2023\应收账款-测试\22.xlsx"
# 整理excel
excel01 = openpyxl.load_workbook(path)
sheet01 = excel01["Sheet"]
sheet01.unmerge_cells('A1:K1')
sheet01.unmerge_cells('A2:K2')
sheet01.unmerge_cells('A3:K3')
sheet01.unmerge_cells('A4:A5')
sheet01.unmerge_cells('B4:B5')
sheet01.unmerge_cells('C4:C5')
sheet01.unmerge_cells('D4:D5')
sheet01.unmerge_cells('E4:E5')
sheet01.unmerge_cells('F4:G4')
sheet01.unmerge_cells('H4:I4')
sheet01.unmerge_cells('J4:K4')
sheet01.delete_rows(1)
sheet01.delete_rows(1)
sheet01.delete_rows(1)
sheet01.delete_rows(1)
sheet01.cell(1, 1).value = "科目编号"
sheet01.cell(1, 2).value = "科目名称"
sheet01.cell(1, 3).value = "部门名称"
sheet01.cell(1, 4).value = "往来单位名称"
sheet01.cell(1, 5).value = "合同项目名称"
sheet01.insert_cols(idx=4)
sheet01.insert_cols(idx=6)
excel01.save(path)
# 数据排序
sheet01 = pd.read_excel(path, sheet_name='Sheet')
sheet01.sort_values(by=['部门名称', '合同项目名称', '往来单位名称'], inplace=True)
sheet01.to_excel(path, sheet_name='Sheet', index=False)
# 计算应收款---------------------------------------------------------
excel01 = openpyxl.load_workbook(path)
sheet01 = excel01["Sheet"]
row01 = sheet01.max_row
row02 = row01 + 1
column01 = sheet01.max_column
a = 1
for j in range(2, row01):
q = j + 1
cell01 = sheet01.cell(j, 7)
cell02 = sheet01.cell(q, 7)
if cell01.value == cell02.value:
sheet01.cell(j, 6).value = a
sheet01.cell(q, 6).value = a
else:
a = a + 1
sheet01.cell(j, 6).value = a - 1
sheet01.cell(q, 6).value = a
# 往来单位分组
# 1)判断该组项目有行
# GroupRow = 1
# JumpRow = 1
#
# for r in range(2, row02):
# cell21 = sheet01.cell(r + JumpRow - 1, 6)
# GroupRow = 1
# JumpRow = 1
# for w in range(r + JumpRow, row02):
# cell22 = sheet01.cell(w, 6)
# if cell21.value == cell22.value:
# GroupRow = GroupRow + 1
# JumpRow = JumpRow + 1
# else:
# pass
# sheet01.cell(cell21.row, 17).value = GroupRow
# 2)分组
sheet01.cell(2, 4).value = 1
EnterpIndex = 1
d = 0
for e in range(2, row01):
cell03 = sheet01.cell(e, 6)
cell04 = sheet01.cell(e + 1, 6)
if cell03.value == cell04.value:
if sheet01.cell(e, 5).value == sheet01.cell(e + 1, 5).value:
sheet01.cell(e + 1, 4).value = sheet01.cell(e, 4).value
else:
sheet01.cell(e + 1, 4).value = sheet01.cell(e, 4).value + 1
else:
sheet01.cell(e + 1, 4).value = EnterpIndex
EnterpIndex = 1
d = 0
# 计算数值
for f in range(2, row02):
if "应收" in str(sheet01.cell(f, 2).value):
if sheet01.cell(f, 12).value == "借":
sheet01.cell(f, 14).value = sheet01.cell(f, 13).value
else:
sheet01.cell(f, 14).value = 0
else:
if sheet01.cell(f, 12).value == "借":
sheet01.cell(f, 14).value = sheet01.cell(f, 13).value / 6 * 106
else:
sheet01.cell(f, 14).value = sheet01.cell(f, 13).value / 6 * 106 * -1
# 合并企业项目数字
for L in range(2, row02):
cell_a = "O" + str(L)
cell_b = "F" + str(L)
cell_c = "D" + str(L)
sheet01[cell_a] = "="+str(cell_b)+"&\"_\"&"+str(cell_c)
# sheet01["O2"] = "=F2&D2"
excel01.save(path)
# 关闭文件,打开文件
def just_open(filename):
xlapp = Dispatch("Excel.Application")
xlapp.Visible = False
xlbook = xlapp.Workbooks.Open(filename)
xlbook.Save()
xlbook.Close()
just_open(path)
ret1 = os.access(path, os.W_OK)
print(ret1)
excel01 = openpyxl.load_workbook(path, data_only=True)
sheet01 = excel01["Sheet"]
# 比较循环
h = 0
for W in range(2, row02):
cell_f = "O" + str(W + h)
cell_g = "N" + str(W + h)
cell_h = "P" + str(W + h)
StandValue = sheet01[cell_f]
Result = sheet01[cell_g].value
for G in range(W + h + 1, row02):
cell_d = "O" + str(G)
cell_u = "N" + str(G)
CompValue = sheet01[cell_d]
CompValue_after = sheet01[cell_u]
if CompValue.value == StandValue.value:
Result = Result + float(CompValue_after.value)
h = h + 1
else:
pass
sheet01[cell_h].value = Result
h = 0
# 删除多余值
for W in range(2, row02):
cell_f = "O" + str(W)
StandValue_2 = sheet01[cell_f]
for G in range(W + 1, row02):
cell_d = "O" + str(G)
cell_u = "P" + str(G)
CompValue_2 = sheet01[cell_d]
CompValue_before = sheet01[cell_u]
if CompValue_2.value == StandValue_2.value:
CompValue_before.value = 0
else:
pass
# 标名列
sheet01["P1"] = "应收账款"
sheet01["T1"] = "区域"
sheet01.insert_cols(idx=3)
for DepStr in range(1, row02):
sheet01.cell(DepStr, 3).value = "=TRIM(D" + str(DepStr) + ")"
excel01.save(path)
just_open(path)
ret2 = os.access(path, os.W_OK)
print(ret2)
excel01 = openpyxl.load_workbook(path, data_only=True)
sheet01 = excel01["Sheet"]
sheet01.delete_cols(4)
# 匹配区域-----------------------------------------------------------
for T in range(2, row02):
sheet01.cell(T, 19).value = "=TRIM(G" + str(T) + ")&E" + str(T)
for X in range(2, row02):
sheet01.cell(X, 20).value = "=VLOOKUP(S" + str(
X) + ",区域标准库!$AF$2:$AG$10021,2,FALSE)"
excel01.save(path)
# 导入标准库
df2 = pd.read_excel(region_path, sheet_name='Sheet1')
writer2 = pd.ExcelWriter(path, mode='a', engine='openpyxl')
df2.to_excel(writer2, sheet_name="区域标准库", index=False)
writer2.save()
print("程序结束")
程序2,手动测试时没有问题,正常运行
import win32com.client
import pythoncom
# 定义路径
path = r'D:\python课件\数据分析\python练习集\应收账款-2023\应收账款-测试\测试-全部 - 副本 (7).xlsx'
# 初始化
pythoncom.CoInitialize()
# 独占方式打开DispatchEx
excel = win32com.client.DispatchEx('Excel.application')
# 是否隐藏打开的Excel窗口
excel.Visible = False
# 打开文件
wb = excel.Workbooks.Open(path, UpdateLinks=False, ReadOnly=False)
# 获取第一个Sheet页
shet1 = wb.Worksheets(1)
# 获取实际有数据的行和列
rowNum = shet1.UsedRange.Rows.Count+1
colNum = shet1.UsedRange.Columns.Count+1
for i in range(1, rowNum):
for j in range(1, colNum): # 遍历获取Cell对象
cell = shet1.Cells(i, j)
# 获取值,如果存在公式的话,
value = cell.Value
if cell.hasFormula: # 清除内容 包括公式 然后单独赋值
cell.ClearContents()
cell.Value = value
# 关闭文件并保存
wb.Close(SaveChanges=True)
# 关闭Excel窗口
excel.Quit()
# 注销接口
pythoncom.CoUninitialize()
print("程序结束")
程序3,手动测试时没有问题,正常运行
import openpyxl
import pandas as pd
from win32com.client import Dispatch
import os
import gc
# 文件路径
path = r"D:\python课件\数据分析\python练习集\应收账款-2023\应收账款-测试\测试-全部 - 副本 (7).xlsx"
region_path = r"C:\Users\eric\Desktop\应收账款\区域标准库.xlsx"
output_path = r"D:\python课件\数据分析\python练习集\应收账款-2023\应收账款-测试\22.xlsx"
# 分类写入
excel01 = openpyxl.load_workbook(path)
writer = pd.ExcelWriter(output_path)
df = pd.read_excel(path, sheet_name='Sheet')
col_data = df.loc[:, '部门名称']
list_dep = []
liat_region = ['东北', '华北', '华东', '华南', '华中', '西北', '西南', '新疆', '中原']
list_dep.append(col_data[0])
for row_a in range(0, len(col_data) - 1):
row_b = row_a + 1
if col_data[row_a] != col_data[row_b]:
list_dep.append(col_data[row_b])
else:
pass
for ListNum in range(0, len(list_dep)):
if list_dep[ListNum] == "直管项目":
for RegNum in liat_region:
filtered_data3 = df[(df['应收账款'] != 0) & (df["部门名称"] == list_dep[ListNum]) & (df['区域'] == RegNum)]
filtered_data4 = filtered_data3[['部门名称', '往来单位名称', '合同项目名称', '应收账款', '区域']]
filtered_data4.to_excel(writer, sheet_name=RegNum, index=False)
else:
filtered_data = df[(df['应收账款'] != 0) & (df["部门名称"] == list_dep[ListNum])]
filtered_data2 = filtered_data[['部门名称', '往来单位名称', '合同项目名称', '应收账款']]
filtered_data2.to_excel(writer, sheet_name=list_dep[ListNum], index=False)
writer.save()
# 保存excel
excel01.save(path)
print("程序结束")
自动运行后提示的错误
Traceback (most recent call last):
File "进程1.py", line 29, in <module>
sheet01.delete_rows(1)
AttributeError: 'Worksheet' object has no attribute 'delete_rows'
程序1结束