/// <summary>
/// 返回已存在的编号集合
/// </summary>
/// <param name="fs">Excel文件流</param>
/// <param name="type">0学生，1员工</param>
/// <returns></returns>
public static List<string> 导入excel(Stream stream, int type)
{
try
{
HSSFWorkbook workBook = new HSSFWorkbook(stream);
HSSFSheet sheet = (HSSFSheet)workBook.GetSheetAt(0);
//判断是否符合格式
Cell cell0 = sheet.GetRow(0).GetCell(0);
if (cell0 == null)
{
抛出格式不符异常();
}
string cell0Value = 得到流中列值(cell0.CellType, sheet, 0, 0);
if (cell0Value != "学号" && cell0Value != "编号")
{
抛出格式不符异常();
}
Cell cell1 = sheet.GetRow(0).GetCell(1);
if (cell1 == null)
{
抛出格式不符异常();
}
string cell1Value = 得到流中列值(cell1.CellType, sheet, 0, 1);
if (cell1Value != "姓名")
{
抛出格式不符异常();
}
Cell cell2 = sheet.GetRow(0).GetCell(2);
if (cell2 == null)
{
抛出格式不符异常();
}
string cell2Value = 得到流中列值(cell2.CellType, sheet, 0, 2);
if (cell2Value != "性别")
{
抛出格式不符异常();
}
Cell cell3 = sheet.GetRow(0).GetCell(3);
if (cell3 == null)
{
抛出格式不符异常();
}
string cell3Value = 得到流中列值(cell1.CellType, sheet, 0, 3);
if (cell3Value != "班级"&&cell3Value!="部门")
{
抛出格式不符异常();
}
LoveKaoExamEntities db = new LoveKaoExamEntities();
//取出班级,学号
List<string> listClassName = new List<string>();
List<string> listNum = new List<string>();
for (int k = sheet.FirstRowNum + 1; k < sheet.LastRowNum + 1; k++)
{
string className = string.Empty;
string classType = string.Empty;
if (type == 0)
{
classType = "班级";
}
else
{
classType = "部门";
}
Cell cellClass = sheet.GetRow(k).GetCell(3);
if (cellClass == null)
{
throw new Exception("第" + k + "行" + classType + "未填写，请先填写，然后再导入！");
}
CellType classCellType = cellClass.CellType;
className = 得到流中列值(classCellType, sheet, k, 3);
if (className.Length > 16)
{
throw new Exception("第" + k + "行" + classType + "名称长度大于16，请填写16以内的名称！");
}
string num = string.Empty;
string numType = string.Empty;
if (type == 0)
{
numType = "学号";
}
else
{
numType = "编号";
}
Cell cellNum=sheet.GetRow(k).GetCell(0);
if (cellNum == null)
{
throw new Exception("第" + k + "行" + numType + "未填写，请先填写，然后再导入！");
}
CellType numCellType = cellNum.CellType;
num = 得到流中列值(numCellType, sheet, k, 0);
if (num.Length < 4 || num.Length > 16)
{
throw new Exception("第" + k + "行" + numType + "长度不符，必须是4到16位！");
}
else if (Regex.IsMatch(num, @"[^a-zA-Z0-9]"))
{
throw new Exception("第" + k + "行" + numType + "格式不符，" + numType + "必须只含数字或字母！");
}
listClassName.Add(className);
listNum.Add(num);
}
//有不存在的班级，则先添加
listClassName = listClassName.Distinct().ToList();
List<string> listExistClassName = db.部门表.Where(a => listClassName.Contains(a.名称))
.Select(a => a.名称).ToList();
List<string> listNotExistClassName = listClassName.Except(listExistClassName).ToList();
foreach (string className in listNotExistClassName)
{
部门表 department = new 部门表();
department.ID = Guid.NewGuid();
department.名称 = className;
department.添加人ID = 用户信息.CurrentUser.用户ID;
department.添加时间 = DateTime.Now;
db.部门表.AddObject(department);
}
db.SaveChanges();
//已存在的学生则不再导入
List<string> listExistNum = db.用户表.Where(a => listNum.Contains(a.编号)).Select(a => a.编号).ToList();
List<string> listNotExistNum = listNum.Except(listExistNum).ToList();
List<部门表> listClass = db.部门表.Where(a => listClassName.Contains(a.名称)).ToList();
for (int i = sheet.FirstRowNum+1; i < sheet.LastRowNum+1; i++)
{
CellType numCellType = sheet.GetRow(i).GetCell(0).CellType;
string stuNum = 得到流中列值(numCellType, sheet, i, 0);
if (listNotExistNum.Contains(stuNum))
{
Cell cellName=sheet.GetRow(i).GetCell(1);
if (cellName == null)
{
throw new Exception("第" + i + "行姓名未填写，请先填写，然后再导入！");
}
CellType nameCellType = cellName.CellType;
string name = 得到流中列值(nameCellType, sheet, i, 1);
if (name.Length < 2 || name.Length > 8)
{
throw new Exception("第" + i + "行姓名长度不符，必须是2到8位！");
}
else if (Regex.IsMatch(name, @"[^a-zA-Z\u4e00-\u9fa5]"))
{
throw new Exception("第" + i + "行姓名格式不符，姓名必须只含中文或字母！");
}
用户表 user = new 用户表();
user.ID = Guid.NewGuid();
user.编号 = stuNum;
user.姓名 = name;
user.密码 = stuNum;
Cell cellSex=sheet.GetRow(i).GetCell(2);
if (cellSex == null)
{
throw new Exception("第" + i + "行性别未填写，请先填写，然后再导入！");
}
CellType sexCellType = cellSex.CellType;
string sex = 得到流中列值(sexCellType, sheet, i, 2);
if (sex == "男")
{
user.性别 = 1;
}
else
{
user.性别 = 2;
}
CellType classCellType = sheet.GetRow(i).GetCell(3).CellType;
string className = 得到流中列值(classCellType, sheet, i, 3);
user.部门ID = listClass.Where(a => a.名称 == className).First().ID;
user.角色 = 0;
user.邮箱 = "";
user.添加时间 = DateTime.Now;
db.用户表.AddObject(user);
}
}
db.SaveChanges();
return listExistNum;
}
catch (Exception ex)
{
if (ex.Message.Contains("Table"))
{
throw new Exception("格式错误，请按模板格式来导入！");
}
else
{
throw ex;
}
}
}
/// <summary>
/// 用于向 部门表 EntitySet 添加新对象的方法，已弃用。请考虑改用关联的 ObjectSet<T> 属性的 .Add 方法。
/// </summary>
public void AddTo部门表(部门表 部门表)
{
base.AddObject("部门表", 部门表);
}
/// <summary>
/// 返回0成功,1部门名已存在
/// </summary>
public static int 添加部门(部门 部门)
{
LoveKaoExamEntities db = new LoveKaoExamEntities();
if (db.部门表.Any(a => a.名称 == 部门.名称) == true)
{
return 1;
}
部门表 department = new 部门表();
department.ID = 部门.ID;
department.名称 = 部门.名称;
department.上级部门ID = 部门.上级部门ID;
department.添加人ID = 部门.创建人ID;
department.添加时间 = DateTime.Now;
db.部门表.AddObject(department);
db.SaveChanges();
return 0;
}
/// <summary>
/// 创建新的 部门表 对象。
/// </summary>
/// <param name="id">ID 属性的初始值。</param>
/// <param name="名称">名称 属性的初始值。</param>
/// <param name="添加人ID">添加人ID 属性的初始值。</param>
/// <param name="添加时间">添加时间 属性的初始值。</param>
public static 部门表 Create部门表(global::System.Guid id, global::System.String 名称, global::System.Guid 添加人ID, global::System.DateTime 添加时间)
{
部门表 部门表 = new 部门表();
部门表.ID = id;
部门表.名称 = 名称;
部门表.添加人ID = 添加人ID;
部门表.添加时间 = 添加时间;
return 部门表;
}