Npoi+Npoi.Mapper 导出导入简单操作

1：Nuget package

  <PackageReference Include="NPOI" Version="2.5.1" />
  <PackageReference Include="Npoi.Mapper" Version="3.5.1" />

　　using Newtonsoft.Json.Linq;

　　using Npoi.Mapper;

　　using NPOI.HSSF.UserModel;

　　using NPOI.SS.UserModel;

　　using NPOI.XSSF.UserModel;

　　using System.Collections.Generic;

　　using System.Data;

　　using System.IO;

　　using System.Threading.Tasks;

2：帮助类

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Npoi.Mapper;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Threading.Tasks;

namespace GDBS.Shared.ToolKits
{
    public class ExcleHelper : IExcleHelper
    {
        //2021-12-16引入Npoi.Mapper，同时支持excle导入导出，导出方式为excle字节流数组导出直接返回给web前端
        //使用方法参考https://www.cnblogs.com/wucy/p/14125392.html

        /// <summary>
        /// Excel导入成DataTable
        /// </summary>
        /// <param name="file">导入路径(包含文件名与扩展名)</param>
        /// <returns></returns>
        public static async Task<DataTable> ExcelToTable(string file)
        {
            var dt = new DataTable();
            var fileExt = Path.GetExtension(file).ToLower();
            await using var fs = new FileStream(file, FileMode.Open, FileAccess.Read);

            //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
            IWorkbook workbook = fileExt switch
            {
                ".xlsx" => new XSSFWorkbook(fs),
                ".xls" => new HSSFWorkbook(fs),
                _ => null
            };
            if (workbook == null) { return null; }
            var sheet = workbook.GetSheetAt(0);

            //表头  
            var header = sheet.GetRow(sheet.FirstRowNum);
            var columns = new List<int>();
            for (var i = 0; i < header.LastCellNum; i++)
            {
                var obj = GetValueType(header.GetCell(i));
                if (obj == null || obj.ToString() == string.Empty)
                {
                    dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                }
                else
                    dt.Columns.Add(new DataColumn(obj.ToString()));
                columns.Add(i);
            }
            //数据  
            for (var i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
            {
                var dr = dt.NewRow();
                var hasValue = false;
                foreach (var j in columns)
                {
                    dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
                    if (dr[j] != null && dr[j].ToString() != string.Empty)
                    {
                        hasValue = true;
                    }
                }
                if (hasValue)
                {
                    dt.Rows.Add(dr);
                }
            }

            return dt;
        }

        /// <summary>
        /// JArray转DataTable
        /// </summary>
        /// <param name="dataArr"></param>
        /// <returns></returns>
        public static DataTable ConvertToDataTable(JArray dataArr)
        {
            if (dataArr == null || dataArr.Count <= 0)
                return null;
            DataTable result = new DataTable();
            var colnames = ((JObject)(dataArr.First)).Properties();
            List<string> columnNames = new List<string>();
            if (colnames == null)
                return null;
            foreach (var item in colnames)
            {
                if (!columnNames.Contains(item.Name))
                    columnNames.Add(item.Name);
                result.Columns.Add(item.Name, typeof(string));
            }
            foreach (JObject data in dataArr)
            {
                JObject jo = JObject.Parse(data.ToString());
                DataRow row = result.NewRow();
                foreach (var columnName in columnNames)
                {
                    if (jo.Property(columnName) == null)
                    {
                        data.Add(columnName, "");
                        row[columnName] = data[columnName].ToString();
                    }
                    else
                    {
                        row[columnName] = data[columnName].ToString();
                    }
                }
                result.Rows.Add(row);
            }
            return result;
        }

       


        /// <summary>
        /// DataTable导出成Excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="file">导出路径(包括文件名与扩展名)</param>
        public static MemoryStream TableToExcel(DataTable dt, string file)
        {
            var fileExt = Path.GetExtension(file).ToLower();
            IWorkbook workbook = fileExt switch
            {
                ".xlsx" => new XSSFWorkbook(),
                ".xls" => new HSSFWorkbook(),
                _ => null
            };
            if (workbook == null) { return null; }
            var sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);

            //表头  
            var row = sheet.CreateRow(0);
            for (var i = 0; i < dt.Columns.Count; i++)
            {
                var cell = row.CreateCell(i);
                cell.SetCellValue(dt.Columns[i].ColumnName);
            }

            //数据  
            for (var i = 0; i < dt.Rows.Count; i++)
            {
                var row1 = sheet.CreateRow(i + 1);
                for (var j = 0; j < dt.Columns.Count; j++)
                {
                    var cell = row1.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }

            //转为字节数组  
            var stream = new MemoryStream();
            workbook.Write(stream);
            return stream;
            //var buf = stream.ToArray();

            ////保存为Excel文件  
            //using var fs = new FileStream(file, FileMode.Create, FileAccess.Write);
            //fs.Write(buf, 0, buf.Length);
            //fs.Flush();
            //return fs;
        }

        /// <summary>
        /// 获取单元格类型
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueType(ICell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:  
                    return null;
                case CellType.Boolean: //BOOLEAN:  
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:  
                    return cell.NumericCellValue;
                case CellType.String: //STRING:  
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:  
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:  
                default:
                    return "=" + cell.CellFormula;
            }
        }
        /// <summary>
        /// NpoiMapper对象
        /// </summary>
        /// <returns></returns>
        public static Mapper GetNpoiMapper()
        {
            return new Mapper();//使用方法参考：https://www.cnblogs.com/wucy/p/14125392.html
                                //或在05BridgeService搜索参考'GetExcleTest'
        }
        /// <summary>
        /// 文件ContentType
        /// </summary>
        /// <returns></returns>
        public static string XlsxContentType()
        {
            const string xlsxContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";//.xlsx
            //const string xls_contentType = "application/vnd.ms-excel";//.xls 

            return xlsxContentType;
        }
        /// <summary>
        /// 文件名
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public static string XlsxFileName(string name)
        {
            const string fileExtension = ".xlsx";
            var fullName = name + fileExtension;
            return fullName;
        }

