/// <summary> /// 将Excel内的数据转化为SkipEmployee对象 /// </summary> /// <param name="row">Excel行</param> /// <param name="columnOrder">列名顺序列表</param> /// <returns>SkipEmployee对象</returns> public static SkipEmployee GetSkipEmployeeFromExcel(IRow row, List <int> columnOrder) { SkipEmployee skipEmployee = new SkipEmployee(); skipEmployee.card_id = row.GetCell(columnOrder[0]).ToString(); skipEmployee.job_num = row.GetCell(columnOrder[1]).ToString().Trim(); skipEmployee.emp_name = row.GetCell(columnOrder[2]).ToString(); return(skipEmployee); }
/// <summary> /// 将需要跳过的员工信息从Excel表格导入到Mysql数据库 /// </summary> /// <param name="folder_name">文件夹路径</param> /// <param name="file_name">文件名</param> public void ImportSkipEmployeeToMySQL(string folder_name, string file_name) { string path_name = Path.Combine(folder_name, file_name); //检测文件是否存在 if (!excelToSQLUtil.FileExistsOrNot(path_name)) { return; } using (var stream = new FileStream(path_name, FileMode.Open)) { stream.Position = 0; //获取sheet ISheet sheet = excelToSQLUtil.GetSheet(file_name, stream); if (sheet == null) { return; } //获取列名顺序列表 List <int> column_order_list = excelToSQLUtil.GetColumnOrderList(sheet, new List <string> { "卡号", "工号", "姓名" }); if (column_order_list == null) { return; } IRow row = null; List <SkipEmployee> skip_employee_list_original = new List <SkipEmployee>(); List <SkipEmployee> skip_employee_list = new List <SkipEmployee>(); using (var context = new Context()) { skip_employee_list_original = context.SkipEmployee.AsNoTracking().ToList(); //数据库里已经有的数据数量 int already_has_count = 0; for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { row = sheet.GetRow(i); if (row == null) { continue; } if (row.Cells.All(d => d.CellType == CellType.Blank)) { continue; } SkipEmployee skip_employee = excelToSQLUtil.GetSkipEmployeeFromExcel(row, column_order_list); //假如已经录入了,就避免再次录入 int already_exists = skip_employee_list_original.Where(x => x.card_id == skip_employee.card_id && x.job_num == skip_employee.job_num && x.emp_name == skip_employee.emp_name).Count(); if (already_exists == 0) { skip_employee_list.Add(skip_employee); } else { already_has_count++; } } context.SkipEmployee.AddRange(skip_employee_list); var count = context.SaveChanges(); Console.WriteLine("此次录入 " + count + " 条数据," + ",重复数据 " + already_has_count + " 条"); } } }