【Angular】.NetCore导出Excel和文件下载

相比之前用windows.open(url);向服务器下载文件做了些改进

之前的方式不能携带上token，还需要在服务器上存一份下载的文件；

.NetCore导出Excel中的NPOI组件，写了一个基于配置导出Excel的方法，美化了导出文件的格式：

[Authorize]
[HttpGet("download")]
public IActionResult Download()
{
    DataTable dataTable = new DataTable();

    List<ExportDataColumn> columnList = new List<ExportDataColumn>();
    columnList.Add(new ExportDataColumn() { Prop = "id", Label = "序号", ColumnWidth = 256 * 10 });
    dataTable.Columns.Add(new DataColumn("id", Type.GetType("System.String")));

    columnList.Add(new ExportDataColumn() { Prop = "name", Label = "姓名", ColumnWidth = 256 * 10 });
    dataTable.Columns.Add(new DataColumn("name", Type.GetType("System.String")));

    columnList.Add(new ExportDataColumn() { Prop = "phone", Label = "手机号", ColumnWidth = 256 * 10 });
    dataTable.Columns.Add(new DataColumn("phone", Type.GetType("System.String")));

    columnList.Add(new ExportDataColumn() { Prop = "email", Label = "邮箱", ColumnWidth = 256 * 10 });
    dataTable.Columns.Add(new DataColumn("email", Type.GetType("System.String")));

    columnList.Add(new ExportDataColumn() { Prop = "recordTime", Label = "记录时间", ColumnWidth = 256 * 20 });
    dataTable.Columns.Add(new DataColumn("recordTime", Type.GetType("System.String")));

    for (int i = 0; i < 1000; i++)
    {
        DataRow dr = dataTable.NewRow();
        dr["id"] = i + 1;
        dr["name"] = $"管星-{i}";
        dr["phone"] = $"15377011087-{i}";
        dr["email"] = $"xguan2014@gmail.com-{i}";
        dr["recordTime"] = DateTime.Now;

        dataTable.Rows.Add(dr);
    }

    string path = $"{_hosting.WebRootPath}//{_config["ExportPath"]}";
    string fileName = NPOIHelper.Export(dataTable, columnList, "Export_", path);

    FileStream fs = new FileStream($"{path}//{fileName}", FileMode.Open);
    return File(fs, "application/vnd.ms-excel", fileName);
}

using Core.Correlation;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Reflection;
using static NPOI.HSSF.Util.HSSFColor;

namespace XXXX.Helpers
{
    public class NPOIHelper
    {
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="columnTitle"></param>
        /// <param name="title"></param>
        /// <param name="path"></param>
        /// <returns></returns>
        public static string Export(DataTable dt, List<ExportDataColumn> columnTitle, string title, string path)
        {
            XSSFWorkbook book = new XSSFWorkbook();
            XSSFSheet sheet = book.CreateSheet("Export") as XSSFSheet;

            CellRangeAddress region = new CellRangeAddress(0, 0, 0, columnTitle.Count - 1);
            sheet.AddMergedRegion(region);

            IFont font12 = book.CreateFont();
            font12.FontHeightInPoints = 12;
            font12.FontName = "微软雅黑";
            font12.Boldweight = short.MaxValue;
            font12.Color = Black.Index;

            ICellStyle cellStyle = book.CreateCellStyle();
            cellStyle.Alignment = HorizontalAlignment.Center;
            cellStyle.BorderBottom = BorderStyle.Thin;
            cellStyle.BorderLeft = BorderStyle.Thin;
            cellStyle.BorderRight = BorderStyle.Thin;
            cellStyle.BorderTop = BorderStyle.Thin;
            cellStyle.SetFont(font12);
            cellStyle.FillBackgroundColor = BlueGrey.Index;

            for (int i = region.FirstRow; i <= region.LastRow; i++)
            {
                IRow row = sheet.CreateRow(i);
                for (int j = region.FirstColumn; j <= region.LastColumn; j++)
                {
                    ICell singleCell = row.CreateCell((short)j);
                    singleCell.CellStyle = cellStyle;
                }
            }

            IRow hrow = sheet.GetRow(0);
            hrow.Height = 20 * 20;
            ICell icellltop0 = hrow.GetCell(0);
            icellltop0.CellStyle = cellStyle;
            icellltop0.SetCellValue(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"));

            ICellStyle TitleStyle = book.CreateCellStyle();
            TitleStyle.FillForegroundColor = PaleBlue.Index;
            TitleStyle.FillPattern = FillPattern.SolidForeground;
            TitleStyle.Alignment = HorizontalAlignment.Center;
            TitleStyle.BorderBottom = BorderStyle.Thin;
            TitleStyle.BorderLeft = BorderStyle.Thin;
            TitleStyle.BorderRight = BorderStyle.Thin;
            TitleStyle.BorderTop = BorderStyle.Thin;

            IRow TitleRow = sheet.CreateRow(1);
            TitleRow.Height = 20 * 15;
            for (int i = 0; i < columnTitle.Count; i++)
            {
                ICell cell = TitleRow.CreateCell(i);
                cell.CellStyle = TitleStyle;
                cell.SetCellValue(columnTitle[i].Label);
                sheet.SetColumnWidth(i, columnTitle[i].ColumnWidth);
            }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row = sheet.CreateRow(i + 2);
                for (int j = 0; j < columnTitle.Count; j++)
                {
                    row.CreateCell(j).SetCellValue(dt.Rows[i][columnTitle[j].Prop].ToString());
                }
            }

            ICellStyle borStyle = book.CreateCellStyle();
            borStyle.Alignment = HorizontalAlignment.Center;
            borStyle.BorderBottom = BorderStyle.Thin;
            borStyle.BorderLeft = BorderStyle.Thin;
            borStyle.BorderRight = BorderStyle.Thin;
            borStyle.BorderTop = BorderStyle.Thin;

            for (int i = 1; i <= dt.Rows.Count + 1; i++)
            {
                IRow row = sheet.GetRow(i);
                row.Height = 20 * 16;
                for (int j = 0; j < columnTitle.Count; j++)
                {
                    ICell singleCell = row.GetCell((short)j);
                    singleCell.CellStyle = borStyle;
                }
            }
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            string fileName = title + DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xlsx";
            using (FileStream fsWrite = File.OpenWrite($"{path}//{fileName}"))
            {
                book.Write(fsWrite);
            }
            return fileName;
        }

