一，NPOI是个啥
NPOI可以对Word或Excel文档等进行读写操作。NPOI 是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目。想更详细的了解NPOI大家可以到官网：https://archive.codeplex.com/?p=npoi下载文档或自行百度哈。
说说NPOI的使用，NPOI是开源的框架，在vs中我们可以通过nuget来安装：
安装成功或你会在引用中看到多出这5个引用：
我们通过这5个dll来实现相关文档的导入到出功能
二，MVC导出Excel
下面通过一个小例子来给简单介绍一下在MVC中如何导出Excel
public FileResult ToExcel2007() { //数据源 var source = ProductsService.GetDataTable(); //保存路径 string prirkdFilePath = HttpContext.Server.MapPath("~/DownLoad") + "\" + DateTime.Now.ToString("yyyyMM") + "\"; if (!Directory.Exists(prirkdFilePath)) Directory.CreateDirectory(prirkdFilePath); //文件名称 string fileNameNoExtension = "测试列表(" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ")"; string FileName = fileNameNoExtension + ".xlsx"; //创建工作簿对象 XSSFWorkbook workbook = new XSSFWorkbook(); //创建工作表 ISheet sheet = workbook.CreateSheet("产品列表"); //创建表头 IRow row = sheet.CreateRow(0); for (int i = 0; i < source.Columns.Count; i++) { ICell cell = row.CreateCell(i); cell.SetCellValue(source.Columns[i].ColumnName); } //填充数据 for (int i = 0; i < source.Rows.Count; i++) { IRow row1 = sheet.CreateRow(i + 1); for (int j = 0; j < source.Columns.Count; j++) { ICell cell = row1.CreateCell(j); cell.SetCellValue(source.Rows[i][j].ToString()); } } //合并路径 prirkdFilePath = Path.Combine(prirkdFilePath, FileName); //转化为流保存 FileStream files = new FileStream(prirkdFilePath, FileMode.Create); workbook.Write(files); files.Close(); files.Dispose(); return File(prirkdFilePath,"application/ms-excel",FileName); }
前端代码：
<button id="toExcel" onclick="daochu()">导出EXCEL</button> <script> function daochu(){ window.open("ToExcel, "_blank"); } </script>
通过这个例子先了解一下NPOI如何简单的使用，本例是通过NPOI自动创建Excel文档，当然我们导出数据也可以用预先设计好的Excel模板，并且可以根据数据类型的不同给表格添加样式
下面我们通过已有模板，设置单元格样式，并且导出图片到Excel中：
public FileResult ToExcel() { //数据源 var source = ProductsService.List(); //保存路径 string prirkdFilePath = HttpContext.Server.MapPath("~/DownLoad") + "\" + DateTime.Now.ToString("yyyyMM") + "\"; if (!Directory.Exists(prirkdFilePath)) Directory.CreateDirectory(prirkdFilePath); //文件名称 string fileNameNoExtension = "产品列表(" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ")"; string fileName = fileNameNoExtension + ".xlsx"; //模板路径 string priModel = Server.MapPath(@"~/DownLoadExcelTemp产品列表.xlsx"); //生成模板读取IO流 FileStream fileStreamRead = new FileStream(priModel, FileMode.Open, FileAccess.Read); //创建工作簿对象 XSSFWorkbook workBook = new XSSFWorkbook(fileStreamRead); //预设单元格样式 XSSFCellStyle comStyle = (XSSFCellStyle)workBook.CreateCellStyle(); comStyle.Alignment = HorizontalAlignment.Center;//垂直居中 XSSFCellStyle dateStyle = (XSSFCellStyle)workBook.CreateCellStyle(); dateStyle.Alignment = HorizontalAlignment.Center;//垂直居中 XSSFDataFormat format = (XSSFDataFormat)workBook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd HH:mm:ss");//日期样式 XSSFCellStyle decimalStyle = (XSSFCellStyle)workBook.CreateCellStyle(); decimalStyle.Alignment = HorizontalAlignment.Center; XSSFDataFormat format2 = (XSSFDataFormat)workBook.CreateDataFormat(); decimalStyle.DataFormat = format2.GetFormat("0.00"); //获取工作表 XSSFSheet xssfSheet = workBook.GetSheetAt(0) as XSSFSheet; int rowIndex = 1; //填充数据 foreach (V_Product row in source) { //创建行 XSSFRow xssfRow = xssfSheet.CreateRow(rowIndex) as XSSFRow; xssfRow.CreateCell(0).SetCellValue(row.ProductNumber); xssfRow.Cells[0].CellStyle = comStyle; xssfRow.CreateCell(1).SetCellValue(row.ProductName); xssfRow.Cells[1].CellStyle = comStyle; xssfRow.CreateCell(2); xssfRow.CreateCell(3).SetCellValue(row.ProductStandard); xssfRow.Cells[3].CellStyle = comStyle; xssfRow.CreateCell(4).SetCellValue(Convert.ToDouble(row.Price)); xssfRow.Cells[4].CellStyle = decimalStyle; xssfRow.CreateCell(5).SetCellValue(Convert.ToDouble(row.OrderId)); xssfRow.Cells[5].CellStyle = comStyle; xssfRow.CreateCell(6).SetCellValue(row.CategoryName); xssfRow.Cells[6].CellStyle = comStyle; xssfRow.CreateCell(7).SetCellValue(row.Creator); xssfRow.Cells[7].CellStyle = comStyle; xssfRow.CreateCell(8).SetCellValue(Convert.ToDateTime(row.Createtime)); xssfRow.Cells[8].CellStyle = dateStyle; //第二列添加图片 if (!string.IsNullOrEmpty(row.ProductImg)) { byte[] picBytes = getImageByte(row.ProductImg); int pictureIdx = workBook.AddPicture(picBytes, NPOI.SS.UserModel.PictureType.JPEG); //添加图片 XSSFDrawing patriarch = (XSSFDrawing)xssfSheet.CreateDrawingPatriarch(); XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 100, 50, 2, rowIndex, 2 + 1, rowIndex + 1); //图片位置，图片左上角为（col, row） XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); //pict.Resize(); //用图片原始大小来显示 } } //合并路径 prirkdFilePath = Path.Combine(prirkdFilePath, fileName); //转化为流保存 FileStream files = new FileStream(prirkdFilePath, FileMode.Create); workBook.Write(files); files.Close(); files.Dispose(); return File(prirkdFilePath, "application/ms-excel", fileName); }
前端代码同上，当然还可以设置其他样式字体，背景色，字体颜色等
三，MVC导入Excel
下面例子是上传数据及图片Excel：
/// <summary> /// 上传Excel /// </summary> /// <returns></returns> [HttpPost] public ActionResult DoUpload(HttpPostedFileBase file) { //保存路径 string prirkdFilePath = HttpContext.Server.MapPath("~/Excel") + "\" + DateTime.Now.ToString("yyyyMM") + "\"; if (!Directory.Exists(prirkdFilePath)) Directory.CreateDirectory(prirkdFilePath); if (file.ContentLength <= 0) { return Json("导入失败"); } //文件名称 string fileNameNoExtension = "产品列表(" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ")"; string fileName = fileNameNoExtension + ".xlsx"; //合并路径 prirkdFilePath = Path.Combine(prirkdFilePath, fileName); //保存 file.SaveAs(prirkdFilePath); IWorkbook workBook; var extension = Path.GetExtension(fileName); string fileExt = extension.ToLower(); using (FileStream fs = new FileStream(prirkdFilePath, FileMode.Open, FileAccess.Read)) { //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式 if (fileExt == ".xlsx") { workBook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workBook = new HSSFWorkbook(fs); } else { workBook = null; } if (workBook == null) { return null; } ISheet sheet = workBook.GetSheetAt(0); //表头 IRow header = sheet.GetRow(sheet.FirstRowNum); List<T_Product> productList = new List<T_Product>(); //数据 for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) { var product = new T_Product(); product.ProductNumber = GetValueType(sheet.GetRow(i).GetCell(0)).ToString(); product.ProductName = GetValueType(sheet.GetRow(i).GetCell(1)).ToString(); // product.ProductStandard = GetValueType(sheet.GetRow(i).GetCell(3)).ToString(); product.Price = Convert.ToDecimal(GetValueType(sheet.GetRow(i).GetCell(4))); productList.Add(product); } //列总数 int cellCount = header.LastCellNum; //行总数 int rowCount = sheet.LastRowNum;
//Ipoi扩展方法 List<PicturesInfo> picture_list = sheet.GetAllPictureInfos(1, rowCount, 0, cellCount, false); foreach (var a in picture_list) { string imageName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".png"; Image image = Image.FromStream(new MemoryStream(a.PictureData)); string path = ""; path = Path.Combine(HttpContext.Server.MapPath("~/Upload/Img/"), imageName); image.Save(path);//保存 productList[a.MinRow - 1].ProductImg = "/Upload/Img/" + imageName; } foreach (T_Product t in productList) { ProductsService.Add(t); } } return Json("导入成功"); }
扩展方法来自原园友原文地址：https://www.cnblogs.com/hanzhaoxin/p/4442369.html
前端代码如下：
<form method="post" enctype="multipart/form-data" action="/DoUpload" > <button name="file" type="button" id="btnfile">选择文件</button> <button id="btnupload" class="layui-btn"> 上 传 </button> </form>
就这样吧，颈椎疼 ^…^