NetCoreAPI操作Excel表格

一、开源框架MiniExcel

MiniExcel（推荐使用）

MiniExcel简单、高效避免OOM的.NET处理Excel查、写、填充数据工具。

目前主流框架(EPPlus ,NPIO)大多需要将数据全载入到内存方便操作，但这会导致内存消耗问题，MiniExcel 尝试以 Stream 角度写底层算法逻辑，能让原本1000多MB占用降低到几MB，避免内存不够情况。

二、引入MiniExce的Nuget包

Install-Package MiniExcel -Version 1.26.5

三、操作Excel示例

1.准备两个Excel表格

两张数据格式

2.创建Student类

因为MiniExcel生成的是强类型数据，所以需要一个类来承接数据

using System.ComponentModel.DataAnnotations;

namespace _02_EFWithOptionExcel
{
    public class Student
    {
        public int ID { get; set; }
        [StringLength(50)]
        public string Name { get; set; }
        [StringLength(2)]
        public string Sex { get; set; }
        [StringLength(11)]
        public string Phone { get; set; }
        [StringLength(200)]
        public string Address { get; set; }
    }
}

3.使用EFCore来操作数据

创建Excel控制器

using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using MiniExcelLibs;
using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;

namespace _02_EFWithOptionExcel.Controllers
{
   
    [Route("api/[controller]")]
    [ApiController]
    public class MinExcelController : ControllerBase
    {
        private readonly StudentDbContext _studentDbContext;
        private readonly IWebHostEnvironment _webHostingEnvironment;
        public MinExcelController(IWebHostEnvironment hostingEnvironment,StudentDbContext studentDbContext)
        {
            _webHostingEnvironment = hostingEnvironment;
            _studentDbContext = studentDbContext;
        }

        [HttpPost]
       public List<Student>  Get([FromForm] IFormCollection formCollection)
        {
            //文件集合
            FormFileCollection fileCollection = (FormFileCollection)formCollection.Files;
            //学生集合
            List<Student> lists = new List<Student>();
            //遍历集合中的文件
            foreach (IFormFile file in fileCollection)
            {
                //打开文件stream
                Stream stream = file.OpenReadStream();
                //操纵stream，生成集合
                var rows = stream.Query<Student>().ToList();
                //将两个excel合并为一个集合
                lists = lists.Union(rows).ToList();
            }
            //批量添加数据
            _studentDbContext.Student.AddRange(lists);
            //保存数据
            _studentDbContext.SaveChanges();

            return lists;
        }
    }
}

四、测试Api

五、试用NPOI来创建excel文件

1.项目引入NPOI包

2.创建文件数据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; }
    }

3.控制器

        [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");
        }

4.创建序列化方法类NpoiData

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

5.生成文件方法

        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();
            }
        }