import requestsfrom bs4 import BeautifulSoupimport pandas as pdfrom urllib.parse import quoteimport osimport timeimport randomdef search_standard(text):print(f查询标准的检索关键词{text})url_encoded_text quote(text, encodingGBK) url fhttp://www.csres.com/s.jsp?keyword{url_encoded_text}pageSize100000pageNum1 print(f工标网的查询网址{url}) headers { User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36 } try: response requests.get(url, headersheaders, timeout15) response.raise_for_status() response.encoding GBK html_content response.text except requests.RequestException as e: print(f请求失败: {e}) return None soup BeautifulSoup(html_content, html.parser) tr_tags soup.find_all(tr, attrs{bgcolor: #FFFFFF}) if not tr_tags: print(未找到匹配的数据行) return None data_list [] column_names [标准编号, 标准名称, 发布部门, 实施日期, 状态] for tr_tag in tr_tags: td_tags tr_tag.find_all(td) if len(td_tags) 5: data [ td_tags[0].text.strip(), td_tags[1].text.strip(), td_tags[2].text.strip(), td_tags[3].text.strip(), td_tags[4].text.strip() ] data_list.append(data) else: print(f跳过不完整的数据行仅找到 {len(td_tags)} 个单元格) if not data_list: print(未提取到有效数据) return None df pd.DataFrame(data_list, columnscolumn_names) try: output_dir output_工标网 os.makedirs(output_dir, exist_okTrue) df.to_csv(os.path.join(output_dir, data_工标网.csv), modea, headerFalse, indexFalse, encodingutf-8-sig) filename text.replace(/, _).replace(., _).replace( , _) df.to_csv(os.path.join(output_dir, f{filename}.csv), indexFalse, encodingutf-8-sig) print(f数据已保存到文件{filename}.csv) except Exception as e: print(f保存文件失败: {e}) return None pd.set_option(display.max_columns, None) pd.set_option(display.max_rows, None) pd.set_option(display.width, None) print(df) return dfdef get_standards_from_excel(file_path, sheet_name, col_indexNone):try:# 尝试读取 Excel 文件# 对于 .xls 文件需要 xlrd 库对于 .xlsx 文件需要 openpyxl 库try:df pd.read_excel(file_path, sheet_namesheet_name, engine‘xlrd’)except Exception as e_xlrd:# 如果 xlrd 失败可能未安装或版本过高尝试 openpyxltry:df pd.read_excel(file_path, sheet_namesheet_name, engine‘openpyxl’)except Exception as e_openpyxl:print(f读取 Excel 文件失败 (xlrd): {e_xlrd}“)print(f读取 Excel 文件失败 (openpyxl): {e_openpyxl}”)return Noneprint(f成功读取文件: {file_path}) print(f工作表: {sheet_name}) print(f数据总行数: {len(df)}) print(f数据列数: {len(df.columns)}) if col_index is None: print(\n请选择要查询的标准号所在的列输入列号从1开始) for i, col in enumerate(df.columns): print(f {i 1}. {col}) col_input input(请输入列号: ).strip() try: col_index int(col_input) - 1 except ValueError: print(无效的列号输入默认使用第1列。) col_index 0 if col_index 0 or col_index len(df.columns): print(f错误列号 {col_index 1} 超出范围。) return None col_name df.columns[col_index] print(f\n将从列 {col_name} (第 {col_index 1} 列) 提取标准号...) # 提取该列数据去除空值和NaN standards df[col_name].dropna().astype(str).str.strip() # 过滤掉空字符串 standards standards[standards ! ] standards_list standards.tolist() print(f共提取到 {len(standards_list)} 个标准号。) return standards_list except Exception as e: print(f处理 Excel 文件时出错: {e}) return Noneifname “main”:# Excel 文件路径input_file rC:\Users\Lenovo\PycharmProjects\标准查询工标网\input_工标网\橡胶及软管检测项目表 检测能力.xlsx# 工作表名称sheet_name “Sheet2”standards_list get_standards_from_excel(input_file, sheet_name) if standards_list is None or len(standards_list) 0: print(未能获取到要查询的标准列表程序退出。) else: print(f\n准备查询 {len(standards_list)} 个标准...) for idx, text in enumerate(standards_list, 1): print(f\n{ * 60}) print(f[{idx}/{len(standards_list)}] 正在查询标准{text}) print(f{ * 60}) try: search_standard(text) except Exception as e: print(f查询 {text} 时发生异常: {e}) if idx len(standards_list): wait_time random.uniform(3, 8) print(f等待 {wait_time:.1f} 秒后继续下一个查询...) time.sleep(wait_time) print(\n * 60) print(所有标准查询完成)