【VUE前端+ASP.NET WEB API后端】下载Excel文件

一、VUE前端

1.1、元素定义

元素使用 element 控件

<el-button type="info" 
plain size="medium " 
@click="exportExcel">
<i class="el-icon-edit el-icon--left" />
导出Excel</el-button>

1.2、事件定义

import { GetInfoToExcel } from '@/api/xlsInfo'

 methods: {
exportExcel() {
      this.listLoading = true
      GetInfoToExcel(this.QueryParams).then(res => {
        this.listLoading = false
        const content = res
        const blob = new Blob([content])
        const fileName = '统计.xls'
        if ('download' in document.createElement('a')) { // 非IE下载
          const 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)
        }
      })
    }
}

 1.3 API定义  axios

    responseType: 'blob', 属性最为关键，如果没有设置下载的文件将出现如下报错

Microsoft Excel
"xxxx.xls"的文件格式和扩展名不匹配。文件可能已损坏或不安全。除非您信任其来源，否则请勿打开。是否仍要打开它？

export function GetInfoToExcel(data) {
  return request({
    url: '/api/v1/xls/GetInfoToExcel',
    method: 'post',
    responseType: 'blob',
    data
  })
}

二、ASP.NET WEB API后端

2.1 api路由接口

public class V1PresController : ApiController
{
   [AuthFilterOutside]
   [HttpPost]
   [Route("api/v1/xls/GetInfoToExcel")]
   public async Task<IHttpActionResult> GetInfoToExcel([FromBody]GetInfoRequest jsonText)
   {
       try
       {
           jsonText.CurPage = 1;
           jsonText.PageSize = 9999999;
           List<xlsInfo> list = (await Task.WhenAll(API.GetxlsInfo(jsonText, HttpContext.Current)))[0].list;
           byte[] info = list.ToExcelBytes(xlsInfo.TITLES, xlsInfo.SHEETNAME);
           return ResponseFileExcel(info);
        }
        catch
        {
            return Ok();
         }
    }
}

2.2、从数据获取数据，类型为 List<T>

 

public async Task<GetxlsInfoResponse> GetxlsInfo(GetlxsInfoRequest request, HttpContext httpContext)
        {
            GetxlsInfoResponse res = new GetxlsInfoResponse();
            res.Success();
            try
            {
                using (ssddsEntities db = new ssddsEntities())
                {

                    string sqlFormat = "{0} prescription {1} Order By create_time {2}";

                    string sqlwhere = "";
                    sqlwhere = string.Format(" WHERE create_time {0}",request.BT);

                    if(request.machine_id != 0)
                    {
                        sqlwhere += string.Format(" AND fetch_window = '{0}'", request.machine_id);
                    }

                    string sqlCount = string.Format(sqlFormat, request.SELECTALL,sqlwhere,"");
                    List<prescription> listSrc = await db.Database.SqlQuery<prescription>(sqlCount).ToListAsync();

                    //List<sys_code> sysCodes = await SCGroupCode.machine_type.GetSysCodes();
                    //List<sys_unit> sysUnits = PublicMysql.GetSysUnits();
                    var query = listSrc.GroupBy(o => o.create_time.Date).OrderBy(o=>o.Key);
                    List<PresInfo> Infos = new List<PresInfo>();
                    foreach (var item in query)
                    {
                        int nE = item.Where(o => o.pre_state == 2).Count();
                        int nN = item.Where(o => o.pre_state == 1).Count();
                        int nT = item.Count();
                        string sR = (nN + nE) == 0 ?"0.00%" : string.Format("{0}%", ((float)nE*100.0F / (float)(nN + nE)).ToString("0.00"));
                        PresInfo Info = new PresInfo()
                        {
                            date = item.Key.ToString(Global.DATE),
                            num_total = nT,
                            num_error = nE,
                            num_normal = nN,
                            rate = sR
                        };
                        Infos.Add(Info);
                    }
                    res.list = Infos.ToPageList(request.CurPage,request.PageSize);
                    res.count = Infos.Count;
                }
                return res;
            }
            catch (Exception ex)
            {
                log.Error("GetUserInfo 异常" + ex.Message);
                res.Exception(ex);
                return res;
            }
        }

2.3、将List<T> 通过NPOI第三方Excel库转化为 Byte []数组 

注意：NOPI的工作薄名称规则应该适应Excel文件的命名规则，工作薄名称不能使用，冒号:，中括号[]，斜杠\等符号,否侧将出现以下报错，或其他未知错误。

