Excel VBA 编程 -- 大数据量写入数据库(一)
- 2026-04-08 21:11:54
Excel VBA 编程 -- 大数据量写入数据库(一)在《Excel 编程 -- 访问数据库》篇，有朋友问：有没有一次性上传数据库的办法？遍历数据太慢。 为研究这个问题，先通过网络生成了一个含有 10 万条记录、每条记录 5 个字段的 Excel 数据表，如下图所示： 
然后分别利用 VBA、Delphi、Python/Pandas 将这 10 万条记录的 Excel 数据表写入 SQL Server 2025 数据库，得到三种方式所花费的时间（单位：秒）如下： 
由于 VBA 和 Delphi 都是通过 ADO 与数据库打交道，而且都是通过 Excel 获取数据，因此所花时间差不多，所差的那点时间可能就是 Delphi 编译为本机代码所带来的性能提升。 相反，Python/Pandas 是通过 OpenPyxl 直接读取 Excel 文件，并且通过 ODBC 与数据库沟通，因此性能提升非常明显。 今天我们先来看如果通过 VBA 代码将这 10 万条记录写入数据库。 先来看 VBA 的顶层代码： 
WriteDataTest 过程是最顶层的测试代码，这个过程的功能是将 10 万条记录所在的 Range 转换为数组 rArray，然后用这个数组调用 WriteDataToDb。 WriteDataToDb 过程用了两个类模块，一个是先前定义的 Stopwatch，用来统计代码的执行时间。另一个是今天我们定义的 DbClient，用来与数据库打交道。 关于类模块的实例化方式，昨天已经讨论过。在 PiVBALib 中定义了两个 Create 函数，分别创建 Stopwatch 和 DbClient 类的实例。 DbClient 类中定义了一个 WriteDataToDb 的方法，用前面的数组作为参数，将该数组表示的数据集写入数据库。 Stopwatch 用来统计执行时间，并将结果写入 Sheet2 的 B2 单元格。 在进入 DbClient 的讨论之前，先来看在 PiVBALib 的过程模块中创建的 Create 函数： 
CreateStopwatch 和 CreateDbClient 分别用来创建类 Stopwatch 和 DbClient 的实例，以供外部代码使用。 Stopwatch 类已经在前面的文章中讨论过了，今天主要讨论 DbClient。 
在 DbClient 类中，首先声明两个私有变量，分别是表示数据库连接的 cn 和表示执行 SQL 语句的 cmd。然后是 DbClient 呈现给外界的公有过程 WriteDataToDb，这个过程接受一个 Variant 类型的参数，实际是一个二维数组。如果传递过来的不是一个表示二维数组的 Variant 类型，则在程序执行过程中会出错（LBound 和 UBound 函数对于非数组类型的参数会出错）。 WriteDataToDb 过程中的代码，首先是用 OpenConnection 打开与数据库的连接，然后遍历二维数组，每一行都生成一个 INSERT INTO 类型的 SQL 语句，向数据库表中插入一条记录。这个过程往复循环，直到遍历完所有的行。 最后调用 CloseConnection 关闭数据库连接。下面看 OpenConnection、CloseConnection、ExecuteSQL 三个过程的实现代码： 
关于 Connection 和 Command，我们以前已经涉及过，但今天的不同在于“事务”这一概念。 从上面的代码中可以看出，在 cn.Open 之后，我们写了 cn.BeginTrans，在 cn.Close 之前，我们写了 cn.CommitTrans。这表示，在 cn.Open 和 cn.Close 之间执行的所有 SQL 语句（10 万条记录就有 10 条 INSERT INTO 类型的 SQL 语句），都作为一个“事务”进行处理。作为一个事务进行处理的好处是能显著提升数据库的性能。相反，如果不将这 10 万条 SQL INSERT 语句作为一个事务，则默认每一条 INSERT 语句都是一个单独的事务，这将大大降低批量插入数据的性能。 事务是数据库中一个很复杂的概念，今天只是简单涉及：将批量插入数据的 SQL 语句作为一个事务进行处理，能显著提升批量 SQL 语句的执行性能。 相关阅读 Excel VBA 编程 -- 访问数据库
本文来自网友投稿或网络内容，如有侵犯您的权益请联系我们删除，联系邮箱：wyl860211@qq.com 。
