<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div class="p_nav">
<asp:Label ID="lblNav" runat="server" Text="系统首页 >> 基层单位管理 >> 法制宣传志愿者管理"></asp:Label>
</div>
<div style="float: left; margin-top: 5px; margin-bottom: 5px; margin-left: 5px">
<asp:FileUpload ID="File1" runat="server" />
<asp:Button ID="btnUp" runat="server" OnClick="btnUp_Click" Text="上传" />
<asp:Button ID="btnInsert" runat="server" OnClick="btnInsert_Click" Text="导入" />
<asp:Button ID="btnBack" runat="server" OnClick="btnBack_Click"
Text="返回" />
<a href="http://www.cnblogs.com/Resources/UploadExecl/Temp/法制宣传教育志愿者导入模板.xls" target="_blank">法制宣传志愿者导入数据模版下载</a>
<br />
<asp:Label ID="lblInfo" runat="server" Style="color: Red; font-weight: bold;"></asp:Label>
<br />
<asp:GridView ID="GridView1" runat="server" CssClass="black" Width="100%" OnRowDataBound="GridView1_RowDataBound">
</asp:GridView>
</div>
</form>
</body>
</html>
public static string address;
public static int count, cell1;
public static bool sf;
public static readonly string tablename = "Volunteer";
public static System.Data.DataSet objDataset1;
public static List<M.CjVolunteer> list = new List<M.CjVolunteer>();
private M.Message msg = new M.Message();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
btnInsert.Enabled = false;
}
}
public int ExcleBand(string name)
{
/*----------------取EXCLE表中的值-----------*/
String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + name + ";" +
"Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
OleDbConnection objConn = new OleDbConnection(sConnectionString);
// Open connection with the database.
objConn.Open();
try
{
string sell = "SELECT * FROM [Sheet1$] ";
OleDbCommand objCmdSelect = new OleDbCommand(sell, objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
objDataset1 = new DataSet();
objAdapter1.Fill(objDataset1, "aaa");
// Bind data to DataGrid control.
GridView1.DataSource = objDataset1.Tables["aaa"].DefaultView;
GridView1.DataBind();
}
catch
{ }
// Clean up objects.
cell1 = objDataset1.Tables[0].Columns.Count;
objConn.Close();
return objDataset1.Tables[0].Rows.Count;
}
protected void btnUp_Click(object sender, EventArgs e)
{
list = new List<M.CjVolunteer>();
// File1.MaxLength = File1.PostedFile.ContentLength;
int Length = File1.PostedFile.ContentLength;
string name = File1.PostedFile.FileName;
name = name.Substring(name.LastIndexOf(".") + 1);//取扩展名
// HxDataPage1.Visible = false;
if (Length > 0 && (name.ToLower() == "xls" || name.ToLower() == "xlsx"))
{
try
{
string y, j;
y = Server.MapPath("http://www.cnblogs.com/Resources/UploadExecl/" + tablename + "/"); //取虚拟路径
j = Path.GetFileName(DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");//只取本文件名
j = Path.Combine(y, j);//合并路径
address = j;
File1.PostedFile.SaveAs(j);//存储文件
try
{
count = ExcleBand(j);
ResponseScript("alert('上传成功,请核对后再导入数据')");
}
catch
{
ResponseScript("alert('请核对EXCLE文件中格式是否正确')");
}
}
catch
{
ResponseScript("alert('上传失败');");
}
}
else
{
ResponseScript("alert('输入上传EXCLE文件，并确定上传的EXCLE文件是根据模版文件进行数据编辑的');");
}
}
public string xslstr(string name, int row, int cl)
{
return objDataset1.Tables[0].Rows[row].ItemArray[cl].ToString().Trim(); ;
}
protected void btnInsert_Click(object sender, EventArgs e)
{
B.CjVolunteerBLL bll = new B.CjVolunteerBLL();
foreach (M.CjVolunteer obj in list)
{
bll.SaveOrUpdate(obj);
}
ResponseScript("alert('导入成功');");
list = new List<M.CjVolunteer>();
btnInsert.Enabled = false;
}
protected void btnBack_Click(object sender, EventArgs e)
{
Response.Redirect("Volunteer_List.aspx");
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
//鼠标移到GridView某一行时改变该行的背景色方法二
if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Height = Unit.Pixel(22);
string info = "";
M.CjVolunteer obj = new M.CjVolunteer();
if (e.Row.RowIndex == 0)
{
e.Row.Font.Bold = true;
e.Row.Style.Add("background-color", "#A0A0A0");
}
else
{
info = "";
#region 验证导入的数据
string phone=B.CommonBLL.HtmlTrim(e.Row.Cells[3].Text);
if ( phone== "")
{
info = "移动电话不能为空!";
}
else
{
string pattern = @"(^189\d{8}$)|(^13\d{9}$)|(^15\d{9}$)";
if (System.Text.RegularExpressions.Regex.IsMatch(phone, pattern)) //正则表达式匹配
{
List<M.CjVolunteer> clist = new B.CjVolunteerBLL().GetListByPhone(phone);
if (clist.Count > 0)
{
info = "根据移动电话匹配数据库已存在该人员信息!";
}
else
{
obj.Id = Guid.NewGuid().ToString();
obj.Position = "qt";
obj.Mobile = phone;
obj.Company = B.CommonBLL.HtmlTrim(e.Row.Cells[4].Text);
obj.InService = B.CommonBLL.HtmlTrim(e.Row.Cells[5].Text);
obj.OrgCode = this.CurrentUserInfo.UserOrg[0].ORGCODE;
obj.OrgName = this.CurrentUserInfo.UserOrg[0].ORGNAME;
obj.ParentOrgCode = AppCode.UserCenter.GetOrgInfo(this.CurrentUserInfo.UserOrg[0].ORGCODE).PARORGCODE;
obj.CreateCode = this.CurrentUserInfo.USERCODE;
obj.CreateName = this.CurrentUserInfo.USERNAME;
obj.CreateTime = DateTime.Now;
obj.DelStatus = 0;
}
}
else
{
info = "移动电话格式不正确!";
}
}
string name = B.CommonBLL.HtmlTrim(e.Row.Cells[1].Text);
if (name == "")
{
info = "姓名不能为空!";
}
else
{
obj.Name = name;
}
string sex = B.CommonBLL.HtmlTrim(e.Row.Cells[2].Text);
if (sex == "")
{
info = "性别不能为空!";
}
else
{
if (sex == "男")
{
obj.Sex = 0;
}
else if (sex == "女")
{
obj.Sex = 1;
}
else
{
info = "性别输入格式不正确!";
}
}
#endregion
if (info == "")
{
btnInsert.Enabled = true;
list.Add(obj);
//鼠标经过时，行背景色变
e.Row.Attributes.Add("onmouseover", "this.style.backgroundColor='#E6F5FA'");
//鼠标移出时，行背景色变
e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor='#FFFFFF'");
}
else
{
lblInfo.Text = "红色行的数据不正确，不能导入。错误信息为：" + info;
//鼠标经过时，行背景色变
e.Row.Attributes.Add("onmouseover", "this.style.backgroundColor='#E6F5FA'");
//鼠标移出时，行背景色变
e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor='#ff0000'");
e.Row.Style.Add("background-color", "#ff0000");
e.Row.Attributes.Add("title", info);
}
}
}
else
e.Row.Visible = false;
}