发现“xxx.xls”中的部分内容有问题。是否让我们尽量尝试恢复？如果您信任此工作薄的源，请单机“是”。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace RSS_SSDDS.API.BIZ
{
    /// <summary>
    /// 
    /// </summary>
    public static class PublicExcel
    {
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <param name="titles"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static byte[] ToExcelBytes<T>(this List<T> data,Dictionary<string,string> titles,string sheetName)
        {
            try
            {
                HSSFWorkbook workbook = new HSSFWorkbook(); // 工作簿
                ExcelAddRow(titles, data, sheetName, ref workbook);
                using ( MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms);
                    byte[] info = ms.ToArray();
                    workbook.Close();
                    return info;
                }
            }
            catch(Exception exp)
            {

            }
            return null;
        }



        /// <summary>
        /// 
        /// </summary>
        public static void ExcelAddRow<T>(Dictionary<string, string> cellHeard, List<T> enList, string sheetName, ref HSSFWorkbook workbook)
        {
            try
            {
                ISheet sheet = workbook.CreateSheet(sheetName); // 工作表
                IRow row = sheet.CreateRow(0);
                List<string> keys = cellHeard.Keys.ToList();
                for (int i = 0; i < keys.Count; i++)
                {
                    row.CreateCell(i).SetCellValue(cellHeard[keys[i]]); // 列名为Key的值
                }
                // 3.List对象的值赋值到Excel的单元格里
                int rowIndex = 1; // 从第二行开始赋值(第一行已设置为单元头)
                foreach (var en in enList)
                {
                    IRow rowTmp = sheet.CreateRow(rowIndex);
                    for (int i = 0; i < keys.Count; i++) // 根据指定的属性名称，获取对象指定属性的值
                    {
                        string cellValue = ""; // 单元格的值
                        object properotyValue = null; // 属性的值
                        System.Reflection.PropertyInfo properotyInfo = null; // 属性的信息

                        // 3.1 若属性头的名称包含'.',就表示是子类里的属性，那么就要遍历子类，eg：UserEn.UserName
                        if (keys[i].IndexOf(".") >= 0)
                        {
                            // 3.1.1 解析子类属性(这里只解析1层子类，多层子类未处理)
                            string[] properotyArray = keys[i].Split(new string[] { "." }, StringSplitOptions.RemoveEmptyEntries);
                            string subClassName = properotyArray[0]; // '.'前面的为子类的名称
                            string subClassProperotyName = properotyArray[1]; // '.'后面的为子类的属性名称
                            System.Reflection.PropertyInfo subClassInfo = en.GetType().GetProperty(subClassName); // 获取子类的类型
                            if (subClassInfo != null)
                            {
                                // 3.1.2 获取子类的实例
                                var subClassEn = en.GetType().GetProperty(subClassName).GetValue(en, null);
                                // 3.1.3 根据属性名称获取子类里的属性类型
                                properotyInfo = subClassInfo.PropertyType.GetProperty(subClassProperotyName);
                                if (properotyInfo != null)
                                {
                                    properotyValue = properotyInfo.GetValue(subClassEn, null); // 获取子类属性的值
                                }
                            }
                        }
                        else
                        {
                            // 3.2 若不是子类的属性，直接根据属性名称获取对象对应的属性
                            properotyInfo = en.GetType().GetProperty(keys[i]);
                            if (properotyInfo != null)
                            {
                                properotyValue = properotyInfo.GetValue(en, null);
                            }
                        }
                        // 3.3 属性值经过转换赋值给单元格值
                        if (properotyValue != null)
                        {
                            cellValue = properotyValue.ToString();
                            // 3.3.1 对时间初始值赋值为空
                            if (cellValue.Trim() == "0001/1/1 0:00:00" || cellValue.Trim() == "0001/1/1 23:59:59")
                            {
                                cellValue = string.Empty;
                            }
                        }

                        // 3.4 填充到Excel的单元格里
                        rowTmp.CreateCell(i).SetCellValue(cellValue);
                    }
                    rowIndex++;
                }
            }
            catch (Exception) { }
        }

    }
}

 2.4、将Byte[] 数组回传给前端

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http.Results;
using System.Threading.Tasks;
using System.Web;
using System.Web.Http;
using System.IO;
using RSS_SSDDS.API.OAuth;
using RSS_SSDDS.API.Models;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Net.Http.Headers;
using System.Web.Http.Results;

namespace RSS_SSDDS.API.Controllers.V1
{
    /// <summary>
    /// 基础控制器
    /// </summary>
    public class BaseController: ApiController
    {
        log4net.ILog log = log4net.LogManager.GetLogger(typeof(BaseController));
        /// <summary>
        /// 
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public ResponseMessageResult ResponseFileExcel(byte[] data)
        {
            var browser = String.Empty;
            if (HttpContext.Current.Request.UserAgent != null)
            {
                browser = HttpContext.Current.Request.UserAgent.ToUpper();
            }
            HttpResponseMessage httpResponseMessage = new HttpResponseMessage(HttpStatusCode.OK);
            Stream stream = new MemoryStream(data);
            httpResponseMessage.Content = new StreamContent(stream);
            httpResponseMessage.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
            httpResponseMessage.Content.Headers.ContentType.CharSet= "utf8";
            httpResponseMessage.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
            {
                FileName =
                    browser.Contains("FIREFOX")
                        ? Path.GetFileName("test.xlsx")
                        : HttpUtility.UrlEncode(Path.GetFileName("test.xls"))
            };
            return ResponseMessage(httpResponseMessage);
        }
        /// <summary>
        /// 获取当前用户编号
        /// </summary>
        /// <returns></returns>
        public async Task<int> getuid()
        {
            try
            {
                HttpContextBase Content = (HttpContextBase)Request.Properties["MS_HttpContext"];
                var token = Content.Request.Headers["Token"];
                return await ValidateTicket(token.ToString());

            }
            catch (Exception ex)
            {
                log.Err(ex);
                return -1;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public async Task<int> ValidateTicket(string encryptToken)
        {
            int uid = -1;
            try
            {
                using (ssddsEntities db = new ssddsEntities())
                {
                    var temp = await db.sys_ticketauth.Where(m => m.token.Equals(encryptToken) && m.state == 1).ToListAsync();
                    int count = temp.Count();
                    if (count > 0)
                    {
                        uid = temp.FirstOrDefault().user_id.Value;
                    }
                }
            }
            catch (Exception ex)
            {
                log.Err(ex);
            }
            return uid;
        }
    }
}