相比之前用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) => {
this.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();
}
