public string InsertUserDetail(List <UserInfo> data, string username)
{
var result = "";
try
{
foreach (var item in data)
{
//判断是否再继续报名
var listuserdatail = new List <ExamUserDetailInfo>();
listuserdatail = Data.ExamUserDetailInfo.Get_All_ExamUserDetailInfo(new { UserCode = item.UserCode, SubjectName = item.SubjectName, IsStop = false });
if (listuserdatail != null && listuserdatail.Count() > 0)
{
result += "此工号已报名,不可重复报名";
}
else
{
ExamUserDetailInfo v = new ExamUserDetailInfo();
v.UserCode = item.UserCode;
v.UserName = item.UserName;
v.DepartCode = item.DepartCode;
v.PostName = item.PostName;
v.RankName = item.RankName;
v.SkillName = item.SkillLevel; //本职等技能G1
v.EntryDate = item.EntryDate; //入职日期
v.Achievement = item.Achievement; //绩效
v.PracticeScore = item.PracticalID;
v.PlanExamDate = item.PlanExamDate;
v.ExamStatus = "HrSignUp";
v.IsReview = item.IsReview;
v.RuleName = item.RuleName;
v.TypeName = item.TypeName;
v.ApplyLevel = item.ApplicationLevel; //本次申请等级满级
v.IsAchievement = item.IsAchment; //是否符合绩效
v.HighestLevel = item.HighestTestSkill; //最高可考技能
v.IsExam = "false";
v.IsUserExam = item.IsUserExam;
v.HrCreateUser = username;
v.HrCreateDate = DateTime.Now;
v.SubjectName = item.SubjectName;
Data.ExamUserDetailInfo.Insert_ExamUserDetailInfo(v, null, new string[] { "ID" });
result += "已报名成功!";
}
}
;
}
catch (Exception ex)
{
throw;
}
return(result);
}
public MemoryStream GetPageExcelInfo(string UserCode, string SubjectName, string DepartCode, string ExamDate)
{
try
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
var model = new ExamUserDetailInfo();
var c = Data.ExamUserDetailInfo.Get_All_ExamUserGetDetailInfo(UserCode, SubjectName, ExamDate, DepartCode);
//获取list数据
var tlst = c;
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("工号");
row1.CreateCell(1).SetCellValue("姓名");
row1.CreateCell(2).SetCellValue("部门");
row1.CreateCell(3).SetCellValue("科目");
row1.CreateCell(4).SetCellValue("理论成绩");
row1.CreateCell(5).SetCellValue("职称");
row1.CreateCell(6).SetCellValue("职等");
row1.CreateCell(7).SetCellValue("入职日期");
row1.CreateCell(8).SetCellValue("本职等技能");
row1.CreateCell(9).SetCellValue("目前技能等级");
row1.CreateCell(10).SetCellValue("考试时间");
for (int i = 0; i < tlst.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(tlst[i].UserCode); //工号
rowtemp.CreateCell(1).SetCellValue(tlst[i].UserName); //姓名
rowtemp.CreateCell(2).SetCellValue(tlst[i].DepartCode); //部门
rowtemp.CreateCell(3).SetCellValue(tlst[i].SubjectName); //科目
rowtemp.CreateCell(4).SetCellValue(tlst[i].ExamScore.ToString()); //理论成绩
rowtemp.CreateCell(5).SetCellValue(tlst[i].PostName); //职称
rowtemp.CreateCell(6).SetCellValue(tlst[i].RankName); //职等
rowtemp.CreateCell(7).SetCellValue(tlst[i].EntryDate.ToString()); //入职日期
rowtemp.CreateCell(8).SetCellValue(tlst[i].SkillName); //本职等技能
rowtemp.CreateCell(9).SetCellValue(tlst[i].ApplyLevel); //目前技能等级
rowtemp.CreateCell(10).SetCellValue(tlst[i].UserExamDate.ToString()); //考试时间
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(ms);
}
catch (Exception)
{
throw;
}
}
public void UploadUserInfo(string filepath)
{
DataTable dt = new DataTable();
FileStream files = null;
IWorkbook Workbook = null;
var Ldetailinfo = new List <Entity.ExamUserDetailInfo>();
try
{
using (files = new FileStream(filepath, FileMode.Open, FileAccess.Read))//C#文件流读取文件
{
if (filepath.IndexOf(".xlsx") > 0)
{
//把xlsx文件中的数据写入Workbook中
Workbook = new XSSFWorkbook(files);
}
else if (filepath.IndexOf(".xls") > 0)
{
//把xls文件中的数据写入Workbook中
Workbook = new HSSFWorkbook(files);
}
if (Workbook != null)
{
ISheet sheet = Workbook.GetSheetAt(0);//读取第一个sheet
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
//得到Excel工作表的行
IRow headerRow = sheet.GetRow(0);
//得到Excel工作表的总列数
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
//得到Excel工作表指定行的单元格
ICell cell = headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dt.Rows.Add(dataRow);
}
}
}
using (var ds = dt)
{
var q = from DataRow dr in ds.Rows
select new Entity.ExamUserDetailInfo
{
UserCode = dr[0].ToString().Trim(),
UserName = dr[1].ToString().Trim(),
TypeName = dr[2].ToString().Trim(),
SubjectName = dr[3].ToString().Trim()
};
Ldetailinfo = q.ToList();
}
foreach (var item in Ldetailinfo)
{
ExamUserDetailInfo detail = new ExamUserDetailInfo();
detail.UserCode = item.UserCode;
detail.UserName = item.UserName;
detail.TypeName = item.TypeName;
detail.SubjectName = item.SubjectName;
detail.ExamStatus = "HrSignUp";
Data.ExamUserDetailInfo.Insert_ExamUserDetailInfo(detail, null, new string[] { "ID" });
Result = "success";
}
ListDetailInfo = Data.ExamUserDetailInfo.Get_All_ExamUserDetailInfo(new { ExamStatus = "HrSignUp", IsStop = false });
}
catch (Exception ex)
{
files.Close();//关闭当前流并释放资源
}
}
public void Qualification(string filepath, string username)
{
DataTable dt = new DataTable();
FileStream files = null;
IWorkbook Workbook = null;
var LDetail = new List <ExamUserDetailInfo>();
try
{
using (files = new FileStream(filepath, FileMode.Open, FileAccess.Read))//C#文件流读取文件
{
if (filepath.IndexOf(".xlsx") > 0)
{
//把xlsx文件中的数据写入Workbook中
Workbook = new XSSFWorkbook(files);
}
else if (filepath.IndexOf(".xls") > 0)
{
//把xls文件中的数据写入Workbook中
Workbook = new HSSFWorkbook(files);
}
if (Workbook != null)
{
ISheet sheet = Workbook.GetSheetAt(0);//读取第一个sheet
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
//得到Excel工作表的行
IRow headerRow = sheet.GetRow(0);
//得到Excel工作表的总列数
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
//得到Excel工作表指定行的单元格
ICell cell = headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
if (row != null)
{
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j);
}
}
dt.Rows.Add(dataRow);
}
}
}
}
using (var ds = dt)
{
var data = new ExamUserDetailInfo();
foreach (DataRow item in ds.Rows)
{
if (item != null)
{
LDetail.Add(new ExamUserDetailInfo
{
TypeName = item[0].ToString().Trim(),
SubjectName = item[1].ToString().Trim(),
UserCode = item[2].ToString().Trim(),
UserName = item[3].ToString().Trim(),
DepartCode = item[4].ToString().Trim(),
RuleName = item[5].ToString().Trim(),
ExamDate = Convert.ToDateTime(item[6].ToString()),
ExamPlace = item[7].ToString().Trim(),
HrCheckCreateUser = username,
HrCheckCreateDate = DateTime.Now,
IsExam = "false",
IsStop = false,
ExamStatus = "HrCheck"
});
}
}
}
var successcount = 0;
foreach (var item in LDetail)
{
var d = Data.ExamUserDetailInfo.Get_ExamUserDetailInfo(new { UserCode = item.UserCode, TypeName = item.TypeName, SubjectName = item.SubjectName, IsStop = false, IsExam = false });
if (d != null)
{
d.IsStop = true;
Data.ExamUserDetailInfo.Update_ExamUserDetailInfo(d, null, new string[] { "ID" });
}
item.IsStop = false;
var c = Data.ExamUserDetailInfo.Insert_ExamUserDetailInfo(item, null, new string[] { "ID" });
successcount += c;
}
GetHrAuditUser();
Result = "成功插入" + successcount + "记录";
}
catch (Exception ex)
{
Result = ex.Message;
files.Close();//关闭当前流并释放资源
}
}
