/// <summary>
/// excel读到table
/// </summary>
/// <returns></returns>
private DataTable excelToDt()
{
DataTable dt1 = new DataTable();
int regId;
if (user.RoleId.RoleName == "超级管理员")
{
regId = Convert.ToInt32(Request["regId"]);
}
else
{
regId = user.ReginId.RegionId;
}
string path = Session["path"].ToString();
try
{
dt1 = ExcelHelp.excelToDt(path, "excel");
DataColumn dc = new DataColumn("地区ID", typeof(int));
dc.DefaultValue = regId;
dt1.Columns.Add(dc);
row = dt1.Rows.Count;
}
catch (Exception ex)
{
Response.Write(ex);
Response.End();
}
return(dt1);
}
protected void Page_Load(object sender, EventArgs e)
{
tealogin = (Teacher)Session["user"];
state = Convert.ToInt32(Session["state"]);
string op = Context.Request["op"];
if (op == "add")
{
saveTeacher();
}
if (op == "change")
{
saveChange();
}
//删除教师
if (op == "del")
{
delTeal();
Search();
getdata(Search());
}
if (op == "import")
{
int collegeId;
if (state == 0)
{
collegeId = Convert.ToInt32(Context.Request["collegeId"]);
}
else
{
collegeId = tealogin.college.ColID;
}
string path = Security.Decrypt(Request["fileName"]);
DataTable dt = TableHelper.GetDistinctSelf(ExcelHelp.excelToDt(path, "excel"), "工号");
int i = ImportHelper.Teacher(dt, collegeId);
int row = ExcelHelp.excelToDt(path, "excel").Rows.Count;
int repeat = row - i;
if (i > 0)
{
LogHelper.Info(this.GetType(), tealogin.TeaAccount + "教师信息导入 -" + i + " " + "条信息");
Response.Write("导入成功，总数据有" + row + "条，共导入" + i + "条数据，重复数据有" + repeat + "条");
Response.End();
}
else
{
Response.Write("导入失败，总数据有" + row + "条，共导入" + i + "条数据，重复数据有" + repeat + "条");
Response.End();
}
}
//if (op == "upload")
//{
// upload();
//}
if (!IsPostBack)
{
Search();
getdata(Search());
}
colds = colbll.Select();
}
private DataTable npoi()
{
string path = Session["path"].ToString();
DataTable dtNpoi = new DataTable();
dtNpoi = ExcelHelp.excelToDt(path, "excel");
dtNpoi.Columns.Add("id").SetOrdinal(0);
row = dtNpoi.Rows.Count;
return(dtNpoi);
}
/// <summary>
/// 判断重复数据
/// </summary>
public void check()
{
string path = Session["path"].ToString();
DataTable dt = ExcelHelp.excelToDt(path, "excel");
string[] huojiaId = { "货架编号" };
if (DataTableHelper.isRepeatDt(dt, huojiaId))
{
Response.Write("重复数据");
Response.End();
}
else
{
Response.Write("上传成功");
Response.End();
}
}
protected void Page_Load(object sender, EventArgs e)
{
//获取state 判断是分管理员还是超级管理员
userType = Session["state"].ToString();
teacher = (Teacher)Session["user"];
string op = Context.Request.Form["op"];
//添加专业
if (op == "add")
{
saveProfession();
Search();
getPage(Search());
}
//修改专业
if (op == "change")
{
saveChange();
Search();
getPage(Search());
}
//删除专业
if (op == "del")
{
delPro();
Search();
getPage(Search());
}
if (op == "upload")
{
upload();
}
if (op == "import")
{
int collegeId;
if (userType == "0")
{
collegeId = Convert.ToInt32(Context.Request["collegeId"]);
}
else
{
collegeId = teacher.college.ColID;
}
string path = Security.Decrypt(Request["fileName"]);
DataTable dt = TableHelper.GetDistinctSelf(ExcelHelp.excelToDt(path, "excel"), "专业名称");
int i = ImportHelper.Major(dt, collegeId);
int row = ExcelHelp.excelToDt(path, "excel").Rows.Count;
int repeat = row - i;
if (i > 0)
{
LogHelper.Info(this.GetType(), teacher.TeaAccount + "专业信息导入 -" + i + " " + "条信息");
Response.Write("导入成功，总数据有" + row + "条，共导入" + i + "条数据，重复数据有" + repeat + "条");
Response.End();
}
else
{
Response.Write("导入失败，总数据有" + row + "条，共导入" + i + "条数据，重复数据有" + repeat + "条");
Response.End();
}
}
if (!IsPostBack)
{
getPage(Search());
colds = colbll.Select();
}
}
/// <summary>
/// 取差
/// </summary>
/// <returns></returns>
//private DataTable differentDt()
//{
// LibraryCollectionBll libraryCollectionBll = new LibraryCollectionBll();
// DataTable dt3 = new DataTable();//接受差集的dt3
// int j = libraryCollectionBll.Select(custom).Rows.Count;
// //数据库无数据时直接导入excel
// if (j<=0)
// {
// dt3 = excelToDt();
// }
// else
// {
// dt3.Columns.Add("id", typeof(string));
// dt3.Columns.Add("ISBN", typeof(string));
// dt3.Columns.Add("书名", typeof(string));
// dt3.Columns.Add("定价", typeof(double));
// dt3.Columns.Add("馆藏数量", typeof(int));
// dt3.Columns.Add("客户ID", typeof(string));
// dt3.Columns.Add("state", typeof(int));
// DataRowCollection count = excelToDt().Rows;
// foreach (DataRow row in count)//遍历excel数据集
// {
// DataRow[] rows = libraryCollectionBll.Select(custom).Select(string.Format("customerId='{0}' and ISBN='{1}'",custom, row[1].ToString().Trim()));
// //DataRow[] rows = libraryCollectionBll.Select(custom).Select("customerId='" + custom + "' and ISBN='" + row[1].ToString().Trim() + "'");//查询excel数据集是否存在于表A，如果存在赋值给DataRow集合
// if (rows.Length == 0)//判断如果DataRow.Length为0，即该行excel数据不存在于表A中，就插入到dt3
// {
// dt3.Rows.Add(row[0], row[1], row[2], row[3], row[4], row[5]);
// }
// }
// }
// return dt3;
//}
private DataTable excelToDt()
{
int custom = Convert.ToInt32(Request["custom"]);
string path = Session["path"].ToString();
DataTable dt1 = new DataTable();
//string strConn = "";
//文件类型判断
string[] sArray = path.Split('.');
int count = sArray.Length - 1;
if (sArray[count].ToLower() == "xls" || sArray[count].ToLower() == "xlsx")
{
dt1 = ExcelHelp.excelToDt(path, "excel");
dt1.Columns.Add("id").SetOrdinal(0);
row = dt1.Rows.Count; //获取总数
DataColumn dc = new DataColumn("客户ID", typeof(string));
dc.DefaultValue = custom; //默认客户值值列
dt1.Columns.Add(dc);
//if (sArray[count] == "xls")
//{
// strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
//}
//else if (sArray[count] == "xlsx")
//{
// strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
//}
//OleDbConnection conn = new OleDbConnection(strConn);
//try
//{
// conn.Open();
// string strExcel1 = "select * from [Sheet1$]";
// OleDbDataAdapter oda1 = new OleDbDataAdapter(strExcel1, strConn);
// dt1.Columns.Add("id"); //id自增列
// oda1.Fill(dt1);
// row = dt1.Rows.Count; //获取总数
// DataColumn dc = new DataColumn("客户ID", typeof(string));
// dc.DefaultValue = custom; //默认客户值值列
// dt1.Columns.Add(dc);
// //GetDistinctSelf(dt1, "ISBN", "客户ID"); //去重字段
//}
//catch (Exception ex)
//{
// Response.Write(ex.Message);
// Response.End();
//}
//finally
//{
// conn.Close();
//}
}
else if (sArray[count].ToLower() == "iso")
{
string fisbn = Request["fisbn"];
string sisbn = Request["sisbn"];
string fbookName = Request["fbookName"];
string sbookName = Request["sbookName"];
string fprice = Request["fprice"];
string sprice = Request["sprice"];
string fnum = Request["fnum"];
string snum = Request["snum"];
int fcustom = Convert.ToInt32(Request["custom"]);
MarcToDt marcToDt = new MarcToDt();
dt1 = marcToDt.MarcTodt(path, fisbn, sisbn, fbookName, sbookName, fprice, sprice, fnum, snum);
DataColumn dc = new DataColumn("客户ID", typeof(string));
dc.DefaultValue = fcustom; //默认客户值值列
dt1.Columns.Add(dc);
}
return(dt1);
}
protected void Page_Load(object sender, EventArgs e)
{
user = (Teacher)Session["user"];
string op = Context.Request["op"];
//第一次加载页面时
if (!Page.IsPostBack)
{
Search();
getdata(Search());
}
//添加学院
if (op == "add")
{
saveCollege();
Search();
getdata(Search());
}
//编辑学院信息
else if (op == "edit")
{
editCollege();
Search();
getdata(Search());
}
//批量上传
//else if (op == "upload")
//{
// upload();
// Search();
// getdata(Search());
//}
else if (op == "import")
{
string path = Security.Decrypt(Request["fileName"]);
DataTable dt = TableHelper.GetDistinctSelf(ExcelHelp.excelToDt(path, "excel"), "学院名称");
int i = ImportHelper.College(dt);
int row = ExcelHelp.excelToDt(path, "excel").Rows.Count;
int repeat = row - i;
if (i > 0)
{
LogHelper.Info(this.GetType(), user.TeaAccount + "学院信息导入 -" + i + " " + "条信息");
Response.Write("导入成功，总数据有" + row + "条，共导入" + i + "条数据，重复数据有" + repeat + "条");
Response.End();
}
else
{
Response.Write("导入失败，总数据有" + row + "条，共导入" + i + "条数据，重复数据有" + repeat + "条");
Response.End();
}
}
//删除学院
else if (op == "dele")
{
deleteCollege();
Search();
getdata(Search());
}
//批量删除
else if (op == "batchDel")
{
batchDeleteCollege();
Search();
getdata(Search());
}
}
public void Page_Load(object sender, EventArgs e)
{
string op = Context.Request["op"];
string editorOp = Context.Request["editorOp"];
//下拉专业id
string proId = Request.QueryString["proId"];
//下拉学院id
string collegeId = Request.QueryString["collegeId"];
//输入框信息
string strsearch = Request.QueryString["search"];
userType = Session["state"].ToString();
if (userType == "0")
{
colds = colBll.Select();
prods = proBll.Select();
stuAddProds = proBll.Select();
if (collegeId == null || collegeId == "0" || collegeId == "null")
{
prods = proBll.Select();
}
else
{
prods = proBll.SelectByCollegeId(int.Parse(collegeId));
}
if (proId != null && proId != "null" && collegeId == "null")
{
//学院为空 专业不为空
getdata(Searchdrop());
}
else if (collegeId != null && collegeId != "null" && (proId == "null" || proId == "0"))
{
//学院不为空 专业为空
getdata(SearchByCollege());
}
else if (collegeId != null && collegeId != "null" && proId != null && proId != "null")
{
//两个都不为空
getdata(SearchProAndCollege());
}
else if (strsearch != null)
{
getdata(Search());
}
else
{
getdata("");
colds = colBll.Select();
}
}
else if (userType == "2")
{
tea = (Teacher)Session["user"];
int usercollegeId = tea.college.ColID;
colds = colBll.Select();
prods = proBll.SelectByCollegeId(usercollegeId);
stuAddProds = proBll.SelectByCollegeId(usercollegeId);
if (strsearch != null)
{
getdata(Search());
}
else if (proId != null && proId != "null")
{
getdata(Searchdrop());
}
else
{
getdata("");
}
}
if (op == "add")//添加
{
saveStudent();
}
if (editorOp == "editor")//编辑
{
editorStu();
}
if (op == "delete")//删除
{
deleteStu();
}
if (op == "stuadd")
{
//学院与专业下拉联动
int addcollegeId = int.Parse(Context.Request["stuAddcollegeId"]);
stuAddProds = proBll.SelectByCollegeId(addcollegeId);
DataTable dt = stuAddProds.Tables[0];
string data = DataTableToJson(dt);
Response.Write(data);
Response.End();
}
if (op == "reset")
{
//重置密码
stuPasswordReset();
}
if (op == "import")
{
int proid = Convert.ToInt32(Context.Request["proId"]);
string path = Security.Decrypt(Request["fileName"]);
DataTable dt = TableHelper.GetDistinctSelf(ExcelHelp.excelToDt(path, "excel"), "学号");
int i = ImportHelper.Student(dt, proid);
int row = ExcelHelp.excelToDt(path, "excel").Rows.Count;
int repeat = row - i;
if (i > 0)
{
Response.Write("导入成功，总数据有" + row + "条，共导入" + i + "条数据，重复数据有" + repeat + "条");
Response.End();
}
else
{
Response.Write("导入失败，总数据有" + row + "条，共导入" + i + "条数据，重复数据有" + repeat + "条");
Response.End();
}
}
}