NPOI实现解析与导出EXCEL文件

准备工作

下载Nuget包
NPOI

解析

1. 创建接口

  [HttpPost]
  [Route("UpLoad")]
  public string UpLoadCityLabel(IFormFile file)
  {
      using (var stream = new MemoryStream())
      {
          file.CopyTo(stream);
          stream.Position = 0;
          IWorkbook workbook = new XSSFWorkbook(stream);
          //读取excel第一个sheet
          ISheet sheet = workbook.GetSheetAt(0);
          //设置城市排序
          int provinceIndex = 1;
          List<string> listFirst = new List<string>();
          StringBuilder stringBuilder = new StringBuilder();
          for (int rowFirstIndex = 1; rowFirstIndex <= sheet.LastRowNum; rowFirstIndex++)
		  {
		      // 获取弟excel第几行
		      IRow row = sheet.GetRow(rowFirstIndex);
		      //获取第几行的第几列数据
		      var cellValue = row.GetCell(0);
		      listFirst.Add(cellValue.ToString());
		  }
		  foreach (var item in listFirst.Distinct().ToList())
		 {
			//拼接成sql
		     stringBuilder.Append($"INSERT INTO TableA(X,Y,Z) VALUES('{item}',{provinceIndex++},0);\r\n");
		 }
          return stringBuilder.ToString();;
      }
  }

2.excel 表

3.通过NPOI实现插入城市sql拼接

导出

创建文件数据Dto类

public class Test
    {
        public int Id { get; set; } 
        public string Name { get; set; }   
        public int Age { get; set; }
        public string Address { get; set; }
        public string Email { get; set; }
    }

2.控制器接口

        [HttpGet("npoi")]
        public async Task<FileContentResult> Npoi()
        {
            List<Test> tests = new List<Test>();
            tests.Add(new Test { Id = 1, Address = "上海市普陀区", Age = 6, Email = "12345678@qq.com", Name = "nihao" });
            tests.Add(new Test { Id = 2, Address = "上海市普陀区", Age = 6, Email = "12345678@qq.com", Name = "nihao" });
            tests.Add(new Test { Id = 3, Address = "上海市普陀区", Age = 6, Email = "12345678@qq.com", Name = "nihao" });
            tests.Add(new Test { Id = 4, Address = "上海市普陀区", Age = 6, Email = "12345678@qq.com", Name = "nihao" });
            //"application/octet-stream": 这是响应的 MIME 类型，表示二进制流数据。对于下载文件，通常使用这个 MIME 类型。
            //test.xls文件名
            return File(await NpoiData(tests), "application/octet-stream", $"test.xls");
        }

3.创建序列化方法类NpoiData

        public async Task<byte[]> NpoiData(List<Test> test)
        {
            return GenerateExcelFile(test);
        }

4.生成文件方法

        private byte[] GenerateExcelFile(List<Test> orders)
        {
            var excel = new NPOI.HSSF.UserModel.HSSFWorkbook();
            var sheet = excel.CreateSheet("sheet1");
            NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(0);
            row1.CreateCell(0).SetCellValue("Id");
            row1.CreateCell(1).SetCellValue("地址");
            row1.CreateCell(2).SetCellValue("年龄");
            row1.CreateCell(3).SetCellValue("邮件地址");
            row1.CreateCell(4).SetCellValue("姓名");
        
            //将数据逐步写入sheet1各个行
            for (int i = 0; i < orders.Count; i++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet.CreateRow(i + 1);
                rowtemp.CreateCell(0).SetCellValue(orders[i].Id);
                rowtemp.CreateCell(1).SetCellValue(orders[i].Address);
                rowtemp.CreateCell(2).SetCellValue(orders[i].Age);
                rowtemp.CreateCell(3).SetCellValue(orders[i].Email);
                rowtemp.CreateCell(4).SetCellValue(orders[i].Name);
            }
            using (MemoryStream ms = new MemoryStream())
            {
                excel.Write(ms);
                return ms.ToArray();
            }
        }