        /// <summary>
        /// 文件ContentType
        /// </summary>
        /// <returns></returns>
        public static string XlsContentType()
        {
            //const string xlsxContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";//.xlsx
            const string xlsContentType = "application/vnd.ms-excel";//.xls 

            return xlsContentType;
        }
        /// <summary>
        /// 文件名
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public static string XlsFileName(string name)
        {
            const string fileExtension = ".xls";
            var fullName = name + fileExtension;
            return fullName;
        }
    }
}

  /// <summary>
        /// 导出wxjg Excel数据
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        [HttpPost("ExportExcelForRepaireDatasByChoseId")]
        public async Task<IActionResult> ExportExcelForRepaireDatasByChoseId([FromBody] List<int> ids)
        {
            try
            {
                using MemoryStream str = await _server.ExportExcelForRepaireDatasByChoseId(ids);
                return File(str.ToArray(), ExcleHelper.XlsxContentType(), "维修加固Excel列表");
            }
            catch (Exception)
            {
                return BadRequest(new { msg = "请求异常" });
            }
        }

    /// <summary>
        /// 导出wxjg Excel数据
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public async Task<MemoryStream> ExportExcelForRepaireDatasByChoseId(List<int> ids)
        {
            var data = await (from r in _RepairReinforceEntity
                              join b in _BgeInfo on r.BrigeId equals b.Id
                              where ids.Contains(r.Id) && r.IsDeleted == false
                              select new RepairReinforceExcelDto// MaintainInputAddUpdateDto
                              {
                                  BrigeName = r.BrigeName,
                                  ProjectType = r.ProjectType,
                                  BridgeComponentName = r.BridgeComponentName,
                                  InfomationUser = r.InfomationUser,
                                  ManagerUnitNames = r.ManagerUnitNames,
                                  ProvessMethod = r.ProvessMethod,
                                  ProjectName = r.ProjectName,

                                  InsId = r.Id,
                                  AreaCode = b.AreaCode,
                                  CityCode = b.CityCode
                              }).ToListAsync();
//excel导出
            var mapper = ExcleHelper.GetNpoiMapper();
            var memoryStream = new MemoryStream();
            //mapper.Put<dynamic>(data,0); //多个sheet
            mapper.Save(memoryStream, data, "sheet1");
            return await Task.FromResult(memoryStream);
        }

 导出Excel 单个sheet和多个sheet

using Jabil.Service.Extension.Customs.Dtos;
using jb.quotation.Service.Application.Contracts;
using Volo.Abp.Application.Services;

