基于Excel程序实现检测标准自动化查新.pdf

2023 年 8 月 25 日第 7 卷 第 16 期现代信息科技Modern Information TechnologyAug.2023 Vol.7 No.161041042023.082023.08收稿日期：2023-03-13基于 Excel 程序实现检测标准自动化查新王群，陈艳芳（竹山县公共检验检测中心，湖北 十堰 442200）摘 要：食品检验标准数量多，更新快，标准查新工作较难开展。为了高效开展标准查新工作，特开发一种基于 Excel VBA 平台的自动化程序。该程序通过自动分析网页信息的方式开展标准查新工作。利用该文方法一分钟最高可以查新 15 个标准，程序使用门槛较低，自动化程度较高，能够帮助检验机构对标准信息进行分析和加工，提高了工作效率。关键词：食品检验；标准查新；自动化查新；VBA；Selenium中图分类号：TP311.5 文献标识码：A 文章编号：2096-4706（2023）16-0104-05Implementing Automated Novelty Search for Inspection Standards Based on Excel ProgramsWANG Qun,CHEN Yanfang(Zhushan County Public Inspection and Testing Center,Shiyan 442200,China)Abstract:There are a large number of food inspection standards and they are updated quickly,making it difficult to carry out standard novelty search work.In order to efficiently carry out standard novelty search work,an automated program based on the Excel VBA platform has been developed.This program conducts standard novelty search work by automatically analyzing web page information.By using the method in this paper,up to 15 new standards can be found in one minute.The program has a low threshold for use and a high degree of automation,which can help inspection institutions analyze and process standard information and improve work efficiency.Keywords:food inspection;standard novelty search;automated novelty search;VBA;Selenium0 引 言标准是检验检测机构衡量产品质量的重要工具，检测机构工作的准确性、公正性需要标准的支持，确保标准的时效性是检验检测工作最重要的环节之一1。标准查新是检验机构比较重要的工作之一，检验检测机构在使用国家，地方或者行业标准时，要认真地核实标准，必须要保证标准时现行有效的状态。标准查新这是行业的法律法规以及认证认可标准的强制要求。CNAS-CL01检测和校准实验室能力认可准则7.2.1.3 要求：“对于标准方法，应定期跟踪标准的制修订情况，及时采用最新版本标准”。CMA 评审准则以及国家标准均要求实验室标准为最新有效版本2，3。并且在检验检测机构资质扩项或者更换到期的资质证书工作中，专家组所需要的重要材料之一就是机构的资质标准的查新报告。标准查新是检验检测机构自身技术能力提升的重要手段。一些标准在更新过程中，是朝着更精密，DOI:10.19850/ki.2096-4706.2023.16.023高效和统一的方向去做具体改进。检验检测机构通过标准查新，能够迅速了解最新的检验检测方法，能更加科学严谨地开展工作。例如 GB 5009.34-2022食品安全国家标准 食品中二氧化硫的测定标准和它的 2016 版对比，首先是在原先的方法基础上，不仅进了蒸馏法，还增加了比色法和离子色谱法两种检验方法。本次标准变更将食品行业中的二氧化硫测定与药典对药材的二氧化硫测定方法做了基本统一，这避免了一些药食同源的产品，测量方法不能统一的弊病。与旧方法相比，新方法全程通氮气保护，有效降低了平行实验的相对偏差，在实验精密程度方面，也有一定提升。标准查新是检验检测机构有效开展质量工作的根本前提。通过标准查新，能够更好地把握最新的产品质量要求，避免了出具错误的检验报告而导致粮食浪费，以及一些不必要的法律纠纷。如 GB 27622022 食品安全国家标准 食品中污染物限量的标准中，由于稻谷在碾磨加工成大米的过程中可以减少约 50%的无机砷，稻米中无机砷限量设置与旧版本相比稻谷和糙米中无机砷限量由 0.2 mg/kg现代信息科技8月下16期.indd 104现代信息科技8月下16期.indd 1042023/8/15 17:38:182023/8/15 17:38:181051052023.082023.08第 16 期调整为 0.35 mg/kg。食品行业标准查新目前主要有两种手段，一种是手工查新，一种是委外机构或者部门进行查新，这两种方法都有各自优缺点。手工查新主要是检验检测的标准管理相关人员，依次对自身资质证书涉及的标准，在互联网上的一些网站，如食品安全国家标准数据检索平台（https:/:8086/db）、食安通（http:/ Python的方法，利用计算机技术去实现标准查新4。但是这种方法，需要标准查新网站具有对应的 API（应用程序编程接口）来对接，也需要去搭建 Python 平台去实现代码功能，其方法实施门槛较高，难以普及。对于食品行业检验检测机构来说，存在较大的标准自动化查新的需求。但迄今为止，少见文献报道如何利用计算机和互联网，自动开展食品行业领域的标准查新工作。1 基于 Excel 的标准查新Excel 是标准管理者常用的办公软件之一，检验检测机构的资质附表，一般就是保存在 Excel 表格中。并且，Excel 自带的 VBA 语言易于学习掌握，学习和使用的成本较低，用户可以容易地将日常工作转换为 VBA 程序代码，使工作自动化5。Selenium 是 Jason Huggins 于 2004 年开发的，是一种开源免费的 Web 应用程序自动化测试的软件6。Selenium Basic 是 Selenium 在 VBA 平台上的开发的软件库，结合对应浏览器的 WebDriver 使用，可以实现以较低学习成本实现 Web 应用程序自动化。本文在 Excel 的 VBA 平台上，编写出自动检索并识别标准信息的 Web 应用程序代码，利用海量的互联网标准数据资源，来实现自动开展标准查新工作的目的。1.1 前期准备软件要求：电脑需要安装EXCEL、Selenium Basic软件以及 Microsoft.NET Framework 3.5 及以上，并将浏览器版本对应的 WebDriver 复制到 Selenium 安装目录。以上 Selenium Basic 和 WebDriver 均可以在互联网上免费获取，WebDriver 版本要和用户电脑安装的浏览器，如 Chrome,Ms Edge,IE 浏览器等版本一致。本文以 Win 10 操作系统，Microsoft Excel 2019 Student and Home,Microsoft Edge 浏览器版本“110.0.1587.57（正式版本）（64 位）”以及对应版本的 WebDriver,NET Framework 4.0 为例。标准准备：标准查新前，须对正在使用的标准进行系统梳理，将需要查询的标准进行整理，如图1 所示将整理好的标准号填入表格第一列。1.2 程序代码在填好对应的标准代码后，右击左下角 Sheet1查看代码，进入 VBA 代码编辑页面。如图 1 所示，点击工具后点击引用按钮，将 Selenium Type Library勾选并确认，再点击插入按钮，插入模块 1。为了便于使用和修改程序，本文代码单引号后附带的有对应的注释。如图 2 所示，双击左侧工程栏模块 1，在其内输入以下代码：Sub BZCX()Dim str,str0,str1,str2 As StringDim i,j,k As IntegerApplication.ScreenUpdating=Falsestr0=http:/ str0Dim driver As New EdgeDriverFor i=1 To ActiveSheet.A65536.End(xlUp).Row 遍历第一列所有的标准号str=Cells(i,1)str=str0&str 将标准号和食品伙伴网搜索栏网址通过连字符连接，为网页检索做准备driver.Get str 打开对应的检索网页driver.Wait 2500 设置检索间隔，防止时间耗尽On Error Resume Nextstr1=driver.FindElementByXPath(/html/body/div6/div2/div2/div2/ul/div3/span1/a/img).Attribute(src)将打开网页的特征元素的文件名赋王群，等：基于 Excel 程序实现检测标准自动化查新现代信息科技8月下16期.indd 105现代信息科技8月下16期.indd 1052023/8/15 17:38:182023/8/15 17:38:18106106第 16 期现代信息科技2023.082023.08值给 str1str2=driver.FindElementByXPath(/html/body/div6/div2/div2/div2/ul/div3/span1/a).Text 将打开网页的特征文本内容赋值给 str2If str1=http:/ Then 进行判断看是否为现行有效，然后将信息赋值给对应的第二列和第三列单元格Cells(i,2)=现行有效 ElseCells(i,2)=需要确认 End IfCells(i,3)=str2NextEnd Sub图 2 查新结果界面1.3 查新数据分析和保存在完成上述操作后，按 F5 运行程序即可开始标准自动查新。程序运行会在 Excel 界面上会出现一个浏览器界面，该界面即计算机自动检索页面。程序根据查新标准的数量，需要一定时间等待程序完成查新工作。完成查新后，查新结果如图 2 所示，其第一列是预先整理填好的待查新的标准号，表格的第二列是查新结果，如果标准不是现行有效，则会右侧标注“需要确认”。标准管理员可以直接筛选出需要确认的标准，并保存表格结果，提交给机构的技术负责人。技术负责人根据结果，将需要确认的标准进行查看，根据标准变化情况，再开展标准变更工作。程序也会将标准的全部名称抓取放在表格第三列，方便标准管理员管理标准，可以在后续的资质项目扩增或者资质证书更换的工作中使用。1.4 程序原理分析本文涉及的程序，其原理是 VBA 代码自动遍历标准号，在食品伙伴网进行检索，并通过识别网页的特征标识，来判断该标准是否为现行有效。程序的自动识别和判定，是建立在对网页信息的前期研究基础上的。以 Edge 浏览器为例，如图 3 所示在程序中的网页中 GB/T 188102002 标准进行检索，利用浏览器的开发人员工具，选择搜索结果标准名称最左的“现行有效”这个元素进行检查，发现其对应的网页代码：“=$0”。同样的操作如图 4 所示，对 NY/T 8322004 这个图 1 引用库文件现代信息科技8月下16期.indd 106现代信息科技8月下16期.indd 1062023/8/15 17:38:182023/8/15 17:38:181071072023.082023.08第 16 期标准进行检索，对同样位置的“即将废止”元素检查，其对应的网页代码是“=$0”。图 3 GB/T 18810-2002 网页分析界面图 4 NY/T 832-2004 网页界面在网页图片元素中，标签的 src 属性的值是图像文件的 URL，也就是引用该图像的文件的绝对路径或相对路径。对比两种标准对应结果的元素所对应的 img src属性，可以发现其与标准有效性有一定规律，现行有效的标准 img src 路径为：“http:/ img src 文件名不是该路径。可以将 src 路径里的网站复制到浏览器进行查看，可以看到其为写有“现行有效”四个字的图片。因此可以通过分析对应位置元素的 img src 路径，来对标准的有效性进行判断。在网页中，XPath 是一种表达式语言。它使用路径表达式来选取 XML 文档中的节点或节点集。通过它的定位作用，可以获取网页中的指定元素和指定节点的值，提取到我们想要的结果。为了实现该目的，本文通过浏览器开发人员工具，获取并复制写有“现行有效”四个字的图片元素完整的 XPath 为：“/html/body/div6/div2/div2/div2/ul/div3/span1/a/img”，然后通过 driver.FindElementByXPath语句，对指定 XPath 的元素的 srcs 属性进行抓取，识别和判断。完整代码为：“driver.FindElementByXPath(/html/body/王群，等：基于 Excel 程序实现检测标准自动化查新现代信息科技8月下16期.indd 107现代信息科技8月下16期.indd 1072023/8/15 17:38:182023/8/15 17:38:18108108第 16 期现代信息科技2023.082023.08div6/div2/div2/div2/ul/div3/span1/a/img).Attribute(src)”。同样的原理，本文也分析了查询结果的“标准名称”一列的元素，其对应代码为 GB/T18810-2002 糙米，代码中“ ”在超文本标记语言中是指输出一个不换行空格。标准名称一列元素均为 Text 纯文本字段，内容即是标准的全程。可以利用程序对文本进行抓取并赋值到对应单元格，获取标准全称。利用浏览器开发人员工具，获取其完整 Xpath 为/html/body/div6/div2/div2/div2/ul/div3/span1/a，具体实现，也是通过和上述一致的 driver.FindElementByXPath 语句来实现。完整代码为“driver.FindElementByXPath(/html/body/div6/div2/div2/div2/ul/div3/span1/a).Text”。事实上，不仅仅是标准的有效性的信息以及标准号码所对应的全名，对于标准的发布日期和实施日期等信息，也可以采取类似的方法，获取对应元素的 Xpath，利用 driver.FindElementByXPath 语句，返回对应元素的各种属性的值，通过程序自动化完成信息的识别，判断和保存。为了防止标准查新网站的资源耗尽，本程序设置了浏览器的查询间隔，在实际使用过程中，不建议为了减少查新时间，缩短本文所述程序的设定值。由于此原因，本文所述的方法，理论上 1 分钟最高可以实现 15 个标准的查新工作。也就是说，本例实际完成 150 个标准的查新工作，仅需要 10 余分钟就可以完成。2 结 论本文对食品行业检验检测机构标准查新工作，程序烦琐，效率低下的问题，开发了一种采用Excel 实现机构标准的自动化查新工作的方法。本文所述方法，使用门槛较低，程序自动化程度较高，能够及时准确获取标准的有效信息，利于操作者对信息进行分析和加工，提高标准查新的工作效率。可以很好地填补食品行业检验检测机构，利用计算机和互联网实现标准自动查新的这一空白。不仅是标准查新工作，对于食品检验检测行业从业人员来说，除了基本的检验检测业务之外，还存在大量的如管理体系运行、实验室药品管理、报告管理等繁杂琐碎的工作。很多工作人工开展，不仅容易出错，还需要层层审核，效率低下，如何高效准确开展工作，是检验检测机构亟须解决的问题之一。检验检测机构利用好计算机和互联网来处理这些工作，提高工作效率和质量，将是一种较好的解决办法。参考文献：1 周建团.标准对检验检测机构的重要性 J.发展，2017（6）：61-62.2 周洁，华添翼，朱洁，等.浅谈检验检测机构方法变更中的方法验证 J.中国检验检测，2020，28（6）：89-90+88.3 陈薇，桑彤，陆敏仪.标准查新和标准确认体系的建立对检验/检测机构的重要性J.中国药事，2015，29（4）：369-373.4 李骏，张鸽，纪海婷，等.Python 在标准查新中的应用 J.科技创新与应用，2020（12）：184-186.5 杜菁，武文芳，周震数据库技术与应用新概念教程：第 2 版 M.北京：中国铁道出版社，2016.6 王旭丽.基于 Selenium 的自动化程序设计与实现 J.现代信息科技，2022，6（22）：12-15+21.作者简介：王群（1992），男，汉族，湖北竹山人，硕士研究生，研究方向：食品安全；通讯作者：陈艳芳（1980），女，汉族，湖北竹山人，工程师，本科，研究方向：食品安全。4 结 论本文通过 Python 语言结合 Requests 模块实现了简单的网页中源代码文本和图片数据的获取，经试验验证，本文使用的实现思路、步骤和代码编写均简单易学，实现效果较好。在“互联网+”和大数据时代背景下，能够为学习爬虫框架、获取大量网络数据并进行数据分析、深入研究大数据和人工智能技术奠定数据基础。参考文献：1 邓晓璐，姚松.基于 Scrapy 的新浪微博数据爬虫研究 J.现代信息科技，2023，7（3）：44-47.2 曾水新，黄日胜.基于 Scrapy 的房产信息爬取系统设计 J.电脑编程技巧与维护，2022（12）：26-28.3 胡学军，李嘉诚.基于 Scrapy-Redis 的分布式爬取当当网图书数据 J.软件工程，2022，25（10）：8-11.4 魏海昱，林伟鸿，贺超波.基于 Scrapy 的食品安全舆情数据爬取与分析 J.现代计算机，2022，28（14）：49-54+95.5 伍高巍，刘杰，葛晓龙，等.基于 Scrapy 的研招网硕士目录爬虫的设计与实现 J.电脑知识与技术，2022，17（18）：53-55.6 杨健，陈伟.基于 Python 的三种网络爬虫技术研究 J.软件工程，2023，26（2）：24-27+19.作者简介：姜庆玲（1991.05），女，汉族，湖北孝感人，教师，硕士，研究方向：计算机基础应用，数据分析与数据挖掘。（上接103页）现代信息科技8月下16期.indd 108现代信息科技8月下16期.indd 1082023/8/15 17:38:192023/8/15 17:38:19