public string AddSchedul(ScheduleProperty Model)
{
string Str = "匯入成功";
var AddSchedul = new Models.ScheduleSetting()
{
Date = Model.WorkDate,
EmpName = Model.EmpName,
DayWeek = Model.DayWeek,
PoNo = Model.PoNo,
WorkShift = Model.WorkShift,
ScheduleID = Guid.NewGuid(),
WorkGroup = Model.WorkGroup
};
_db.MXIC_ScheduleSettings.Add(AddSchedul);
_db.SaveChanges();
return(Str);
}
public string ImportSchedul(HttpPostedFileBase file)
{
string str = "匯入成功";
try
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
if (file != null && file.ContentLength > 0)
{
//年,月,PO號碼
string Year, Month, PoNo;
//以下是讀檔
using (var excelPkg = new ExcelPackage(file.InputStream))
{
ExcelWorksheet sheet = excelPkg.Workbook.Worksheets["出勤班表"]; //取得Sheet
int startRowIndex = sheet.Dimension.Start.Row + 5; //起始列
int endRowIndex = sheet.Dimension.End.Row; //結束列
int startColumn = sheet.Dimension.Start.Column; //開始欄
int endColumn = sheet.Dimension.End.Column; //結束欄
Year = sheet.Cells[3, 14].Text;
Month = sheet.Cells[3, 17].Text;
PoNo = sheet.Cells[3, 2].Text;
//清除當月資料
CleanSchedul(Year, Month, PoNo);
for (int currentRow = startRowIndex; currentRow <= endRowIndex; currentRow++)
{
//排班組別,姓名,班別,上班日期,星期
string WorkGroup = "", EmpName = "", WorkShift = "", workdate = "", DayWeek = "";
for (int currentColumn = startColumn; currentColumn <= endColumn; currentColumn++)
{
if (string.IsNullOrWhiteSpace(sheet.Cells[currentRow, 1].Text))
{
continue;
}
if (currentColumn == 1)
{
WorkGroup = sheet.Cells[currentRow, currentColumn].Text;
}
if (currentColumn == 2)
{
EmpName = sheet.Cells[currentRow, currentColumn].Text;
}
if (currentColumn >= 3)
{
if (!string.IsNullOrEmpty(sheet.Cells[4, currentColumn].Text) && !string.IsNullOrWhiteSpace(sheet.Cells[4, currentColumn].Text))
{
WorkShift = sheet.Cells[currentRow, currentColumn].Text;
DayWeek = "星期" + sheet.Cells[5, currentColumn].Text;
workdate = Year + '/' + Month + '/' + sheet.Cells[4, currentColumn].Text;
ScheduleProperty model = new ScheduleProperty();
model.PoNo = PoNo;
model.WorkDate = Convert.ToDateTime(workdate);
model.DayWeek = DayWeek;
model.EmpName = EmpName;
model.WorkShift = WorkShift;
model.WorkGroup = WorkGroup;
//新增進資料庫
str = AddSchedul(model);
}
}
}
}
}
}
else
{
str = "請先選擇匯入檔案!";
}
}
catch (Exception ex)
{
//str = ex.ToString();
str = "匯入檔案時發生錯誤!";
}
return(str);
}
SetValue(ScheduleProperty, value);
