Excel 表导入数据
#region inExl
private DataSet inExl(string filenameurl, string table)
{
DataSet ds = new DataSet();
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filenameurl + ";Extended Properties='Excel 8.0';";
OleDbConnection conn1 = new OleDbConnection(strConn);
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn1);
odda.Fill(ds, table);
}
catch
{
}
return ds;
}
#endregion
private void UpFile()
{
try
{
newname = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString();
filepath = Server.MapPath("http://www.cnblogs.com/Upload/") + newname + ".xls";
fileup.PostedFile.SaveAs(filepath);
}
catch
{
Response.Write("<script>alert('请正确操作。')</script>");
return;//当选择的不是Excel文件时,返回
}
}
private void ExcelCommon()
{
//导入
int counta = 0; int countb = 0;
UpFile();
DataSet ds = inExl(filepath, newname);
int count = Convert.ToInt32(ds.Tables[0].Rows.Count);
if (count == 0)
{
Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
}
else
{
try
{
for (int i = 0; i < count; i++)
{
int countc = 0;
string user_name = ds.Tables[0].Rows[i][0].ToString();
string user_pwd = "123456";
string user_realname = ds.Tables[0].Rows[i][2].ToString();
string user_depid = this.user_depid.SelectedValue;
string user_powerid = this.user_powerid.SelectedValue;
string user_sex = ds.Tables[0].Rows[i][1].ToString();
string user_age = ds.Tables[0].Rows[i][3].ToString();
string user_telephone = ds.Tables[0].Rows[i][5].ToString();
string user_address = ds.Tables[0].Rows[i][6].ToString();
string user_love = "";
string user_briday = ds.Tables[0].Rows[i][4].ToString();
string user_Email = ds.Tables[0].Rows[i][7].ToString();
string user_qq = ds.Tables[0].Rows[i][8].ToString();
if (user_name.Length > 24) { countc = countc + 1; }
if (user_pwd.Length > 24) { countc = countc + 1; }
if (user_realname.Length > 24) { countc = countc + 1; }
if (user_depid == "0") { countc = countc + 1; }
if (user_powerid == "0") { countc = countc + 1; }
if (user_age.Length > 4) { countc = countc + 1; }
if (user_telephone.Length > 24) { countc = countc + 1; }
if (user_address.Length > 120) { countc = countc + 1; }
if (user_briday.Length > 24) { countc = countc + 1; }
if (user_Email.Length > 48) { countc = countc + 1; }
if (user_qq.Length > 24) { countc = countc + 1; }
if (countc > 0)
{
countb = countb + 1;
}
else
{
BLL.UserBLL.FMCRM_System_User userinforbll = new BLL.UserBLL.FMCRM_System_User();
Model.UserModel.FMCRM_System_User userinformodel = new Model.UserModel.FMCRM_System_User();
userinformodel.user_pwd = FormsAuthentication.HashPasswordForStoringInConfigFile(user_pwd, "MD5");
int userid = Int32.Parse(Request.Cookies["FMCRM"]["code"].ToString());
userinformodel.user_name = user_name; userinformodel.user_id = userid;
userinformodel.user_realname = user_realname; userinformodel.user_depid = Int32.Parse(user_depid); userinformodel.user_powerid = Int32.Parse(user_powerid);
userinformodel.user_sex = Int32.Parse(user_sex); userinformodel.user_age = Int32.Parse(user_age); userinformodel.user_telephone = user_telephone;
userinformodel.user_address = user_address; userinformodel.user_love = user_love; userinformodel.user_briday = user_briday; userinformodel.user_Email = user_Email;
userinformodel.user_qq = user_qq;
if (userinforbll.selectmorebyusername(userinformodel).Tables[0].Rows.Count == 0)
{
if (userinforbll.Add(userinformodel) > 0)
{
counta = counta + 1;
}
else
{
countb = countb + 1;
}
}
else
{
countb = countb + 1;
}
}
}
}
catch
{
Response.Write("<script>alert('导入Excel文件不规范')</script>");
}
Response.Write("<script>alert('成功导入" + counta + "条记录,导入失败" + countb + "条记录')</script>");
ClientScript.RegisterClientScriptBlock(this.GetType(), "FF", "<script>Istest1();</script>");
}
}
//按钮点击事件，调用导入方法
protected void btnfujiaExl_Click(object sender, EventArgs e)
{
if (Request.Cookies["FMCRM"] == null)
{
ClientScript.RegisterStartupScript(this.GetType(), "FF", "<script>alert('请登录！');top.window.location.href='http://www.cnblogs.com/Default.aspx';</script>");
}
else
{
string strpath = fileup.PostedFile.FileName.ToString();
if (strpath != "")
{
string IsXls = Path.GetExtension(fileup.FileName).ToString().ToLower();
if (IsXls != ".xls")
{
Response.Write("<script>alert('只可以选择Excel文件')</script>");
return;//当选择的不是Excel文件时,返回
}
else
{
ExcelCommon();
}
}
else
{
Response.Write("<script>alert('请先选择Excel文件！')</script>");
}
}
}
