/// <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 + " 条");
}
}
}
