实现12345678

应收账款
part1

import openpyxl
import pandas as pd
from win32com.client import Dispatch
import os
import gc
import path_1
# 文件路径
path = path_1.path
region_path = path_1.region_path
output_path = path_1.output_path
GenCon_path = path_1.GenCon_path
# 整理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)

print("程序结束")

part2

import openpyxl
import pandas as pd
from win32com.client import Dispatch
import os
import gc
import path_1
# 文件路径
path = path_1.path
region_path = path_1.region_path
output_path = path_1.output_path
GenCon_path = path_1.GenCon_path

# 导入标准库-总承包标准库
df2 = pd.read_excel(region_path, sheet_name='Sheet1')

df3 = pd.read_excel(GenCon_path, sheet_name='Sheet1')

writer2 = pd.ExcelWriter(path, mode='a', engine='openpyxl')

df2.to_excel(writer2, sheet_name="区域标准库", index=False)

df3.to_excel(writer2, sheet_name="总包标准库", index=False)

writer2.save()

print("程序结束")

part3

import openpyxl
import pandas as pd
from win32com.client import Dispatch
import os
import gc
import path_1
# 文件路径
path = path_1.path
region_path = path_1.region_path
output_path = path_1.output_path
GenCon_path = path_1.GenCon_path

# 计算应收款
excel01 = openpyxl.load_workbook(path)

sheet01 = excel01["Sheet"]

row01 = sheet01.max_row

row02 = row01 + 1

column01 = sheet01.max_column
# 匹配区域-----------------------------------------------------------
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)"
# 判断是否是总包项目-----------------------------------------------------
for Z in range(2, row02):
    sheet01.cell(Z, 22).value = "=VLOOKUP(S" + str(Z) + ",总包标准库!$L$2:$M$11,2,FALSE)"

excel01.save(path)
# ---------------------------------------------------------------------
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
# 往来单位分组
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"]
# 删除总包项目-待转销项税额-数值
for DZ in range(2, row02):
    if "待转" in str(sheet01.cell(DZ, 2).value) and "总包" in str(sheet01.cell(DZ, 22).value):
        sheet01.cell(DZ, 14).value = 0
    else:
        pass
# 比较循环
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["V1"] = "总包项目"

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)

excel01.save(path)

print("程序结束")

part4

import win32com.client
import pythoncom
import path_1
# 定义路径
path = path_1.path
region_path = path_1.region_path
output_path = path_1.output_path
GenCon_path = path_1.GenCon_path
# 初始化
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("程序结束")

part5

import openpyxl
import pandas as pd
from win32com.client import Dispatch
import os
import gc
import path_1
# 文件路径
path = path_1.path
region_path = path_1.region_path
output_path = path_1.output_path
GenCon_path = path_1.GenCon_path
# 分类写入
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("程序结束")

【以下回答由 GPT 生成】

首先，可以对代码进行优化和简化。下面是优化后的代码：

import openpyxl
import pandas as pd
import os
import glob

# 文件路径
path = path_1.path
region_path = path_1.region_path
output_path = path_1.output_path
GenCon_path = path_1.GenCon_path

# 处理单个Excel文件
def process_excel(file_path):
    # 打开Excel文件
    excel = openpyxl.load_workbook(file_path)
    sheet = excel["Sheet"]

    # 解决合并单元格问题
    sheet.unmerge_cells('A1:K1')
    sheet.unmerge_cells('A2:K2')
    sheet.unmerge_cells('A3:K3')
    sheet.unmerge_cells('A4:A5')
    sheet.unmerge_cells('B4:B5')
    sheet.unmerge_cells('C4:C5')
    sheet.unmerge_cells('D4:D5')
    sheet.unmerge_cells('E4:E5')
    sheet.unmerge_cells('F4:G4')
    sheet.unmerge_cells('H4:I4')
    sheet.unmerge_cells('J4:K4')

    # 删除多余行
    sheet.delete_rows(1, 4)

    # 设置单元格内容
    sheet.cell(1, 1).value = "科目编号"
    sheet.cell(1, 2).value = "科目名称"
    sheet.cell(1, 3).value = "部门名称"
    sheet.cell(1, 4).value = "往来单位名称"
    sheet.cell(1, 5).value = "合同项目名称"

    # 插入列
    sheet.insert_cols(idx=4)
    sheet.insert_cols(idx=6)

    # 保存文件
    excel.save(file_path)

    # 数据排序
    df = pd.read_excel(file_path, sheet_name='Sheet')
    df.sort_values(by=['部门名称', '合同项目名称', '往来单位名称'], inplace=True)
    df.to_excel(file_path, sheet_name='Sheet', index=False)

    print(f"文件 {file_path} 处理完成")

# 处理多个Excel文件
def process_excel_files():
    # 获取指定路径下的所有Excel文件
    excel_files = glob.glob("*.xlsx")

    for file in excel_files:
        file_path = os.path.join(path, file)
        process_excel(file_path)

    print("所有文件处理完成")

# 执行处理函数
process_excel_files()

# 导入标准库
df2 = pd.read_excel(region_path, sheet_name='Sheet1')
df3 = pd.read_excel(GenCon_path, sheet_name='Sheet1')

# 合并写入Excel文件
writer = pd.ExcelWriter(output_path, mode='a', engine='openpyxl')
df2.to_excel(writer, sheet_name='区域标准库', index=False)
df3.to_excel(writer, sheet_name='Sheet1', index=False)
writer.save()
writer.close()

上述优化的主要变化有以下几点：

  1. 将文件处理函数 process_excel 提取出来，可以用于处理单个Excel文件。
  2. 添加函数 process_excel_files，用于处理多个Excel文件，通过遍历文件列表并调用 process_excel 函数来实现。
  3. 使用 glob 模块获取指定路径下的所有Excel文件。
  4. 将写入Excel文件的代码提取出来，使用 pd.ExcelWriter 对象实现合并写入。

这样代码更加清晰、模块化，也更容易进行扩展和维护。

最后，使用 pd.ExcelWriter 对象实现合并写入时，记得要调用 save 和 close 方法来保存并关闭文件。

希望这些优化方法对您有帮助！如果还有其他问题，请随时提问。


如果你已经解决了该问题, 非常希望你能够分享一下解决方案, 写成博客, 将相关链接放在评论区, 以帮助更多的人 ^-^