public string TransferDataConvert2Html(UserTransferList transfer)
{
StringBuilder result = new StringBuilder();
string name = transfer.StaffName;
string contentRespect = string.Format(_contentRespect, name);
//剔除调休时间为0的数据记录
transfer.UserTransferDetail = transfer.UserTransferDetail.AsEnumerable().Where(r => r.TransferRemainingTime != 0).ToList();
double timeLeft = transfer.UserTransferDetail.Count == 0 ? 0 : transfer.UserTransferDetail.Sum(r => r.TransferRemainingTime);
//数据结构转化为HTML表格
DataTable detail = JsonConvert.DeserializeObject <DataTable>(JsonConvert.SerializeObject(transfer.UserTransferDetail));
string data = TransferTableConvertHtml(detail, transfer.StaffName);
result.Append(contentRespect).Append(newLine).Append(newLine)
.Append(string.Format(_transferContentComment, timeLeft)).Append(newLine).Append(newLine)
.Append(data).Append(newLine)
.Append(_mailSenderSignature);
return(result.ToString());
}
/// <summary>
/// 读取所有的调休excel信息
/// </summary>
public TransferResult ReadTransferFile()
{
var num = this._workBook.NumberOfSheets;
List <UserTransferList> result = new List <UserTransferList>();
while (num > 0)
{
var sheet = this._workBook.GetSheetAt(num - 1);
if (sheet == null)
{
return(new TransferResult()
{
hasError = true,
ErrorMsg = $"错误定位：工作表=>{sheet.SheetName},<br />" +
$"错误信息：当前工作表无数据"
});
}
IRow title = sheet.GetRow(0);
if (!title.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString().Trim().Equals("Analyst Involved"))
{
return(new TransferResult()
{
hasError = true,
ErrorMsg = $"错误定位：工作表=>{sheet.SheetName},<br />" +
$"错误信息：当前工作表数据格式不正确，请确保'A1'单元格为数据起始点，其值为'Analyst Involved'"
});
}
//读取所有调休信息
int cols = title.LastCellNum;
UserTransferList list = new UserTransferList();
var allUsers = new UserService().GetAllUsers();
try
{
for (int row = 1; row < sheet.LastRowNum + 1; row++)
{
IRow sRow = sheet.GetRow(row);
if (sRow == null || sRow.Cells.Count == 0)
{
continue;
}
string name = sRow.GetCell(0) == null ? null : sRow.GetCell(0).ToString();
bool nameExt = name != null && name != ""; //名字单元格不为空
//确保第一个名字单元格存在
if (nameExt)
{
var user = allUsers.Where(r => r.EnglishName.ToLower() == name.ToLower().Trim());
if (user.Count() > 0)
{
var userModel = user.FirstOrDefault();
try
{
//读取所有基本信息详情
var staffRecord = result.Where(r => r.StaffName.ToLower() == name.ToLower());
//读取公式列（剩余调休时间）
var specialCol = sRow.GetCell(6, MissingCellPolicy.CREATE_NULL_AS_BLANK);
double val = 0.0;
if (specialCol.CellType == CellType.Formula)
{
val = specialCol.NumericCellValue;
}
else
{
val = double.Parse(specialCol.ToString().Trim());
}
string date = sRow.GetCell(1, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString().Trim();
if (DateTime.TryParse(date, out DateTime transDate))
{
date = transDate.ToString("yyyy-MM-dd");
}
if (result.Count > 0 && staffRecord.Count() > 0)
{
staffRecord.First().UserTransferDetail.Add(
new TransferDetail()
{
ExtraWorkDate = date,
ExtraWorkPeriod = sRow.GetCell(2, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString().Trim(),
ExtraWorkTime = double.Parse(sRow.GetCell(3, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString().Trim()),
TransferPeriod = sRow.GetCell(4, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString().Trim(),
TransferRemainingTime = val,
MaturityDate = DateTime.Parse(sRow.GetCell(8, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString().Trim()).ToString("yyyy-MM-dd")
});
}
else
{
var detail = new TransferDetail()
{
ExtraWorkDate = date,
ExtraWorkPeriod = sRow.GetCell(2, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString().Trim(),
ExtraWorkTime = double.Parse(sRow.GetCell(3, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString().Trim()),
TransferPeriod = sRow.GetCell(4, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString().Trim(),
TransferRemainingTime = val,
MaturityDate = DateTime.Parse(sRow.GetCell(8, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString().Trim()).ToString("yyyy-MM-dd")
};
var userlist = new UserTransferList()
{
StaffName = name,
StaffEmail = userModel.EmailAddress,
};
userlist.UserTransferDetail.Add(detail);
result.Add(userlist);
}
}
catch (Exception ex)
{
return(new TransferResult()
{
hasError = true,
ErrorMsg = $"错误定位：工作表=>{sheet.SheetName}, 姓名=>{name}, <br />" +
$"错误信息：当前文件字段类型有误，无法转换字段: {ex.Message}" +
$"错误追踪：{ex.StackTrace}"
});
}
}
else
{
return(new TransferResult()
{
hasError = true,
ErrorMsg = $"错误定位：工作表=>{sheet.SheetName}, 姓名=>{name}, <br />" +
"错误信息：当前文件姓名有误，无法匹配数据库员工字段"
});
}
}
//数据读取结束，返回result
}
}
catch (Exception ex)
{
return(new TransferResult()
{
hasError = true,
ErrorMsg = $"错误定位：工作表=>{sheet.SheetName}, <br />" +
$"错误追踪：{ex.StackTrace}"
});
}
num--;
}
return(new TransferResult()
{
hasError = false,
ErrorMsg = "",
result = result
});
}
