public List <AttendanceInfo> Import_Zara(Stream file, string clientCode, int BU) { //get mapping table of desc to timslot Dictionary <string, string> descToTimeslot = new TimeslotMapping().GetDescToTimeslot(); List <AttendanceInfo> attendanceList = new List <AttendanceInfo>(); List <AttendanceInfo> invalidAttendanceList = new List <AttendanceInfo>(); Attendance attendanceObj = new Attendance(); AttendanceInfo tempAttendance; string[] workerInfo; XSSFWorkbook hssfwb = new XSSFWorkbook(file); // ISheet sheet = hssfwb.GetSheet("每日出勤表"); ISheet sheet = hssfwb.GetSheetAt(0); IRow currentRow; //ICell currentCell; string dateFormat = "yyyy-MM-dd"; string datetimeFormat = "yyyy-MM-dd HH:mm"; DateTime firstAttendanceDate = DateTime.MinValue; //get the week day currentRow = sheet.GetRow(3); //the day of month stored in row 4 Dictionary <DateTime, int> dateColMapping = new Dictionary <DateTime, int>(); DateTime workingDate = DateTime.MaxValue; for (int col = 2; col < currentRow.LastCellNum - 1; col = col + 2) { System.Diagnostics.Debug.WriteLine(col); if (currentRow.GetCell(col) == null) { continue; } if (currentRow.GetCell(col).CellType == CellType.Numeric) { dateColMapping.Add(currentRow.GetCell(col).DateCellValue, col); workingDate = currentRow.GetCell(col).DateCellValue; } else if (!string.IsNullOrEmpty(currentRow.GetCell(col).StringCellValue)) { if (currentRow.GetCell(col).StringCellValue == "Grand Total") { break; } workingDate = DateTime.ParseExact(currentRow.GetCell(col).StringCellValue, dateFormat, CultureInfo.InvariantCulture); dateColMapping.Add(workingDate, col); } else if (string.IsNullOrEmpty(currentRow.GetCell(col).StringCellValue)) { break; } } //get the working hours from excel file: data row start at row 8 for (int row = 7; row < sheet.LastRowNum; row++) { System.Diagnostics.Debug.WriteLine(row); currentRow = sheet.GetRow(row); if (currentRow != null) //null is when the row only contains empty cells { if (currentRow.GetCell(0) == null || string.IsNullOrEmpty(currentRow.GetCell(0).StringCellValue)) { break; } foreach (DateTime attendanceDate in dateColMapping.Keys) { System.Diagnostics.Debug.WriteLine(attendanceDate.ToString("ddMMyyyy")); if (currentRow.GetCell(dateColMapping[attendanceDate]) == null) { continue; } tempAttendance = new AttendanceInfo(); tempAttendance.Client = clientCode; tempAttendance.BU = BU; tempAttendance.AttendanceDate = attendanceDate; tempAttendance.ClientStaffNo = currentRow.GetCell(0).StringCellValue; if (tempAttendance.ClientStaffNo == "Grand Total") { break; } if (!descToTimeslot.ContainsKey(currentRow.GetCell(1).StringCellValue)) { tempAttendance.Remarks = string.Format("Unknown timeslot: {0}", currentRow.GetCell(1).StringCellValue); invalidAttendanceList.Add(tempAttendance); continue; } tempAttendance.TimeSlot = descToTimeslot[currentRow.GetCell(1).StringCellValue]; tempAttendance.Hours = currentRow.GetCell(dateColMapping[attendanceDate]).NumericCellValue; //after 22:00 if (currentRow.GetCell(dateColMapping[attendanceDate] + 1) != null) { tempAttendance.OTHours = currentRow.GetCell(dateColMapping[attendanceDate] + 1).NumericCellValue; } else { tempAttendance.OTHours = 0; } if (tempAttendance.Hours == 0) { continue; } //get WorkerID /Gender/ PositionGrade workerInfo = attendanceObj.GetWorkderID(clientCode, BU, tempAttendance.ClientStaffNo); if (workerInfo == null) { tempAttendance.Remarks = "Worker Not Found"; invalidAttendanceList.Add(tempAttendance); continue; } else { tempAttendance.WorkerID = workerInfo[0]; tempAttendance.Gender = workerInfo[1]; tempAttendance.PositionGrade = workerInfo[2]; } attendanceList.Add(tempAttendance); } } } if (invalidAttendanceList.Count == 0) { return(attendanceObj.BatchInsert(attendanceList)); } else { return(invalidAttendanceList); } }
public List <AttendanceInfo> Import_Nike(Stream file, string clientCode, int BU) { //get mapping table of desc to timslot Dictionary <string, string> descToTimeslot = new TimeslotMapping().GetDescToTimeslot(); List <AttendanceInfo> attendanceList = new List <AttendanceInfo>(); List <AttendanceInfo> invalidAttendanceList = new List <AttendanceInfo>(); Attendance attendanceObj = new Attendance(); AttendanceInfo tempAttendance; string[] workerInfo; XSSFWorkbook hssfwb = new XSSFWorkbook(file); // ISheet sheet = hssfwb.GetSheet("每日出勤表"); ISheet sheet = hssfwb.GetSheetAt(0); IRow currentRow; //ICell currentCell; string dateFormat = "yyyy-MM-dd"; string datetimeFormat = "yyyy-MM-dd HH:mm"; DateTime firstAttendanceDate = DateTime.MinValue; try { currentRow = sheet.GetRow(1); firstAttendanceDate = DateTime.ParseExact(currentRow.GetCell(0).StringCellValue, dateFormat, CultureInfo.InvariantCulture); } catch { } if (firstAttendanceDate != DateTime.MinValue) { for (int row = 1; row <= sheet.LastRowNum; row++) { //System.Diagnostics.Debug.WriteLine(row); currentRow = sheet.GetRow(row); if (currentRow != null) //null is when the row only contains empty cells { //考勤日期 小組 員工編號 員工姓名 班次 扣減工時 (分鐘) 上班 下班 考勤工時 考勤工時2 加班 加班2 Total hrs 備注 hrs 22:00後 if (currentRow.GetCell(0) == null || string.IsNullOrEmpty(currentRow.GetCell(0).StringCellValue)) { break; } if (currentRow.GetCell(2) == null) { continue; } tempAttendance = new AttendanceInfo(); System.Diagnostics.Debug.WriteLine(currentRow.GetCell(0).StringCellValue); tempAttendance.ClientStaffNo = currentRow.GetCell(2).StringCellValue; //get WorkerID /Gender/ PositionGrade workerInfo = attendanceObj.GetWorkderID(clientCode, BU, tempAttendance.ClientStaffNo); tempAttendance.AttendanceDate = DateTime.ParseExact(currentRow.GetCell(0).StringCellValue, dateFormat, CultureInfo.InvariantCulture); tempAttendance.Client = clientCode; tempAttendance.BU = BU; tempAttendance.ClientStaffName = currentRow.GetCell(3).StringCellValue; tempAttendance.Remarks = currentRow.GetCell(4).StringCellValue; tempAttendance.TimeSlot = descToTimeslot[currentRow.GetCell(4).StringCellValue]; tempAttendance.Hours = currentRow.GetCell(12).NumericCellValue; if (workerInfo == null) { tempAttendance.Remarks = "Worker Not Found"; invalidAttendanceList.Add(tempAttendance); continue; } else { tempAttendance.WorkerID = workerInfo[0]; tempAttendance.Gender = workerInfo[1]; tempAttendance.PositionGrade = workerInfo[2]; } //time in if (currentRow.GetCell(6).CellType == CellType.Numeric) { tempAttendance.TimeIn = currentRow.GetCell(6).DateCellValue; } else if (!string.IsNullOrEmpty(currentRow.GetCell(6).StringCellValue)) { tempAttendance.TimeIn = DateTime.ParseExact(currentRow.GetCell(0).StringCellValue + " " + getTimeInExceptionInput(currentRow.GetCell(6).StringCellValue), datetimeFormat, CultureInfo.InvariantCulture); } //time out if (currentRow.GetCell(7).CellType == CellType.Numeric) { tempAttendance.TimeIn = currentRow.GetCell(7).DateCellValue; } else if (!string.IsNullOrEmpty(currentRow.GetCell(7).StringCellValue)) { tempAttendance.TimeOut = DateTime.ParseExact(currentRow.GetCell(0).StringCellValue + " " + getTimeInExceptionInput(currentRow.GetCell(7).StringCellValue), datetimeFormat, CultureInfo.InvariantCulture); } attendanceList.Add(tempAttendance); } } } else { //get the week day currentRow = sheet.GetRow(1); Dictionary <DateTime, int> dateColMapping = new Dictionary <DateTime, int>(); DateTime workingDate = DateTime.MaxValue; for (int col = 3; col < currentRow.LastCellNum - 1; col++) { System.Diagnostics.Debug.WriteLine(col); if (currentRow.GetCell(col) == null) { continue; } if (currentRow.GetCell(col).CellType == CellType.Numeric) { dateColMapping.Add(currentRow.GetCell(col).DateCellValue, col); workingDate = currentRow.GetCell(col).DateCellValue; } else if (!string.IsNullOrEmpty(currentRow.GetCell(col).StringCellValue)) { if (currentRow.GetCell(col).StringCellValue == "Grand Total") { break; } workingDate = DateTime.ParseExact(currentRow.GetCell(col).StringCellValue, dateFormat, CultureInfo.InvariantCulture); dateColMapping.Add(workingDate, col); } } //get the working hours from excel file for (int row = 3; row < sheet.LastRowNum; row++) { System.Diagnostics.Debug.WriteLine(row); currentRow = sheet.GetRow(row); if (currentRow != null) //null is when the row only contains empty cells { if (currentRow.GetCell(1) == null || string.IsNullOrEmpty(currentRow.GetCell(1).StringCellValue)) { continue; } foreach (DateTime attendanceDate in dateColMapping.Keys) { System.Diagnostics.Debug.WriteLine(attendanceDate.ToString("ddMMyyyy")); if (currentRow.GetCell(dateColMapping[attendanceDate]) == null) { continue; } tempAttendance = new AttendanceInfo(); tempAttendance.Client = clientCode; tempAttendance.BU = BU; tempAttendance.AttendanceDate = attendanceDate; tempAttendance.ClientStaffNo = currentRow.GetCell(1).StringCellValue; if (!descToTimeslot.ContainsKey(currentRow.GetCell(2).StringCellValue)) { tempAttendance.Remarks = string.Format("Unknown timeslot: {0}", currentRow.GetCell(2).StringCellValue); invalidAttendanceList.Add(tempAttendance); continue; } tempAttendance.TimeSlot = descToTimeslot[currentRow.GetCell(2).StringCellValue]; tempAttendance.Hours = currentRow.GetCell(dateColMapping[attendanceDate]).NumericCellValue; if (tempAttendance.Hours == 0) { continue; } //get WorkerID /Gender/ PositionGrade workerInfo = attendanceObj.GetWorkderID(clientCode, BU, tempAttendance.ClientStaffNo); if (workerInfo == null) { tempAttendance.Remarks = "Worker Not Found"; invalidAttendanceList.Add(tempAttendance); continue; } else { tempAttendance.WorkerID = workerInfo[0]; tempAttendance.Gender = workerInfo[1]; tempAttendance.PositionGrade = workerInfo[2]; } attendanceList.Add(tempAttendance); } } } } if (invalidAttendanceList.Count == 0) { return(attendanceObj.BatchInsert(attendanceList)); } else { return(invalidAttendanceList); } }