// excel 导入 并写入数据库
public void importExcel(string fileName)
{
string phyPath = HttpContext.Request.MapPath("/");
fileName = phyPath + "test.xls";
FileStream file = null;
try
{
file = new FileStream(fileName, FileMode.Open);
IWorkbook workbook = new HSSFWorkbook(file);
ISheet sheet = workbook.GetSheetAt(0);//取第一个表
{
IRow headerRow = sheet.GetRow(0);//第一行为标题行
int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1
for (int i = (sheet.FirstRowNum + 2); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
if (row != null)
{
//for (int j = row.FirstCellNum; j < cellCount; j++)
//{
// if (row.GetCell(j) != null)
// Console.WriteLine(row.GetCell(j));
BasicProject bp = new BasicProject();
bp.Name = row.GetCell(2).ToString();
//项目类型
String typeName = row.GetCell(3).ToString();
//项目资金来源
String moneyresource = row.GetCell(4).ToString();
if (typeName.Equals("")) break;
//var moneys=db.
var type = db.ProjectTypes.First(d => d.Name == typeName);
bp.Type = type;
//}
}
}
}
}
finally
{
if (file != null)
file.Close();
}
}
//
// GET: /Projects/Search/
public ActionResult Search(string number, string name, int? type, int? department, int? stage)
{
var bp = new BasicProject();
ViewData["Type"] = bp.GetProjectTypeList();
ViewData["Department"] = bp.GetUserDepartmentList();
ViewData["Stage"] = bp.GetProjectStageList();
if (number == null && name == null && type == null && department == null && stage == null)
{
return View(new List<ProjectModel>());
}
// 查询符合条件的项目
var projects = from p in db.BasicProjects
from t in db.ProjectTypes
from d in db.UserDepartments
from s in db.ProjectStages
where p.Type.ID == t.ID &&
p.Department.DepartmentId == d.DepartmentId &&
p.Stage.ID == s.ID
select new ProjectModel
{
Number = p.Number,
Name = p.Name,
TypeId = t.ID,
TypeName = t.Name,
DepartmentId = d.DepartmentId,
DepartmentName = d.DepartmentName,
StageId = s.ID,
StageName = s.Name
};
if (!String.IsNullOrEmpty(number))
{
projects = projects.Where(s => s.Number.Contains(number));
}
if (!String.IsNullOrEmpty(name))
{
projects = projects.Where(s => s.Name.Contains(name));
}
if (type != null)
{
projects = projects.Where(s => s.TypeId == type);
}
if (department != null)
{
projects = projects.Where(s => s.DepartmentId == department);
}
if (stage != null)
{
projects = projects.Where(s => s.StageId == stage);
}
//
var projects2 = projects.ToList();
int count = 1;
foreach (var project in projects2)
{
project.Sequence = count++;
}
return View(projects2);
}
private void RestoreObjectFields(BasicProject project)
{
project.Type = (from p in db.BasicProjects from t in db.ProjectTypes where p.ID == project.ID && p.Type.ID == t.ID select t).Single();
project.Department = (from p in db.BasicProjects from d in db.UserDepartments where p.ID == project.ID && p.Department.DepartmentId == d.DepartmentId select d).Single();
project.Stage = (from p in db.BasicProjects from s in db.ProjectStages where p.ID == project.ID && p.Stage.ID == s.ID select s).Single();
}
public ActionResult Create(BasicProject project)
{
if (!ModelState.IsValidField("Type"))
{
var typeId = int.Parse(Request.Params["Type"]);
var type = db.ProjectTypes.First(d => d.ID == typeId);
if (type == null)
{
ModelState.AddModelError("Type", "项目类型模型转换失败");
}
else
{
project.Type = type;
ModelState.Remove("Type");
}
}
if (!ModelState.IsValidField("Department"))
{
var departmentId = int.Parse(Request.Params["Department"]);
var department = db.UserDepartments.First(d => d.DepartmentId == departmentId);
if (department == null)
{
ModelState.AddModelError("Department", "部门模型转换失败");
}
else
{
project.Department = department;
ModelState.Remove("Department");
}
}
if (!ModelState.IsValidField("Stage"))
{
var stageId = int.Parse(Request.Params["Stage"]);
var stage = db.ProjectStages.First(d => d.ID == stageId);
if (stage == null)
{
ModelState.AddModelError("Stage", "项目类型模型转换失败");
}
else
{
project.Stage = stage;
ModelState.Remove("Stage");
}
}
if (ModelState.IsValid)
{
if (db.BasicProjects.Any(p => p.Name.ToLower().Equals(project.Name.ToLower())))
{
ModelState.AddModelError("Name", "该项目名称已经存在");
}
else
{
db.BasicProjects.Add(project);
db.SaveChanges();
return RedirectToAction("Manage");
}
}
return View(project);
}