        /// <summary>
        /// 导入 用户数据 
        /// </summary>
        /// <param name="stream"></param>
        /// <returns></returns>
        public static void Import(string filePath)
        {
            FileStream fs = null;
            IWorkbook workbook = null;
            using (fs = new FileStream(filePath, FileMode.Open))
            {
                if (filePath.EndsWith(".xlsx"))
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else if (filePath.EndsWith(".xls"))
                {
                    workbook = new HSSFWorkbook(fs);
                }
                if (workbook != null)
                {
                    // 添加系统用户
                    ISheet userSheet = workbook.GetSheetAt(0);
                    if (userSheet != null)
                    {
                        int rowCount = userSheet.LastRowNum;
                        for (int i = 1; i <= rowCount; i++)
                        {
                            IRow row = userSheet.GetRow(i);
                            string account = row.GetCell(0).ToString();
                            string name = row.GetCell(1).ToString();
                            string email = row.GetCell(2).ToString();
                            Console.WriteLine($"{account}--{name}--{email}");
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 实体列表转换成DataTable
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="list"> 实体列表</param>
        /// <returns></returns>
        public DataTable ListToDataTable<T>(IList<T> list)
            where T : class
        {
            if (list == null || list.Count <= 0)
            {
                return null;
            }
            DataTable dt = new DataTable(typeof(T).Name);
            DataColumn column;
            DataRow row;

            PropertyInfo[] myPropertyInfo = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

            int length = myPropertyInfo.Length;
            bool createColumn = true;

            foreach (T t in list)
            {
                if (t == null)
                {
                    continue;
                }
                row = dt.NewRow();
                for (int i = 0; i < length; i++)
                {
                    PropertyInfo pi = myPropertyInfo[i];
                    string name = pi.Name;
                    if (createColumn)
                    {
                        column = new DataColumn(name, typeof(string));
                        dt.Columns.Add(column);
                    }
                    row[name] = pi.GetValue(t, null) is null ? "" : pi.GetValue(t, null).ToString();
                }
                if (createColumn)
                {
                    createColumn = false;
                }
                dt.Rows.Add(row);
            }
            return dt;
        }
    }
}

Angualr前端调用接口：

Download() {
    const url = appConfig.apiUrl + '/api/export/download';
    return this.http.get(url, { responseType: 'blob' }).subscribe((results) => {
        SaveExcel(results, '导出数据');
    });
}
  
SaveExcel(data: Blob, name: string) {
    const a = document.createElement('a');
    // tslint:disable-next-line: quotemark
    // tslint:disable-next-line: object-literal-key-quotes
    const blob = new Blob([data], { 'type': 'application/vnd.ms-excel' });
    a.href = URL.createObjectURL(blob);
    a.download = name + '.xlsx';
    a.click();
}