用asp.net实现将excal导入Sqlserver数据库 用asp.net实现将excal导入Sqlserver数据库 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 你可以首先把Excel文档上传到web服务器的一个相当路径下,然后得到该路径,用oledb connection直接把excel中的内容读到Dataset中,然后自己再去匹配相应的逻辑。 string filepath = openFileDialog1.FileName; string filename = filepath.Substring(filepath.LastIndexOf("\\") + 1); if (filename.Substring(filename.LastIndexOf(".") + 1).ToUpper() != "XLS") { MessageBox.Show("请选择正确的文件"); return; }System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection( System.Configuration.ConfigurationSettings.AppSettings["ConnString"] ); try { sc.Open(); string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + " Data Source=" + filepath.Replace(@"\",@"\\") + ";" +" Extended Properties=Excel 8.0;";OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM fff", conn); myCommand.SelectCommand.Prepare(); DataSet myDataSet = new DataSet(); myCommand.Fill(myDataSet); conn.Dispose(); myCommand.Dispose(); int c = myDataSet.Tables[0].Rows.Count; if (c == 0) { MessageBox.Show("没有满足条件的记录"); return; } string sexStr; for(int i = 0;i < c; i++) { SqlCommand cmm = new SqlCommand ( " INSERT INTO hl_Student " + "( " + " DID," + " SName," + " StuNo," + " Class," + " Sex, " + " MobileNum," + " FamilyNo," + " Memo," + " Deleted" + ")" + VALUES (NULL, " + "'" + myDataSet.Tables[0].Rows[i]["姓名"].ToString() + "', " + "'" + myDataSet.Tables[0].Rows[i]["学号"].ToString() + "', " + "'', " + " " + (bool.Parse(sexStr)?"0":"1") + "," + "'" + myDataSet.Tables[0].Rows[i]["手机号码"].ToString() + "', " + "'', " + "'', " + " 0)" ,sc ); cmm.ExecuteNonQuery(); cmm = new SqlCommand ("select @@identity from hl_Student" ,sc ); object newid = cmm.ExecuteScalar(); cmm.Dispose(); }}MessageBox.Show("成功导入!"); return; } catch(Exception ex) { MessageBox.Show(ex.ToString()); return; } finally { if (sc.State == System.Data.ConnectionState.Open) sc.Close(); } 我写的win程序的,但是web也一样,先上传再导 listview更改数据源 请教 怎么获取checkbox选定的值 关于页面播放视频的问题 请求帮助!怎样在ie中实现编辑word然后提交到数据库 请教高手一个问题。 未将对象引用设置到对象的实例。 AspnetMenu怎么改变背景色和字体颜色?? 请大家帮忙看看错再哪了,谢谢! 如何使FLASH达到如下的功能? datagrid多行加表制过程中一个百思不得其解的问题,请思归等专家帮忙看一下。 用户登录后需要把他的所有权限放入session吗? EventLog.CreateEventSource("ThePhile.COM", "Application")出现错误
string filename = filepath.Substring(filepath.LastIndexOf("\\") + 1); if (filename.Substring(filename.LastIndexOf(".") + 1).ToUpper() != "XLS")
{
MessageBox.Show("请选择正确的文件");
return;
}System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection(
System.Configuration.ConfigurationSettings.AppSettings["ConnString"]
);
try
{
sc.Open();
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
" Data Source=" + filepath.Replace(@"\",@"\\") + ";" +
" Extended Properties=Excel 8.0;";OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM fff", conn);
myCommand.SelectCommand.Prepare();
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet);
conn.Dispose();
myCommand.Dispose(); int c = myDataSet.Tables[0].Rows.Count;
if (c == 0)
{
MessageBox.Show("没有满足条件的记录");
return;
}
string sexStr;
for(int i = 0;i < c; i++)
{
SqlCommand cmm = new SqlCommand (
" INSERT INTO hl_Student " +
"( " +
" DID," +
" SName," +
" StuNo," +
" Class," +
" Sex, " +
" MobileNum," +
" FamilyNo," +
" Memo," +
" Deleted" +
")" +
VALUES (NULL, " +
"'" + myDataSet.Tables[0].Rows[i]["姓名"].ToString() + "', " +
"'" + myDataSet.Tables[0].Rows[i]["学号"].ToString() + "', " +
"'', " +
" " + (bool.Parse(sexStr)?"0":"1") + "," +
"'" + myDataSet.Tables[0].Rows[i]["手机号码"].ToString() + "', " +
"'', " +
"'', " +
" 0)"
,sc
);
cmm.ExecuteNonQuery();
cmm = new SqlCommand ("select @@identity from hl_Student"
,sc
);
object newid = cmm.ExecuteScalar();
cmm.Dispose();
}
}MessageBox.Show("成功导入!");
return;
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
return;
}
finally
{
if (sc.State == System.Data.ConnectionState.Open)
sc.Close();
}