C# MiniExcel 导入和导出表格数据

C# MiniExcel 导入和导出表格数据

1.首先从NuGet包里下载MIniExcel文件引入

然后下面是一段Api控制器导入的代码

导入导出的表格用.xlsx或.xls工作表

 /// <summary>
 /// 上传文件到服务器端
 /// </summary>
 /// <param name="formFile"></param>
 /// <returns></returns>
 [HttpPost]
 public IActionResult Test(IFormFile formFile)
 {
     try
     {
         //获取文件名
         var FileName = formFile.FileName;
         //获取文件后缀
         var fileExtension = Path.GetExtension(FileName);
         //新文件名
         var newFileName = DateTime.Now.ToString("yyyyMMddhhmmss") + fileExtension;
         //文件保存路径
         var filePath = Path.Combine(Directory.GetCurrentDirectory(), "wwwroot", "Upload", newFileName);
         //判断路径是否存在
         if (!Directory.Exists(filePath))
         {
             //创建路径
             Directory.CreateDirectory(filePath);
         }
         filePath = Path.Combine(filePath, newFileName);
         using (var stream = System.IO.File.Create(filePath))
         {
             formFile.CopyTo(stream);
             var list = MiniExcel.Query<ImportDTO>(filePath);
         }
         return Ok(newFileName);
     }
     catch (Exception)
     {

         throw;
     }
 }

 //下面是异步方法导入表格数据到数据库里  表格里的数据类型和字段必须和数据库一一对应

 /// <summary>
 /// 异步方法内存流导入  
 /// </summary>
 /// <param name="formFile">文件</param>
 /// <returns></returns>
 [HttpPost]
 public async Task<IActionResult> ImportExcel()
 {
     try
     {
         var formFile = Request.Form.Files[0];
         Api_Result<string> api_Result = new Api_Result<string>();
         //获取文件名
         var fileName = formFile.FileName;
         // 获取文件后缀
         var fileExtension = Path.GetExtension(fileName);
         // 判断后缀是否是xlsx或者xls
         if (fileExtension != ".xlsx" && fileExtension != ".xls")
         {
             return BadRequest("文件格式错误");
         }
         var length = formFile.Length;
         if (length > 1024 * 1024 * 10)
         {
             return BadRequest("文件大小不能超过10M");
         }
         // MemoryStream 内存流 在内存当中创建一个流（开辟空间）
         using (var stream = new MemoryStream())
         {
             //蒋文件写入内存流
             formFile.CopyTo(stream);
             //将流的位置归零
             stream.Position = 0;
             //将内存流转成List集合
             var list = await stream.QueryAsync<ImportDTO>();
             //将ImportDTO转成User
             //后期这个地方可以使用AutoMapper 来进行转换
             //可以将DTO转成实体 - 或实体类转成DTO
             var users = list.Select(x => new Users
             {
                 UId = YitIdHelper.NextId().ToString(),
                 UName = x.UName,
                 UPwd = x.UPwd,
                 UserName = x.UserName,
                 Age = x.Age,
                 Email = x.Email,
             }).ToList();
             //调用添加方法
             await _iusersService.AddRangeAsync(users);
         }
         api_Result.Success(formFile.ToString());
         return Ok(api_Result);
     }
     catch (Exception)
     {

         throw;
     }
 }

//下面是Excel导出数据库数据到表格的方法

/// <summary>
/// Excel导出
/// </summary>
/// <returns></returns>
[HttpPost]
public async Task<IActionResult> GetExcel(List<UsersDTO> usersDTO)
{
    try
    {
        //  var list = await _usersService.GetUsersExcel();
        List<object> dataTable = new List<object>();
        foreach (var item in usersDTO)
        {
            dataTable.Add(new
            {
                用户名 = item.UName,
                密码 = item.UPwd,
                姓名 = item.UserName,
                年龄 = item.Age,
                邮箱 = item.Email
            });
        }
        var ms = new MemoryStream();
        ms.SaveAs(dataTable);
        ms.Seek(0, SeekOrigin.Begin);
        return new FileStreamResult(ms, contentType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
        {
            FileDownloadName = "domo.xlsx"
        };

    }
    catch (Exception)
    {

        throw;
    }
}