首先说明一下Microsoft.Jet.OLEDB.4.0和Microsoft.ACE.OLEDB.12.0区别：

有两种接口可供选择：Microsoft.Jet.OLEDB.4.0（以下简称 Jet 引擎）和Microsoft.ACE.OLEDB.12.0（以下简称 ACE 引擎）。

Jet 引擎大家都很熟悉，可以访问 Office 97-2003，但不能访问 Office 2007。

ACE 引擎是随 Office 2007 一起发布的数据库连接组件，既可以访问 Office 2007，也可以访问 Office 97-2003。

另外：Microsoft.ACE.OLEDB.12.0 可以访问正在打开的 Excel 文件，而 Microsoft.Jet.OLEDB.4.0 是不可以的。

Microsoft.ACE.OLEDB.12.0 安装文件：

http://www.microsoft.com/downloads/details.aspx?displaylang=zh-cn&FamilyID=7554f536-8c28-4598-9b72-ef94e038c891

====================================================================================================

进入正题

====================================================================================================

导入

如果表已存在，SQL语句为：

insert into aa select * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=D:/OutData.xls;Extended Properties=Excel 8.0')...[sheet1$]

其中，aa是表名，D:/OutData.xls是excel的全路径 sheet1后必须加上$

如果表不存在，SQL语句为：

SELECT * INTO aa FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=D:/OutData.xls;Extended Properties=Excel 8.0')...[sheet1$]

其中，aa是表名，D:/OutData.xls是excel的全路径 sheet1后必须加上$

可能会发生的异常：

如果发生“链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 报错。提供程序未给出有关错误的任何信息。

无法初始化链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 的数据源对象。”异常可能是excel文件未关闭.

如果发生“不能将值 NULL 插入列 'Grade'，表 'student.dbo.StuGrade'；列不允许有空值。INSERT 失败。

语句已终止。”异常，则可能是excel文件与数据库表中的字段不匹配

以上操作的是office 2003,如果要操作office 2007则需采用如下方式

如果表已存在，SQL语句为：

insert into aa select * from OPENDATASOURCE('Microsoft.Ace.OLEDB.12.0',

'Data Source=D:/OutData.xls;Extended Properties=Excel 12.0')...[sheet1$]

其中，aa是表名，D:/OutData.xls是excel的全路径 sheet1后必须加上$

如果表不存在，SQL语句为：

SELECT * INTO aa FROM OPENDATASOURCE('Microsoft.Ace.OLEDB.12.0',

'Data Source=D:/OutData.xls;Extended Properties=Excel 12.0')...[sheet1$]

其中，aa是表名，D:/OutData.xls是excel的全路径 sheet1后必须加上$

如果发生“链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 报错。提供程序未给出有关错误的任何信息。

无法初始化链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.Jet.OLEDB.4.0" 的数据源对象。”异常可能是excel文件未关闭.

如果发生“不能将值 NULL 插入列 'Grade'，表 'student.dbo.StuGrade'；列不允许有空值。INSERT 失败。

语句已终止。”异常，则可能是excel文件与数据库表中的字段不匹配

以上操作的是office 2003,如果要操作office 2007则需采用如下方式

另外，还要对一些功能进行配置：

1、打开SQL Server 2005外围应用配置器，选择“功能的外围应用配置器”，选中“启用OPENROWSET或OPENDATASOURCE支持”，点击确定。

2、在C:/WINDOWS目录下将temp文件夹的安全选项卡中，在用户或组名称中，选择“SQLServer2005ReportingServicesWebServiceUser$PC17$MSSQLSERVER(PC17/SQLServer2005ReportingServicesWebServiceUser$PC17$MSSQLSERVER”用户，将此用户的写入，修改权限选中。点击确定。（设置它是因为将此将excel文件读入SQL数据库时，是在C:/WINDOWS/temp下建立了一个临时文件，所以需要将此文件夹的SQLServer2005权限设置为可写入的。如果使用的是管理员帐户，则需要不需此项设置。因为管理员有读写的权限。）

导出

使用insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;IMEX=YES;DATABASE=C://Documents and Settings//Administrator//桌面//export2.xls',[sheet1$]) select * from StuGrade可以将数据导出至excel2003中，但前提必须是表已经存在，字段名都已有且与表对应。而使用下面的自动创建文件和表头，又会发生异常，插不进去。目前看来只能一条一条插。

解决这个问题可以先创建一个excel文件并添加表头，可以使用下面的语句：

string filePath = "C://Documents and Settings//Administrator//桌面//export3.xls";

SqlConnection conn = new SqlConnection("Server=.;Database=Student;Integrated Security=true");

conn.Open();

SqlCommand comm = new SqlCommand("select * from StuGrade", conn);

SqlDataAdapter da = new SqlDataAdapter(comm);

DataSet ds = new DataSet();

da.Fill(ds, "StuGrade");

Microsoft.Office.Interop.Excel._Application xlapp = new ApplicationClass();

Workbook xlbook = xlapp.Workbooks.Add(true);

Worksheet xlsheet = (Worksheet)xlbook.Worksheets[1];

int colIndex = 0;

int RowIndex = 1;

//开始写入每列的标题

foreach (DataColumn dc in ds.Tables[0].Columns)

{

colIndex++;

xlsheet.Cells[RowIndex, colIndex] = dc.Caption;

}

xlbook.Saved = true;

xlbook.SaveCopyAs(filePath); //创建文件

使用这个方法必须添加“using Microsoft.Office.Interop.Excel;” 引用

这样使用上面那个SQL语句即可实现。

我在与office2007导的时候，将MICROSOFT.JET.OLEDB.4.0和Excel 5.0换成了MICROSOFT.ACE.OLEDB.12.0和Excel 12.0，将表名换成excel2003的表，这样只能导出一行，而且还会发生异常，这个问题还有待解决。

使用insert into opendatasource('microsoft.jet.oledb.4.0',

'Data source=D:/export.xls;Extended Properties=Excel 5.0')...[Sheet1$]

(字段名) VALUES (对应值)也可以实现导入。前提必须是表已经存在，字段名都已有且与表对应。而且一次只能导入一条，必须是office2003，换成office2007则可以导入，但会发生异常。

通常导入与导出用一条SQL语句不太实用，因为用一条SQL语句限制太多，所以大多数情况下是一条记录一条记录写入数据库中，使用一条一条导入数据库的方法如下。其原理是将excel文件当作数据表来用：

导入

代码为：

string strExcelFileName = @"D:/OutData.xls"; //excel文件

string strSheetName = "sheet1"; //工作表名

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + strExcelFileName + ";Extended Properties = 'Excel 8.0;HDR=NO;IMEX=1'";

//连接字符串

string strExcel = "select * from [" + strSheetName + "$] ";//SQL语句

//定义存放的数据表

DataSet ds = new DataSet();

//连接数据源

OleDbConnection conn = new OleDbConnection(strConn);

conn.Open();

//适配到数据源

OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, conn);

