.net core导入导出excel。下载excel。上传文件与下载文件。下载内存中构建的文件

使用的是npoi先下载依赖

<PackageReference Include="NPOI" Version="2.5.6" />

.net core导入excel

其实就是excel的上传和读取的问题

public ActionResult UpLoadExcel()
{
    string filePath = "";
    try
    {
        IFormFileCollection cols = Request.Form.Files;
        if (cols == null || cols.Count == 0)
        {
            return Content("没有上传文件");
        }
        foreach (IFormFile file in cols)
        {
            //定义可以接收的文件类型数组后缀格式
            string[] LimitPictureType = { ".XLS" };
            //获取图片后缀是否存在数组中
            string currentPictureExtension = Path.GetExtension(file.FileName).ToUpper();
            if (LimitPictureType.Contains(currentPictureExtension))
            {
                string guid = Guid.NewGuid().ToString().Replace("-", "");
                // var new_path = DateTime.Now.ToString("yyyyMMdd")+ file.FileName;
                var new_path = Path.Combine("excelupload/", guid + file.FileName);
                filePath = Path.Combine(Directory.GetCurrentDirectory(), "wwwroot", new_path);
                //这步之前最好做一下文件夹是否存在的判断，如果不存在就创建一下
                using (var stream = new FileStream(filePath, FileMode.Create))
                {
                    file.CopyTo(stream);
                    stream.Flush();
                }
            }
            else
            {
                return Content("请上传指定格式的文件");
            }
        }
    }
    catch (Exception ex)
    {
        return Content("上传文件出错");
    }
    //打开excel所在的位置
    FileStream getStream = new FileStream(filePath, FileMode.Open);
    //获取excel
    HSSFWorkbook workbook = new HSSFWorkbook(getStream);
    //获取excel中的表
    HSSFSheet sheet = workbook.GetSheetAt(0) as HSSFSheet;
    ////获取行
    //HSSFRow row = sheet.GetRow(0) as HSSFRow;
    ////获取列
    //HSSFCell cell = row.GetCell(0) as HSSFCell;
    //string str = cell.StringCellValue;
    List<WacUsers> wacUsersList = new List<WacUsers>();
    if (sheet.LastRowNum == 0 || sheet.LastRowNum == 1) 
    {
        return Content("上传的excel表中数据为空");
    }
    //遍历行
    for (int i = 1; i <= sheet.LastRowNum; i++)
    {
        WacUsers wacUsers = new WacUsers();
        HSSFRow itemrow = sheet.GetRow(i) as HSSFRow;
        string username = itemrow.GetCell(0).StringCellValue;
        string nickname = itemrow.GetCell(1).StringCellValue;
        int utype = 2;
        try
        {
            utype = Convert.ToInt32(itemrow.GetCell(2).NumericCellValue);
        }
        catch (Exception ex)
        {
            return Content("用户类型输入错误,只能输入数字1表示管理员，2表示普通用户");
        }
        wacUsers.username = username;
        //初始密码123456
        wacUsers.userpassword = "123456";
        wacUsers.nickname = nickname;
        wacUsers.utype = utype;
        wacUsersList.Add(wacUsers);
    }
    foreach (WacUsers item in wacUsersList)
    {
        WacUsersDAL wacUsersDAL = new WacUsersDAL();
        int count = wacUsersDAL.Add(item);
    }
    return RedirectToAction("Index", "WacUser");
}

.net core导出excel

其实就是内存中构建好excel然后在下载内存中构建的excel

