VUE + NET API 根据前端列名导出

前端代码

 //导出
    exportToExcel() {
      //处理表头
      let _this = this
      let Entity = _this.queryParam
      let col = []
      let name = []
      _this.columns.forEach((x) => {
        if (x.children) {
          x.children.forEach((i) => {
            col.push(i.title)
            name.push(i.dataIndex)
          })
        } else {
          col.push(x.title)
          name.push(x.dataIndex)
        }
      })
      let Org = this.useOrg.orgId
      let coldata = { col, name, Entity, Org }
      Axios({
        //用axios发送post请求
        method: 'post',
        url: '/CBO/CBO_LabelStock/ExportToExec', // 请求地址 ，也可以传递参数
        headers: {},
        responseType: 'blob', // 表明返回服务器返回的数据类型
        data: coldata,
      }).then((res) => {
        var blob = new Blob([res], { type: 'application/vnd.ms-excel' })
        let headNames = new Date().toDateString('yyyy-MM-dd HH:mm:ss')
        var fileName = 'CBO_LabelStock' + headNames + '.xlsx' //要保存的文件名称
        if ('download' in document.createElement('a')) {
          // 非IE下载
          var elink = document.createElement('a')
          elink.download = fileName
          elink.style.display = 'none'
          elink.href = URL.createObjectURL(blob)
          document.body.appendChild(elink)
          elink.click()
          URL.revokeObjectURL(elink.href) // 释放URL 对象
          document.body.removeChild(elink)
        } else {
          // IE10+下载
          navigator.msSaveBlob(blob, fileName)
        }
        console.log(res)
      })
    },

后端API

1：Controller方法

/// <summary>
        /// 导出
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        [HttpPost]
        public FileContentResult ExportToExec(ExportDTO<CBO_LabelStock> data)
        {
            var addrUrl = _cBO_LabelStockBus.ExportExecAsync(data);
            byte[] fileBytes = System.IO.File.ReadAllBytes(addrUrl);
            string fileName = "finle.xlsx";
            return File(fileBytes, "application/ms-excel", fileName); //关键语句
        }

2：业务处理类

GetIQueryable()方法为一个实体查询扩展类。

   #region 导出
        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public string ExportExecAsync(ExportDTO<CBO_LabelStock> data)
        {
            var se = data.Entity;
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Export");
            var list = GetIQueryable().Where(x => !se.Code.IsNullOrEmpty() ? x.Code.Contains(se.Code) : true)
                .Where(x => !se.Name.IsNullOrEmpty() ? x.Name.Contains(se.Name) : true)
                .Where(x => !se.DwgNo.IsNullOrEmpty() ? x.DwgNo.Contains(se.DwgNo) : true)
                .Where(x => !se.Statue.IsNullOrEmpty() ? x.Statue.Equals(se.Statue) : true)
                 .Where(x => se.StockQty != 0 ? (se.StockQty == 1 ? x.StockQty > x.SafeStockQty : x.StockQty < x.SafeStockQty) : true)
                .Select(x=> new CBO_LabelStockDTO
                {
                    Id = x.Id,
                    Name = x.Name,
                    Code = x.Code,
                    Location = x.Location,
                    Spec = x.Spec,
                    SafeStockQty = x.SafeStockQty,
                    StockQty = x.StockQty,
                    BOMCode = x.BOMCode,
                    DwgNo = x.BOMCode,
                    CreateBy = x.CreateBy,
                    CreateDate = x.CreateDate,
                    StatueText = x.Statue == 0 ? "正常" : "锁定",
                    StatueNow = x.StockQty,
                    strokeColor = x.StockQty > x.SafeStockQty ? "#87d068" : "red",
                    SafeStroks = Convert.ToDouble(x.StockQty) / Convert.ToDouble((x.SafeStockQty * 3)) * 100
                }) .ToList();
            var col = data.col;
            var name = data.name;
            int index = 0;
            var dic = new Dictionary<string, string>();
            name.ForEach(i => { dic.Add(i, col[index]); index++; });
            var exe = new ExportToExcel<CBO_LabelStockDTO>();
            string fileName = StaticVar.DownPath + "CBO_LabelStock" + DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xlsx";
            var file = exe.ByteToFile(exe.Export2Excel(list, dic), fileName);
            return fileName;
        }
        #endregion