adapter.Fill(ds, strSheetName + "$");

conn.Close();

// 一般的情况下. Excel 表格的第一行是列名

dataGridView1.DataSource = ds.Tables["res"]; //将数据和dataGridView绑定

导出

方法1：从DataSet向excel中导出数据

string filePath = "C://Documents and Settings//Administrator//桌面//export4.xls"； //导出的文件名和路径

string ReportName=”aaaaa”; //导出时给文件加上文件头

SqlConnection conn = new SqlConnection("Server=.;Database=Student;Integrated Security=true"); //定义连接

conn.Open();

SqlCommand comm = new SqlCommand("select * from StuGrade", conn);

SqlDataAdapter da = new SqlDataAdapter(comm);

DataSet ds = new DataSet();

da.Fill(ds, "StuGrade");

Microsoft.Office.Interop.Excel._Application xlapp = new ApplicationClass();

Workbook xlbook = xlapp.Workbooks.Add(true);

Worksheet xlsheet = (Worksheet)xlbook.Worksheets[1];

Range range = xlsheet.get_Range(xlapp.Cells[1, 1], xlapp.Cells[1, ds.Tables[0].Columns.Count]);

range.MergeCells = true;

//定义单元格中存放文本的样式

xlapp.ActiveCell.FormulaR1C1 = ReportName;

xlapp.ActiveCell.Font.Size = 20;

xlapp.ActiveCell.Font.Bold = true;

xlapp.ActiveCell.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;

int colIndex = 0;

int RowIndex = 2;

//开始写入每列的标题

foreach (DataColumn dc in ds.Tables[0].Columns)

{

colIndex++;

xlsheet.Cells[RowIndex, colIndex] = dc.Caption;

}

//开始写入内容

int RowCount = ds.Tables[0].Rows.Count;//行数

for (int i = 0; i < RowCount; i++)

{

RowIndex++;

int ColCount = ds.Tables[0].Columns.Count;//列数

for (colIndex = 1; colIndex <= ColCount; colIndex++)

{

xlsheet.Cells[RowIndex, colIndex] = ds.Tables[0].Rows[i][colIndex - 1];//dg[i, colIndex - 1];

xlsheet.Cells.ColumnWidth = 10;

}

xlbook.Saved = true;

xlbook.SaveCopyAs(filePath);

xlapp.Quit();

GC.Collect();

方法2：从DataGridView中向excel导出数据：

SqlConnection conn = new SqlConnection("Server=.;Database=student;Integrated Security=true");

conn.Open();

SqlCommand comm = new SqlCommand("select * from StuGrade where StuID='0000000'", conn); //StuGrade是表名，StuID是字段名

SqlDataAdapter da = new SqlDataAdapter(comm);

DataSet ds = new DataSet();

da.Fill(ds, "StuGrade");

Microsoft.Office.Interop.Excel.Application myExcel = new Microsoft.Office.Interop.Excel.Application();

myExcel.Visible = false;

//定义导出的路径

string Path = "C://Documents and Settings//Administrator//桌面";

myExcel.Application.Workbooks.Add(true);

myExcel.Caption = "abcdefghe";

int Colunm = 1;

for (int i = 0; i < ds.Tables[0].Columns.Count; i++)

{

myExcel.Cells[1, Colunm++] = ds.Tables[0].Columns[i].Caption;

}

for (int i = 0; i < ds.Tables[0].Rows.Count; i++)

{

Colunm = 1;

for (int j = 0; j < ds.Tables[0].Columns.Count; j++)

{

myExcel.Cells[i + 2, Colunm++] = ds.Tables[0].Rows[i][j];

}

myExcel.ActiveWorkbook.SaveAs(Path, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

myExcel.Quit();

理论上来说，从DataSet中导出和从DataGridView中导出执行的速度应该是一样的，但是，从我数次的实验来看，从DataGridView中导出数据比从DataSet中快的多，在数据量为300条记录时，用DataGridView比DataSet快2倍左右，这我目前还不知道为什么。这种方法，只适合少量的数据，如果数据量过大，则时间开销会很大。

分类: .net

秒客网

Excel据导入导出至SQL数据库

将excel文件中的数据导入导出至SQL数据库中（Microsoft.Jet.OLEDB.4.0和Microsoft.ACE.OLEDB.12.0|office2003和office2007）

相关文章