/// <summary>/// ASP.NET将excel中数据导入到sql server2005数据表(注意:excel中的字段名、字段个数、字段类型和sql server中的一样)
/// </summary>
/// <param name="path">excel文件路径</param>
/// <param name="StyleSheet">excel文件StyleSheet值</param>
/// <param name="con">连接目标数据库所需的数据库连接字符串</param>
public void LoadData(string path, string StyleSheet, string con)
{
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + Server.MapPath(path) + ";Extended Properties=Excel 8.0";
OleDbConnection myConn = new OleDbConnection(strCon);
myConn.Open(); //打开数据链接,得到一个数据集
DataSet myDataSet = new DataSet(); //创建DataSet对象
string StrSql = "select * from [" + StyleSheet + "$]";
OleDbDataAdapter myCommand = new OleDbDataAdapter(StrSql, myConn);
myCommand.Fill(myDataSet, "[" + StyleSheet + "$]");
myCommand.Dispose();
DataTable DT = myDataSet.Tables["[" + StyleSheet + "$]"];
myConn.Close();
myCommand.Dispose();
//连接目标数据库
string strConn = con;
SqlConnection conn = new SqlConnection(strConn);
conn.Open();
for (int j = 0; j < DT.Rows.Count; j++)
{
string strSql = "insert into " + StyleSheet + " values (";//这里数据库中表名和excel文件中的StyleSheet名一样
for (int i = 0; i < DT.Columns.Count; i++)
{
strSql += "'" + DT.Rows[j][i] + "',";
}
strSql = strSql.Substring(0, strSql.Length - 1);
strSql += ")";
SqlCommand comm = new SqlCommand(strSql, conn);
comm.ExecuteNonQuery();
}
conn.Close();
}