如何将excel里的数据导入SQL
如T 需代码

[解决办法]
代码在里面
http://support.microsoft.com/kb/321686/zh-cn
[解决办法]
将Excel的数据导入SQL server :

-- ======================================================

SELECT * into newtable

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0 ',

'Data Source= "c:\book1.xls ";User ID=Admin;Password=;Extended properties=Excel 5.0 ')...[Sheet1$]



实例:

SELECT * into newtable

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0 ',

'Data Source= "c:\Finance\account.xls ";User ID=Admin;Password=;Extended properties=Excel 5.0 ')...xactions

[解决办法]
--如果接受数据导入的表已经存在
insert into 表 select * from
OPENROWSET( 'MICROSOFT.JET.OLEDB.4.0 '
, 'Excel 5.0;HDR=YES;DATABASE=c:\test.xls ',sheet1$)

--如果导入数据并生成表
select * into 表 from
OPENROWSET( 'MICROSOFT.JET.OLEDB.4.0 '
, 'Excel 5.0;HDR=YES;DATABASE=c:\test.xls ',sheet1$)
[解决办法]
程序导入还是要做工具导入？
[解决办法]
//Excel文件的连接
　　　　string excelConnectionString = "Provider = Microsoft.Jet.OleDb.4.0; Data Source = " + Excel文件路径 + " ; Extended Properties=Excel 8.0; ";
OleDbConnection excelConn = new OleDbConnection(excelConnectionString);
excelConn.Open();
OleDbCommand excelCmd = new OleDbCommand( "SELECT * FROM [Sheet1$] ", excelConn);

　　　　//数据库的连接
string accessConnectionString = "Provider = Microsoft.Jet.OleDb.4.0; Data Source = " + 数据库路径;
OleDbConnection accessConn = new OleDbConnection(accessConnectionString);
accessConn.Open();
OleDbTransaction trans = accessConn.BeginTransaction();


string sql = "INSERT INTO [表] (字段) VALUES ( '{0} ', ...) ";

try
{
using (OleDbDataReader excelDr = excelCmd.ExecuteReader())
{
while (excelDr.Read())
{
OleDbCommand accessCmd = new OleDbCommand(string.Format(sql, System.Convert.ToString(excelDr[0])), accessConn);
accessCmd.Transaction = trans;
accessCmd.ExecuteNonQuery();

}
}
//添加成功，提交事务
trans.Commit();
}
catch (Exception ex)
{
//添加失败，回滚事务
trans.Rollback();
}
finally
{
//关闭数据库连接
excelConn.Close();
accessConn.Close();
}
[解决办法]
public string sConnectionString
{

get
{ //先将EXCEL导入到数据库,一:先把EXCEL导入dateView,二:然后将dateView里的数据导入到数据库里面
//string xlsPath = Server.MapPath( "~/app_data/book1.xls ");
//EXCEL 的连接串
string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " + "Data Source= " + File1.PostedFile.FileName.ToString() + "; " + "Extended Properties=Excel 8.0; ";

return sConnectionString;
}
}
protected void Button2_Click(object sender, EventArgs e)
{
try
{

//开始导入
//建立EXCEL的连接
OleDbConnection objConn = new OleDbConnection(sConnectionString);
objConn.Open();
string sqlStr = "select * from [Sheet1$] ";
OleDbCommand myCmd = new OleDbCommand(sqlStr, objConn);
OleDbDataAdapter myda = new OleDbDataAdapter(myCmd);
DataSet myDs = new DataSet();
myda.Fill(myDs, "Temp ");
DataGrid1.DataSource = myDs;
try
{
string strConn = System.Configuration.ConfigurationSettings.AppSettings[ "ConnectionString "];
SqlConnection mySqlConn = new SqlConnection(strConn);
mySqlConn.Open();
string strCmd = " ";
int rows = myDs.Tables[0].Rows.Count;
int cols = myDs.Tables[0].Columns.Count;
int k = 0;
string a = " ";
string chongfu = " ";
for (int i = 0; i < rows; i++)
{
a = myDs.Tables[0].Rows[i].ItemArray[0].ToString();
string strSql = "select * from studys where idkey= ' " + myDs.Tables[0].Rows[i].ItemArray[0].ToString() + " ' ";
SqlDataAdapter myAda = new SqlDataAdapter(strSql, mySqlConn);
myAda.Fill(myDs, "Temp ");
if (myAda.Fill(myDs, "Temp ") != 0)
{
k += 1;
chongfu += ", " + myDs.Tables[0].Rows[i].ItemArray[0].ToString();
}
else
{
strCmd = "insert into Studys (idkey,title,who,describe,place,begindate,enddate) values( ' ";
for (int j = 0; j < cols; j++)
{
if (j == 0)
{
strCmd = strCmd + myDs.Tables[0].Rows[i].ItemArray[j].ToString();
}
else
{
strCmd += " ', ' " + myDs.Tables[0].Rows[i].ItemArray[j].ToString();
}
}
strCmd += " ') ";
//strCmd = " select * into studys from opendatasource ( 'Microsoft.Jet.OLEDB.4.0 ', 'Data Source= " + File1.PostedFile.FileName.ToString() + ";User ID=sa;Password=;HDR=YES;Extended Properties=Excel 8.0;Persist Security Info=false ')...sheet1$ ";

//try
// {
SqlCommand mySqlCmd = new SqlCommand(strCmd, mySqlConn);
mySqlCmd.ExecuteNonQuery();
// }
// catch (System.Data.SqlClient.SqlException ex)
// {
// Label1.Text = "导入出错，可能字段类型不匹配 " + "出错在第 " + myDs.Tables[0].Rows[i].ItemArray[0].ToString() + "行 ";

// }
}
}
mySqlConn.Close();
objConn.Close();
Label1.Text = "当前Excel文件中有 " + k + "条记录与Sql数据库中的记录重复 ";
Label2.Text = "重复的记录为 " + chongfu;
BindGrid();//在页面显示出来
}
catch (System.Data.SqlClient.SqlException ex)
{
Label1.Text =ex.Message ;
}
}
catch (System.Data.OleDb.OleDbException ex)
{
Label1.Text = "请确保excel文件版本及路径正确，并现未被访问!操作未被进行 ";
return;
}
}
============================
这是一个button事件，实现把input(file)浏览的excel文件导入到SQL里面，并在显示在页面的datagrid1中
[解决办法]
顶下吧，学习下高手们