Python批量更新Excel土木工程施工标准

最新推荐文章于 2023-11-11 17:51:42 发布

Mochadrone

最新推荐文章于 2023-11-11 17:51:42 发布

阅读量520

点赞数 1

分类专栏：土木从业者文章标签： python 开发语言

本文链接：https://blog.csdn.net/Mochadrone/article/details/128827007

版权

土木从业者专栏收录该内容

1 篇文章 0 订阅

订阅专栏

Python批量更新Excel土木工程施工标准

前言

前言

作为一个土木狗，我一直在寻找通过编程解决施工、技术问题的应用场景。这次的任务的目的是更新一个长期没人维护的施工、设计标准目录，为后续开展技术质量培训和交底做参考资料。提示一下，本人编程水平确属小白，在不断学习进步中，欢迎大佬指教，但请轻喷，谢谢。
Alt

任务描述

提供一个包含9个sheet，分别对应不同标准：
sheet_names = ['建筑工程施工标准', '市政及公路工程标准', '石化工程施工标准', '安全施工标准', '建筑节能及绿色施工标准', '管理标准', '法律法规', '施工图集']
每个sheet包含如下列：
col_names = ['序号', '标准代号和编号', '标准名称', '实施日期', '被代替标准号', '备注'。
在这里插入图片描述
的.xls文件。任务是要将标准代号和编号一列更新至现行或即将实施的最新标准编号，并同步更新实施日期和被替代编号。

任务思路

工标网（http://www.csres.com/）可根据关键词检索相关规范，并包含所需信息。根据规范编号命名逻辑：组织号（GB， GB/T等）+标准号+年代号，可采取检索组织号+标准号的方式定位相关规范，再结合年代号及状态确定最新标准。例：搜索房屋建筑制图统一标准，即可通过搜索GBT50001得到不同年代号的各项规范表格。
将网页中的表格信息存储到本地进行处理。
对本地Excel表格进行修改。.xls文件不支持openpyxl等命令，需要修改后缀为.xlsx文件。此外，添加两列：‘最新标准代号和编号’及‘是否为最新版本’。
将步骤2中的表格信息传入本地Excel表格列3。
判断列2与列3是否有更新，写入列4。
进行复核，美化表格。

Part0：环境和库说明

本文使用Anaconda 自带Python 3.9 环境。导入库如下：

import os
import pandas as pd
import re
import openpyxl
import importlib
import sys

Part1：读取网页表格数据，并做数据处理。

# Part1 Function：读取网页表格数据，输出成df格式，并做数据处理
# Helper Function 1:处理url
def get_url(key):
    # 根据关键词key 生成工标网search网址
	# key = re.sub('[\u4e00-\u9fa5]', '', p1)
    url = 'http://www.csres.com/s.jsp?keyword='+key
    return url
# Helper Function 2:
def url2df(url):
    # 根据url创建包含所需内容的df
    if os.path.exists('temp1.csv'):
        os.remove('temp1.csv')
    df = pd.read_html(url)[0]
    df.to_csv('temp1.csv', mode='a', encoding='utf_8_sig', header = 1, index = 0)
    df1 = pd.read_csv('temp1.csv')
    # 处理网页上无用的信息
    df1.drop(index=[df1.shape[0]-5,
                    df1.shape[0]-4,
                    df1.shape[0]-3,
                    df1.shape[0]-2,
                    df1.shape[0]-1],
        inplace = True)
    return df1
    
def df_sid(df):
    # 通过状态寻找正确的标准编号:sid
    for item in (df['状态'] == '即将实施'):
        if item is True:
            return df.loc[df['状态'] == '即将实施','标准编号'].iloc[0]
    for item_2 in (df['状态'] == '现行'):
        if item_2 is True:
            return df.loc[df['状态'] == '现行','标准编号'].iloc[0]
            
def latestsid(key):
    # 根据关键词key获取最新sid
    print('开始查询'+str(key))
    sid = df_sid(url2df(get_url(key)))
    return sid

Part2：根据本地Excel文件提取key值

# Part2 Function：根据本地excel 提取key值，根据所在行数将返回的sid列入对应行后置列
def get_keydict(file, sheet_name):
    df = pd.read_excel(file, index_col=None, header = 1, sheet_name=sheet_name)
    df.dropna(subset='标准代号和编号', inplace = True)
    def parse(item):
        pattern = r',|-|:|：|—'
        result_list = re.split(pattern, item)
        return result_list
    # 通过dict存储原始标准号
    keydict = dict()
    ori_key = list()
    for item in df.iloc[:,1]:
        ori_key.append(item)
        tmp = parse(item)
        tmp2 = tmp[0].strip()
        key = tmp2.replace(' ','')
        keydict[item] = key
    return keydict, ori_key

Part3：从工标网上自动搜索最新标准，并更新入原Excel。

# Main function
# 通过Excel表格读取工程建设技术标准名称，从工标网查询适用该条目的最新标准名称，并更新入原Excel表格。
file = r"xxxx.xlsx"
sheet_name = sheet_names[0] #0-8 每个分别运行一边
wb = openpyxl.load_workbook(file)
sheet = wb[sheet_name]
keydict, ori_key = get_keydict(file, sheet_name)
start_row = 2 #起始两行为表头，跳过即可
while sheet.cell(start_row, 3).value != None:
    start_row += 1
print('本次输入从第'+str(start_row)+'行开始')
for row in sheet.iter_rows(min_row = start_row, 
                           min_col = 2, 
                           max_col = 2, 
#                            max_row = 100):
                           max_row = len(sheet['B'])-1):
    if row[0].value in ori_key:
        i = row[0].row
#         i = ori_key.index(row[0].value)
        sheet.cell(row=i, column=3, value = latestsid(keydict[row[0].value]))
        print('已完成第'+str(i)+'行数据查询，查询结果为'+str(sheet.cell(i,3).value))
wb.save('xxxx.xlsx')
print('已成功保存为xxxx.xlsx')

输出结果如下：
在这里插入图片描述
执行过程中有如下问题，暂时还未通过修改代码本体来解决，后续会想办法改进：

502 Bad Gateway：网络响应时间过长，初步判断是因为没有设置超时时间。保存后重新启动程序就能解决。
“状态”：打开csv文件可以看到来自网站的提示，每天搜索数量达到上限。我认为可以通过每查询100条自动切换proxy代理服务器的方式来解决，但是不会做。手动改全局连接的服务器，能跳过这个限制。
[-4],[-3],[-2],[-1]：网站上没有对应的标准。这种可以通过跳过后，手动百度空值对应的条目来进行，但是数量不多，程序中断后手动去查了，没改代码。
重启程序后没有从最新的进度向下进行：没有现行或即将实施的标准导致有空值，一般是整个标准号进行了更改。从对应的行数找到空值，手动搜索后填上即可。

Part4：对比新旧两段标准号

# Part 4: 根据查询结果年份代号自动输入初步筛选是否相同
def get_samedict(file, sheet_name):
    df = pd.read_excel(file, index_col=None, header = 1, sheet_name=sheet_name)
    df.dropna(subset='标准代号和编号', inplace = True)
    def parse(item):
        pattern = r',|-|:|：|—'
        result_list = re.split(pattern, item)
        return result_list
    # 通过对比同一行的年份号是否相同来初步筛选。
    for i in enumerate(df.iloc[:,1]):
        if parse(df.iloc[i[0],1])[-1] != parse(df.iloc[i[0],2])[-1]:
            df.iloc[i[0],3] = 0
        else:
            df.iloc[i[0],3] = 1
    samedict = dict()
    for index, row in df.iterrows():
        samedict[row[1]] = row[3]
    return samedict

file = r"xxxx.xlsx"
sheet_name = sheet_names[0] #0-8 每个分别运行一边
wb = openpyxl.load_workbook(file)
sheet = wb[sheet_name]
samedict = get_samedict(file,sheet_name)
start_row = 3
print('本次输入从第'+str(start_row)+'行开始')
for row in sheet.iter_rows(min_row = start_row, 
                           min_col = 2, 
                           max_col = 2, 
                           max_row = len(sheet['B'])-1):
    if row[0].value != None:
        sheet.cell(row = row[0].row, column = 4, value = samedict[row[0].value])
        print('已完成'+str(row[0].row)+'行数据对比，录入结果为'+str(sheet.cell(row[0].row,4).value))
wb.save('xxxx.xlsx')
print('已成功保存为xxxx.xlsx')