3：调用处理类，将根据前端传递的列名命名表格列名

 #region 导出Excel文件
        /// <summary>
        /// 导出Excel文件
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="entities">数据实体</param>
        /// <param name="dicColumns">列对应关系,如Name->姓名</param>
        /// <param name="title">标题</param>
        /// <returns></returns>
        public byte[] Export2Excel(List<T> entities, Dictionary<string, string> dicColumns, string title = null)
        {
            if (entities.Count <= 0)
            {
                return null;
            }
            //HSSFWorkbook => xls
            //XSSFWorkbook => xlsx
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("sheet1");//名称自定义
            IRow cellsColumn = null;
            IRow cellsData = null;
            //获取实体属性名
            PropertyInfo[] properties = entities[0].GetType().GetProperties();
            int cellsIndex = 0;
            //标题
            if (!string.IsNullOrEmpty(title))
            {
                ICellStyle style = workbook.CreateCellStyle();
                //边框  
                style.BorderBottom = BorderStyle.Dotted;
                style.BorderLeft = BorderStyle.Hair;
                style.BorderRight = BorderStyle.Hair;
                style.BorderTop = BorderStyle.Dotted;
                //水平对齐  
                style.Alignment = HorizontalAlignment.Left;

                //垂直对齐  
                style.VerticalAlignment = VerticalAlignment.Center;

                //设置字体
                IFont font = workbook.CreateFont();
                font.FontHeightInPoints = 10;
                font.FontName = "微软雅黑";
                style.SetFont(font);

                IRow cellsTitle = sheet.CreateRow(0);
                cellsTitle.CreateCell(0).SetCellValue(title);
                cellsTitle.RowStyle = style;
                //合并单元格
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 0, dicColumns.Count - 1));
                cellsIndex = 2;
            }
            //列名
            cellsColumn = sheet.CreateRow(cellsIndex);
            int index = 0;
            Dictionary<string, int> columns = new Dictionary<string, int>();
            foreach (var item in dicColumns)
            {
                cellsColumn.CreateCell(index).SetCellValue(item.Value);
                columns.Add(item.Value, index);
                index++;
            }
            cellsIndex += 1;
            //数据
            foreach (var item in entities)
            {
                cellsData = sheet.CreateRow(cellsIndex);
                for (int i = 0; i < properties.Length; i++)
                {
                    if (!dicColumns.ContainsKey(properties[i].Name)) continue;
                    //这里可以也根据数据类型做不同的赋值，也可以根据不同的格式参考上面的ICellStyle设置不同的样式
                    object[] entityValues = new object[properties.Length];
                    entityValues[i] = properties[i].GetValue(item);
                    //获取对应列下标
                    index = columns[dicColumns[properties[i].Name]];
                    cellsData.CreateCell(index).SetCellValue(entityValues[i]==null?"": entityValues[i].ToString());
                }
                cellsIndex++;
            }

            byte[] buffer = null;
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                buffer = ms.GetBuffer();
                ms.Close();
            }
            return buffer;

        }
        #endregion

 

4：将内存保存到磁盘中的方法类

     #region 将byte[]数组保存成文件
        /// <summary>
        /// 将byte[]数组保存成文件
        /// </summary>
        /// <param name="byteArray">byte[]数组</param>
        /// <param name="fileName">保存至硬盘的文件路径</param>
        /// <returns></returns>
        public  bool ByteToFile(byte[] byteArray, string fileName)
        {
            bool result = false;
            if (byteArray!=null)
            {
                try
                {
                    using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write))
                    {
                        fs.Write(byteArray, 0, byteArray.Length);
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    result = false;
                }
            }
            return result;
        }
        #endregion