将excel文件中的数据导入导出至SQL数据库中(重要)

导入

如果表已存在，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倍左右，这我目前还不知道为什么。这种方法，只适合少量的数据，如果数据量过大，则时间开销会很大。