前段时间考试系统要新添加一个功能，要把学生表的信息批量导入，也就是需要从excel中导入到数据库表，小女子不才，找了好长时间才解决。
一、如果表是没有建立的，我们需要在数据库表中重新建立一个表盛放excel数据的时候：
在sql server中的导入语句：
SELECT * intocity2 FROM OpenDataSource( 'Micros 前段时间考试系统要新添加一个功能，要把学生表的信息批量导入，也就是需要从excel中导入到数据库表，小女子不才，找了好长时间才解决。
一、如果表是没有建立的，我们需要在数据库表中重新建立一个表盛放excel数据的时候：
在sql server中的导入语句：
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表中的数据进行判断。
则先需要把数据导入到datatable中
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();
}
}
|
当然，我感觉我这里把datatable中的数据一条一条的取出来判断至少是非常耗时间耗内存的，而且这里最好加上回滚事物，因为在我们导入过程中会经常出现这样活那样的问题，采用事物，可以在出错的时候把数据回滚到没有导入之前的状态，防止意外事件发生，这里我就不往上加了。
以上是三种是我们实现了的excel导入，当然，我想方法还不止这些，当然，除了导入，还有的就是从数据库表导出到excel表中，因为我做的那部分系统没有涉及到，所以这里就不再提了。
下面说一下我在解决的过程中绕的弯路：
一、我没有把导入数据库的种种做法弄清楚，比如是直接创建表呢还是在已经存在的表中导入，所以以至于刚开始总是找不到合适的方法。
二、在后来的查找过程中，我发现我有一个很大的毛病，就是我的关键字是在“asp.net中、、、、、”，其实，既然是往数据库中导入，asp.net只是一个执行一下，所以，因为有了关键字的约束，查出来的资料少不说，而且还形成了一种思维定式，除了asp.net之外的其他都一概不看。
三、对查不来的信息不能加以理解，只是能用就用，不能用就换，也就是我因为转义字符串那一块弄了很长时间的原因，因为当我们在sql执行查询语句没有错误的时候，需要把它放在asp.net中执行，因为这些语句需要string字符串来显示，而这个执行语句中有包含引号，所以需要转义字符，在解决引号的问题之后，我发现还是不正确，一直折腾了好久才发现是路径F:\test.xls中“\t“是table的转义字符，所以这里需要两个\来转义\，这就是应该写成这样“F:\\test.xls”，而当我写成这样的时候，我才想起来，其实最开始查询的时候所有的代码都是这样的，只是那些我没用上，当时也没多想，以为路径就应该是这样的，最终导致还继续在这个上面栽跟头。