VBS与.Net Jet共同实现将大量数据导入Excel

参考：http://support.microsoft.com/kb/306023/zh-cn

 

单独使用VBS可以完成数据导入，但限制于语言本身，效率不高

单独使用.Net可以完成数据导入，若使用Excel对象模型，对象模型的效率并不高；若使用Ms Jet引擎，则有单个单元格255字符的限制；

回避255字符的限制，需要在Excel工作表中的前8行中有超过255长度字符的单元格（每列至少一个）。使用Excel对象模型+Ms Jet引擎应该是较好的选择，但是.Net使用对象模型时，初始化很慢，至少比使用VBS操作对象模型要慢得多。

因此，VBS完成Excel文件的创建和初始化工作(主要是回避255字符限制)，.Net Ms Jet完成数据导入可能是较为平衡的方法

准备一

Excel的对象模型（Object Model ）可以实现Excel自动化，可以由多种语言实现，包括脚本语言。

Excel的对象模型（Object Model ）的基础对象是：Application、WorkBook、Sheet（WorkSheet和Chart，本文只涉及WorkSheet）、Range。对应的Excel的概念是Excel应用程序，Excel工作簿，Excel工作表（WorkSheet），Excel单元格

层级关系如下

一个Application可以有多个WorkBook，可以通过Application.WorkBooks访问

一个WorkBook可以有多个Sheet，可以通过WorkBook.Sheets访问；WorkBook.WorkSheets是WorkSheet的集合。

一个WorkSheet由一个或多个Range组成，而这些Range又可以构成一个大的Range，可以通过WorkSheet.Cells或WorkSheet.Range（两者是有区别的，前者是所有单元格，也就是整张工作表，后者是部分单元格，可能是整张工作表）

一个简单的使用Excel对象模型的VBS示例

Set app=WScript.CreateObject ("Excel.Application")  '只有安装了Office Excel才会创建成功app.Visible=true '程序界面可见Set workbook=app.WorkBooks.Add   ' 添加一个工作簿;一般情况下，默认有3个工作表，但并不全是。Set worksheet=workbook.WorkSheets.Add   ' 默认添加活动工作表; workbook.WorkSheets是1基的WorkSheet数组set range=worksheet.Cells(1,1) '第一个单元格range.Value="test"Dim array(100)For i=0 To 100  array(i)="test"Nextrange.Resize(100,1).Value=array  '返回（1,1）单元格开始的100行x1列的单元格 并赋值，这样可以降低对Excel请求接口次数，提高效率workbook.SaveAs "e:\test",18 '另存为文件，18代表Excel 97 -2003格式app.Quit  ' 退出Excel程序

 

准备二

.Net 提供的Ms Jet 4.0引擎可以读写多种数据库，其中包括Excel。尽管Excel不是标准的数据库，Ms Jet对其驱动时有不少限制，但是基本的读写功能还是能满足的。

Ms Jet会将Excel工作表中的第一个行的单元格当作数据库的列名（对于MSDN上说的默认列名F1，F2，F3...，我没使用成功，本文不再涉及）,

//Extended Properties=Excel 8.0告诉Ms Jet数据库是Excel格式的，不指定的话，Ms Jet会认为是Access格式String ^connStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\\test.xls;Extended Properties=Excel 8.0;";System::Data::OleDb::OleDbConnection ^conn=gcnew  System::Data::OleDb::OleDbConnection(connStr);conn->Open();//往工作表中写入一行数据System::Data::OleDb::OleDbCommand  ^cmd = gcnew System::Data::OleDb::OleDbCommand();cmd->Connection = conn;cmd->CommandText = "Insert Into [Sheet1$] ( Col1, Col2 )  Values( 100,200)"; cmd->ExecuteNonQuery();conn->Close(); //关闭连接

 

两者结合

创建vbs脚本，名为 E:\test.vbs ，代码如下

Set app=WScript.CreateObject ("Excel.Application") Set workbook=app.WorkBooks.AddSet worksheet=workbook.WorkSheets.Addworksheet.Name="TestTable"Set range=worksheet.Cells(1,1)range.Value="Col1"Set range=worksheet.Cells(2,1)range.Value=String(256,30) '为回避255字符限制，30是我无意中试出来的,这个字符不可见，也不影响Excel的列宽、行高Set range=worksheet.Cells(1,2)range.Value="Col2"Set range=worksheet.Cells(2,2)range.Value=String(256,30)workbook.SaveAs "e:\test",18app.Quit

上面代码创建 E:\test.xls  文件，文件包含一个TestTable 表，表的第一行前两列为Col1，Col2，第二行前两列都是256个ASCII 码为30的字符

使用下面代码调用VBS脚本，并往E:\test.xls 写入数据

//调用vbs脚本System::Diagnostics::Process ^p=gcnew System::Diagnostics::Process();p->StartInfo->FileName = "CScript.exe";p->StartInfo->Arguments="E:\\test.vbs";p->Start();p->WaitForExit();//此处等待脚本运行完毕//写入数据String ^connStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\\test.xls;Extended Properties=Excel 8.0;";System::Data::OleDb::OleDbConnection ^conn=gcnew  System::Data::OleDb::OleDbConnection(connStr);conn->Open();//往工作表中写入一行数据System::Data::OleDb::OleDbCommand  ^cmd = gcnew System::Data::OleDb::OleDbCommand();cmd->Connection = conn;cmd->CommandText = "Insert Into [TestTable$] ( Col1, Col2 )  Values( \"abcde\", 200)"; cmd->ExecuteNonQuery();conn->Close(); //关闭连接

上面的代码只是阐述对Excel文件处理的一个基本流程，并不适合直接使用。例如，在写入数据时，使用OleDbDataAdapter更加自动化