里面有好多项,需要学生填好表之后,根据所填项判断出标准代码。所以老师会给我们很多excel表格,里面有对应的代码,我们的做法就是把这些数据录入到数据库中,根据 这个表,由学生所填项,判断出所对应的标准代码。另外还有全校几千人的基本数据信息需要导入,学号,姓名,专业等信息。但是这个excel数据量很大,也很多,不可能手动录入进去,于是我们就用NPOI导入数据,这样操作数据就方便多了。
首先先获得新建一个操作对象
HSSFWorkbook hssfworkbook;
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
先获取到sheet,再获取到行
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
rows.MoveNext();//移动到第一行,数据为表头的代码
HSSFRow row = (HSSFRow)rows.Current;//获取第一行的值
然后新建一个表datatable
DataTable dt = new DataTable();
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
dt.Columns.Add(row.GetCell(j).ToString());//保存表头
}
再创建表头行,以及数据行,并附上值
DataRow header = dt.NewRow();//保存表头数据
DataRow dr = dt.NewRow();//保存数据
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
header[j] = row.GetCell(j).ToString();//保存表头
}
rows.MoveNext();//再移动一行,为实际数据
实际数据较多,处理比较麻烦
while (rows.MoveNext())
{
row = (HSSFRow)rows.Current;
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
然后新建一些数据model的对象,
设置几个标志位isNull
然后根根据学号筛选出来每个表的信息,若没有数据,直接新建一个模型,并表示设置为Null,若有数据,就把数据模型放到model里面,把标志位设置为不空
然后对每个head情况进行判断,然后把model里面的数据填满,并判断,如果是空话,就添加,不空话,就进行更新。
总代码
public static void ImportExcelFile(string filePath)
{
DateTime nowTime = DateTime.Now;
DeletModel time = new DeletModel { time = nowTime };
GraduationDBContent db1 = new GraduationDBContent();
db1.deleteTb.Add(time);
db1.SaveChanges();
var time1 = db1.deleteTb.ToList().LastOrDefault();//删除时间模型中
HSSFWorkbook hssfworkbook;
#region//初始化信息
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
#endregion
#region 读取信息并保存
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
rows.MoveNext();//移动到第一行,数据为表头的代码
HSSFRow row = (HSSFRow)rows.Current;//获取第一行的值
DataTable dt = new DataTable();
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
dt.Columns.Add(row.GetCell(j).ToString());//保存表头
}
DataRow header = dt.NewRow();//保存表头数据
DataRow dr = dt.NewRow();//保存数据
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
header[j] = row.GetCell(j).ToString();//保存表头
}
rows.MoveNext();//再移动一行,为实际数据
while (rows.MoveNext())
{
row = (HSSFRow)rows.Current;
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
UploadModel model = new UploadModel();
ESchoolInfoModel eschool = new ESchoolInfoModel();
ESchoolInfoModel eschool1 = new ESchoolInfoModel();
FillBaseInfoModel baseInfo = new FillBaseInfoModel();
UploadModel student = new UploadModel();
FillBaseInfoModel baseInfo1 = new FillBaseInfoModel();
string isNull = "";//上传信息表中有没有
string isNull1 = "";//基本信息表中有没有
string isNull2 = "";//就业信息表中有没有
for (int i = 0; i < header.ItemArray.Length; i++)
{
string number = "";
if (header.ItemArray[i].ToString() == "xh")
{
number = dr.ItemArray[i].ToString();
student = db1.UploadTb.Find(number);
baseInfo1 = db1.BaseInfoTb.Find(number);
eschool1 = db1.ESchoolInfoTb.Find(number);
#region 判断是否在表中已经存在
if (student == null)
{
model = new UploadModel();
isNull = "null";
}
else
{
model = student;
isNull = "NotNull";
}
if (baseInfo1 == null)
{
baseInfo = new FillBaseInfoModel();
isNull1 = "null";
}
else
{
baseInfo = baseInfo1;
isNull1 = "NotNull";
}
if (eschool1 == null)
{
eschool = new ESchoolInfoModel();
isNull2 = "null";
}
else
{
eschool = eschool1;
isNull2 = "NotNull";
}
#endregion
}
}
#region 将每一行的数据进行保存
for (int i = 0; i < header.ItemArray.Length; i++)
{
string head = header.ItemArray[i].ToString();
string data = dr.ItemArray[i].ToString();
#region 上传数据的判断
switch (head)
{
case "ksh"://考生号
model.KSH = data;
break;
case "szxy"://学院
model.Academy = data;
break;
case "szyx"://系
model.Department = data;
break;
case "bj"://班级
model.Class = data;
break;
case "jxb"://教学班
model.TeachingClass = data;
break;
case "xh"://学号
model.StudentNumber = data;
eschool.StudentNumber = data;
baseInfo.StudentNumber = data;
break;
case "xm"://姓名
model.Name = data;
break;
case "xb"://性别
model.Sex = data;
if (data == "男")
model.SexCode = "1";
else if (data == "女")
model.SexCode = "2";
break;
case "mz"://民族
model.Nation = data;
model.NationCode = NationCode(data);
break;
case "csrq"://出生年月
model.BirthTime = data;
break;
case "sfzh"://身份证号
model.IDNumber = data;
break;
case "zy"://专业名称
model.Major = data;
if (model.Education != null)
{
GraduationDBContent db = new GraduationDBContent();
var maj = db.MajorTb.Where(m => m.Name == data && m.Edu == model.Education).FirstOrDefault();
if (maj != null)
model.MajorCode = maj.Code;
}
break;
case "zyfx"://专业方向
model.MajorDirection = data;
break;
case "sfzyxw"://是否专业学位
model.Sfzyxw = data;
break;
case "syszdgkkq"://生源地所在地(高考生源地)只有省份
baseInfo.OriginProvince = data;//在基本信息表中
break;
case "xl"://学历
model.Education = data;
if (data == "本科生" || data == "本科")
{
model.StudentType = "0";
model.EducationCode = "31";
}
else if (data == "研究生")
{
model.EducationCode = "11";
model.StudentType = "1";
}
if (model.Major != null)
{
GraduationDBContent db = new GraduationDBContent();
var maj = db.MajorTb.Where(m => m.Name == model.Major && m.Edu == model.Education).FirstOrDefault();
if (maj != null)
model.MajorCode = maj.Code;
}
break;
case "xz"://学制
model.LengthOfSch = data;
break;
case "pyfs"://培养方式
model.TrainType = data;
if (data == "非定向")
model.TrainTypeCode = "1";
else if (data == "定向")
model.TrainTypeCode = "2";
else if (data == "在职")
model.TrainTypeCode = "3";
else if (data == "委培")
model.TrainTypeCode = "4";
else if (data == "自筹")
model.TrainTypeCode = "5";
break;
case "dxhwpdw"://定向委托单位
model.Weituo = data;
break;
case "rxsj"://入学时间
model.EntranceTime = data;
model.EntranceYear = data.Substring(0, 4);
break;
case "bysj"://毕业时间
model.GraduationTime = data;
break;
case "xysbh"://协议书编号
eschool.AgreementID = data;
break;
}
#endregion
}
model.Pwd = "012345";
model.School = "华北电力大学";
model.SchoolCode = "10079";
model.SchoolBeCode = "360";
model.SchoolAddCode = "130600";
model.SFstudentCode = "2";
model.deleteId = time1.id;
#region 用于判断为更新还是为上传,上传的是哪个表
if (isNull == "null")//为空则为添加,修改的为上传表
{
db1.UploadTb.Add(model);
db1.SaveChanges();
}
else
{
db1.Entry(student).CurrentValues.SetValues(model);
db1.SaveChanges();
}
if (baseInfo.OriginProvince != null)
{
if (isNull1 == "null")//为空则为添加,修改的为基本信息表
{
db1.BaseInfoTb.Add(baseInfo);
db1.SaveChanges();
}
else
{
db1.Entry(baseInfo1).CurrentValues.SetValues(baseInfo);
db1.SaveChanges();
}
}
if (eschool.AgreementID != null)
{
if (isNull2 == "null")//为空则为添加,修改的为就业信息表
{
db1.ESchoolInfoTb.Add(eschool);
db1.SaveChanges();
}
else
{
db1.Entry(eschool1).CurrentValues.SetValues(eschool);
db1.SaveChanges();
}
}
#endregion
#endregion
}
#endregion
}
#endregion