.net core导入导出excel。下载excel。上传文件与下载文件。下载内存中构建的文件 电脑版发表于：2018/5/9 11:12 **使用的是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); } ```
