protected void Button1_Click(object sender, EventArgs e)
{
string fianlfilename = upload();
show(fianlfilename);
string strSQL = "";
DataTable dt=show(fianlfilename);
for (int i = 0; i <dt.Rows.Count; i++)
{
strSQL +="insert into test1 values('" + dt.Rows[i][0].ToString () + "','" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "')";
}
INSERT(strSQL);
}
private string upload()
{
HttpPostedFile file = FileUpload1.PostedFile;
string filename = file.FileName;//文件名+文件路径
filename = System.IO.Path.GetFileName(filename);//提取filename中的文件名(不带路径)
string serverfilepath=Server.MapPath(".")+"//wenjian//";//获得服务器保存的文件夹路径
string fianlfilename=serverfilepath+filename;
file.SaveAs(fianlfilename);
return fianlfilename;
}
private DataTable show(string strpath)
{
string strConn = "provider=Microsoft.JET.OLEDB.4.0;Data Source='"+strpath+"';Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";
//string strConn = "Provider=Microsolf.JET.OLEDB.4.0;Data Source='D:\123.xlsx';Extended Properties=Excel 8.0;";
//string strConn = "Provider=Microsolf.ACE.OLEDB.12.0;Data Source='D:\Book1.xls';Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strSQL = "select * from[Sheet1$]";
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, conn);
DataTable dt = new DataTable();
da.Fill(dt);
conn.Close();
GridView1.DataSource = dt;
GridView1.DataBind();
return dt;
}
private void INSERT(string strSQL)
{
string strConn = "data source=DESKTOP-JAUDH78\\QWERT;initial catalog=KAOQINXITONG;uid=sa;password=123456;";
SqlConnection conn = new SqlConnection(strConn);
conn.Open();
SqlCommand cmd = new SqlCommand(strSQL,conn);
cmd.ExecuteNonQuery();
conn.Close();
}
}