批量操作

╰半橙微兮° 2022-07-03 14:59 229阅读 0赞

**批量操作**

批量操作挺常用的，在做积分系统过程中，有需要做一个批量加分，需求是这样，下载模板，编辑后将excel导入，实现批量加分。

一、前台效果：

![Center][]

前台实现该弹框的代码：

<div id="importDiv" style="display: none;"> <form action="/McoinBonus/batchBonus" method="post" enctype="multipart/form-data" style="text-align:center;" > <div style="margin-bottom: 50px"></div> <div style="margin-bottom: 20px"> <span class="tip">提示：导入前请先下载EXCEL模板 </span><a class="btn-lit" href="../../ExcelFile/加分模板.xlsx"> <span>点击下载EXCEL模板</span></a> </div> <div style="margin-bottom: 20px"> <input id="FileUpload" type="file" name="files" data-options="prompt:'Choose a file...'" /> </div> </form> </div>

js:

//批量加分 function batchBonus() { $("#importDiv").dialog({ width: 500, height: 250, //modal: true, title: "批量导入", //resizable: true, buttons: [{ id: 'btnAdd', text: '添加', iconCls: 'icon-add', handler: function () { //让表单提交 doImport(); } }, { id: 'btnCancelAdd', text: '取消', iconCls: 'icon-cancel', handler: function () { $("#importDiv").dialog("close"); } }] }); } //确定导入Excel function doImport() { var file = ($("#FileUpload").val()); if (file == "") { $.messager.alert('系统提示', '请选择将要上传的文件!'); return; } else { var stuff = file.match(/^(.*)(\.)(.{1,8})$/)[3]; if (stuff != 'xlsx') { $.messager.alert('警告', '文件类型不正确，请选择.xls文件!', 'warning'); return; } else { $("#importDiv form").submit(); } } }

二、后台核心代码实现：

批量插入：

Controller代码：

DataTable table = new DataTable();
                McoinBonusBLL mcoinBonusBll = new McoinBonusBLL();
                HttpPostedFileBase file = Request.Files["files"];
                string strFileName;
                string strSavePath;
                string ClientPath = AppDomain.CurrentDomain.BaseDirectory + "ExcelFile\\";
                string strPaperId = "sheet1";
    
                if (file == null || file.ContentLength <= 0)
                {
                    ViewBag.error = "文件不能为空";
                    return View();
                }
                else
                {
                    string strFilename = Path.GetFileName(file.FileName);
                    int intFilesize = file.ContentLength;//获取上传文件的大小（单位是字节）
                    string fileEX = System.IO.Path.GetExtension(strFilename);//获取上传文件的扩展名
                    string strNoFileName = System.IO.Path.GetFileNameWithoutExtension(strFilename);//获取上传文件的无扩展名
                    int Maxsize = 4000 * 1024;//定义上传文件的最大空间大小为4M
                    string FileType = ".xls,.xlsx";
    
                    strFileName = strNoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEX;
                    if (!FileType.Contains(fileEX))
                    {
                        ViewBag.error = "文件类型不对，只能导入xls和xlsx格式的文件";
                    }
                    if (intFilesize >= Maxsize)
                    {
                        ViewBag.error = "上传文件超过4M，不能上传";
                    }
                    strSavePath = Path.Combine(ClientPath, strFileName);
                    file.SaveAs(strSavePath);
    
                }
    
                string tablename = "t_addmcoinrecord";
                table = mcoinBonusBll.ExcelToDataTable(strSavePath, strPaperId, tablename);
    
                table.Columns["addRecordID"].ColumnName = "addRecordID";
                table.Columns["年级"].ColumnName = "gradeLevel";
                table.Columns["姓名"].ColumnName = "userID";
                table.Columns["加分"].ColumnName = "addMcoin";
                table.Columns["操作员"].ColumnName = "operateName";
                table.Columns["加分备注"].ColumnName = "note";
                table.Columns["操作时间"].ColumnName = "date";
                            
                DataTable newtable = table.Copy();
                newtable.Clear();
    
                for (int i = 0; i < table.Rows.Count-1; i++)
                {
                    //动态添加GUID
                    table.Rows[i]["addRecordID"] = PublicBean.GetGuId();
    
                    newtable.Rows.Add(table.Rows[i].ItemArray);
    
                }
                //添加加分记录到数据库
                DAL.MySQLHelper.BulkInsert(newtable);

