前段时间考试系统要新添加一个功能,要把学生表的信息批量导入,也就是需要从excel中导入到数据库表,小女子不才,找了好长时间才解决。
一、如果表是没有建立的,我们需要在数据库表中重新建立一个表盛放excel数据的时候:
SELECT * intocity2 FROM OpenDataSource( 'Micros 前段时间考试系统要新添加一个功能,要把学生表的信息批量导入,也就是需要从excel中导入到数据库表,小女子不才,找了好长时间才解决。
一、如果表是没有建立的,我们需要在数据库表中重新建立一个表盛放excel数据的时候:
SELECT * intocity2 FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'DataSource="f:\test.xls";User ID=Admin;Password=;Extendedproperties=Excel 5.0')...[Sheet1$]
这里需要注意的是,如果直接写这个语句,会出现这样的错误:
SQL Server 阻止了对组件'Ad HocDistributed Queries' 的STATEMENT'OpenRowset/OpenDatasource' 的访问,因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用sp_configure 启用'Ad Hoc Distributed Queries'。有关启用'Ad HocDistributed Queries' 的详细信息,请参阅SQL Server 联机丛书中的"外围应用配置器"。
所以,我们这里需要启动服务:
启动语句为:
execsp_configure 'show advanced options',1 reconfigure execsp_configure 'Ad Hoc Distributed Queries',1 reconfigure |
当然,用完之后要记得关闭:
关闭语句为:
execsp_configure 'Ad Hoc Distributed Queries',0 reconfigure execsp_configure 'show advanced options',0 reconfigure |
因为考试系统是基于asp.net实现的,所以,一下是asp.net的实现代码,需要注意的是,因为语句中存在”,\等特殊符号,所以,我们需要使用转义字符来使这些特殊符号成为字符串类型,这里是一些常用的转义字符符号:http://baike.baidu.com/view/73.htm
protectedvoid btntoLaad_Click(object sender, EventArgs e) { SqlConnectionmycon = new SqlConnection("server=.;database=qingniao;uid=sa;pwd=123"); string sqlstr = "SELECT * into cityFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source=\"f:\\test.xls\";User ID=Admin;Password=;Extended properties=Excel5.0')...[Sheet1$]"; SqlCommand cmd = new SqlCommand(sqlstr, mycon); mycon.Open(); cmd.ExecuteNonQuery(); mycon.Close(); } |
这样,数据库中会建立一个city表,来存储excel中的数据。
二、将excel表导入到已经存在的数据库表
这里需要注意的是,excel表中的数据必须要和数据库表中的数据一致
比如,如果数据库表的字段为
则相应的excel的表字段为:
oft.Jet.OLEDB.4.0', 'DataSource="f:\test.xls";User ID=Admin;Password=;Extendedproperties=Excel 5.0')...[Sheet1$]
这里需要注意的是,如果直接写这个语句,会出现这样的错误:
SQL Server 阻止了对组件'Ad HocDistributed Queries' 的STATEMENT'OpenRowset/OpenDatasource' 的访问,因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用sp_configure 启用'Ad Hoc Distributed Queries'。有关启用'Ad HocDistributed Queries' 的详细信息,请参阅SQL Server 联机丛书中的"外围应用配置器"。
所以,我们这里需要启动服务:
启动语句为:
execsp_configure 'show advanced options',1 reconfigure execsp_configure 'Ad Hoc Distributed Queries',1 reconfigure |
当然,用完之后要记得关闭:
关闭语句为:
execsp_configure 'Ad Hoc Distributed Queries',0 reconfigure execsp_configure 'show advanced options',0 reconfigure |
因为考试系统是基于asp.net实现的,所以,一下是asp.net的实现代码,需要注意的是,因为语句中存在”,\等特殊符号,所以,我们需要使用转义字符来使这些特殊符号成为字符串类型,这里是一些常用的转义字符符号:http://baike.baidu.com/view/73.htm
protectedvoid btntoLaad_Click(object sender, EventArgs e) { SqlConnectionmycon = new SqlConnection("server=.;database=qingniao;uid=sa;pwd=123"); string sqlstr = "SELECT * into cityFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source=\"f:\\test.xls\";User ID=Admin;Password=;Extended properties=Excel5.0')...[Sheet1$]"; SqlCommand cmd = new SqlCommand(sqlstr, mycon); mycon.Open(); cmd.ExecuteNonQuery(); mycon.Close(); } |
这样,数据库中会建立一个city表,来存储excel中的数据。
二、将excel表导入到已经存在的数据库表
这里需要注意的是,excel表中的数据必须要和数据库表中的数据一致
比如,如果数据库表的字段为
则相应的excel的表字段为:
protectedvoid btnExist_Click(object sender, EventArgs e) { SqlConnection mycon = new SqlConnection("server=.;database=qingniao;uid=sa;pwd=123"); string sqlstr = " insert intocity1 SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'DataSource=\"f:\\test.xls\";User ID=Admin;Password=;Extendedproperties=Excel 5.0')...[Sheet1$]"; SqlCommand cmd = new SqlCommand(sqlstr,mycon); mycon.Open(); cmd.ExecuteNonQuery(); mycon.Close(); } |
三、既然已经存在的表,一般都会存在一些设置,比如说主键、外键或者是其他,如果主键或者外键冲突,就会出现导入失败的问题。所以,我们需要对excel表中的数据进行判断。
protected void btnLeadingIn_Click(objectsender, EventArgs e) { DataTable dt=new DataTable(); dt = CreateExcelDataSource("F:\\abc.xls"); SqlConnection sqlCon = con(); sqlCon.Open(); GridView1.DataSource = dt; GridView1.DataBind(); for (int i = 0; i < dt.Rows.Count;i++) { //导入数据库,把数据写入数据库应该就是非常简单了,这里就不多写了 } } public static DataTableCreateExcelDataSource(string url) { DataTable dt = null; // string connetionStr ="Provider=Microsoft.Ace.OleDb.12.0;" + "Data Source=" + url+ ";" + "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"; string connetionStr = "Provider=Microsoft.Jet.OleDb.4.0;"+ "data source=" + url + ";Extended Properties='Excel 8.0;HDR=YES; IMEX=1'"; string strSql = "select * from[Sheet1$]"; OleDbConnection oleConn = new OleDbConnection(connetionStr); OleDbDataAdapter oleAdapter = new OleDbDataAdapter(strSql,connetionStr); try { dt = new DataTable(); oleAdapter.Fill(dt); return dt; } catch (Exception ex) { throw ex; } finally { oleAdapter.Dispose(); oleConn.Close(); oleConn.Dispose(); } } |
以上是三种是我们实现了的excel导入,当然,我想方法还不止这些,当然,除了导入,还有的就是从数据库表导出到excel表中,因为我做的那部分系统没有涉及到,所以这里就不再提了。
一、我没有把导入数据库的种种做法弄清楚,比如是直接创建表呢还是在已经存在的表中导入,所以以至于刚开始总是找不到合适的方法。
====================================分割线================================
最新内容请见作者的GitHub页:http://qaseven.github.io/