<div style="width: 100%; text-align: center;">
<%-- <asp:Image ID="imgUp" runat="server" ImageUrl="~/Images/uploadify_file.png" Style="cursor: pointer;" />--%>
<asp:FileUpload ID="upFile" runat="server" CssClass="txtFile" />
<asp:Button ID="btnUp" runat="server" Text="立即导入" CssClass="rbtn23" OnClick="btnUp_Click" />
</div>
<div id="validSummary" runat="server" class="validSummary">
<asp:Label ID="lblMessage" runat="server"></asp:Label>
</div>
<div style="width: 100%; overflow-x: auto">
<asp:GridView ID="gvCustomerList" runat="server" AutoGenerateColumns="False" CssClass="grid"
DataKeyNames="CustomerID" Caption="已导入客户列表">
<Columns>
<asp:TemplateField HeaderText="客户编号">
<HeaderStyle Width="15%" />
<ItemTemplate>
<a target="_blank" href="CustomerInfo.aspx?CustomerID=<%#Eval("CustomerID").ToString()%>">
<%#Eval("CustomerCode")%>
</a>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="客户名称">
<HeaderStyle Width="20%" />
<ItemTemplate>
<a target="_blank" href="CustomerInfo.aspx?CustomerID=<%#Eval("CustomerID").ToString() %>">
<%#Eval("CustomerName")%>
</a>(<%#Eval("TypeName")%>)
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="证件类型">
<HeaderStyle Width="15%" />
<ItemTemplate>
<%#Eval("CDTTypeName")%>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="证件号码">
<HeaderStyle Width="15%" />
<ItemTemplate>
<%#Eval("CDTCode")%>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="发证机关">
<HeaderStyle Width="15%" />
<ItemTemplate>
<%#Eval("CDTIssue")%>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="联系人">
<HeaderStyle Width="15%" />
<ItemTemplate>
<%#Eval("LinkMan")%>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="联系电话">
<HeaderStyle Width="15%" />
<ItemTemplate>
<%#Eval("LinkPhone")%>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="电话号码">
<HeaderStyle Width="15%" />
<ItemTemplate>
<%#Eval("Phone")%>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="客户所有人">
<ItemTemplate>
<%#Eval("BelongUserName") != null ? Eval("BelongUserName") : "<label style='color: #CDCDCD;'>未锁定</label>"%>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="备注">
<HeaderStyle Width="15%" />
<ItemTemplate>
<%#Eval("Remark")%>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<EmptyDataTemplate>
<span class="noRecord">没有记录!</span>
</EmptyDataTemplate>
</asp:GridView>
</div>
<webdiyer:AspNetPager ID="AspNetPager" runat="server" LastPageText="尾页" NextPageText="下一页"
PrevPageText="上一页" CssClass="pager" PageSize="10" FirstPageText="首页" OnPageChanging="AspNetPager_PageChanging">
</webdiyer:AspNetPager>
using引用
using System.Data.OleDb;
using System.Text;
protected void btnUp_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(this.upFile.FileName) && this.upFile.PostedFile.ContentLength <= 0)
{
lblMessage.Text = "导入错误:未选择文件!";
this.lblMessage.CssClass = BasicParams.MESSAGE_CSSCLASS_ERROR;
this.validSummary.Visible = true;
return;
}
if (this.upFile.FileName.Substring(this.upFile.FileName.Length - 3) != "xls")
{
lblMessage.Text = "导入错误:只能上传xls格式的Excel文档!";
this.lblMessage.CssClass = BasicParams.MESSAGE_CSSCLASS_ERROR;
this.validSummary.Visible = true;
return;
}
StringBuilder msgInfo = new StringBuilder();
string NewName = LogonUser.UserID + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
ViewState["ImpGUID"] = LogonUser.UserID + "_" + DateTime.Now.ToString("yyyyMMddHHmmss");
string SavePath = Server.MapPath("~/Upload_Files/xlsCustomer/" + NewName);
this.upFile.PostedFile.SaveAs(SavePath);
#region 读取Excel内容,写入数据库
//客户名称 客户类型 联系人 联系电话 电话号码 证件类型 证件号码 发证机关 证件地址 备注
try
{
DataSet ds = ExcelSqlConnection(SavePath); //调用自定义方法
DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
lblMessage.Text = "导入错误:Excel表为空表,无数据!";//当Excel表为空时,对用户进行提示
this.lblMessage.CssClass = BasicParams.MESSAGE_CSSCLASS_ERROR;
this.validSummary.Visible = true;
return;
}
for (int i = 0; i < dr.Length; i++)
{
//获取Excel的值,插入到数据库里面
string CustomerName = dr[i]["客户名称"].ToString().Trim();
string LinkMan = dr[i]["联系人"].ToString().Trim();
string LinkPhone = dr[i]["联系电话"].ToString().Trim();
string Phone = dr[i]["电话号码"].ToString().Trim();
string CDType = dr[i]["证件类型"].ToString().Trim();
string CDTCode = dr[i]["证件号码"].ToString().Trim();
string CDTIssue = dr[i]["发证机关"].ToString().Trim();
string CDTAddress = dr[i]["证件地址"].ToString().Trim();
string Remark = dr[i]["备注"].ToString().Trim();
string TypeName = dr[i]["客户类型"].ToString().Trim();
string CDTTypeName = dr[i]["证件类型"].ToString().Trim();
int TypeID = -1;
int CDTTypeID = -1;
//客户名称验证
if (CustomerBL.SelectCount(n => n.CustomerName == CustomerName) > 0)
{ msgInfo.AppendFormat("<br/>第[ {0} ]行,客户名称[ {1} ]已存在!", i + 1, CustomerName); }
else
{
//客户类型验证
if (CustomerTypeBL.SelectOne(n => n.TypeName == TypeName) != null)
{ TypeID = CustomerTypeBL.SelectOne(n => n.TypeName == TypeName).TypeID; }
else
{ msgInfo.AppendFormat("<br/>第[ {0} ]行,客户类型[ {1} ]不存在!", i + 1, TypeName); }
//证件类型验证
if (CDTypeBL.SelectOne(n => n.CDTTypeName == CDTTypeName) != null)
{ CDTTypeID = CDTypeBL.SelectOne(n => n.CDTTypeName == CDTTypeName).CDTTypeID; }
else
{ msgInfo.AppendFormat("<br/>第[ {0} ]行,证件类型[ {1} ]不存在!", i + 1, CDTTypeName); }
//联系号码 - 为空或手机号码或座机号码,则通过验证
if (!(string.IsNullOrEmpty(LinkPhone) || (Validator.CheckMobile(LinkPhone) || Validator.CheckPhonePlus(LinkPhone))))
{
msgInfo.AppendFormat("<br/>第[ {0} ]行,联系号码[ {1} ]格式不正确!", i + 1, LinkPhone);
}
bool phoneValid = true;
if (!string.IsNullOrEmpty(Phone))
{
//电话号码 - 正则,将多个空格替换成单个空格
Phone = System.Text.RegularExpressions.Regex.Replace(Phone, @" +", " ");
string[] inputArr = Phone.Split(' ');
foreach (string item in inputArr)
{
if (!(Validator.CheckMobile(item) || Validator.CheckPhonePlus(item)))
{
msgInfo.AppendFormat("<br/>第[ {0} ]行,电话号码[ {1} ]格式不正确!", i + 1, item);
phoneValid = false;
}
}
//if (!phoneValid)
// Phone = "";
}
rtMD.CustomerInfo entity = new rtMD.CustomerInfo();
entity.CustomerCode = "系统自动生成";
entity.CustomerName = CustomerName.Trim();
entity.BelongUserID = this.LogonUser.UserID;
entity.LinkMan = LinkMan.Trim();
entity.LinkPhone = LinkPhone.Trim();
entity.Phone = Phone.Trim();
entity.CDTCode = CDTCode.Trim();
entity.CDTIssue = CDTIssue.Trim();
entity.Remark = Remark.Trim();
entity.CDTAddress = CDTAddress.Trim();
entity.IsEnable = true;
entity.TypeID = TypeID;
entity.CDTTypeID = CDTTypeID;
entity.CompanyID = this.LogonUser.CompanyID;
entity.CreateUserID = this.LogonUser.UserID;
entity.CreateTime = DateTime.Now;
entity.ImpGUID = ViewState["ImpGUID"].ToString();
CustomerBL.Insert(entity);
this.SetCustomerLock(entity);
}
}
}
catch (Exception)
{
System.IO.File.Delete(SavePath);
lblMessage.Text = "导入错误:数据格式不正确!";
this.lblMessage.CssClass = BasicParams.MESSAGE_CSSCLASS_ERROR;
this.validSummary.Visible = true;
return;
}
#endregion
if (msgInfo.ToString() != "")
{
this.lblMessage.Text = "导入完成,但存在异常:" + msgInfo.ToString();
this.lblMessage.CssClass = BasicParams.MESSAGE_CSSCLASS_REMINDR;
}
else
{
this.lblMessage.Text = "导入完成。";
this.lblMessage.CssClass = BasicParams.MESSAGE_CSSCLASS_VALID;
}
this.validSummary.Visible = true;
BindCustomerList();
}
private void BindCustomerList()
{
this.AspNetPager.RecordCount = CustomerBL.GetCustomerListCount("ImpGUID='" + ViewState["ImpGUID"].ToString() + "'");
this.gvCustomerList.DataSource = CustomerBL.GetCustomerList(this.AspNetPager.PageSize, this.AspNetPager.CurrentPageIndex, "CustomerID DESC", "ImpGUID='" + ViewState["ImpGUID"].ToString() + "'");
this.gvCustomerList.DataBind();
}
protected void AspNetPager_PageChanging(object src, Wuqi.Webdiyer.PageChangingEventArgs e)
{
this.AspNetPager.CurrentPageIndex = e.NewPageIndex;
BindCustomerList();
}
/// <summary>
/// 连接Excel 读取Excel数据 并返回DataSet数据集合
/// </summary>
/// <param name="filepath">Excel服务器路径</param>
/// <param name="tableName">Excel表名称</param>
/// <returns></returns>
public static System.Data.DataSet ExcelSqlConnection(string filepath)
{
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
OleDbConnection ExcelConn = new OleDbConnection(strCon);
try
{
string strCom = string.Format("SELECT * FROM [Sheet1$]");
ExcelConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, ExcelConn);
DataSet ds = new DataSet();
myCommand.Fill(ds);
ExcelConn.Close();
return ds;
}
catch
{
ExcelConn.Close();
return null;
}
}