D层代码：

/// <summary> ///大批量数据插入,返回成功插入行数 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="table">数据表</param> /// <returns>返回成功插入行数</returns> public static int BulkInsert(DataTable table) { if (string.IsNullOrEmpty(table.TableName)) throw new Exception("请给DataTable的TableName属性附上表名称"); if (table.Rows.Count == 0) return 0; int insertCount = 0; string tmpPath = Path.GetTempFileName(); string csv = DataTableToCsv(table); File.WriteAllText(tmpPath, csv); using (MySqlConnection conn = GetConnection) { MySqlTransaction tran = null; try { conn.Open(); tran = conn.BeginTransaction(); MySqlBulkLoader bulk = new MySqlBulkLoader(conn) { FieldTerminator = ",", FieldQuotationCharacter = '"', EscapeCharacter = '"', LineTerminator = "\r\n", FileName = tmpPath, NumberOfLinesToSkip = 0, TableName = table.TableName, }; bulk.Columns.AddRange(table.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName).ToList()); insertCount = bulk.Load(); tran.Commit(); } catch (MySqlException ex) { if (tran != null) tran.Rollback(); throw ex; } } File.Delete(tmpPath); return insertCount; } /// <summary> ///将DataTable转换为标准的CSV /// </summary> /// <param name="table">数据表</param> /// <returns>返回标准的CSV</returns> private static string DataTableToCsv(DataTable table) { //以半角逗号（即,）作分隔符，列为空也要表达其存在。 //列内容如存在半角逗号（即,）则用半角引号（即""）将该字段值包含起来。 //列内容如存在半角引号（即"）则应替换成半角双引号（""）转义，并用半角引号（即""）将该字段值包含起来。 StringBuilder sb = new StringBuilder(); DataColumn colum; foreach (DataRow row in table.Rows) { for (int i = 0; i < table.Columns.Count; i++) { colum = table.Columns[i]; if (i != 0) sb.Append(","); if (colum.DataType == typeof(string) && row[colum].ToString().Contains(",")) { sb.Append("\"" + row[colum].ToString().Replace("\"", "\"\"") + "\""); } else sb.Append(row[colum].ToString()); } sb.AppendLine(); } return sb.ToString(); }

批量插入数据后，还需要将另一张表的某个字段更新，在Controller中接着下一部分更新：

//更新用户表中用户分数
                UserManagementBLL userbll = new UserManagementBLL();
                DataTable dtUser = new DataTable();
                dtUser = userbll.GetAllUser();
                //循环遍历两个datatable
                for (int i = 0; i < newtable.Rows.Count; i++)
                {
                    for (int j = 0; j < dtUser.Rows.Count; j++)
    			    {
                        //获取两个datatable中的姓名，这里userID当做姓名来用
                        string s1 = newtable.Rows[i]["userID"].ToString();
                        string s2 = dtUser.Rows[j]["userName"].ToString();
                        //如果相同，则将excel中向数据库加分
                        if (s1==s2)
                        {
                            int coin = Convert.ToInt32(dtUser.Rows[j]["totalMcoin"]);
                            int updatecoin;
                            updatecoin = coin + Convert.ToInt32(newtable.Rows[i]["addMcoin"]);
                            //更新用户表的分数
                            bool flag = userbll.UpdateUser(updatecoin,s1);
                        }
    			    }                
                }
                return null;

到此批量插入是完成了。

大家有什么更好的办法，欢迎提出！

**感谢您的阅读！**

[Center]: /images/20220618/af1db8ff596f4c85a3063f2e41c73c44.png