分类： 数据库开发技术
2011-09-08 17:57:34
导入
http://blog.csdn.net/qygaojiansheng/article/details/4126364
如果表已存在，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//桌面"； //导出的文件名和路径
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倍左右，这我目前还不知道为什么。这种方法，只适合少量的数据，如果数据量过大，则时间开销会很大。
