.net mvc 利用NPOI导入导出excel
来源:互联网 发布:linux 入门基础教程 编辑:程序博客网 时间:2024/04/30 00:06
因近期项目做到,所以记录一下:
1、导出Excel :
首先引用NPOI包,从这里下载》download
(Action一定要用FileResult)
http://www.cnblogs.com/puzi0315/p/3265958.html
http://blog.csdn.net/xhccom/article/details/7687264
http://blog.csdn.net/bestreally/article/details/23257851
2、导入Excel:
首先说一些前台吧,mvc上传注意必须加 new { enctype = "multipart/form-data" }
1、导出Excel :
首先引用NPOI包,从这里下载》download
(Action一定要用FileResult)
/// <summary> /// 批量导出需要导出的列表 /// </summary> /// <returns></returns> public FileResult ExportStu2() { //获取list数据 var checkList = (from oc in db.OrganizeCustoms join o in db.Organizes.DefaultIfEmpty() on oc.custom_id equals o.id where oc.organize_id == 1 select new { customer_id = o.id, customer_name = o.name }).ToList(); //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //貌似这里可以设置各种样式字体颜色背景等,但是不是很方便,这里就不设置了 //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("编号"); row1.CreateCell(1).SetCellValue("姓名"); //....N行 //将数据逐步写入sheet1各个行 for (int i = 0; i < checkList.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(checkList[i].customer_id.ToString()); rowtemp.CreateCell(1).SetCellValue(checkList[i].customer_name.ToString()); //....N行 } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); DateTime dt = DateTime.Now; string dateTime = dt.ToString("yyMMddHHmmssfff"); string fileName = "查询结果" + dateTime + ".xls"; return File(ms, "application/vnd.ms-excel", fileName); }前台直接写就可实现:
@Html.ActionLink("点击导出Excel", "ExportStu2")这里有一篇专门介绍设置样式的文章:
http://www.cnblogs.com/puzi0315/p/3265958.html
http://blog.csdn.net/xhccom/article/details/7687264
http://blog.csdn.net/bestreally/article/details/23257851
2、导入Excel:
首先说一些前台吧,mvc上传注意必须加 new { enctype = "multipart/form-data" }
<td> @using (@Html.BeginForm("ImportStu", "ProSchool", FormMethod.Post, new { enctype = "multipart/form-data" })) { <text>选择上传文件:(工作表名为“Sheet1”,“电脑号”在A1单元格。)</text> <input name="file" type="file" id="file" /> <input type="submit" name="Upload" value="批量导入第一批名册" /> }</td>后台实现:只传路径得出DataTable:
/// <summary> /// Excel导入 /// </summary> /// <param name="filePath"></param> /// <returns></returns> public DataTable ImportExcelFile(string filePath) { HSSFWorkbook hssfworkbook; #region//初始化信息 try { using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } } catch (Exception e) { throw e; } #endregion using (NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0)) { DataTable table = new DataTable(); IRow headerRow = sheet.GetRow(0);//第一行为标题行 int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1 //handling header. for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = table.NewRow(); if (row != null) { for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) dataRow[j] = GetCellValue(row.GetCell(j)); } } table.Rows.Add(dataRow); } return table; } }补充一个类:
/// <summary> /// 根据Excel列类型获取列的值 /// </summary> /// <param name="cell">Excel列</param> /// <returns></returns> private static string GetCellValue(ICell cell) { if (cell == null){ return string.Empty; } switch (cell.CellType) { case CellType.BLANK: return string.Empty; case CellType.BOOLEAN: return cell.BooleanCellValue.ToString(); case CellType.ERROR: return cell.ErrorCellValue.ToString(); case CellType.NUMERIC: case CellType.Unknown: default: return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number case CellType.STRING: return cell.StringCellValue; case CellType.FORMULA: try { HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook); e.EvaluateInCell(cell); return cell.ToString(); } catch { return cell.NumericCellValue.ToString(); } } }得到DataTable后,就想怎么操作就怎么操作了。
3 1
- .net mvc 利用NPOI导入导出excel
- .net mvc利用NPOI导入导出excel
- .net mvc 利用NPOI导入导出excel
- ASP.Net MVC利用NPOI导入导出Excel - RuleLu
- C#利用NPOI导出Excel-ASP.NET MVC示例
- .NET 使用NPOI导入导出标准Excel
- Asp.net NPOI导入导出Excel表格
- NPOI Excel导入导出
- 【推荐】.NET(webform,MVC)使用NPOI组件将数据导出、导入Excel
- 利用npoi导出excel
- 利用npoi导出excel
- C# MVC .Net NPOI实现excel导出功能
- Npoi导入导出到Excel
- .NET调用NPOI组件导入导出Excel的操作类
- ASP.NET NPOI Excel导入、导出,高级、全面、总结版
- .net中关于 DataTable利用NPOI导出到Excel
- .net中关于 DataTable利用NPOI导出到Excel
- asp.net 利用NPOI导出数据到Excel模版
- Maven+Spring+Spring MVC+MyBatis+MySQL,搭建SSM框架环境
- PackageInfo、ResolveInfo
- 物联网要学习的技术
- JavaScript实现图片上传预览功能
- OpenCV 3 将会为我们带来什么?
- .net mvc 利用NPOI导入导出excel
- android 中 系统日期时间的获取
- 小流量网站站长照样赚钱的方法
- 炫彩logo粒子效果
- java中文乱码解决之道(八)-----解决URL中文乱码问题
- Android 可以移动的悬浮框
- ABAP_Excel_处理总结
- 经常用到的 编程语句
- Python高级设计结构及使用方法