private void upfile_ServerClick(object sender, System.EventArgs e)
{
if(myFile.PostedFile.FileName!="")
{
string filename=myFile.PostedFile.FileName;
string fileExtName = filename.Substring(filename.LastIndexOf(".")+1,3);
count1 = myFile.PostedFile.ContentLength;
if (count1 == 0||fileExtName !="xls")
{
Response.Write("请选择excel文件!");return;
}
else if (count1 >200000)
{
Response.Write("excel文件过大!"); return;
}
string y = DateTime.Now.Year.ToString();
string m = DateTime.Now.Month.ToString();
string d = DateTime.Now.Day.ToString();
string h = DateTime.Now.Hour.ToString();
string n = DateTime.Now.Minute.ToString();
string s = DateTime.Now.Second.ToString();
string filePath =Server.MapPath("../excel/");
string Filename = y + m + d + h + n + s;
Filename = Filename + "." + fileExtName;
myFile.PostedFile.SaveAs(filePath+Filename);//保存文件
upfile.Visible = false;//上传按钮不可用
//先将EXCEL导入到数据库,一:先把EXCEL导入dateView,二:然后将dateView里的数据导入到数据库里面
//EXCEL 的连接串
string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +filePath+Filename + ";Extended Properties=Excel 5.0;";
//建立EXCEL的连接
OleDbConnection objConn = new OleDbConnection(sConnectionString);
objConn.Open();
OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM [Sheet1$]", objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
DataSet objDataset1 = new DataSet();
objAdapter1.Fill(objDataset1, "XLData");
//dd.DataSource = objDataset1.Tables[0].DefaultView; //测试代码,用来测试是否能读出EXCEL上面的数据
//dd.DataBind();
DataTable dt = objDataset1.Tables[0];
DataView myView = new DataView(dt);
int count=0;//用来记录出错的条数
string errorstr="";
foreach (DataRowView myDrv in myView)
{
count++;
if(user.addtelnumexcel(userid,myDrv[0].ToString().Trim(),myDrv[1].ToString().Trim(),myDrv[2].ToString().Trim(),myDrv[3].ToString().Trim())==0)
{
errorstr+=count+",";
}
}
objConn.Close();
if(errorstr!="")
{
Page.Response.Write("<script>alert('第"+errorstr+"条数据出错,请检查!');</script>");
Response.End();
}
else
{
base.Response.Write("<script language=javascript>alert('提交成功。');</script> ");
Response.End();
}
}
}
public int addtelnumexcel(string uid,string telnum,string tocom,string toarea,string cash) {
int num;
SqlCommand command = new SqlCommand("admin_addexcel", connection);
command.CommandType = CommandType.StoredProcedure;
SqlParameter parameter=new SqlParameter("@uid",SqlDbType.NVarChar,20);
parameter.Value = uid;
command.Parameters.Add(parameter);
SqlParameter parameter1 = new SqlParameter("@telnum", SqlDbType.NVarChar,20);
parameter1.Value = telnum;
command.Parameters.Add(parameter1);
SqlParameter parameter2 = new SqlParameter("@com", SqlDbType.NVarChar,20);
parameter2.Value = tocom;
command.Parameters.Add(parameter2);
SqlParameter parameter3 = new SqlParameter("@area", SqlDbType.NVarChar,20);
parameter3.Value = toarea;
command.Parameters.Add(parameter3);
SqlParameter parameter4 = new SqlParameter("@cash", SqlDbType.Money,4);
parameter4.Value = cash;
command.Parameters.Add(parameter4);
connection.Open();
try
{
num=command.ExecuteNonQuery();
}
catch(SqlException exc)
{ num=0;}
finally
{
connection.Close();
}
return num;
}