namespace jb.quotation.Service.Application;
using Npoi.Mapper;
using Microsoft.AspNetCore.Mvc;

public class ExportReportAppService : ApplicationService, IExportReportAppService
{
    public ExportReportAppService()
    {

    }
    public MemoryStream Dotest()
    {
        List<Student> students = new List<Student>{
            new Student{Id=1001,Name="QQ",Age=18,Birthday=DateTime.Now.AddYears(-20)},
            new Student{ Id = 1002,Name="aa",Age=11,Birthday=DateTime.Now.AddYears(-22) },
            new Student{ Id = 1003,Name="zz",Age=12,Birthday=DateTime.Now.AddYears(-30)},
            new Student{ Id = 1004,Name="xx",Age=13,Birthday=DateTime.Now.AddYears(-26)},
            new Student{ Id = 1005,Name="cc",Age=10,Birthday=DateTime.Now.AddYears(-28)}
        };

        List<Student> students2 = new List<Student>{
            new Student{Id=1001,Name="ww",Age=18,Birthday=DateTime.Now.AddYears(-20)},
            new Student{ Id = 1002,Name="ee",Age=11,Birthday=DateTime.Now.AddYears(-22) },
            new Student{ Id = 1003,Name="rr",Age=12,Birthday=DateTime.Now.AddYears(-30)},
            new Student{ Id = 1004,Name="tt",Age=13,Birthday=DateTime.Now.AddYears(-26)},
            new Student{ Id = 1005,Name="yy",Age=10,Birthday=DateTime.Now.AddYears(-28)}
        };
        var mapper = new Npoi.Mapper.Mapper();

        mapper.Map<Student>("生日", s => s.Birthday).Format<Student>("yyyy-MM-dd", s => s.Birthday);//日期格式
        MemoryStream stream = new MemoryStream();
        //將students集合生成的Excel直接放置到Stream中

        mapper.Put<Student>(students, "student1", true);
        mapper.Put<Student>(students2, "student2", true);
        mapper.Save(stream, leaveOpen: true);

        //单个sheet导出 mapper.Save<Student>(stream, students, "student", leaveOpen: true, overwrite: true, xlsx: true);

        return stream;
    }
}

using System.Threading.Tasks;
using Jabil.Service.Extension.Customs.Dtos;
using jb.quotation.Service.Application.Contracts;
using Microsoft.AspNetCore.Mvc;
using Volo.Abp.AspNetCore.Mvc;

namespace jb.quotation.Service.HttpApi;

[Route("api/eme/export")]
[ApiController]
public class ExportReportController : AbpController
{
    private readonly IExportReportAppService _exportReportAppService;
    public ExportReportController(IExportReportAppService exportReportAppService)
    {
        _exportReportAppService = exportReportAppService;
    }

    [HttpGet("dotest")]
    public ActionResult Dotest()
    {
        var stream = _exportReportAppService.Dotest();
        byte[] by = stream.ToArray();
        stream.Dispose();
        return File(by, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Student.xlsx");
    }
}

测试效果

 Npoi样式操作

 Npoi演示设置

 使用Npoi生成Excel简单封装

 /// <summary>
    ///     var pmains = listProduct.Select(c => c.MainInfo).ToList();
    /// </summary>
    private void DoRowCellWork_productInfos<T>(int rowCount, ISheet sheet, ICellStyle cellStyleBgColor, List<T> datas, bool myorder = false)
    {
        int product_rowIndex = rowCount;
        PropertyInfo[] pts = typeof(T).GetProperties();
        if (myorder)
        {
            //order propertity
            pts = pts.Where(c => Attribute.IsDefined(c, typeof(DataMemberAttribute)))
            .OrderBy(c => ((DataMemberAttribute)Attribute.GetCustomAttribute(c, typeof(DataMemberAttribute))).Order).ToArray();
        }

        IRow headRow_product = sheet.CreateRow(product_rowIndex);
        for (int i = 0; i < pts.Length; i++)
        {
            ICell cell = headRow_product.CreateCell(i);
            cell.SetCellValue(pts[i].Name);
            cell.CellStyle = cellStyleBgColor;
        }
        for (int s = 0; s < datas.Count; s++)
        {
            product_rowIndex += 1;
            var row = sheet.CreateRow(product_rowIndex);
            for (int p = 0; p < pts.Length; p++)
            {
                string strV = pts[p].GetValue(datas[s] ?? default)?.ToString() ?? "";
                row.CreateCell(p).SetCellValue(strV);
            }
        }
    }

