/// <summary>
/// 将Excel内的数据转化为HolidayChanges对象
/// </summary>
/// <param name="row">Excel行</param>
/// <param name="columnOrder">列名顺序列表</param>
/// <returns>HolidayChanges对象</returns>
public static HolidayChanges GetHolidayChangesFromExcel(IRow row, List <int> columnOrder)
{
HolidayChanges holidayChanges = new HolidayChanges();
holidayChanges.day = DateTime.Parse(row.GetCell(columnOrder[0]).DateCellValue.ToString()).Date;
holidayChanges.type = row.GetCell(columnOrder[1]).ToString();
return(holidayChanges);
}
/// <summary>
/// 将特殊日期信息从Excel表格导入到Mysql数据库
/// </summary>
/// <param name="folder_name">文件夹路径</param>
/// <param name="file_name">文件名</param>
public void ImportHolidayChangesToMySQL(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 <HolidayChanges> holiday_changes_list_original = new List <HolidayChanges>();
List <HolidayChanges> holiday_changes_list = new List <HolidayChanges>();
using (var context = new Context())
{
holiday_changes_list_original = context.HolidayChanges.AsNoTracking().ToList();
//数据库里已经有的数据数量
int alreadyHasCount = 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;
}
HolidayChanges holiday_changes = excelToSQLUtil.GetHolidayChangesFromExcel(row, column_order_list);
//假如已经录入了，就避免再次录入
int alreadyExists = holiday_changes_list_original.Where(x => x.day == holiday_changes.day && x.type == holiday_changes.type).Count();
if (alreadyExists == 0)
{
holiday_changes_list.Add(holiday_changes);
}
else
{
alreadyHasCount++;
}
}
context.HolidayChanges.AddRange(holiday_changes_list);
var count = context.SaveChanges();
Console.WriteLine("此次录入 " + count + " 条数据," + ",重复数据 " + alreadyHasCount + " 条");
}
}
}
