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"
Next
range.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.Add
	Set worksheet=workbook.WorkSheets.Add
	worksheet.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",18
	app.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更加自动化