public void OutPutExcel()
{
    //在内存中构建一个excel
    HSSFWorkbook workbook = new HSSFWorkbook();
    //在创建一个excel的表
    HSSFSheet sheet = workbook.CreateSheet("用户信息表") as HSSFSheet;
    //创建行
    HSSFRow row = sheet.CreateRow(0) as HSSFRow;
    //创建列(填充数据)
    row.CreateCell(0).SetCellValue("编号");
    row.CreateCell(1).SetCellValue("用户");
    //HSSFCell cell = row.CreateCell(1) as HSSFCell;
    //cell.SetCellValue("用户名");
    row.CreateCell(2).SetCellValue("昵称");
    row.CreateCell(3).SetCellValue("类型");
    //合并单元格(合并第一行的第1列到第3列)
    //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 3));
    ////合并单元格(合并行，合并第一行的第2到4列)
    //sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 4, 0, 0));
    //创建一个样式类
    HSSFCellStyle cellstyle = workbook.CreateCellStyle() as HSSFCellStyle;
    //水平居中
    cellstyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
    //垂直居中
    cellstyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
    //cell.CellStyle = cellstyle;
    WacUsersDAL wacUsersDAL = new WacUsersDAL();
    List<WacUsers> wacUsers = wacUsersDAL.GetAllWacUsers(null);
    int rowNumber = 1;
    foreach (WacUsers item in wacUsers)
    {
        HSSFRow rowContent = sheet.CreateRow(rowNumber) as HSSFRow;
        //创建列(填充数据)
        rowContent.CreateCell(0).SetCellValue(item.uid);
        rowContent.CreateCell(1).SetCellValue(item.username);
        //HSSFCell cell = row.CreateCell(1) as HSSFCell;
        //cell.SetCellValue("用户名");
        rowContent.CreateCell(2).SetCellValue(item.nickname);
        rowContent.CreateCell(3).SetCellValue(item.utypeName);
        rowNumber++;
    }
    //把内存流做为文件下载中转
    MemoryStream memoryStream = new MemoryStream();
    workbook.Write(memoryStream);
    //Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
    Response.ContentType = "application/octet-stream;charset=UTF-8"; ;
    string newName = Guid.NewGuid().ToString().Replace("-", "");
    Response.Headers.Add("Content-Disposition", "attachment;filename=" + WebUtility.UrlEncode("用户信息表.xls"));
    //Response.Headers.Add("Content-Disposition", "attachment;filename=用户信息表.xls");
    Response.BodyWriter.WriteAsync(memoryStream.ToArray());
    Response.BodyWriter.FlushAsync();
}

根据模板导出Excel

先读取模板,然后在模板的基础上填充数据

/// <summary>
/// 根据模板导出
/// </summary>
/// <param name="fileId"></param>
public void DownloadContentByTemplate(int? fileId)
{
    /* 先读取模板 */
    string baseDir = Path.Combine(Directory.GetCurrentDirectory(), "wwwroot");
    string filePath = baseDir + "/template/filetemplate.xls";
    FileStream fileTemplate = new FileStream(filePath, FileMode.Open, FileAccess.Read);
    HSSFWorkbook hssfworkbook = new HSSFWorkbook(fileTemplate);
    HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
    /* 然后在模板的基础上填充数据 */
    for (int i = 3; i < 10; i++)
    {
        HSSFRow row = sheet.CreateRow(i) as HSSFRow;
        row.Height = 700;
        for (int j = 0; j < 8; j++)
        {
            HSSFCell cell = row.CreateCell(j) as HSSFCell;
            //创建一个样式类
            HSSFCellStyle cellstyle = hssfworkbook.CreateCellStyle() as HSSFCellStyle;
            //水平居中
            cellstyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            //垂直居中
            cellstyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            cell.SetCellValue("富婆" + i * j);
            cell.CellStyle = cellstyle;
        }
    }
    /* 在把填充好的数据下载即可 */
    MemoryStream memoryStream = new MemoryStream();
    hssfworkbook.Write(memoryStream);
    //Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
    Response.ContentType = "application/octet-stream;charset=UTF-8"; ;
    string newName = Guid.NewGuid().ToString().Replace("-", "");
    Response.Headers.Add("Content-Disposition", "attachment;filename=" + WebUtility.UrlEncode("用户信息表.xls"));
    //Response.Headers.Add("Content-Disposition", "attachment;filename=用户信息表.xls");
    Response.BodyWriter.WriteAsync(memoryStream.ToArray());
    Response.BodyWriter.FlushAsync();
}

下载excel的模板文件

和下载内存中构建的文件不一样，这个是下载已经存在的文件

/// <summary>
/// 下载excel的模板文件
/// </summary>
/// <returns></returns>
public IActionResult OutPutTemp()
{
    var filePath = "/exceltemp/users.xls";
    var fileName = "users.xls";
    /* FileStream fs = new FileStream(_webHostEnvironment.WebRootPath + filePath, FileMode.OpenOrCreate);
                fs.Close();*/
    return File(new FileStream(_webHostEnvironment.WebRootPath + filePath, FileMode.Open),
    "application/octet-stream", fileName);
}

路径也可以这样写：

/// <summary>
/// 模板下载
/// </summary>
/// <returns></returns>
public FileResult DownloadTemplate()
{
    string baseDir = Path.Combine(Directory.GetCurrentDirectory(), "wwwroot");
    string filePath = baseDir + "/template/filetemplate.xls";
    string fileName = "filetemplate.xls";
    return File(new FileStream(filePath, FileMode.Open),
    "application/octet-stream", fileName);
}