ASP.Net WebApi 操作Excel文件（NPOI实现）

前提

安装NPOI

导出

1. 新建service方法，搭建基本框架，获取Excel数据，返回byte[] public byte[] GetByte(){ byte[] bt = null; try { List<SalesOrder> list = SalesOrderBll.Instance.GetList(); //获取数据 } catch (Exception e) { bt = null; Logger.Exception(e); //保存日志 } return bt;}
2. 导入模板（定义好的表头），新建NPOI相关字段 public byte[] GetByte(){ byte[] bt = null; try { List<SalesOrder> list = SalesOrderBll.Instance.GetList(); //获取数据 //导出模板 string fileTemplate = HttpContext.Current.Server.MapPath("/ExportTemplate/导出模板.xlsx"); FileStream fs = new FileStream(fileTemplate, FileMode.Open, FileAccess.Read); //文件流 XSSFWorkbook workbook = new XSSFWorkbook(fs); ISheet sheet = workbook.GetSheet("导出数据"); //工作表 //如果有样式需求可添加(在下方应用) // 设置字体为黑体 IFont font = workbook.CreateFont(); font.FontName = "黑体"; font.Color = HSSFColor.Black.Index; // 设置单元格外边框 ICellStyle cellStyle = workbook.CreateCellStyle(); cellStyle.BorderTop = BorderStyle.Thin; cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; } catch (Exception e) { bt = null; Logger.Exception(e); //保存日志 } return bt;} 如果没有模板就自定义表头(每一列单独设置) public byte[] GetByte(){ byte[] bt = null; try { List<SalesOrder> list = SalesOrderBll.Instance.GetList(); //获取数据 FileStream fs = new FileStream(fileTemplate, FileMode.Open, FileAccess.Read); //文件流 XSSFWorkbook workbook = new XSSFWorkbook(fs); ISheet sheet = workbook.GetSheet("导出数据"); //工作表 IRow row = sheet.CreateRow(0); //Excel的第一行，从0开始 ICell cell = row.CreateCell(0); //Excel的第一行第一列，从0开始 cell.SetCellValue("标题1"); ICell cell2 = row.CreateCell(1); cell2.SetCellValue("标题2"); //以此类推。。 } catch (Exception e) { bt = null; Logger.Exception(e); //保存日志 } return bt;}
3. 新增私有辅助方法，用于设置单元格值 /// <summary>/// 辅助方法/// </summary>/// <param name="row">当前行</param>/// <param name="columnIndex">单元格列数</param>/// <param name="value">单元格值</param>/// <param name="cellStyle">单元格样式</param>/// <param name="font">字体样式</param>private void SetCellValue(IRow row, int columnIndex, object value, ICellStyle cellStyle = null, IFont font = null){ ICell cell = row.CreateCell(columnIndex); if (cellStyle != null) { cell.CellStyle = cellStyle; cell.CellStyle.SetFont(font); } cell.SetCellValue(value?.ToString());}
4. 遍历数据，设置每一行每一列的值 public byte[] GetByte(){ byte[] bt = null; try { List<SalesOrder> list = SalesOrderBll.Instance.GetList(); //获取数据 //导出模板 string fileTemplate = HttpContext.Current.Server.MapPath("/ExportTemplate/光学镜订单导出模板.xlsx"); FileStream fs = new FileStream(fileTemplate, FileMode.Open, FileAccess.Read); //文件流 XSSFWorkbook workbook = new XSSFWorkbook(fs); ISheet sheet = workbook.GetSheet("导出数据"); //工作表 // 设置字体为黑体 IFont font = workbook.CreateFont(); font.FontName = "黑体"; font.Color = HSSFColor.Black.Index; // 设置单元格外边框 ICellStyle cellStyle = workbook.CreateCellStyle(); cellStyle.BorderTop = BorderStyle.Thin; cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; int index = 2; //数据开始行数 foreach (SalesOrder order in list) { IRow row = sheet.CreateRow(index); SetCellValue(row, 0, order.SalesOrderNo, cellStyle, font); //使用私有辅助方法赋值 SetCellValue(row, 1, order.ContactName, cellStyle, font); SetCellValue(row, 2, order.Mobile, cellStyle, font); } } catch (Exception e) { bt = null; Logger.Exception(e); //保存日志 } return bt;}/// <summary>/// 辅助方法/// </summary>/// <param name="row">当前行</param>/// <param name="columnIndex">单元格列数</param>/// <param name="value">单元格值</param>/// <param name="cellStyle">单元格样式</param>/// <param name="font">字体样式</param>private void SetCellValue(IRow row, int columnIndex, object value, ICellStyle cellStyle = null, IFont font = null){ ICell cell = row.CreateCell(columnIndex); if (cellStyle != null) { cell.CellStyle = cellStyle; cell.CellStyle.SetFont(font); } cell.SetCellValue(value?.ToString());}
5. 合并单元格（有需要的使用） 新建私有赋值方法，合并单元格 /// <summary>/// 辅助方法/// </summary>/// <param name="sheet">工作表</param>/// <param name="startRow">合并开始行数</param>/// <param name="endRow">合并结束行数</param>/// <param name="startCol">合并开始列数</param>/// <param name="endCol">合并结束列数</param>private void SetMergedCellStyle(ISheet sheet, int startRow, int endRow, int startCol, int endCol){ CellRangeAddress mergedRegion = new NPOI.SS.Util.CellRangeAddress(startRow, endRow, startCol, endCol); sheet.AddMergedRegion(mergedRegion);} 根据需求进行合并 public byte[] GetByte(){ byte[] bt = null; try { List<SalesOrder> list = SalesOrderBll.Instance.GetList(); //获取数据 //导出模板 string fileTemplate = HttpContext.Current.Server.MapPath("/ExportTemplate/光学镜订单导出模板.xlsx"); FileStream fs = new FileStream(fileTemplate, FileMode.Open, FileAccess.Read); //文件流 XSSFWorkbook workbook = new XSSFWorkbook(fs); ISheet sheet = workbook.GetSheet("导出数据"); //工作表 // 设置字体为黑体 IFont font = workbook.CreateFont(); font.FontName = "黑体"; font.Color = HSSFColor.Black.Index; // 设置单元格外边框 ICellStyle cellStyle = workbook.CreateCellStyle(); cellStyle.BorderTop = BorderStyle.Thin; cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; int index = 2; //数据开始行数 foreach (SalesOrder order in list) { IRow row = sheet.CreateRow(index); IRow row2 = sheet.CreateRow(index + 1); //设置row2是因为我想合并两行数据 SetCellValue(row, 0, order.SalesOrderNo, cellStyle, font); SetMergedCellStyle(sheet, index, index + 1, 0, 0); //合并excel中第i行到i+1行第一列的数据 SetCellValue(row, 1, order.ContactName, cellStyle, font); SetMergedCellStyle(sheet, index, index + 1, 1, 1); SetCellValue(row, 2, order.Mobile1); //第三列不进行合并 SetCellValue(row2, 2, order.Mobile2); } } catch (Exception e) { bt = null; Logger.Exception(e); //保存日志 } return bt;}/// <summary>/// 辅助方法/// </summary>/// <param name="row">当前行</param>/// <param name="columnIndex">单元格列数</param>/// <param name="value">单元格值</param>/// <param name="cellStyle">单元格样式</param>/// <param name="font">字体样式</param>private void SetCellValue(IRow row, int columnIndex, object value, ICellStyle cellStyle = null, IFont font = null){ ICell cell = row.CreateCell(columnIndex); if (cellStyle != null) { cell.CellStyle = cellStyle; cell.CellStyle.SetFont(font); } cell.SetCellValue(value?.ToString());}效果如下，第1、2列进行合并，第三列不合并
6. 重写Npoi流方法（否则会自己关闭流从而报错） /// <summary>/// 重写Npoi流方法/// </summary>internal class NpoiMemoryStream : MemoryStream{ public NpoiMemoryStream() { AllowClose = true; } public bool AllowClose { get; set; } public override void Close() { if (AllowClose) base.Close(); }}
7. 返回byte[] NpoiMemoryStream streamOut = new NpoiMemoryStream { AllowClose = false };workbook.Write(streamOut);streamOut.Seek(0, SeekOrigin.Begin);streamOut.AllowClose = true;bt = streamOut.ToArray(); 完整代码 public byte[] GetByte(){ byte[] bt = null; try { List<SalesOrder> list = SalesOrderBll.Instance.GetList(); //获取数据 //导出模板 string fileTemplate = HttpContext.Current.Server.MapPath("/ExportTemplate/光学镜订单导出模板.xlsx"); FileStream fs = new FileStream(fileTemplate, FileMode.Open, FileAccess.Read); //文件流 XSSFWorkbook workbook = new XSSFWorkbook(fs); ISheet sheet = workbook.GetSheet("导出数据"); //工作表 // 设置字体为黑体 IFont font = workbook.CreateFont(); font.FontName = "黑体"; font.Color = HSSFColor.Black.Index; // 设置单元格外边框 ICellStyle cellStyle = workbook.CreateCellStyle(); cellStyle.BorderTop = BorderStyle.Thin; cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; int index = 2; //数据开始行数 foreach (SalesOrder order in list) { IRow row = sheet.CreateRow(index); IRow row2 = sheet.CreateRow(index + 1); //设置row2是因为我想合并两行数据 SetCellValue(row, 0, order.SalesOrderNo, cellStyle, font); SetMergedCellStyle(sheet, index, index + 1, 0, 0); //合并excel中第i行到i+1行第一列的数据 SetCellValue(row, 1, order.ContactName, cellStyle, font); SetMergedCellStyle(sheet, index, index + 1, 1, 1); SetCellValue(row, 2, order.Mobile1); //第三列不进行合并 SetCellValue(row2, 2, order.Mobile2); } //返回byte[] NpoiMemoryStream streamOut = new NpoiMemoryStream { AllowClose = false }; workbook.Write(streamOut); streamOut.Seek(0, SeekOrigin.Begin); streamOut.AllowClose = true; bt = streamOut.ToArray(); } catch (Exception e) { bt = null; Logger.Exception(e); //保存日志 } return bt;}/// <summary>/// 辅助方法/// </summary>/// <param name="row">当前行</param>/// <param name="columnIndex">单元格列数</param>/// <param name="value">单元格值</param>/// <param name="cellStyle">单元格样式</param>/// <param name="font">字体样式</param>private void SetCellValue(IRow row, int columnIndex, object value, ICellStyle cellStyle = null, IFont font = null){ ICell cell = row.CreateCell(columnIndex); if (cellStyle != null) { cell.CellStyle = cellStyle; cell.CellStyle.SetFont(font); } cell.SetCellValue(value?.ToString());}
8. WebApi返回文件 [HttpPost]public HttpResponseMessage Post(){ var response = Request.CreateResponse(HttpStatusCode.OK); response.Content = new ByteArrayContent(OrderServices.Instance.GetByte()); //刚刚实现的方法 response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment") { FileName = "导出文件.xlsx" }; response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); return response;}

导入

1. 安装MultipartDataMediaFormatter用于控制器接收文件
2. WebApi接收文件 我这里是自己生成了一个类VHttpFile可以添加别的参数，HttpFile用于接收文件，如果只有文件那么控制器那里只写HttpFile就可以了 public class VHttpFile{ public string FileName { get; set; } public HttpFile File { get; set; }} [HttpPost]public bool Import(VHttpFile file){ return ImportServices.Instance.Import(file);}
3. 创建Import方法（基础框架，格式校验） public bool Import(VHttpFile model){ bool result = false; try { HttpFile file = model.File; if (file == null || file.Buffer.Length == 0) return result.Fail("上传文件为空"); if (Path.GetExtension(file.FileName).ToLower() != ".xlsx" && Path.GetExtension(file.FileName).ToLower() != ".xls") return result.Fail("文件格式错误，请上传xlsx格式文件"); } catch(Exception ex) { result = false; Logger.Exception(e); //添加日志 } return result;}
4. 创建NPOI相关类（Excel操作相关类） public bool Import(VHttpFile model){ bool result = false; try { HttpFile file = model.File; if (file == null || file.Buffer.Length == 0) return result.Fail("上传文件为空"); if (Path.GetExtension(file.FileName).ToLower() != ".xlsx" && Path.GetExtension(file.FileName).ToLower() != ".xls") return result.Fail("文件格式错误，请上传xlsx格式文件"); IWorkbook workbook = null; StringBuilder errorMessage = new StringBuilder(); //用于存储有异常的数据 using (var stream = new MemoryStream(file.Buffer)) { if (Path.GetExtension(file.FileName).ToLower() == ".xlsx") //不同版本Excel生成不一样的类 { workbook = new XSSFWorkbook(stream); } else if (Path.GetExtension(file.FileName).ToLower() == ".xls") { workbook = new HSSFWorkbook(stream); } ISheet sheet = workbook.GetSheetAt(0); //获取工作表 int rowCount = sheet.LastRowNum; //最后一行数据 } } catch(Exception ex) { result = false; Logger.Exception(e); //添加日志 } return result;}
5. 遍历表数据，保存到数据库 为什么我从1开始，因为一般表格的第一行是标题，数据从第二行开始，在代码中索引从0开始，所以数据应该从索引1开始。 public bool Import(VHttpFile model){ bool result = false; try { HttpFile file = model.File; if (file == null || file.Buffer.Length == 0) return result.Fail("上传文件为空"); if (Path.GetExtension(file.FileName).ToLower() != ".xlsx" && Path.GetExtension(file.FileName).ToLower() != ".xls") return result.Fail("文件格式错误，请上传xlsx格式文件"); IWorkbook workbook = null; StringBuilder errorMessage = new StringBuilder(); //用于存储有异常的数据 using (var stream = new MemoryStream(file.Buffer)) { if (Path.GetExtension(file.FileName).ToLower() == ".xlsx") //不同版本Excel生成不一样的类 { workbook = new XSSFWorkbook(stream); } else if (Path.GetExtension(file.FileName).ToLower() == ".xls") { workbook = new HSSFWorkbook(stream); } ISheet sheet = workbook.GetSheetAt(0); //获取工作表 int rowCount = sheet.LastRowNum; //最后一行数据 List<SalesOrder> list = new List<SalesOrder>(); //存储需要新增的数据 for (int row = 1; row <= rowCount; row++) { IRow currentRow = sheet.GetRow(row); //当前行 string saleOrderNo = currentRow.GetCell(0).StringCellValue; //订单号 if (string.IsNullOrEmpty(saleOrderNo)) { errorMessage.AppendLine($"第{row}行错误：订单号为空"); continue; } decimal price = (decimal)currentRow.GetCell(1).NumericCellValue; //订单金额 int amount = (int)currentRow.GetCell(2).NumericCellValue; //订单数量 list.Add(new SalesOrder { SaleOrderNo = saleOrderNo, Price = price, Amount = amount }); } result = DBHelper.Save(list); //保存到数据库 if (errorMessage.Length > 0) { Logger.Exception(errorMessage.ToString()); //添加日志 } } } catch(Exception ex) { result = false; Logger.Exception(e); //添加日志 } return result;} 关于上面使用 **StringCellValue、NumericCellValue **或者是其他，取决于单元格的格式，需要注意。如果单元格格式为货币但是你调用的是StringCellValue那么就会报错
6. 待补充（合并单元格读数据） internal class ImportServices{ private static readonly Dictionary<string, ImportServices> DicData = new Dictionary<string, ImportServices>(); /// <summary> /// 属性集合 /// </summary> public Dictionary<int, Params> ParamDic = new Dictionary<int, Params>(); public int StartIndex { get; set; } public int EndIndex { get; set; } public string CategoryCode { get; set; } public static ImportServices GetInstance(ISheet sheet,string categoryCode,int startIndex,int endIndex) { string sheetName = sheet.SheetName; if (!DicData.ContainsKey(sheetName)) { DicData[sheetName] = new ImportServices(sheet, categoryCode, startIndex,endIndex); } return DicData[sheetName]; } private static CellRangeAddress GetRangeAddress(ICell cell) { ISheet sheet = cell.Sheet; for (int i = 0; i < sheet.NumMergedRegions; i++) { CellRangeAddress region = sheet.GetMergedRegion(i); if (region.ContainsRow(cell.RowIndex) && region.ContainsColumn(cell.ColumnIndex)) { return region; } } return null; } public ImportServices(ISheet sheet,string categoryCode, int startIndex, int endIndex) { StartIndex = startIndex; EndIndex = endIndex; CategoryCode = categoryCode; IRow row = sheet.GetRow(1); IRow row2 = sheet.GetRow(2); List <ParamGroups> paramsList = ParamGroupsBll.Instance.GetList(categoryCode); for (int i = startIndex; i < endIndex;) { ICell cell = row.GetCell(i); CellRangeAddress mergedRegion = GetRangeAddress(cell); string title = cell.StringCellValue; if(mergedRegion == null) { Params value = paramsList.FirstOrDefault(p => p.PGroupName == title)?.ParamsList.FirstOrDefault(p => p.ParamName == row2.GetCell(i).StringCellValue); if (value != null) { ParamDic.Add(i, value); } i++; } else { for (int j = mergedRegion.FirstColumn; j <= mergedRegion.LastColumn; j++) { Params value = paramsList.FirstOrDefault(p => p.PGroupName == title)?.ParamsList.FirstOrDefault(p => p.ParamName == row2.GetCell(i).StringCellValue); if(value != null) { ParamDic.Add(j, value); } } i = mergedRegion.LastColumn + 1; } } } public List<Params> GetList(IRow row) { List<Params> list = new List<Params>(); for (int col = StartIndex; col <= EndIndex; col++) { ICell cell = row.GetCell(col); string value = cell.StringCellValue; if (!string.IsNullOrEmpty(value) && ParamDic.ContainsKey(col)) { list.Add(ParamDic[col]); } } return list; }}