public void WriteExcel(string fileName, Dictionary <string, string> dic, List <Model.Employee> empList)
{
ExcelHelper excelHelper = null;
try
{
excelHelper = new ExcelHelper(fileName);
foreach (var item in excelHelper.sheets)
{
dynamic range = excelHelper.GetRange(item, Common.EndColumn_Write);
object[,] data = range.Value2;
string employeeName = string.Empty;
Model.Employee emp = null;
for (int i = 1; i <= data.GetLength(0); i++)
{
string morningOrAfternoon = string.Empty;
for (int j = 1; j <= data.GetLength(1); j++)
{
//填充日期
if (i == 1 && j >= int.Parse(Common.StartColumn_WriteWeek) && j < int.Parse(Common.StartColumn_WriteWeek) + Common.TotalDays)
{
string value = dic[(j - int.Parse(Common.StartColumn_WriteWeek) + 1).ToString()];
switch (value)
{
case "六":
excelHelper.SetCellValueWithColor(item, i, j, value, Common.Color.Green);
//修改星期颜色时，把对应的日期颜色也修改
excelHelper.SetCellColor(item, i + 1, j, Common.Color.Green);
break;
case "日":
excelHelper.SetCellValueWithColor(item, i, j, value, Common.Color.Red);
excelHelper.SetCellColor(item, i + 1, j, Common.Color.Red);
break;
default:
excelHelper.SetCellValue(item, i, j, value);
break;
}
}
//填充考勤
else if (i >= int.Parse(Common.StartRow_Write))
{
if (j == 1 && data[i, j] != null)
{
employeeName = data[i, j].ToString().Trim();
emp = empList.FirstOrDefault(e => e.Name == employeeName);
if (emp == null)
{
break;
}
continue;
}
if (emp != null)
{
if (j == 2 && data[i, j] != null)
{
morningOrAfternoon = data[i, j].ToString().Trim();
continue;
}
else if (j >= 3 && j < 3 + Common.TotalDays)
{
Model.Attendance attendance = emp.AttendanceList.Single(A => A.Date == (j - 2).ToString());
if (!attendance.NoAttendance)
{
DateTime?dt = null;
if (morningOrAfternoon == "上午")
{
dt = emp.AttendanceList.Single(A => A.Date == (j - 2).ToString()).Morning;
}
else if (morningOrAfternoon == "下午")
{
dt = emp.AttendanceList.Single(A => A.Date == (j - 2).ToString()).Afternoon;
}
else
{
break;
}
if (dt.HasValue)
{
excelHelper.SetCellValue(item, i, j, "√");
}
//else
// excelHelper.SetCellValue(item, i, j, "√", Common.Color.Red);
}
}
else if (j == 3 + 31)
{
excelHelper.SetCellValue(item, i, j, emp.NormalDays == 0 ? "" : emp.NormalDays.ToString());
}
}
else
{
break;
}
}
}
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
excelHelper.Save();
if (excelHelper != null)
{
excelHelper.Close();
}
}
}
public List <Model.Employee> GetExcelDate_Read2(string fileName)
{
ExcelHelper excelHelper = null;
Model.Employee entity = new Model.Employee();
List <Model.Employee> list = new List <Model.Employee>();
try
{
excelHelper = new ExcelHelper(fileName);
object[,] data = (object[, ])excelHelper.GetData(Common.SheetName_Read2, Common.EndColumn_Read2);
List <Header> headerList = new List <Header>();
for (int i = int.Parse(Common.StartRow_Read2.ToString()); i <= data.GetLength(0); i++)
{
if (i == int.Parse(Common.StartRow_Read2.ToString()))
{
for (int j = 1; j <= data.GetLength(1); j++)
{
if (data[i, j] != null && !headerList.Any(H => H.ColumnName == data[i, j].ToString() && H.ColumnIndex == j))
{
headerList.Add(new Header {
ColumnName = data[i, j].ToString(), ColumnIndex = j
});
}
}
}
else
{
for (int j = 1; j <= data.GetLength(1); j++)
{
if (data[i, j] != null && headerList.Any(H => H.ColumnName == data[i, j].ToString() && H.ColumnIndex == j))
{
break;
}
else if (data[i, j] != null && data[i, j].ToString().Contains("工 号："))
{
entity = new Model.Employee();
entity.Id = data[i, 3].ToString().Trim();
entity.Name = data[i, 11].ToString().Trim();
entity.Department = data[i, 21].ToString().Trim();
list.Add(entity);
break;
}
else if (j <= Common.TotalDays)
{
Model.Attendance attendance = new Model.Attendance(j.ToString(), data[i, j]?.ToString(), 2);
entity.AttendanceList.Add(attendance);
}
}
}
}
return(list);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (excelHelper != null)
{
excelHelper.Close();
}
}
}