/// <summary>
/// 导出用户
/// </summary>
/// <param name="queryInput"></param>
/// <returns></returns>
public async Task <UserExcelOutput> Export(UserQueryInput queryInput)
{
var query = queryInput.Adapt <UserQuery>();
var users = await _userRep.GetListAsync(query);
var exportData = users.Select(a => new Dictionary <string, string> {
{ "姓名", a.Name },
{ "性别", a.Gender.ToDescription() },
{ "出生日期", a.Birthday.ToDateTimeString() },
{ "手机号", a.Phone },
{ "邮箱", a.Email },
{ "微信", a.WeChat },
{ "状态", a.Enabled.IsTrue()?"启用":"注销" },
{ "最后登录时间", a.LastLoginTime.ToDateTimeString() },
{ "最后登录地点", a.LastLoginIP }
});
string fileName = $"{Guid.NewGuid()}.xlsx";
string rootPath = _appSettings.FilePath.ExportExcelPath;
H_File.CreateDirectory(rootPath);
string filePath = Path.Combine(rootPath, $"{fileName}");
await H_Excel.ExportByEPPlus(filePath, exportData);
return(new UserExcelOutput {
FileName = fileName, FileId = _protector.Protect(fileName.Split('.')[0], TimeSpan.FromSeconds(5))
});
}
/// <summary>
/// 导入excel
/// </summary>
/// <param name="files"></param>
/// <returns></returns>
public async Task Import(IFormFileCollection files)
{
H_AssertEx.That(files == null || files.Count == 0, "请选择Excel文件");
//格式限制
var allowType = new string[] { "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" };
H_AssertEx.That(files.Any(b => !allowType.Contains(b.ContentType)), "只能上传Excel文件");
////大小限制
//if (files.Sum(b => b.Length) >= 1024 * 1024 * 4)
//{
//}
var users = new List <SysUser>();
foreach (IFormFile file in files)
{
string rootPath = _appSettings.FilePath.ImportExcelPath;
H_File.CreateDirectory(rootPath);
string filePath = Path.Combine(rootPath, file.FileName);
using (var fs = System.IO.File.Create(filePath))
{
// 复制文件
file.CopyTo(fs);
// 清空缓冲区数据
fs.Flush();
}
using (var ep = new ExcelPackage(new FileInfo(filePath)))
{
var worksheet = ep.Workbook.Worksheets[0];
if (worksheet != null && worksheet.Cells[1, 1].Text.Trim() != "姓名")
{
throw new H_Exception("上传数据列名有误，请检查");
}
foreach (var ws in ep.Workbook.Worksheets)
{
int colStart = ws.Dimension.Start.Column; //工作区开始列,start=1
int colEnd = ws.Dimension.End.Column; //工作区结束列
int rowStart = ws.Dimension.Start.Row; //工作区开始行号,start=1
int rowEnd = ws.Dimension.End.Row; //工作区结束行号
for (int i = rowStart + 1; i <= rowEnd; i++) //第1行是列名,跳过
{
var user = new SysUser();
user.Name = ws.Cells[i, colStart].Text;
user.FirstNameInitial = WordsHelper.GetFirstPinyin(user.Name.Substring(0, 1));
user.Password = H_EncryptProvider.HMACSHA256("123456", _appSettings.Key.Sha256Key);
users.Add(user);
}
}
}
}
if (users.Count == 0)
{
return;
}
await _userRep.InsertAsync(users);
}