 Webapi接口返回Excel文件

    string cpath = Directory.GetCurrentDirectory();
        Console.WriteLine("=====================" + cpath);
        string fulpath = Path.Combine(cpath, "wwwroot/myfiles/1.xlsx");
        using var file = File.Create(fulpath);
        wookbook.Write(file);

        MemoryStream stream = new MemoryStream();
        FileStream fileStream = new FileStream(fulpath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
        fileStream.CopyTo(stream);

        byte[] by = stream.ToArray();
        stream.Dispose();
        fileStream.Dispose();
        return by;

 [HttpGet("exportReportQuotation/{QuotationId}")]
    public async Task<ActionResult> exportReportQuotationNewAsync(Guid QuotationId)
    {
        try
        {
            var by = await _exportReportAppService.ExportReportQuotationByNpoiAsync(QuotationId);
            return File(by, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", $"Quotation{QuotationId}.xlsx");
        }
        catch (System.Exception ex)
        {
            _loggerService.LogError(ex.Message);
            return Json(new ResponseResult { Code = Jabil.Service.Extension.Customs.Enum.ResultCode.Error, Msg = "error" });
        }
    }

 测试完整code

using System.Collections.Generic;
using Jabil.Service.Extension.Customs.Dtos;
using jb.quotation.Service.Application.Contracts;
using Volo.Abp.Application.Services;

namespace jb.quotation.Service.Application;
using Npoi.Mapper;
using Microsoft.AspNetCore.Mvc;
using System.Threading.Tasks;
using jb.quotation.Service.Domain.IRepositories;
using jb.quotation.Service.Application.Contracts.Tools;
using Volo.Abp.Domain.Repositories;
using jb.quotation.Service.Domain.Aggregates.Tools;
using Microsoft.EntityFrameworkCore;
using SqlSugar;
using jb.quotation.Service.Application.Contracts.Waves;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using System.Reflection;
using OfficeOpenXml.FormulaParsing.Excel.Functions.DateTime;
using System.Text;
using Elasticsearch.Net;
using System.Runtime.Serialization;
using AutoMapper.Internal;

public class ExportReportAppService : ApplicationService, IExportReportAppService
{
    private readonly IRepository<Eme_ProcessSection, Guid> _processSectionRepostory;

    private readonly IQuotationProductModelsAppService _configAppService;
    private readonly IQuotationProductModelProcessSectionsAppService _loadingconfigAppService;

    //物料，托盘
    private readonly IComponentTypeListAppService _componentTypeListAppService;
    //产品
    private readonly IProductAppService _productAppService;
    //quotation 主要信息
    private readonly IQuotationAppService _quotationconfigAppService;

    private readonly IMachineCostsAppService _machineCostsAppService;

    private readonly ISpaceCostsAppService _spaceCostconfigAppService;


    public ExportReportAppService(IQuotationProductModelsAppService configAppService,
    IComponentTypeListAppService componentTypeListAppService, IProductAppService productAppService, IQuotationAppService quotationconfigAppService,
    IRepository<Eme_ProcessSection, Guid> processSectionRepostory,
    IQuotationProductModelProcessSectionsAppService loadingconfigAppService,
    IMachineCostsAppService machineCostsAppService,
    ISpaceCostsAppService spaceCostconfigAppService
    )
    {
        _processSectionRepostory = processSectionRepostory;//制程段
        _configAppService = configAppService;
        _spaceCostconfigAppService = spaceCostconfigAppService;//machine space Cost
        _componentTypeListAppService = componentTypeListAppService;//物料，托盘
        _productAppService = productAppService; //产品
        _quotationconfigAppService = quotationconfigAppService;//报价占比--
        _loadingconfigAppService = loadingconfigAppService;

        _machineCostsAppService = machineCostsAppService;//机器cost
    }


    public byte[] ExportReportQuotationByNpoiAsync()
    {
        IWorkbook wookbook = new XSSFWorkbook();
        ISheet sheet = wookbook.CreateSheet("student");

        int rowIndex = 0;//第几行
        IRow headRow = sheet.CreateRow(rowIndex);
        // headRow.RowStyle.FillBackgroundColor = (short)12;
        // headRow.RowStyle.FillPattern = FillPattern.SolidForeground;

        ICellStyle cellStyleBgColor = wookbook.CreateCellStyle();
        // cellStyleBgColor.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.White.Index; //IndexedColors.Grey25Percent.Index;
        // cellStyleBgColor.FillPattern = FillPattern.SolidForeground;

        cellStyleBgColor.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        cellStyleBgColor.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        cellStyleBgColor.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        cellStyleBgColor.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;


        List<Student> students = new List<Student>{
            new Student(){Id=1001,Name="QQ",Age=11,Birthday=DateTime.Now.AddYears(-31)},
            new Student(){Id=1002,Name="ww",Age=18,Birthday=DateTime.Now.AddYears(-33)},
            new Student(){Id=1003,Name="rr",Age=13,Birthday=DateTime.Now.AddYears(-21)},
            new Student(){Id=1004,Name="ee",Age=15,Birthday=DateTime.Now.AddYears(-18)},
            new Student(){Id=1005,Name="tt",Age=12,Birthday=DateTime.Now.AddYears(-23)}
         };
        PropertyInfo[] pts = typeof(Student).GetProperties();
        for (int i = 0; i < pts.Length; i++)
        {
            ICell cell = headRow.CreateCell(i);
            cell.SetCellValue(pts[i].Name);
            cell.CellStyle = cellStyleBgColor;
        }
        for (int s = 0; s < students.Count; s++)
        {
            rowIndex = s + 1;
            var row = sheet.CreateRow(rowIndex);
            for (int p = 0; p < pts.Length; p++)
            {
                string strV = pts[p].GetValue(students[s] ?? null)?.ToString() ?? "";
                row.CreateCell(p).SetCellValue(strV);
            }
        }

        string cpath = Directory.GetCurrentDirectory();
        Console.WriteLine("=====================" + cpath);
        string fulpath = Path.Combine(cpath, "wwwroot/myfiles/1.xlsx");
        using var file = File.Create(fulpath);
        wookbook.Write(file);

        MemoryStream stream = new MemoryStream();
        FileStream fileStream = new FileStream(fulpath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
        fileStream.CopyTo(stream);

        byte[] by = stream.ToArray();
        stream.Dispose();
        fileStream.Dispose();
        return by;
        // return File(by,"application/ms-excel", "fileName.xls");
    }
    public async Task<byte[]> ExportReportQuotationByNpoiAsync(Guid quotationId)
    {
        //Quotation主要信息
        var quotationResult = await _quotationconfigAppService.GetByIdAsync(quotationId);

        var quotationMainExportDtos = new List<QuotationMainExportDto>();
        var quotationAllInfo = quotationResult.Data;
        var quotationMainExportDto = ObjectMapper.Map<QuotationOutput, QuotationMainExportDto>(quotationAllInfo);
        quotationMainExportDtos.Add(quotationMainExportDto);

        var quotationSelctProductExportDto = ObjectMapper.Map<List<QuotationProductModelsOutput>, List<QuotationSelctProductExportDto>>(quotationAllInfo.QuotationProductModels.ToList());

        var productIds = quotationSelctProductExportDto.Select(c => c.productId).ToList();

        var listProduct = new List<QueryProductModelAllInfo>();
        foreach (var _productId in productIds)
        {
            //产品信息
            var productResult = await _productAppService.QueryOneProductModel(_productId);
            if (productResult.Data != null)
                listProduct.Add(productResult.Data);
        }
        IWorkbook wookbook = new XSSFWorkbook();
        ISheet sheet = wookbook.CreateSheet("Quotation");
        int rowIndex = 0;//第几行
        IRow headRow = sheet.CreateRow(rowIndex);
        ICellStyle cellStyleBgColor = wookbook.CreateCellStyle();
        // cellStyleBgColor.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.White.Index; //IndexedColors.Grey25Percent.Index;
        // cellStyleBgColor.FillPattern = FillPattern.SolidForeground;
        cellStyleBgColor.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        cellStyleBgColor.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        cellStyleBgColor.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        cellStyleBgColor.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        //Quotation
        PropertyInfo[] pts_quotation = typeof(QuotationMainExportDto).GetProperties();
        for (int i = 0; i < pts_quotation.Length; i++)
        {
            ICell cell = headRow.CreateCell(i);
            cell.SetCellValue(pts_quotation[i].Name);
            cell.CellStyle = cellStyleBgColor;
        }
        for (int s = 0; s < quotationMainExportDtos.Count; s++)
        {
            rowIndex = s + 1;
            var row = sheet.CreateRow(rowIndex);
            for (int p = 0; p < pts_quotation.Length; p++)
            {
                string strV = pts_quotation[p].GetValue(quotationMainExportDtos[s] ?? null)?.ToString() ?? "";
                row.CreateCell(p).SetCellValue(strV);
            }
        }

        // //product  待优化 /// TODO
        int product_rowIndex = quotationMainExportDtos.Count + 3;
        PropertyInfo[] pts_product = typeof(QuotationSelctProductExportDto).GetProperties();
        IRow headRow_product = sheet.CreateRow(product_rowIndex);
        for (int i = 0; i < pts_product.Length; i++)
        {
            ICell cell = headRow_product.CreateCell(i);
            cell.SetCellValue(pts_product[i].Name);
            cell.CellStyle = cellStyleBgColor;
        }
        for (int s = 0; s < quotationSelctProductExportDto.Count; s++)
        {
            product_rowIndex += (s + 1);
            var row = sheet.CreateRow(product_rowIndex);
            for (int p = 0; p < pts_product.Length; p++)
            {
                string strV = pts_product[p].GetValue(quotationSelctProductExportDto[s] ?? null)?.ToString() ?? "";
                row.CreateCell(p).SetCellValue(strV);
            }
        }

        //product info 产品的3个基础信息
        ISheet sheet2 = wookbook.CreateSheet("Product");
        int rowIndex_productInfo = 0;//第几行
        IRow headRow_ProductBaseInfo = sheet2.CreateRow(rowIndex_productInfo);
        var pmains = listProduct.Select(c => c.MainInfo).ToList();
        DoRowCellWork_productInfos<ProductBaseInfoDto>(rowIndex_productInfo, sheet2, cellStyleBgColor, pmains);

        rowIndex_productInfo += pmains.Count + 3;
        var ppcbs = listProduct.Select(c => c.PCBInfo).ToList();
        DoRowCellWork_productInfos<ProductPCBInfoDto>(rowIndex_productInfo, sheet2, cellStyleBgColor, ppcbs);

        rowIndex_productInfo += ppcbs.Count + 3;
        var p_compoments = listProduct.Select(c => c.ComponentInfo).ToList();
        DoRowCellWork_productInfos<ProductComponentInfomationDto>(rowIndex_productInfo, sheet2, cellStyleBgColor, p_compoments);


        // 3个制程段中的内容

        //3个制程段
        var processSectionEntitys = await (await _processSectionRepostory.GetQueryableAsync()).OrderBy(c => c.sort).Take(3).ToListAsync();
        foreach (var processSection in processSectionEntitys)
        {
            //  mapper.FirstRowIndex = -1;

            //sheet smt, wave ,backend  等名称
            string sheetStrName = processSection.name.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries)[0];
            Guid processSectionId = processSection.Id;
            //line loading  List<TotalOfSummaryOutput>
            var loadingResult = _loadingconfigAppService.GetItemOfSummaryAsync(quotationId, processSectionId).Result;
            if (loadingResult == null || loadingResult.Count <= 0)
            {
                continue;
            }
            ISheet sheetProcessSection = wookbook.CreateSheet(sheetStrName);
            int rowIndex_pr = 0;//第几行
            IRow headRow_processSection = sheetProcessSection.CreateRow(rowIndex_pr);

            //loading
            DoRowCellWork_productInfos<TotalOfSummaryOutput>(rowIndex_pr, sheetProcessSection, cellStyleBgColor, loadingResult.ToList());

            // //machine Cost  List<MachineCostOutput>  // TODO jason 还没有ok
            // var machineCostResult = await _machineCostsAppService.GetListOfAllAsync(quotationId, processSectionId);
            // var machineCostList = machineCostResult.Data;
            // if (machineCostList != null && machineCostList.Count > 0)
            // {
            //     mapper.Put<MachineCostOutput>(machineCostList, sheetStrName, false);
            //     mapper.FirstRowIndex = mapper.FirstRowIndex + machineCostList.Count + 3;
            // }

            // //machine space Cost
            // var machineSpaceCostResult = await _spaceCostconfigAppService.GetListByQuotationProcessSectionIdAsync(quotationId);

            //物料
            var materiResult = _componentTypeListAppService.GetShopSupplierbyMaterialDataNew(quotationId, processSectionId).Result;

            rowIndex_pr += loadingResult.Count + 3;
            DoRowCellWork_productInfos<ShopMaterialNew2Dto>(rowIndex_pr, sheetProcessSection, cellStyleBgColor, materiResult.Data,true);

            //托盘工具
            var palletResult = _componentTypeListAppService.GetShopSupplierbyPalletDataNewAsnyc(quotationId, processSectionId).Result;

            rowIndex_pr += materiResult.Data.Count + 3;
            DoRowCellWork_productInfos<ShopPalletNew2Dto>(rowIndex_pr, sheetProcessSection, cellStyleBgColor, palletResult.Data,true);

        }



        string cpath = Directory.GetCurrentDirectory();
        Console.WriteLine("=====================" + cpath);
        string fulpath = Path.Combine(cpath, "wwwroot/myfiles/1.xlsx");
        using var file = File.Create(fulpath);
        wookbook.Write(file);

        MemoryStream stream = new MemoryStream();
        FileStream fileStream = new FileStream(fulpath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
        fileStream.CopyTo(stream);

        byte[] by = stream.ToArray();
        stream.Dispose();
        fileStream.Dispose();
        return by;

    }


    /// <summary>
    ///     var pmains = listProduct.Select(c => c.MainInfo).ToList();
    /// </summary>
    private void DoRowCellWork_productInfos<T>(int rowCount, ISheet sheet, ICellStyle cellStyleBgColor, List<T> datas, bool myorder = false)
    {
        int product_rowIndex = rowCount;
        PropertyInfo[] pts = typeof(T).GetProperties();
        if (myorder)
        {
            //order propertity
            pts = pts.Where(c => Attribute.IsDefined(c, typeof(DataMemberAttribute)))
            .OrderBy(c => ((DataMemberAttribute)Attribute.GetCustomAttribute(c, typeof(DataMemberAttribute))).Order).ToArray();
        }

        IRow headRow_product = sheet.CreateRow(product_rowIndex);
        for (int i = 0; i < pts.Length; i++)
        {
            ICell cell = headRow_product.CreateCell(i);
            cell.SetCellValue(pts[i].Name);
            cell.CellStyle = cellStyleBgColor;
        }
        for (int s = 0; s < datas.Count; s++)
        {
            product_rowIndex += 1;
            var row = sheet.CreateRow(product_rowIndex);
            for (int p = 0; p < pts.Length; p++)
            {
                string strV = pts[p].GetValue(datas[s] ?? default)?.ToString() ?? "";
                row.CreateCell(p).SetCellValue(strV);
            }
        }
    }


    public async Task<byte[]> ExportReportQuotationAsync(Guid quotationId)
    {
        //Quotation主要信息
        var quotationResult = await _quotationconfigAppService.GetByIdAsync(quotationId);

        var quotationMainExportDtos = new List<QuotationMainExportDto>();
        var quotationAllInfo = quotationResult.Data;
        var quotationMainExportDto = ObjectMapper.Map<QuotationOutput, QuotationMainExportDto>(quotationAllInfo);
        quotationMainExportDtos.Add(quotationMainExportDto);

        var quotationSelctProductExportDto = ObjectMapper.Map<List<QuotationProductModelsOutput>, List<QuotationSelctProductExportDto>>(quotationAllInfo.QuotationProductModels.ToList());

        var productIds = quotationSelctProductExportDto.Select(c => c.productId).ToList();

        var listProduct = new List<QueryProductModelAllInfo>();
        foreach (var _productId in productIds)
        {
            //产品信息
            var productResult = await _productAppService.QueryOneProductModel(_productId);
            if (productResult.Data != null)
                listProduct.Add(productResult.Data);
        }

        var mapper = new Mapper();// Npoi.Mapper.

        // style.FillPattern =FillPattern.SolidForeground;

        // mapper.Workbook.GetCellStyleAt(0).FillBackgroundColor=IndexedColors.Green.Index;

        MemoryStream stream = new MemoryStream();
        //將students集合生成的Excel直接放置到Stream中

        string sheetStr = "Quotation";
        mapper.FirstRowIndex = -1;
        mapper.Put<QuotationMainExportDto>(quotationMainExportDtos, sheetStr, false);

        // var style = mapper.Workbook.CreateCellStyle();
        // style.BorderDiagonalLineStyle = BorderStyle.Double;
        // style.FillBackgroundColor = 12;

        mapper.FirstRowIndex = quotationMainExportDtos.Count + 3;

        mapper.Put<QuotationSelctProductExportDto>(quotationSelctProductExportDto, sheetStr, false);

        mapper.FirstRowIndex = quotationMainExportDtos.Count + quotationSelctProductExportDto.Count + 3;


        mapper.FirstRowIndex = -1;
        string sheetStrp = "Product";
        var pmains = listProduct.Select(c => c.MainInfo).ToList();
        mapper.Put<ProductBaseInfoDto>(pmains, sheetStrp, false);

        mapper.FirstRowIndex = pmains.Count + 3;
        var ppcbs = listProduct.Select(c => c.PCBInfo).ToList();
        mapper.Put<ProductPCBInfoDto>(ppcbs, sheetStrp, false);

        mapper.FirstRowIndex = mapper.FirstRowIndex + ppcbs.Count + 3;
        var p_compoments = listProduct.Select(c => c.ComponentInfo).ToList();
        mapper.Put<ProductComponentInfomationDto>(p_compoments, sheetStrp, false);

        //mapper.Put<Student2>(students2, sheetStr, false);

        //3个制程段
        var processSectionEntitys = await (await _processSectionRepostory.GetQueryableAsync()).OrderBy(c => c.sort).Take(3).ToListAsync();
        foreach (var processSection in processSectionEntitys)
        {
            mapper.FirstRowIndex = -1;

            //sheet smt, wave ,backend  等名称
            string sheetStrName = processSection.name.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries)[0];
            Guid processSectionId = processSection.Id;
            //line loading  List<TotalOfSummaryOutput>
            var loadingResult = await _loadingconfigAppService.GetItemOfSummaryAsync(quotationId, processSectionId);
            if (loadingResult == null || loadingResult.Count <= 0)
            {
                continue;
            }
            mapper.Put<TotalOfSummaryOutput>(loadingResult, sheetStrName, false);
            mapper.FirstRowIndex = loadingResult.Count + 3;

            // //machine Cost  List<MachineCostOutput>  // TODO jason 还没有ok
            // var machineCostResult = await _machineCostsAppService.GetListOfAllAsync(quotationId, processSectionId);
            // var machineCostList = machineCostResult.Data;
            // if (machineCostList != null && machineCostList.Count > 0)
            // {
            //     mapper.Put<MachineCostOutput>(machineCostList, sheetStrName, false);
            //     mapper.FirstRowIndex = mapper.FirstRowIndex + machineCostList.Count + 3;
            // }

            // //machine space Cost
            // var machineSpaceCostResult = await _spaceCostconfigAppService.GetListByQuotationProcessSectionIdAsync(quotationId);

            //物料
            var materiResult = await _componentTypeListAppService.GetShopSupplierbyMaterialDataNew(quotationId, processSectionId);

            mapper.Put<ShopMaterialNew2Dto>(materiResult.Data, sheetStrName, false);
            mapper.FirstRowIndex = mapper.FirstRowIndex + materiResult.Data.Count + 3;

            //托盘工具
            var palletResult = await _componentTypeListAppService.GetShopSupplierbyPalletDataNewAsnyc(quotationId, processSectionId);

            mapper.Put<ShopPalletNew2Dto>(palletResult.Data, sheetStrName, false);

            // throw new NotImplementedException();
        }




        mapper.Save(stream, leaveOpen: true);

        byte[] by = stream.ToArray();
        stream.Dispose();
        return by;
    }
}

 [HttpGet("dotest")]
    public ActionResult ExportReportQuotationByNpoiAsync(Guid? quotationId)
    {
        var by = _exportReportAppService.ExportReportQuotationByNpoiAsync();
        return File(by, "application/ms-excel", "Quotation_Test.xls");
    }