前言
NPOI 是 POI 项目的.NET版本，它不使用 Office COM 组件，不需要安装 Microsoft Office，目前支持 Office 2003 和 2007 版本。
1、整个Excel表格叫做工作表：WorkBook（工作薄），包含的叫页（工作表）：Sheet；行：Row；单元格Cell。
2、NPOI是POI的C#版本，NPOI的行和列的index都是从0开始
3、POI读取Excel有两种格式一个是HSSF，另一个是XSSF。 HSSF和XSSF的区别如下：
HSSF is the POI Project‘s pure Java implementation of the Excel ‘97(-2007) file format.
XSSF is the POI Project‘s pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.
即：HSSF适用2007以前的版本,XSSF适用2007版本及其以上的。
本文将通过详细例子聊聊如何利用NPOI导出Excel,导入Excel,以及合并单元格，设置样式，输入公式的内容。
在使用NPOI之前需要先下载NPOI插件包
下载地址：https://archive.codeplex.com/?p=npoi
NPOI导出Excel
在MVC中 可以使用 FileResult 导出Excel输出到前端下载
???????/// <summary> ???????/// 导出数据 ???????/// </summary> ???????/// <returns></returns> ???????public FileResult ExportJK(string Keyword) ???????{ ???????????TablePager tp = new TablePager(); ???????????tp.Keyword = Keyword; ???????????DateTime now = DateTime.Now; ???????????Random rad = new Random(); ???????????int num = rad.Next(10, 100); ???????????string pathString = Request.ApplicationPath; ???????????var mappath = Server.MapPath(pathString); ???????????var domainPath = AppDomain.CurrentDomain.GetData("DataDirectory").ToString(); ???????????if (!Directory.Exists(string.Format("{0}\\Report", domainPath))) ???????????{ ???????????????Directory.CreateDirectory(string.Format("{0}\\Report", domainPath)); ???????????} ???????????//string fileName = string.Format(@"{0}\temp\SummarySalary_{1}.xlsx", mappath, StringHelper.GetRandom(8)); ???????????string fileName = String.Format(@"{0}\\Report\\Invoice{2}_{1}.xlsx", domainPath, num.ToString(), now.ToString("yyyyMMddHHmmss")); ???????????string download = string.Format(@"Invoice{1}_{0}.xlsx", num.ToString(), now.ToString("yyyyMMddHHmmss")); ???????????IDeliverService _Service = CoreServiceFactory.Used.Build<IDeliverService>(); ???????????List<ExportTemplate1> exps = _Service.ExportPrintTable(this.AppUser, tp); ???????????System.IO.FileStream fs; ???????????try ???????????{ ???????????????//创建Excel文件的对象 ???????????????IWorkbook workbook = new HSSFWorkbook(); ???????????????//添加一个sheet ???????????????NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet("Sheet1"); ???????????????//给sheet1添加第一行的头部标题 ???????????????NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); ???????????????row1.CreateCell(0).SetCellValue("导出单号"); ???????????????row1.CreateCell(1).SetCellValue("渠道Code"); ???????????????row1.CreateCell(2).SetCellValue("渠道名称"); ???????????????row1.CreateCell(3).SetCellValue("销售小组"); ???????????????row1.CreateCell(4).SetCellValue("销售人员"); ???????????????row1.CreateCell(5).SetCellValue("仓库"); ???????????????row1.CreateCell(6).SetCellValue("快递公司"); ???????????????row1.CreateCell(7).SetCellValue("销售单号"); ???????????????row1.CreateCell(8).SetCellValue("客户名称"); ???????????????row1.CreateCell(9).SetCellValue("制单时间"); ???????????????row1.CreateCell(10).SetCellValue("发货单金额"); ???????????????row1.CreateCell(11).SetCellValue("代收款"); ???????????????row1.CreateCell(12).SetCellValue("运单号"); ???????????????//将数据逐步写入sheet1各个行 ???????????????for (int i = 0; i < exps.Count; i++) ???????????????{ ???????????????????NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); ???????????????????rowtemp.CreateCell(0).SetCellValue(exps[i].InvoiceNo); ???????????????????rowtemp.CreateCell(1).SetCellValue(exps[i].SalesChannelCode); ???????????????????rowtemp.CreateCell(2).SetCellValue(exps[i].SalesChannelName); ???????????????????rowtemp.CreateCell(3).SetCellValue(exps[i].SalesGroupName); ???????????????????rowtemp.CreateCell(4).SetCellValue(exps[i].SalesUserName); ???????????????????rowtemp.CreateCell(5).SetCellValue(exps[i].WarehouseName); ???????????????????rowtemp.CreateCell(6).SetCellValue(exps[i].ShipperName); ???????????????????rowtemp.CreateCell(7).SetCellValue(exps[i].OrderNo); ???????????????????rowtemp.CreateCell(8).SetCellValue(exps[i].CustomerName); ???????????????????rowtemp.CreateCell(9).SetCellValue(exps[i].CreateDate); ???????????????????rowtemp.CreateCell(10).SetCellValue(exps[i].Receivable.ToString("n")); ???????????????????rowtemp.CreateCell(11).SetCellValue(exps[i].AgencyFund.ToString("n")); ???????????????????rowtemp.CreateCell(12).SetCellValue(exps[i].LogisticCode); ???????????????} ???????????????// 写入到客户端 ????????????????fs = new FileStream(fileName, FileMode.OpenOrCreate); ???????????????workbook.Write(fs); ???????????????fs.Seek(0, SeekOrigin.Begin); ???????????????//fs = NExcelHelper.DataTableToExcel<ExportTemplate1>(exps, fileName, "未发货列表", true, ""); ???????????} ???????????catch (Exception) ???????????{ ???????????????throw new Exception(); ???????????} ???????????return File(fs, "application/vnd.ms-excel", download); ???????}效果如下图
NPOI导入Excel
导入Excel一般是指通过Execl将数据批量保存到数据库中，所以相对导出要麻烦很多，也要严谨很多。
1. 在前台页面提供Excel模板下载
2. 前端选择文件
注意form表单需要加上 enctype="multipart/form-data"，目的是使请求允许提交文件类型
3. 后台程序
???????/// <summary> ???????/// 导入商品数据 ???????/// </summary> ???????/// <param name="FilePath"></param> ???????/// <param name="user"></param> ???????/// <returns></returns> ???????public MsgResult ImportGoods(string FilePath, ApplicationUser user) ???????{ ???????????MsgResult result = new MsgResult(); ???????????result.ErrorNote = new List<ErrorNote>(); ???????????DateTime now = DateTime.Now; ???????????var dt = NExcelHelper.ImportExcelFile(FilePath, "Sheet1", false); ???????????if (dt.Rows.Count <= 0) ???????????{ ???????????????result.ResultCode = ResultCode.Error; ???????????????result.Message = "检测到文件中没有任何行"; ???????????????return result; ???????????} ???????????if (dt.Columns.Count != 12) ???????????{ ???????????????result.ResultCode = ResultCode.Error; ???????????????result.Message = "检测到文件的数据格式不正确"; ???????????????return result; ???????????} ???????????//事务处理 ???????????using (DbContextTransaction dbTransaction = productRepository.EFDb.Database.BeginTransaction()) ???????????{ ???????????????try ???????????????{ ???????????????????for (int i = 0; i < dt.Rows.Count; i++) ???????????????????{ ???????????????????????decimal UnitPrice = 0.00M; //售价 ???????????????????????decimal Cost = 0.00M; ?//进价 ???????????????????????decimal MinPrice = 0.00M; ?//最小 ???????????????????????decimal MaxPrice = 0.00M; ???????????????????????int IsAllow = 1; ???????????????????????string brandname = dt.Rows[i][0].ToString(); ???????????????????????string proName = dt.Rows[i][2].ToString(); ???????????????????????string proUnitPrice = dt.Rows[i][7].ToString(); ???????????????????????string proCost = dt.Rows[i][6].ToString(); ???????????????????????string proMinPrice = dt.Rows[i][8].ToString(); ???????????????????????string proMaxPrice = dt.Rows[i][9].ToString(); ???????????????????????string proIsdecimal = dt.Rows[i][11].ToString(); ???????????????????????bool IsSucc1 = decimal.TryParse(proUnitPrice, out UnitPrice); ???????????????????????bool IsSucc2 = decimal.TryParse(proCost, out Cost); ???????????????????????bool IsSucc3 = decimal.TryParse(proMinPrice, out MinPrice); ???????????????????????bool IsSucc4 = decimal.TryParse(proMaxPrice, out MaxPrice); ???????????????????????int.TryParse(proIsdecimal, out IsAllow); ???????????????????????if (string.IsNullOrWhiteSpace(brandname)) ???????????????????????{ ???????????????????????????throw new Exception("检测到品牌为空！"); ???????????????????????} ???????????????????????List<Brand> brands = brandRepository.QueryBrands(t => t.BrandName == brandname && t.State != State.Deleted).ToList(); ???????????????????????if (brands != null) ???????????????????????{ ???????????????????????????if (brands.Count > 1) ???????????????????????????{ ???????????????????????????????throw new Exception("系统检测到品牌名称 " + brandname + " 存在二义性！"); ???????????????????????????} ???????????????????????} ???????????????????????if (string.IsNullOrWhiteSpace(proName)) ???????????????????????{ ???????????????????????????throw new Exception("检测到有商品名称为空！"); ???????????????????????} ???????????????????????if (string.IsNullOrWhiteSpace(proUnitPrice)) ???????????????????????{ ???????????????????????????throw new Exception("检测到有销售金额为空！"); ???????????????????????} ???????????????????????if (!IsSucc1) ???????????????????????{ ???????????????????????????throw new Exception("系统检测到有 售价 不是一个金额类型！"); ???????????????????????} ???????????????????????if (!string.IsNullOrWhiteSpace(proCost) && !IsSucc2) ???????????????????????{ ???????????????????????????throw new Exception("系统检测到有 进价 不是一个金额类型！"); ???????????????????????} ???????????????????????if (!string.IsNullOrWhiteSpace(proMinPrice) && !IsSucc3) ???????????????????????{ ???????????????????????????throw new Exception("系统检测到有 最低售价 不是一个金额类型！"); ???????????????????????} ???????????????????????if (!string.IsNullOrWhiteSpace(proMinPrice) && !IsSucc4) ???????????????????????{ ???????????????????????????throw new Exception("系统检测到有 最高售价 不是一个金额类型！"); ???????????????????????} ???????????????????????Product pd = new Product(); ???????????????????????pd.BrandId = brands.FirstOrDefault().BrandId; ???????????????????????pd.BarCode = dt.Rows[i][1].ToString(); ???????????????????????pd.ProductName = proName; ???????????????????????pd.ShortName = dt.Rows[i][3].ToString(); ???????????????????????pd.Spec = dt.Rows[i][4].ToString(); ???????????????????????pd.Unit = dt.Rows[i][5].ToString(); ???????????????????????pd.Cost = Cost; ???????????????????????pd.UnitPrice = UnitPrice; ???????????????????????pd.MinPrice = MinPrice; ???????????????????????pd.MaxPrice = MaxPrice; ???????????????????????pd.Remark = dt.Rows[i][10].ToString(); ???????????????????????pd.AllowTheDecimal = IsAllow == 1; ???????????????????????pd.Status = ProductStatus.Normal; ???????????????????????int res = productRepository.InsertForProduct(pd); ???????????????????????if (res <= 0) ???????????????????????{ ???????????????????????????throw new Exception("第 " + i + " 行插入失败，请检查"); ???????????????????????} ???????????????????} ???????????????????dbTransaction.Commit(); ???????????????} ???????????????catch (Exception ex) ???????????????{ ???????????????????dbTransaction.Rollback(); ???????????????????result.ResultCode = ResultCode.Exception; ???????????????????result.Message = ex.Message; ???????????????????return result; ???????????????} ???????????} ???????????result.ResultCode = ResultCode.Success; ???????????result.Message = "商品批量导入成功！"; ???????????return result; ???????}代码中我使用了事物整体处理，并且加了一些必要的验证，保证数据的安全性，真实性。
设置单元格样式
设置单元格样式时需要注意，务必创建一个新的样式对象进行设置，否则会将工作表所有单元格的样式一同设置，它们应该共享的是一个样式对象：
ICellStyle style = workbook.CreateCellStyle();//设置单元格的样式：水平对齐居中style.Alignment = HorizontalAlignment.CENTER;//新建一个字体样式对象
IFont font = workbook.CreateFont();//设置字体加粗样式
font.Boldweight = short.MaxValue;//使用SetFont方法将字体样式添加到单元格样式中
style.SetFont(font);//将新的样式赋给单元格cell.CellStyle = style;
设置单元格的高度：实际是设置其所在行高，所以要在单元格所在行上设置行高，行高设置数值好像是像素点的1/20，所以*20以便达到设置效果；
设置单元格的宽度：实际上是设置其所在列宽，所以要在单元格所在列上设置（列的设置在工作表上），宽度数值好像是字符的1/256，所以*256以便达到设置效果。
//设置单元格的高度row.Height = 30 * 20;//设置单元格的宽度
sheet.SetColumnWidth(0, 30 * 256);
合并单元格：合并单元格实际上是声明一个区域，该区域中的单元格将进行合并，合并后的内容与样式以该区域最左上角的单元格为准。
//设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));
添加公式：使用Cell的CellFormula来设置公式，是一个字符串，公式前不需要加=号。
//通过Cell的CellFormula向单元格中写入公式//注：直接写公式内容即可，不需要在最前加‘=‘ICell cell2 = sheet.CreateRow(1).CreateCell(0);cell2.CellFormula = "HYPERLINK(\"测试图片.jpg\",\"测试图片.jpg\")";
将工作簿写入文件查看效果：
//将工作簿写入文件using (FileStream fs = new FileStream("生成效果.xls", FileMode.Create, FileAccess.Write)){ ???workbook.Write(fs);}效果如下图：
PS:欢迎扫描下方二维码或点击链接，加入QQ群
在Asp.Net MVC中使用NPOI插件实现对Excel的操作（导入，导出，合并单元格，设置样式，输入公式）
原文地址：https://www.cnblogs.com/ydcnblog/p/9306745.html