当前位置:论文网 > 论文宝库 > 信息科技类 > 软件开发论文 > 正文

基于ExcelVBA的区域试验数据批量提取汇总软件的开发与应用

来源:UC论文网2020-10-19 09:02

摘要:

  摘要:为了快速准确完成区域试验中同一品种在不同试验点数据的汇总整理,笔者利用ExcelVBA编写了此软件。软件通过循环批量打开源数据Word文件提取其中的多个表格,并将相同表格内容汇总放入指定Excel工作表中。通过计算,实现了源数据文件的后台批量打开,实现了每个文件中相同表格数据的提取,实现了相同表格中同一品种在不同试点数据的汇总。在提取表格每行数据时,通过计算每行数据在汇总表中的行号,实现...

  摘要:为了快速准确完成区域试验中同一品种在不同试验点数据的汇总整理,笔者利用ExcelVBA编写了此软件。软件通过循环批量打开源数据Word文件提取其中的多个表格,并将相同表格内容汇总放入指定Excel工作表中。通过计算,实现了源数据文件的后台批量打开,实现了每个文件中相同表格数据的提取,实现了相同表格中同一品种在不同试点数据的汇总。在提取表格每行数据时,通过计算每行数据在汇总表中的行号,实现了表格数据一次性提取拆分,提高了汇总效率,为后续的数据处理提供了基础。软件操作简单,运行速度快,汇总效率高。本软件能够实现区域试验数据以及具有类似特点数据的快速高效汇总,在Word和Excel数据交互方面具有较大的应用推广价值。


  关键词:区域试验;EXCELVBA;软件开发;Word表格数据;数据汇总


  中图分类号:S565.1文献标志码:A论文编号:cjas20190800162


  0引言


  農作物品种区域试验是在同一生态类型区内多个有代表性的试点同时实施的一组对比试验,是准确选择优良品种的重要渠道和宣传、示范与展示新品种的重要平台[1-3],其目的是鉴定新品种的特征特性、评价其利用价值和适宜推广区域[4,5]。区域试验对品种的正确评价,很大程度上有赖于对品种在不同试点的差异表现做出准确的鉴别,通过新品种在各试点的生物学性状、产量、品质性状等因素的调查来鉴定品种的丰产性、抗逆性、适应性等品质,为品种审定提供科学依据[6-8]。


  区域试验涉及众多生态组别、大量品种、试验点以及调查记载项目[9-10],每个生态组中每个试点提交的数据均为格式相同的Word文件,每个Word文件均有多个包含不同调查记载项目的表格,每个表格行数据为具有相同顺序的品种,列为调查记载项目。因此区域试验会涉及大量数据[11]。区域试验数据汇总分析需要将同一生态组内各试验点数据,分别按指定的表格顺序将相同品种数据按指定的试验点顺序进行汇总[12-13]。如何高效完成对这些性状数据的汇集、整理、分析、存储,减少错误率,是农作物区域试验过程中的关键环节,对提高农作物品种区域试验的工作效率极为重要。


  近年来计算机技术发展迅速,各种面向用户需求的计算机软件应运而生[14-17],但并没有能满足区域试验汇总要求的软件。MicrosoftExcel是微软公司推出的一款功能强大、使用方便的电子表格式数据综合管理与分析系统软件[18-20],其VBA技术是VBA程序语言对EXCEL的扩展,利用VBA语言进行编程不仅极大的增强了其数据的处理能力[21-23],且较其他开发工具可更为快捷地形成完整的数据处理软件[20-22],能够批量处理数据,轻松完成大量重复工作,满足不同方面的数据处理需求[24-26]。笔者为满足区域试验汇总要求,编写了此软件。本研究以大豆区域试验数据提取为例,介绍该软件的设计和应用。


  1软件功能及设计思路


  国家农作物品种区域试验涉及大量品种、试点和调查记载项目,不同的调查记载项目形成不同的表格,每个表格均以品种为行内容、调查记载项目为列内容。将包含多个格式相同表格的模板文件发给同一生态区内的多个试验点,由各试验点填写试验数据。区域试验汇总即是对每个表格中相同品种在不同试点的数据进行汇集、计算。以其中一个表格数据整理为例,图1是多个试点的表格数据,利用软件汇总后形成表格数据(图2)。其他表格汇总类似。


  此软件主要实现了以下功能:(1)批量读取各试验点提交数据;(2)按指定表格顺序批量提取每个文件的多个表格数据;(3)按品种顺序批量提取每个表格的性状数据;(4)将提取数据按照表格顺序导入到不同的Excel工作表中;(5)每个Excel工作表中将不同试验点数据按照品种顺序汇总排列。软件运算的流程图如图3。


  2关键技术原理


  此软件基于ExcelVBA,因此要求使用者的Office软件具备VisualBasic组件。由于软件通过VBA循环遍历所有Word文件、所有表格和表格所有行,并依据表格在文件中的顺序进行定位,因此设计该软件的前提是所有Word文件包含的表格数、对应表格位置相同,每个表格格式相同,且除标题外无合并单元格。区域试验记载文件正具有此特点,文件由主持单位确定格式后发至各试验点,试验点负责人将数据整理完成后发到主持单位进行汇总。本软件的开发主要包括以下几个关键技术。


  2.1Word对象引用及版本兼容性


  由于提取的源数据在Word文件中,因此在Excel中利用VBA提取Word中的表格数据,首先需在Excel中建立Word对象引用,通过隐藏Word对象可实现在“不打开”Word文件的情况下提取数据,通过循环可实现每个文件的批量提取。通过FileDialog对象的Filter属性可实现不同Word版本的兼容,不同的Word版本文件均可以提取。软件会调用文件打开框选取文件,因此要求所有待读取的Word文件位于同一文件夹中。核心代码见图4。


  2.2Word表格的定位、数据读取与存放


  Word文件中存在多个表格,通过InputBox设计实现了用户交互,让用户可输入要提取数据的表格以及表格提取的顺序。用户输入表格顺序号,以空格分开,然后通过Split函数生成包含表格顺序信息的数组,作为表格循环的依据。如果用户输入“0”则表示按表格顺序提取所有表格。核心代码见图5。


  2.3文件名称与顺序的提取


  每个Word文件来自不同的试验点,在提取表格数据的同时按照既定的顺序获取试验点信息也至关重要。试验点信息可通过表格内容获取,即增加包含试点信息的表格,也可以通过文件名称获得,文件顺序则通过文件名称中的编号得到。此软件通过文件名称提取试验点信息,核心代码见图6。


  2.4表格数据读取


  2.4.1标题行的提取软件仅适用于标题在行首的表格,不适于标题在左侧列首的表格。对于两行或多行标题的表格,由于存在合并单元格,因此不适宜软件提取。解决办法是将第一个文件标题改为单行,从该文件提取标题。若表格标题为单行则不需要修改第一个文件的标题。核心代码见图7。


  2.4.2数据行的提取数据行至除标题行之外的行,在区域试验中,数据行数即为试验品种数。数据行的起始可通过计算表格总行数(r=.Rows.Count)与品种数(VarNbr)计算得到,然后通过数据行和列的循环逐一提取每个数据,将提取的数据装入数组后通过数组将数据导入Excel工作表中,实现了Word与Excel的数据交互。在数据交互中,将Word文件中不同的行数据(品种)根据固定间隔导入到Excel中,间隔大小等于选择的Word文件(或试点)数。在Excel中,每个数据行的存放位置(ROrder)由数据行(品种数,VarNbr)、当前行(i)、文件数(试点数,DocNow)、当前文件序号(ts)等计算得到。核心代码见图8。


  3结论与讨论


  3.1软件优点分析


  (1)软件基于EXCEL操作,界面熟悉,使用者容易接受,无需专门的技术培训;(2)软件实现了批量数据文件、批量表格提取、批量数据行的一键式操作,使用简单,准确性高;(3)软件运行速度快、提取效率高,提取数据不需打开Word文件,且所有Word版本(.doc和.docx)均可提取;(4)软件开放性好,可直接用于其他作物区域试验、具有类似特点的其他类型数据的快速处理。(5)软件将每个表格数据一次性提取后通过计算行号一次性导出所有数据至汇总表中,避免了对Word对象的反复引用,相较于逐行数据提取后依次导入汇总表,显著提高了运行速度和效率。


  3.2系统缺点与讨论


  (1)软件的运行需要用户的Office软件具有VBA组件;(2)软件对于表格的定位基于表格在文件中的顺序,因此要求不同文件中同一表格的位置相同。虽然软件可以实现对文件中所有表格按顺序全部提取,但一些无关表格或虚框表格的存在会降低运行效率,影响数据提取的准确性。可通过对表格标题内容的模糊判断进行表格定位并提取數据,解决这一局限性;(3)软件要求数据行无合并单元格,因为合并单元格会造成数据重复提取,降低数据准确性。可在汇总前对合并单元格进行检查、提醒或通过对单元格数据拆分实现对合并单元格的容错性。(4)限于Office设置与操作系统不同,使用者需具备一定的VBA知识,以方便解决代码运行过程中出现的问题及对代码程序的个性化自定义。


  软件通过ExcelVBA实现了Word与Excel的数据交互,实现了在不打开Word的情况下快速批量提取Word中具有相同格式的指定表格数据,并将表格每行数据按照文件顺序排列,实现了数据的快速自动汇总,极大地提高了数据汇总效率,降低了错误率。此软件不仅仅适用于大豆品种的区域试验数据汇总,而且适于所有具有类似特点的数据处理,在Word和Excel数据交互方面具有较大推广应用价值。

核心期刊推荐