一、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;
}
}
}