TNBLOG
首页
博客
视频
资源
问答
猿趣
手机
关于
搜索
收藏
便签
笔记
消息
创作
登录
剑轩
故如虹,知恩;故如月,知明
博主信息
排名
6
文章
6
粉丝
16
评论
8
文章类别
CSS
15篇
微服务
41篇
Git
14篇
.NET
102篇
移动开发
33篇
软件架构
23篇
.NET Core
119篇
.NET MVC
11篇
英语
3篇
随笔
86篇
Bootstrap
3篇
Redis
21篇
编辑器
10篇
Js相关
15篇
虚拟化
8篇
更多
Oracle
7篇
Python
14篇
数据库
26篇
EF
17篇
微信
3篇
前端
151篇
消息队列
6篇
docker
41篇
多线程
1篇
Java
4篇
软件基础
2篇
C++
2篇
WCF
7篇
Linux
7篇
nginx
5篇
K8S
9篇
ABP
2篇
最新文章
最新评价
{{item.articleTitle}}
{{item.blogName}}
:
{{item.content}}
关于我们
ICP备案 :
渝ICP备18016597号-1
网站信息:
2018-2024
TNBLOG.NET
技术交流:
群号656732739
联系我们:
contact@tnblog.net
欢迎加群
欢迎加群交流技术
原
.net core导入导出excel。下载excel。上传文件与下载文件。下载内存中构建的文件
1791
人阅读
2018/5/9 11:12
总访问:
3935554
评论:
0
收藏:
0
手机
分类:
.NET Core
**使用的是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); } ```
欢迎加群讨论技术,1群:677373950(满了,可以加,但通过不了),2群:656732739
👈{{preArticle.title}}
👉{{nextArticle.title}}
评价
{{titleitem}}
{{titleitem}}
{{item.content}}
{{titleitem}}
{{titleitem}}
{{item.content}}