asp.net OleDb导入Excel，并显示导入内容
来源：互联网 发布：淘宝基金赔付任务 编辑：程序博客网 时间：2024/06/06 03:54
<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; } } 0 0
- asp.net OleDb导入Excel，并显示导入内容
- 用asp.net将Excel导入到数据库并显示
- asp.net中Excel导入（使用微软OLEDB驱动）
- 使用ASP.NET OleDb驱动程序 导入到处EXCEL
- ASP.NET MVC4上传文件，并将excel内容导入数据库，导入后删除文件
- asp.net 导入excel并删除进程
- .net导入Excel 并显示进度条
- ASP.NET Excel导入
- asp.net导入excel
- asp.net excel导入
- ASP.NET EXCEL导入
- 在ASP.NET中将Excel文件中数据导入数据库并显示进度条
- asp.net 将本地excel表导入网页 并在gridview中显示
- asp.net导入EXCEL内容文件到数据库
- 将Excel内容导入数据库(ASP.NET/C#)
- 将Excel内容导入数据库(ASP.NET/C#)
- 将Excel内容导入数据库(ASP.NET/C#)
- (原作)ASP.NET中数据库数据导入Excel并打印
- raspberry pi怎样输出I2S音频
- python 之 subprocess模块
- Source Insight3.5 注册码以及提示 a valid serial number was not entered解决
- NSUserDefaults写入和读取自定义 用户信息类
- 最少拦截系统（贪心+hdu1257）
- asp.net OleDb导入Excel，并显示导入内容
- tolower()
- VCM音圈马达
- [Flashback]Flashback Database闪回数据库实验
- React-Native for Android
- IOS添加pch预编译文件
- Android中Adapter的notifyDataSetInvalidated()和notifyDataSetChanged()的区别！
- tomcat下发布
- 攻击者可能会试图从item.taobao.com窃取您的信息 此服务器无法证明它是item.taobao.com；其安全证书来自*.tmall.com
