1.数据导出为Excel的Stream
using System;
using System.Collections.Generic;
using System.IO;
using Abp.Collections.Extensions;
using OfficeOpenXml;
using System.Web;
using Abp.Web.Models;namespace Common.Exporting
{
public static class ExcelExporter<T>
{
/// <summary>
/// 导出Excel文件
/// </summary>
/// <returns></returns>
public static AjaxResponse GetFileResponse(string fileName, string sheetName, IList<T> dtoList, string[] header, Func<T, object>[] propertySelectors)
{
AjaxResponse res = new AjaxResponse();
try
{
byte[] data = ExportExcelStream(sheetName, dtoList,header, propertySelectors); var Response = HttpContext.Current.Response;
Response.ContentType = "applicationnd.ms - excel";
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
Response.Clear();
Response.BinaryWrite(data);
Response.End(); res.Success = true;
}
catch (Exception ex)
{
res.Success = false;
res.Error = new ErrorInfo();
res.Error.Code = ;
res.Error.Message = "导出数据错误";
res.Error.Details = ex.ToString();
} return res;
} public static byte[] ExportExcelStream(string sheetName, IList<T> dtoList, string[] header, Func<T, object>[] propertySelectors)
{
return CreateExcelStream(
excelPackage =>
{
var sheet = excelPackage.Workbook.Worksheets.Add(sheetName);
sheet.OutLineApplyStyle = true;
AddHeader(sheet, header);
AddObjects(sheet, , dtoList, propertySelectors); for (var i = ; i <= header.Length; i++)
{
sheet.Column(i).AutoFit();
}
});
} public static byte[] CreateExcelStream(Action<ExcelPackage> creator)
{
using (var excelPackage = new ExcelPackage())
{
creator(excelPackage);
MemoryStream ms = new MemoryStream();
excelPackage.SaveAs(ms);
return ms.GetBuffer();
}
} public static void AddHeader(ExcelWorksheet sheet, params string[] headerTexts)
{
if (headerTexts.IsNullOrEmpty())
{
return;
} for (var i = ; i < headerTexts.Length; i++)
{
AddHeader(sheet, i + , headerTexts[i]);
}
} public static void AddHeader(ExcelWorksheet sheet, int columnIndex, string headerText)
{
sheet.Cells[, columnIndex].Value = headerText;
sheet.Cells[, columnIndex].Style.Font.Bold = true;
} public static void AddObjects<T>(ExcelWorksheet sheet, int startRowIndex, IList<T> items, params Func<T, object>[] propertySelectors)
{
if (items.IsNullOrEmpty() || propertySelectors.IsNullOrEmpty())
{
return;
} for (var i = ; i < items.Count; i++)
{
for (var j = ; j < propertySelectors.Length; j++)
{
sheet.Cells[i + startRowIndex, j + ].Value = propertySelectors[j](items[i]);
}
}
}
}
}
2.在ABP的AppServer应用服务层调用:
/// <summary>
/// 导出列表到Excel
/// </summary>
/// <param name="search">查询条件对象</param>
public AjaxResponse ExportListToExcel(ContractSearch search)
{
int rowCount = ;
List<ContractList> dtoList = Search(search, out rowCount, false);
string fileName = "List.xlsx";
string sheetName = "列表";
const int columnCount = ;
string[] header = new string[columnCount] { "签订日期", "编号", "价格", "付款方式", "执行期", "状态", "负责人" };
Func<ContractList, object>[] propertySelectors = new Func<ContractList, object>[columnCount] {
new Func<ContractList, object>(l => l.SignDateString),
new Func<ContractList, object>(l => l.Code),
new Func<ContractList, object>(l => l.CoalType),
new Func<ContractList, object>(l => l.TotalNumber),
new Func<ContractList, object>(l => l.TotalAmount),
new Func<ContractList, object>(l => l.PayMethods),
new Func<ContractList, object>(l => l.ValidPeriod),
new Func<ContractList, object>(l => l.ContractStatusName),
new Func<ContractList, object>(l => l.AdminName)
};
return ExcelExporter<ContractList>.GetFileResponse(fileName, sheetName, dtoList, header, propertySelectors);
}