我全新开发了一套 Access 通用 Excel 导入工具，现在开源了

摘要我全新开发了一套 Access 通用 Excel 导入工具采用分块读取、暂存校验和批量追加支持错误明细、批次历史、事务回滚和中英文界面现已开源。access开发|access培训|access框架|请添加edonsoft。Hi大家好这次给大家分享一个刚做好的开源项目Access 通用 Excel 批量导入工具accessImportExcel。先说一下这个项目的来历。以前红尘如烟大师写过一套「通用 Excel 数据导入模块」。老版代码里标注的日期是 2011 年 4 月 5 日已经是很多年前的作品了。它的思路很好先选 Access 目标表再选 Excel 文件和工作表把 Excel 列与数据库字段对应起来然后导入。不同业务表可以共用一套界面不用每来一张表就重新写一个导入按钮。不过老版发布已经很多年了面对现在的数据量和使用要求确实有一些地方需要重新考虑。比如数据量大了之后导入等待会变长出现问题时已经写进去多少、哪些行没有进去也需要说得更清楚。这里要明确说明这个开源项目是全部重新开发的不是在红尘如烟大师原有的代码基础上调整或修补。我参考的是原作「选择目标表、选择 Excel、确认字段映射再导入」的使用思路。新版的导入核心、配套窗体以及读取、校验、写入和失败处理的代码都是重新设计、重新编写的。两者解决的是同一类需求但不是同一套代码的局部修改版。项目地址GitHub - miaowei2/accessImportExcel: Access 通用 Excel 批量导入 - 纯 VBA 一键生成导入窗体,支持字段映射、校验与事务追加 | Universal Excel batch import for Microsoft Access, pure VBA with auto-generated forms, field mapping, validation and transactional append · GitHubAccess 通用 Excel 批量导入 - 纯 VBA 一键生成导入窗体,支持字段映射、校验与事务追加 | Universal Excel batch import for Microsoft Access, pure VBA with auto-generated forms, field mapping, validation and transactional append - miaowei2/accessImportExcelhttps://github.com/miaowei2/accessImportExcel重新设计读取和写入流程老版并不是没有考虑效率它已经把字段与列的对应关系放进数组避免在导入时反复查映射。但从核心循环来看读取数据仍然要逐行、逐列访问 Excel 的Cells目标记录也通过AddNew、Update逐条写入。数据不多时这种写法直观也容易跟踪是哪一条出错了。数据多起来每一格都要跨程序访问 Excel 对象再一条条更新记录这些调用的开销就会累积。新版先用Range.Value2把一块区域读到数组再处理这块数据。分块大小会根据列数控制不会把整个工作簿不加区分地塞进内存。写入流程也重新设计了。以 Access 目标表为例先批量写暂存数据校验通过后生成类型化暂存表再通过一条INSERT INTO … SELECT追加到目标表。大致流程是这样Excel 工作表 ↓ 分块读取 Range.Value2 原始暂存数据保留 Excel 行号 ↓ 校验类型、长度、必填项 错误明细 通过校验的数据 ↓ 生成类型化暂存表 在事务内批量追加到 Access 目标表这里仍然有 VBA 的逐行、逐字段处理因为类型转换和错误定位需要检查每个值。减少的是反复访问 Excel 单元格和逐条写目标表不是说用了批量读取就完全没有循环了。SQL Server 目标走另一条追加路径按行数和参数数量限制分批写入连接专属的临时表再在服务器端追加到目标表。不是把 Access 的本地写法原封不动搬过去。这次重新开发我重点考虑了大数据量下的读写方式。但具体能快多少仍要看行数、列数、字段类型、索引和后端环境。这里不写「几万条只要几秒」之类的数字。大家可以用自己的测试文件对照旧版和新版看结果。通用导入不能只认固定列的位置业务里收到的 Excel 文件往往不会一直保持同一个格式。客户名称这次在 B 列下次挪到了 D 列有的文件前面加了一行说明真正的标题在第二行还有一些列只是给人看的根本不需要写进数据库。新版的字段映射功能也重新实现了可以选择工作表指定标题行按字段名或字段 Caption 自动匹配也可以手动调整目标字段不需要的列直接跳过。标题行默认是第 1 行实际不是第 1 行时按文件的情况修改。不要为了迁就程序让用户每次都先把 Excel 改成同一个模板。映射不是自动匹配完就可以不看。尤其是名称接近的字段比如客户名称、联系人、收货人导入前还是要确认一次。程序也会阻止同一个目标字段被两列重复映射。目前支持.xls、.xlsx、.xlsm和.xlsb。源文件以只读方式打开不更新外部链接并禁用源工作簿宏。工具只管理自己创建的 Excel 实例不会为了导入结束就把用户原来打开的 Excel 一起关掉。导入成功之前先把数据检查一遍导入最麻烦的情况不一定是完全报错。有时候是大部分数据进去了少数没有进去用户以为已经完成后面的业务才发现缺记录。新版默认先做整批校验。只要发现类型、长度或必填项错误就不写入目标表让用户先改源数据。例如目标字段只允许整数源单元格却填了小数客户编号超过字段长度必填名称为空日期无法转换。这些都会记录原因而不是只弹一个「导入失败」。也可以勾选「跳过校验失败的行」只追加通过校验的记录。但这是一个明确的选择不是程序悄悄丢掉坏数据。几个具体规则我觉得有必要提前说明空单元格按Null处理不自动变成数字 0映射列全空的行不追加。文本编号、长备注、引号和换行不通过拼接文本 SQL 的方式写入也不交给 Excel 驱动猜测列类型。目标字段是整数时不把小数悄悄四舍五入超过范围则记录为错误。Excel 公式错误值会被识别为错误不当成正常业务数据导入。自动编号、计算列、附件、OLE、多值字段等不支持的字段不作为普通导入字段处理。还有一件事任何导入工具都不能替源文件补回来如果 Excel 实际存的是数字 123只靠显示格式把它显示成00123新版读取原始值也不能恢复本来没有保存的前导零。客户编号、产品编码这类数据源文件就应该按文本保存。看起来是五位不代表底层真的存了五位文本。日期也一样。项目支持 Excel 的日期序号和部分明确的文本格式但其他日期文字仍可能受本机地区设置影响。交换数据时最好先约定清楚年月日的写法。错误留下记录写入失败可以回滚老版已经有错误提示功能失败时会把错误信息放到 Excel 数据右侧方便用户查看。原代码的说明也写了这些信息默认不会保存到源文件里。新版把错误明细和导入历史留在 Access 中。错误明细会记录批次、Excel 原始行号、字段、原始值和失败原因。处理某个单元格时不用自己猜「数据库第几条」对应「Excel 第几行」。批次历史则记录源文件、工作表、目标表、行数、追加数和状态。有人问「昨天那份文件导进来了吗」至少可以先查这次操作的记录。不过字段校验通过不等于数据库一定允许写入。数据库还有唯一索引、主键、关系、验证规则等约束。比如客户编号已经存在新文件又带了一条同样的编号最后仍然可能被数据库拒绝。新版的目标追加放在事务中完成写入失败时回滚本次目标追加。勾选「跳过校验失败的行」也不表示能跳过所有数据库约束错误最终追加触发约束失败仍会整批回滚。这种最终阶段的错误可能是批次级错误错误明细中的ExcelRow0就表示不能把它简单归到某一条源行。不要把它当成 Excel 第零行。如果提交或回滚时连接异常程序会把批次标记为「提交状态待核实」。这时候先核对目标表再决定是否重试不要看到报错就再点一次导入。另外这个工具做的是追加没有自动去重、覆盖旧记录或合并更新的承诺。重复文件再次导入仍可能产生重复数据也可能被唯一索引拒绝。需要更新已有资料的业务应另行设计规则。放到自己的 Access 项目里按这几步做第一步下载项目先备份数据库从 GitHub 下载项目先拿测试数据库或正式数据库的副本操作。当前项目说明要求中文 Windows 环境、Access 2010 及以上并安装 Excel。支持 32 位和 64 位 Access但并不是所有版本、位数和驱动组合都已经验证还是要在自己的环境里试。数据库需要启用内容或放在你确认可信的受信任位置。不要为了省事把任意下载目录都设成可信。第二步导入两个标准模块按Alt F11打开 VBA 编辑器通过「文件 → 导入文件」同时导入modExcelImport.bas和modExcelImportForms.bas。前一个负责导入核心后一个负责配置表和窗体生成不能只导入其中一个。如果已有同名模块先备份你自己修改过的代码再移除旧模块、导入新版。随后检查 DAO 引用执行「调试 → 编译」。ADO 和 Excel 使用后期绑定不需要再额外勾选它们的对象库引用。这里尤其注意编码两个模块是CP936 / GBK不是 UTF-8。通过 VBE 导入不要先转换成 UTF-8也不要拿LoadFromText当成普通模块导入方法。GitHub 网页预览如果看到中文乱码下载后按 GBK 打开再看。第三步一行代码生成导入界面在立即窗口执行CreateExcelImportForm程序会生成主导入窗体frmExcelImport以及字段映射、错误明细、导入历史三个配套窗体同时安装需要的配置和日志表。如果只想创建对象暂时不打开界面可以执行CreateExcelImportForm True重复运行会重建并覆盖四个窗体不删除业务表和导入历史。但如果你已经改过窗体布局或事件代码应该先另存备份并关闭这些窗体后再重建。第四步选文件、确认映射再开始导入打开导入窗体依次选择目标表、Excel 文件、工作表和标题行检查字段映射再点「开始导入」。有错误时查看错误明细完成后查看导入历史。界面默认英文可以点击页脚的「中文」按钮切换也可以在立即窗口执行EI_SetLanguage zh这只是界面语言切换已经写入日志的错误原因仍保留记录时的语言。第五步接到自己窗体的按钮如果项目里已经有客户管理窗体可以在按钮的单击事件里打开导入界面。下面这行放在已有的事件过程中不是一个完整的事件过程DoCmd.OpenForm frmExcelImport, , , , , , 客户这里的「客户」是你的 Access 表名。通过OpenArgs传入后目标表会预设并锁定窗体打开时自动弹出文件选择对话框。用户不必每次在一长串表名中重新选目标也能减少把客户文件误导到其他表里的机会。支持哪些目标也有哪些限制当前支持 Access 本地表、Access 后端链接表以及 SQL Server ODBC 链接的用户表。其他 ODBC 数据库、SQL Server 视图等目标不在当前支持范围内。SQL Server 这边不只是「能看到链接表」就一定能导入还要有对应的驱动、连接权限和临时表权限。使用 ODBC 配置时驱动或 DSN 的位数要与 Access 匹配。多人使用每个人要有自己的前端副本。映射表和暂存表保存在前端里不适合几个人同时共用同一个前端文件。一次最多映射 254 列。大批量导入也会使 Access 文件增长需要预留磁盘空间必要时压缩修复。批量读取只是减少对象调用并没有取消 Access 文件容量和硬件条件的限制。导入过程中支持取消但不是任何时刻都能立即停止。分块读取和字段校验之间可以响应取消单条 SQL 正在执行时不能直接中断已经提交完成的数据也不会因为点击取消就撤回。正式使用前我建议至少试三份文件一份全部正确的一份包含类型或长度错误的一份会触发目标表唯一约束的。核对追加数、错误明细和目标数据再接到正式业务里。代码已经开源欢迎拿自己的文件试项目采用 MIT 协议使用和修改时保留协议要求的版权及许可声明即可。安装方法、转换规则和 SQL Server 连接说明都放在 README 中。我希望这次重写解决的是通用导入真正遇到的几件事数据多了读写方式要调整源文件有问题能定位目标追加失败能回滚操作结束之后能查到发生了什么。红尘如烟大师当年的模块提供了很好的使用思路原作的作者和贡献应该说明清楚。这次开源的是我按现在的需求全新开发的实现不是对原有代码做调整。源码放出来方便有需要的 Access 开发者使用和修改。项目地址再放一次GitHub - miaowei2/accessImportExcel: Access 通用 Excel 批量导入 - 纯 VBA 一键生成导入窗体,支持字段映射、校验与事务追加 | Universal Excel batch import for Microsoft Access, pure VBA with auto-generated forms, field mapping, validation and transactional append · GitHubAccess 通用 Excel 批量导入 - 纯 VBA 一键生成导入窗体,支持字段映射、校验与事务追加 | Universal Excel batch import for Microsoft Access, pure VBA with auto-generated forms, field mapping, validation and transactional append - miaowei2/accessImportExcelhttps://github.com/miaowei2/accessImportExcel如果你手里有比较大的 Excel 文件或者格式比较特别的导入需求欢迎在项目里提交 Issue。说明目标字段类型、文件格式和报错信息即可业务数据先脱敏不要把真实客户资料和数据库密码直接上传。