public ActionResult ExcelToUpload(int id)
{
//用来存储excel表中读出来的数据
DataTable excelTable = new DataTable();
string msg = "";
if (Request.Files.Count > 0) //request.files.count客户端传过来几个文件
{
try
{
HttpPostedFileBase mypost = Request.Files[0]; //取客户端传过来多个文件的第一个
string fileName = Path.GetFileName(mypost.FileName); //通过文件流取文件名称
string serverpath = Server.MapPath(
string.Format("~/{0}", "Excel")); //获取要存入的服务器上的地址
string path = Path.Combine(serverpath, fileName); //将定义的服务器路径和文件名结合，形成完整路径
mypost.SaveAs(path); //将文件保存
//msg = "文件上传成功！";
excelTable = ImportExcel.GetExcelDataTable(path); //获得表数据
msg = SaveExcelToDB.InsertDataToDB(excelTable, id); // 写入基础数据
}
catch (Exception ex)
{
msg = "Excel导入失败，请检查匹配";
}
}
else
{
msg = "请选择文件";
}
return(Json(msg));
}
public ActionResult ImportSignInfo(HttpPostedFileBase file, string SignID, SignListViewModel signListViewModel)
{
DataTable dataTable = new DataTable();
var fileName = file.FileName;
var filePath = Server.MapPath(string.Format("~/{0}", "Files"));
string path = Path.Combine(filePath, fileName);
file.SaveAs(path);
dataTable = ImportExcel.GetExcelDataTable(path);
if (dataTable.Rows.Count > 0)
{
GameClubEntities gameClubEntities = new GameClubEntities();
CovertListHelper toList = new CovertListHelper();
List <SignInfo> signInfos = toList.convertToList <SignInfo>(dataTable);
IEnumerable <SignInfo> signInfosResult = gameClubEntities.SignInfo.Where(s => s.SignID == SignID);
foreach (var item in signInfos)
{
SignInfo signInfo = signInfosResult.Where(s => s.GameID == item.GameID).FirstOrDefault();
if (signInfo != null)
{
if (!string.IsNullOrEmpty(item.VoiceCondition))
{
signInfo.VoiceCondition = item.VoiceCondition;
}
else
{
signInfo.VoiceCondition = "未语音";
}
}
}
gameClubEntities.SaveChanges();
}
signListViewModel = UpdateSignListViewModel(signListViewModel);
return(PartialView("_SignList", signListViewModel));
}
public string Import(HttpPostedFileBase file)
{
if (file == null)
{
return("文件为空");
}
var fileName = file.FileName;
var filePath = Server.MapPath(string.Format("~/{0}", "StudentFile"));
string path = Path.Combine(filePath, fileName);
file.SaveAs(path);
DataTable excelTable = new DataTable();
excelTable = ImportExcel.GetExcelDataTable(path);
DataTable dbdata = new DataTable();
dbdata.Columns.Add("id");
dbdata.Columns.Add("username");
dbdata.Columns.Add("password");
dbdata.Columns.Add("name");
dbdata.Columns.Add("sex");
dbdata.Columns.Add("major");
for (int i = 0; i < excelTable.Rows.Count; i++)
{
DataRow dr = excelTable.Rows[i];
DataRow dr_ = dbdata.NewRow();
dr_["id"] = 0;
dr_["username"] = dr["学号"];
dr["密码"] = encryptPwd(dr["密码"].ToString());
dr_["password"] = dr["密码"];
dr_["name"] = dr["姓名"];
dr_["sex"] = dr["性别"];
dr_["major"] = dr["专业"];
dbdata.Rows.Add(dr_);
}
RemoveEmpty(dbdata);
List <Model.StudentTable> list = student.DataTableToList(dbdata);
if (list.Count > 0)
{
for (int i = 0; i < list.Count; i++)
{
Model.StudentTable studentModel = list[i];
student.Add(studentModel);
}
}
return("导入成功");
}
public ActionResult ImportGameMember(HttpPostedFileBase file, int pageID, int pagesize, string keyword, string sortBy, string currentSort, bool IsOrderBy)
{
DataTable dataTable = new DataTable();
var fileName = file.FileName;
var filePath = Server.MapPath(string.Format("~/{0}", "Files"));
string path = Path.Combine(filePath, fileName);
file.SaveAs(path);
dataTable = ImportExcel.GetExcelDataTable(path);
if (dataTable.Rows.Count > 0)
{
GameClubEntities gameClubEntities = new GameClubEntities();
CovertListHelper toList = new CovertListHelper();
List <GameMember> gameMembers = toList.convertToList <GameMember>(dataTable);
foreach (var item in gameMembers)//对比数据库中的信息，把存在于Excel表中且不存在于数据库表的成员添加上去，如果存在且处于被删除的状态，设为未删除状态
{
GameMember game = gameClubEntities.GameMember.Where(g => g.GameID == item.GameID).FirstOrDefault();
if (game == null)
{
GameMember gameMember = new GameMember
{
GameID = item.GameID,
GameName = item.GameName,
JoinDate = DateTime.Now,
};
if (item.Authority > 0)
{
gameMember.Authority = item.Authority;
}
else
{
gameMember.Authority = EFGameMember.gameAuthorities.Count;
}
gameClubEntities.GameMember.Add(gameMember);
}
else
{
game.IsDel = null;
game.DelTime = null;
}
}
foreach (var item in gameClubEntities.GameMember)//把存在于数据库表中且不存在于Excel表的成员设为删除状态
{
if (gameMembers.Where(g => g.GameID == item.GameID).FirstOrDefault() == null)
{
item.IsDel = true;
item.DelTime = DateTime.Now;
}
}
gameClubEntities.SaveChanges();
}
return(RedirectToAction("GameMemberManage", new { pageID, pagesize, keyword, sortBy, currentSort, IsOrderBy }));
}
public ActionResult ImportContact(string filename)
{
string path = Server.MapPath("/File/UserFile/" + SysUser.UserId + "/") + filename;
DataTable excelTable = new DataTable();
excelTable = ImportExcel.GetExcelDataTable(path);
ImportContactFacade import = new ImportContactFacade();
if (!import.Import(excelTable, SysUser.UserId))
{
return(FailResult(import.PromptInfo.Message));
}
return(SuccessResult("导入成功！"));
}
public ActionResult Import()
{
HttpPostedFileBase file = Request.Files["excel"];
var path = Path.Combine(Request.MapPath("/Upload"), file.FileName);
file.SaveAs(path);
//从NPOI读取到的Excel的数据
DataTable excelTable = new DataTable();
excelTable = ImportExcel.GetExcelDataTable(path);
ImportExcel.RemoveEmpty(excelTable);
int result = 0;
foreach (DataRow dataRow in excelTable.Rows)
{
Jianli obj = new Jianli();
int userID = Convert.ToInt32(dataRow["用户编号"]);
bool flag = new JIanliBLL().FindByUId(userID);
//判断该用户是否存在，若不存在则添加
if (flag)
{
int ret = new JIanliBLL().ExDel(userID);
}
obj = new Jianli
{
userID = Convert.ToInt32(dataRow["用户编号"]),
jlName = dataRow["姓名"].ToString(),
jlPic = dataRow["头像"].ToString(),
jlGender = dataRow["性别"].ToString(),
jlage = dataRow["出生年月"].ToString(),
jlIC = dataRow["身份证号"].ToString(),
jlEdu = dataRow["学历"].ToString(),
jlGw = dataRow["求职岗位"].ToString(),
jlPhone = dataRow["联系方式"].ToString(),
jlEmail = dataRow["电子邮件"].ToString(),
jlXinzi = Convert.ToInt32(dataRow["期望薪资"]),
jlHunyin = dataRow["婚姻情况"].ToString(),
jlWork = Convert.ToInt32(dataRow["工作年限"]),
jlPingJia = dataRow["工作经历"].ToString(),
jl_peixun = dataRow["培训经历"].ToString(),
jl_jiaoyu = dataRow["教育经历"].ToString()
};
result += new JIanliBLL().Add(obj);
}
return
(RedirectToAction("JlList", "Admin", new { info = "导入成功" }));
}
public ActionResult ImportContribution(HttpPostedFileBase file, string ContributionID, ContributionListViewModel contributionListViewModel)
{
DataTable dataTable = new DataTable();
var fileName = file.FileName;
var filePath = Server.MapPath(string.Format("~/{0}", "Files"));
string path = Path.Combine(filePath, fileName);
file.SaveAs(path);
dataTable = ImportExcel.GetExcelDataTable(path);
if (dataTable.Rows.Count > 0)
{
GameClubEntities gameClubEntities = new GameClubEntities();
CovertListHelper toList = new CovertListHelper();
List <Contribution> contributions = toList.convertToList <Contribution>(dataTable);
IEnumerable <Contribution> contributionsResult = gameClubEntities.Contribution.Where(c => c.ContributionID == ContributionID);
ContributionList contributionList = gameClubEntities.ContributionList.Where(c => c.ContributionID == ContributionID).FirstOrDefault();
foreach (var item in contributions)
{
Contribution contribution = contributionsResult.Where(c => c.GameID == item.GameID).FirstOrDefault();
if (contribution != null)
{
if (item.AllContribution != null)
{
contribution.AllContribution = item.AllContribution;
if (contributionList != null && contributionList.Time != null && contributionList.Time > 0)
{
contribution.MinSpeed = (contribution.AllContribution) / contributionList.Time * decimal.Parse("60");
}
}
}
}
gameClubEntities.SaveChanges();
}
contributionListViewModel = UpdateContributionListViewModel(contributionListViewModel);
return(PartialView("_ContributionList", contributionListViewModel));
}
public void Import()
{
string path = this.tb_url.Text;
DataTable dt = new DataTable();
dt = ImportExcel.GetExcelDataTable(path);
if (dt.Rows.Count <= 0)
{
MessageBox.Show("没有数据！");
return;
}
RemoveEmpty(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
Thread.Sleep(300);
count = i;
string email = dt.Rows[i]["Email"].ToString();
DataAccess.Tnsmtp_Spidermail spidermailexist = new DataAccess.Tnsmtp_Spidermail();
if (spidermailexist.SelectByEmail(email))
{
stringBuilder.AppendLine(count + "==已存在：" + email);
m_SyncContext.Post(SetTextSafePost, stringBuilder.ToString());
continue;
}
DataAccess.Tnsmtp_Spidermail spidermail = new DataAccess.Tnsmtp_Spidermail();
spidermail.Email = email;
spidermail.Firstname = dt.Rows[i]["FirstName"].ToString();
spidermail.Lastname = dt.Rows[i]["LastName"].ToString();
spidermail.Address = dt.Rows[i]["Address"].ToString();
spidermail.City = dt.Rows[i]["City"].ToString();
spidermail.State = dt.Rows[i]["State"].ToString();
spidermail.Zip = dt.Rows[i]["Zip"].ToString();
spidermail.Homephone = dt.Rows[i]["Homephone"].ToString();
spidermail.Source = dt.Rows[i]["Source"].ToString();
spidermail.Ipaddress = dt.Rows[i]["IPAddress"].ToString();
spidermail.Regdate = DateTime.Parse(dt.Rows[i]["Regdate1"].ToString());
if (!spidermail.Insert())
{
stringBuilder.AppendLine(count + "==添加失败：" + email);
m_SyncContext.Post(SetTextSafePost, stringBuilder.ToString());
return;
}
stringBuilder.AppendLine(count + "==添加成功：" + email);
m_SyncContext.Post(SetTextSafePost, stringBuilder.ToString());
Thread.Sleep(100);
}
stringBuilder.AppendLine(count + "==导入结束：");
m_SyncContext.Post(SetTextSafePost, stringBuilder.ToString());
}
