（C#）excel数据导⼊SqlServer中
15:08:57
protected void btnReadDataTo_Click(object sender, EventArgs e)
{
if (FuloadExcelFile.FileName == "")
return;
string fileExt = System.IO.Path.GetExtension(FuloadExcelFile.FileName);
if (fileExt != ".xls")//必须是EXCEL⽂件
return;
string filepath = FuloadExcelFile.PostedFile.FileName;//⽂件路径
DataTable dt = new DataTable();
dt = CallExcel(filepath);//返回EXCEL⽂件的数据
if(InsertSQLServer(dt,FuloadExcelFile.FileName.Split('.')[0]))//导⼊数据库
{}
}
protected DataTable CallExcel(string filepath)
{
OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0");
con.Open();
string sql = "select * from [Sheet1$]";//选择第⼀个数据SHEET
//OleDbCommand command = new OleDbCommand(sql, con);
//OleDbDataReader reader = command.ExecuteReader();
//if (reader.Read())
//{
//    reader[0].ToString();//直接读出数据
//}
OleDbDataAdapter adapter = new OleDbDataAdapter(sql, con);
DataTable dt = new DataTable();
adapter.Fill(dt);
//reader.Close();
//command.Dispose();
con.Close();
con.Dispose();
return dt;
}
protected bool InsertSQLServer(DataTable dt,string dataname)
{
string strCon = @"Server=BOBER\SQLExpress;Integrated Security=true;";//⽆数据库名连接
string strTest="testTable";
try
{
SqlConnection con = new SqlConnection(strCon);//创建数据库
con.Open();
string strSQL = "IF NOT EXISTS(SELECT name FROM sys.databases WHERE name=N'" + dataname + "') CREATE DATABASE [" + dataname + "]";
SqlCommand command = new SqlCommand(strSQL, con);
command.ExecuteNonQuery();
//创建数据库表
strSQL = "USE[" + dataname + "] IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[DBO].[" + strTest + "]') AND type in (N' U')) CREATE TABLE " + strTest + " (";
string strColumn=string.Empty;
for (int i = 0; i < dt.Columns.Count; i++)
{
strSQL += "[" + dt.Columns[i].ColumnName + "] [nvarchar](256),";
strColumn+=dt.Columns[i].ColumnName+",";
}
strSQL += " )";
SqlCommand newcom = new SqlCommand(strSQL, con);
newcom.ExecuteNonQuery();
//插⼊数据
strColumn=strColumn.Substring(0,strColumn.Length-1);
for(int i=0;i<dt.Rows.Count;i++)
{
strSQL = "USE[" + dataname + "] Insert into "+strTest+" (" + strColumn + ") values (";
for (int k = 0; k < dt.Columns.Count; k++)
{
strSQL += "'"+dt.Rows[i][k].ToString()+"',";
}
strSQL = strSQL.Substring(0, strSQL.Length - 1);
strSQL += ")";
SqlCommand insertCom = new SqlCommand(strSQL, con);
insertCom.ExecuteNonQuery();
}
return true;
}
catch
excel连接sql数据库教程
{
return false;
}
}

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。