欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

按照一定的规格将XML文件中的内容批量导入至数据库中

程序员文章站 2022-06-11 13:54:54
...
一、前台点击按钮
<li>
       asp:Button ID="ImportMember" runat="server" CssClass="btn btn-primary" Text="导入会员" />
</li>
二、后台按钮声明、编辑点击事件(展示的代码为将表格数据写入数据库,注释掉的代码为将表格数据写入txt文档)
protected Button ImportMember;
protected override void OnInitComplete(EventArgs e)
        {
            base.OnInitComplete(e);
            //一键导入会员           
        }
private void ImportMember_Click(object sender, EventArgs e)
        {
            StringBuilder sbr = new StringBuilder();
            DataTable dt = new DataTable();
            dt.Columns.Add("ID", Type.GetType("System.String"));
            dt.Columns.Add("UserId", Type.GetType("System.String"));
            dt.Columns.Add("UserName", Type.GetType("System.String"));
            dt.Columns.Add("CommissionType", Type.GetType("System.String"));
            dt.Columns.Add("Amount", Type.GetType("System.String"));
            dt.Columns.Add("StarTime", Type.GetType("System.String"));
            string filename = System.Web.HttpContext.Current.Server.MapPath(Globals.ApplicationPath + "/myxls.xls");
            //using (FileStream fs = File.OpenRead(filename))
            using (FileStream fs = File.OpenRead(@"c:/myxls.xls"))   //打开myxls.xls文件
            {
                HSSFWorkbook wk = new HSSFWorkbook(fs);   //把xls文件中的数据写入wk中
                for (int i = 0; i < wk.NumberOfSheets; i++)  //NumberOfSheets是myxls.xls中总共的表数
                {
                    ISheet sheet = wk.GetSheetAt(i);   //读取当前表数据
                    for (int j = 0; j <= sheet.LastRowNum; j++)  //LastRowNum 是当前表的总行数
                    {
                        IRow row = sheet.GetRow(j);  //读取当前行数据
                        if (row != null)
                        {
                            DataRow dr = dt.NewRow();
                            //sbr.Append("-------------------------------------\r\n"); //读取行与行之间的提示界限
                            for (int k = 0; k <= row.LastCellNum; k++)  //LastCellNum 是当前行的总列数
                            { 
                                ICell cell = row.GetCell(k);  //当前表格
                                if (cell != null)
                                {
                                    sbr.Append(cell.ToString());   //获取表格中的数据并转换为字符串类型  
                                    
                                    dr[k+1] = cell.ToString(); 
                                }
                            }
                            dt.Rows.Add(dr);
                        }
                    }
                }
            }
            dt.TableName = "Hishop_ceshi";
            MemberDao member = new MemberDao();
            //将新建的表批量高效的存入数据库中
            if (!member.BulkToDB(dt))
            {
                IDictionary<string, string> param = new Dictionary<string, string>();
                param.Add("secCommission", DateTime.Now.ToString());
                param.Add("secCommissionError", "执行失败!");
                //将键值对写入错误日志中
                Globals.WriteLog(param, "", "", "", "SecCommission");
            }
	else
            {
                this.ShowMsg("导入成功", true);
            }
            //sbr.ToString();
            //using (StreamWriter wr = new StreamWriter(new FileStream(@"c:/myText.txt", FileMode.Append)))  //把读取xls文件的数据写入myText.txt文件中
            //{
            //    this.ShowMsg("导入成功", true);
            //    wr.Write(sbr.ToString());
            //    wr.Flush();
            //}
        }
三、其他调用方法
public bool BulkToDB(DataTable dt)
        {
            Boolean rls = false;
            if (dt.Rows.Count == 0)
            {
                return rls;
            }
            SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["HidistroSqlServer"].ConnectionString);
            SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);
            bulkCopy.DestinationTableName = dt.TableName;
            bulkCopy.BatchSize = dt.Rows.Count;
            try
            {
                sqlConn.Open();
                if (dt != null && dt.Rows.Count != 0)
                    bulkCopy.WriteToServer(dt);
                rls = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                sqlConn.Close();
                if (bulkCopy != null)
                    bulkCopy.Close();              
            }
            return rls;
        }
四、引用Npoi.dll文件
详见“读取目标文件”中的dll文件(C:\Users\Administrator\Desktop\ASP.NET基础知识\程序笔记\读取目标文件\bin\.net 4.0)内的三个文件