public ActionResult ImportHouseUsers()
{
var ImportUsersModel = new ImportUsersModel();
string filePath = System.AppDomain.CurrentDomain.BaseDirectory + "ImportData/ImportTemplate/住宅业主信息导入模板.xlsx";
FileInfo file = new FileInfo(filePath);
ImportUsersModel.HasImportTemplate = file.Exists;
return(View(ImportUsersModel));
}
public IActionResult ImportStep2(ImportUsersModel importUsersModel)
{
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
FileStream fileStream = new FileStream(importUsersModel.FilePath, FileMode.Open);
using (ExcelPackage package = new ExcelPackage(fileStream))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets["Sheet1"];
int count = 2;
while (true)
{
if (worksheet.Cells[$"{importUsersModel.Achternaam}{count}"].Text == "")
{
break;
}
MemberDTO member = new MemberDTO();
member.BondNummer = int.Parse(worksheet.Cells[$"{importUsersModel.Bondsnr}{count}"].Text);
member.LastName = worksheet.Cells[$"{importUsersModel.Achternaam}{count}"].Text;
member.Initials = worksheet.Cells[$"{importUsersModel.Voorletters}{count}"].Text;
member.Insertion = worksheet.Cells[$"{importUsersModel.Tussenvoegsel}{count}"].Text;
member.Name = worksheet.Cells[$"{importUsersModel.Roepnaam}{count}"].Text;
member.Address = new AddressDTO()
{
Street = worksheet.Cells[$"{importUsersModel.Straat}{count}"].Text,
Number = Int32.Parse(worksheet.Cells[$"{importUsersModel.Huisnr}{count}"].Text),
Addition = worksheet.Cells[$"{importUsersModel.Tussenvoegsel}{count}"].Text,
ZipCode = worksheet.Cells[$"{importUsersModel.Postcode}{count}"].Text,
Residence = worksheet.Cells[$"{importUsersModel.Woonplaats}{count}"].Text,
Country = worksheet.Cells[$"{importUsersModel.Land}{count}"].Text
};
member.PhoneNumber = worksheet.Cells[$"{importUsersModel.Telefoon}{count}"].Text;
member.Gender = worksheet.Cells[$"{importUsersModel.ManVrouw}{count}"].Text;
member.BirthDate = DateTime.Parse(worksheet.Cells[$"{importUsersModel.GeboorteDatum}{count}"].Text);
member.Email = worksheet.Cells[$"{importUsersModel.Email}{count}"].Text;
member.PhoneWork = worksheet.Cells[$"{importUsersModel.TelefoonWerk}{count}"].Text;
member.PhoneMobile = worksheet.Cells[$"{importUsersModel.TelefoonMobiel}{count}"].Text;
this._memberSaveRepository.InsertMember(member);
count++;
}
}
fileStream.Close();
return(RedirectToAction("Index"));
}
public IActionResult Import(ImportUsersModel importUsersModel)
{
int format = 0;
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
// Save the file on the server with GUID.xlsx
string uploadsFolder = @"Excel";
string fileName = Guid.NewGuid().ToString() + "_" + importUsersModel.File.FileName;
string filePath = Path.Combine(uploadsFolder, fileName);
importUsersModel.FilePath = filePath;
FileStream fileStream = new FileStream(filePath, FileMode.Create);
importUsersModel.File.CopyTo(fileStream);
fileStream.Close();
// Use the saved file
using (ExcelPackage package = new ExcelPackage(importUsersModel.File.OpenReadStream()))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets["Sheet1"];
if (worksheet.Cells[$"A1"].Text == "Bondsnr" &&
worksheet.Cells[$"B1"].Text == "Achternaam" &&
worksheet.Cells[$"C1"].Text == "Voorletters" &&
worksheet.Cells[$"D1"].Text == "Tussenvoegsel" &&
worksheet.Cells[$"E1"].Text == "Roepnaam" &&
worksheet.Cells[$"F1"].Text == "Straat" &&
worksheet.Cells[$"G1"].Text == "Huisnr" &&
worksheet.Cells[$"H1"].Text == "Toevoeging" &&
worksheet.Cells[$"I1"].Text == "Postcode" &&
worksheet.Cells[$"J1"].Text == "Woonplaats" &&
worksheet.Cells[$"K1"].Text == "Land" &&
worksheet.Cells[$"L1"].Text == "Telefoon" &&
worksheet.Cells[$"M1"].Text == "M/V" &&
worksheet.Cells[$"N1"].Text == "Geboorte datum" &&
worksheet.Cells[$"O1"].Text == "Verenigings lidnummer" &&
worksheet.Cells[$"P1"].Text == "Email" &&
worksheet.Cells[$"Q1"].Text == "Telefoon werk" &&
worksheet.Cells[$"R1"].Text == "Telefoon mobiel")
{
format = 1;
}
}
return(this.ImportStep2(importUsersModel, format));
}
public IActionResult ImportStep2(ImportUsersModel importUsersModel, int format)
{
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
FileStream fileStream = new FileStream(importUsersModel.FilePath, FileMode.Open);
switch (format)
{
case 1:
if (importUsersModel.RemoveCurrent)
{
// TODO: Get club id from session data when login is implemented.
int clubId = 0;
this._memberSaveRepository.RemoveAllMembers(clubId);
}
using (ExcelPackage package = new ExcelPackage(fileStream))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets["Sheet1"];
int count = 2;
while (true)
{
if (worksheet.Cells[$"B{count}"].Text == "")
{
break;
}
MemberDTO member = new MemberDTO();
member.Address = new AddressDTO();
member.BondNummer = int.Parse(worksheet.Cells[$"A{count}"].Text);
member.LastName = worksheet.Cells[$"B{count}"].Text;
member.Initials = worksheet.Cells[$"C{count}"].Text;
member.Insertion = worksheet.Cells[$"D{count}"].Text;
member.Name = worksheet.Cells[$"E{count}"].Text;
member.Address = new AddressDTO()
{
Street = worksheet.Cells[$"F{count}"].Text,
Number = Int32.Parse(worksheet.Cells[$"G{count}"].Text),
Addition = worksheet.Cells[$"H{count}"].Text,
ZipCode = worksheet.Cells[$"I{count}"].Text,
Residence = worksheet.Cells[$"J{count}"].Text,
Country = worksheet.Cells[$"K{count}"].Text
};
member.PhoneNumber = worksheet.Cells[$"L{count}"].Text;
member.Gender = worksheet.Cells[$"M{count}"].Text;
member.BirthDate = DateTime.Parse(worksheet.Cells[$"N{count}"].Text);
member.Email = worksheet.Cells[$"P{count}"].Text;
member.PhoneWork = worksheet.Cells[$"Q{count}"].Text;
member.PhoneMobile = worksheet.Cells[$"R{count}"].Text;
this._memberSaveRepository.InsertMember(member);
count++;
}
}
fileStream.Close();
return(RedirectToAction("Index"));
default:
using (ExcelPackage package = new ExcelPackage(fileStream))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets["Sheet1"];
for (char c = 'A'; c <= 'R'; c++)
{
importUsersModel.Columns.Add(worksheet.Cells[$"{c}1"].Text);
}
}
fileStream.Close();
return(View("ImportStep2", importUsersModel));
}
}
public JsonResult ImportHouseUsers(ImportUsersModel Model)
{
JsonModel jm = new JsonModel();
HttpPostedFileBase file = Model.file;
//Model.file
//存入的文件名
string FileName;
//存入的文件路径
string savePath;
string filename = Path.GetFileName(file.FileName);
string fileEx = System.IO.Path.GetExtension(filename); //获取上传文件的扩展名
//string NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
string FileType = ".xls,.xlsx"; //定义上传文件的类型字符串
if (!FileType.Contains(fileEx))
{
jm.Msg = "文件类型只能是xlsx格式的文件";
jm.Code = "error";
return(Json(jm, JsonRequestBehavior.AllowGet));
}
FileName = DateTime.Now.ToString("yyyyMMddHHmmss") + fileEx;
//存一份及时要导入的数据文件
string path = AppDomain.CurrentDomain.BaseDirectory + "ImportData/ImportHouseUserData/";
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
savePath = Path.Combine(path, FileName);
file.SaveAs(savePath);
//住宅业主接口
IHouseUserBLL houseUserBll = BLLFactory <IHouseUserBLL> .GetBLL("HouseUserBLL");
//当前物业小区Id
var placePropertyId = GetSessionModel().PropertyPlaceId.Value;
//读取存在的要导入的EXCEL数据文件
DataTable dtHouseUser = ImportExcelFile(savePath);
//如果文件中没有内容
if (dtHouseUser.Rows.Count == 0)
{
jm.Msg = "文件内无业主信息";
return(Json(jm, JsonRequestBehavior.AllowGet));
}
//有效业主信息列表
var validateHouseUserlist = new List <T_HouseUser>();
//无效业主信息列表
var invalidateHouseUserList = new List <ImportHouseUserModel>();
for (int i = 0; i < dtHouseUser.Rows.Count; i++)
{
//是否有效标记
bool isValidated = true;
var houseUserModel = new ImportHouseUserModel();
//为导入的ImportHouseUserModel赋值，以便用于无效业主列表
if (dtHouseUser.Rows[i][0] != DBNull.Value)
{
houseUserModel.Name = dtHouseUser.Rows[i][0].ToString().Trim();
}
if (dtHouseUser.Rows[i][1] != DBNull.Value)
{
houseUserModel.Gender = dtHouseUser.Rows[i][1].ToString().Trim();
}
if (dtHouseUser.Rows[i][2] != DBNull.Value)
{
houseUserModel.Phone = dtHouseUser.Rows[i][2].ToString().Trim();
}
if (dtHouseUser.Rows[i][3] != DBNull.Value)
{
houseUserModel.BuildName = dtHouseUser.Rows[i][3].ToString().Trim();
}
if (dtHouseUser.Rows[i][4] != DBNull.Value)
{
houseUserModel.UnitName = dtHouseUser.Rows[i][4].ToString().Trim();
}
if (dtHouseUser.Rows[i][5] != DBNull.Value)
{
houseUserModel.DoorName = dtHouseUser.Rows[i][5].ToString().Trim();
}
if (dtHouseUser.Rows[i][6] != DBNull.Value)
{
houseUserModel.Desc = dtHouseUser.Rows[i][6].ToString().Trim();
}
//如果业主姓名没有就是无效
if (string.IsNullOrEmpty(houseUserModel.Name))
{
isValidated = false;
}
//如果电话没有或者填写不正确就是无效
if (string.IsNullOrEmpty(houseUserModel.Phone) || !CheckPhoneValidate(houseUserModel.Phone))
{
isValidated = false;
}
//如果楼座名称没有就是无效
if (string.IsNullOrEmpty(houseUserModel.BuildName))
{
isValidated = false;
}
//如果单元名称没有就是无效
if (string.IsNullOrEmpty(houseUserModel.UnitName))
{
isValidated = false;
}
//如果单元户名字没有就是无效
if (string.IsNullOrEmpty(houseUserModel.DoorName))
{
isValidated = false;
}
//获取楼座ID，如果没有将新建
int buildId = GetBuildId(houseUserModel.BuildName, placePropertyId);
//获取单元ID，如果没有将新建
int unitId = GetBuildUnitId(houseUserModel.UnitName, buildId);
//获取单元户ID，如果没有将新建
int doorId = GetBuildDoorId(houseUserModel.DoorName, unitId);
//楼座，单元，单元户有一个为0，就是无效数据
if (buildId == 0 || unitId == 0 || doorId == 0)
{
isValidated = false;
}
//如果单元户已经存在也是无效
if (CheckUserExist(doorId, 0))
{
isValidated = false;
}
if (isValidated)
{
var houseuser = new T_HouseUser();
//当前物业小区Id
houseuser.PropertyPlaceId = placePropertyId;
//业主姓名
houseuser.Name = houseUserModel.Name;
//业主电话
houseuser.Phone = houseUserModel.Phone;
//业主单元户
houseuser.DoorId = doorId;
//业主备注
houseuser.Desc = houseUserModel.Desc;
//业主性别（男，女）
if (houseUserModel.Gender == "男")
{
houseuser.Gender = 1;
}
if (houseUserModel.Gender == "女")
{
houseuser.Gender = 0;
}
//将有效的业主加入到有效的列表里面
validateHouseUserlist.Add(houseuser);
}
else
{
//将无效的业主加入到无效的列表里面
invalidateHouseUserList.Add(houseUserModel);
}
}
bool check = true;
//前符合要求的导入数据库
if (validateHouseUserlist.Count > 0)
{
check = houseUserBll.ImportHouseUsers(validateHouseUserlist);
}
//如果有业主信息导入成功
if (validateHouseUserlist.Count > 0 && check)
{
//部分导入失败
if (invalidateHouseUserList.Count > 0)
{
jm.Msg = string.Format("业主信息导入成功{0}条，失败{1}条", validateHouseUserlist.Count, invalidateHouseUserList.Count);
jm.Content = GenerateInvalidateDataExcel(invalidateHouseUserList);
}
else
{
jm.Content = string.Format("{0}条业主信息全部导入成功", validateHouseUserlist.Count);
}
}
else
{
jm.Msg = "业主信息全部导入失败";
}
return(Json(jm, JsonRequestBehavior.AllowGet));
}
private void Import_Click(object sender, RoutedEventArgs e)
{
DataContext = new ImportUsersModel();
}
