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);
}
}
