250 lines
9.3 KiB
C#
250 lines
9.3 KiB
C#
using Microsoft.AspNetCore.Mvc;
|
||
using Newtonsoft.Json.Linq;
|
||
using NPOI.HSSF.UserModel;
|
||
using NPOI.SS.UserModel;
|
||
using NPOI.XSSF.UserModel;
|
||
using System.Data;
|
||
using System.Dynamic;
|
||
|
||
namespace WCS.WebApi.Helper
|
||
{
|
||
public class ExportExcelHelper
|
||
{
|
||
/// <summary>
|
||
/// </summary>
|
||
/// <param name="sheetName">文件名</param>
|
||
/// <param name="columns">标题行</param>
|
||
/// <param name="results">数据源</param>
|
||
/// <returns></returns>
|
||
public static ActionResult ExportExcel(string sheetName, List<ExportableColumn> columns, IList<dynamic> results)
|
||
{
|
||
var isXlsx = false;
|
||
IWorkbook workbook = new HSSFWorkbook();
|
||
if (results.Count + 1 > 65536)// 包括一行标题行 .xls文件最大行数
|
||
{
|
||
workbook = new XSSFWorkbook();
|
||
isXlsx = true;
|
||
}
|
||
workbook.CreateSheet(sheetName);
|
||
|
||
var rowIndex = 0;
|
||
AddHeader(workbook, rowIndex, columns);
|
||
|
||
rowIndex++;
|
||
workbook = AddData(workbook, rowIndex, columns, results);
|
||
|
||
// Save the Excel spreadsheet to a MemoryStream and return it to the client
|
||
using (var exportData = new MemoryStream())
|
||
{
|
||
workbook.Write(exportData);
|
||
var saveAsFileName = string.Format("{0}-{1:d}.{2}", sheetName, DateTime.Now, (isXlsx ? "xlsx" : "xls")).Replace("/", "-");
|
||
var contentType = isXlsx ? "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" : "application/vnd.ms-excel";
|
||
return new FileContentResult(exportData.ToArray(), contentType) { FileDownloadName = saveAsFileName };
|
||
}
|
||
}
|
||
|
||
public static ActionResult Export<T>(string sheetName, IList<ExportableColumn> columns, IEnumerable<T> results)
|
||
{
|
||
var isXlsx = false;
|
||
IWorkbook workbook = new HSSFWorkbook();
|
||
if (results.Count() + 1 > 65536)// 包括一行标题行 .xls文件最大行数
|
||
{
|
||
workbook = new XSSFWorkbook();
|
||
isXlsx = true;
|
||
}
|
||
workbook.CreateSheet(sheetName);
|
||
var rowIndex = 0;
|
||
|
||
AddHeader(workbook, rowIndex, columns);
|
||
|
||
rowIndex++;
|
||
workbook = AddData(workbook, rowIndex, columns, results);
|
||
|
||
// Save the Excel spreadsheet to a MemoryStream and return it to the client
|
||
using (var exportData = new MemoryStream())
|
||
{
|
||
workbook.Write(exportData);
|
||
var saveAsFileName = string.Format("{0}-{1:d}.{2}", sheetName, DateTime.Now, (isXlsx ? "xlsx" : "xls")).Replace("/", "-");
|
||
var contentType = isXlsx ? "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" : "application/vnd.ms-excel";
|
||
return new FileContentResult(exportData.ToArray(), contentType) { FileDownloadName = saveAsFileName };
|
||
}
|
||
}
|
||
|
||
public static ActionResult Export<T>(
|
||
string sheetName,
|
||
string firstData,
|
||
IList<ExportableColumn> columns,
|
||
IEnumerable<T> results
|
||
)
|
||
{
|
||
IWorkbook workbook = new HSSFWorkbook();
|
||
var sheet = workbook.CreateSheet(sheetName);
|
||
var rowIndex = 0;
|
||
|
||
// first row data
|
||
var firstRow = sheet.CreateRow(rowIndex);
|
||
firstRow.CreateCell(0).SetCellValue(firstData);
|
||
rowIndex++;
|
||
|
||
AddHeader(workbook, rowIndex, columns);
|
||
|
||
rowIndex++;
|
||
workbook = AddData(workbook, rowIndex, columns, results);
|
||
|
||
// Save the Excel spreadsheet to a MemoryStream and return it to the client
|
||
using (var exportData = new MemoryStream())
|
||
{
|
||
workbook.Write(exportData);
|
||
string saveAsFileName = string.Format("{0}-{1:d}.xls", sheetName, DateTime.Now).Replace("/", "-");
|
||
return new FileContentResult(exportData.ToArray(), "application/vnd.ms-excel") { FileDownloadName = saveAsFileName };
|
||
}
|
||
}
|
||
|
||
#region 数据操作
|
||
public static IWorkbook AddData(IWorkbook workbook, int rowIndex, List<ExportableColumn> columns, IList<dynamic> results)
|
||
{
|
||
const int MaxRowCount = 1048575; //.xlsx文件最大行数1048576
|
||
var sheet = workbook.GetSheetAt(0);
|
||
|
||
// Add data rows
|
||
foreach (var viewModel in results)
|
||
{
|
||
var row = sheet.CreateRow(rowIndex);
|
||
if (rowIndex >= MaxRowCount)
|
||
{
|
||
row.CreateCell(0).SetCellValue("数据行数已超出Excel文件支持的最大行数！");
|
||
break;
|
||
}
|
||
|
||
for (var index = 0; index < columns.Count; index++)
|
||
{
|
||
var propertyName = columns[index].Name.Trim();
|
||
var cell = row.CreateCell(index);
|
||
var propertyDictionary = (IDictionary<string, object>)viewModel;
|
||
if (propertyDictionary.ContainsKey(propertyName))
|
||
{
|
||
var value = propertyDictionary[propertyName];
|
||
cell.SetCellValue(value == null ? "" : value.ToString());
|
||
}
|
||
}
|
||
|
||
rowIndex++;
|
||
}
|
||
|
||
return workbook;
|
||
}
|
||
|
||
public static IWorkbook AddData<T>(IWorkbook workbook, int rowIndex, IList<ExportableColumn> columns, IEnumerable<T> results)
|
||
{
|
||
const int MaxRowCount = 1048575; //.xlsx文件最大行数1048576
|
||
var sheet = workbook.GetSheetAt(0);
|
||
|
||
// Add data rows
|
||
foreach (var viewModel in results)
|
||
{
|
||
var row = sheet.CreateRow(rowIndex);
|
||
if (rowIndex >= MaxRowCount)
|
||
{
|
||
row.CreateCell(0).SetCellValue("数据行数已超出Excel文件支持的最大行数！");
|
||
break;
|
||
}
|
||
|
||
for (int index = 0; index < columns.Count; index++)
|
||
{
|
||
var cell = row.CreateCell(index);
|
||
var dr = viewModel as DataRow;
|
||
var value = (dr != null) ? dr[columns[index].Name]
|
||
: viewModel.GetType().GetProperty(columns[index].Name)?.GetValue(viewModel, null);
|
||
|
||
if (value == null)
|
||
{
|
||
var item = viewModel as ExpandoObject;
|
||
if (item != null)
|
||
{
|
||
if (item.Any(w => w.Key == columns[index].Name))
|
||
{
|
||
var keyValue = item.FirstOrDefault(w => w.Key == columns[index].Name);
|
||
value = keyValue.Value;
|
||
}
|
||
}
|
||
}
|
||
if (value == null)
|
||
{
|
||
var item = viewModel as JObject;
|
||
if (item != null)
|
||
{
|
||
value = item[columns[index].Name];
|
||
}
|
||
}
|
||
|
||
cell.SetCellValue(value == null ? "" : value.ToString());
|
||
}
|
||
|
||
rowIndex++;
|
||
}
|
||
|
||
return workbook;
|
||
}
|
||
|
||
public static void AddHeader(IWorkbook workbook, int rowIndex, IList<ExportableColumn> columns)
|
||
{
|
||
var sheet = workbook.GetSheetAt(0);
|
||
var format = workbook.CreateDataFormat();
|
||
|
||
// Add header labels
|
||
var headerRow = sheet.CreateRow(rowIndex);
|
||
for (int index = 0; index < columns.Count; index++)
|
||
{
|
||
headerRow.CreateCell(index).SetCellValue(columns[index].Caption);
|
||
if (!string.IsNullOrEmpty(columns[index].Format))
|
||
{
|
||
var columnStyle = workbook.CreateCellStyle();
|
||
columnStyle.DataFormat = format.GetFormat(columns[index].Format);
|
||
sheet.SetDefaultColumnStyle(index, columnStyle);
|
||
}
|
||
if (columns[index].ColumnWidth.HasValue && columns[index].ColumnWidth.Value > 0)
|
||
{
|
||
var columnWidth = columns[index].ColumnWidth.Value;
|
||
sheet.SetColumnWidth(index, columnWidth * 256 / 7);
|
||
}
|
||
}
|
||
}
|
||
#endregion
|
||
}
|
||
|
||
public class ExportableColumn
|
||
{
|
||
public ExportableColumn(string caption, string name) : this(caption, name, null)
|
||
{
|
||
}
|
||
|
||
public ExportableColumn(string caption, string name, string format)
|
||
{
|
||
Format = format;
|
||
Caption = caption;
|
||
Name = name;
|
||
}
|
||
|
||
public ExportableColumn(string caption, string name, string format, int? width)
|
||
: this(caption, name, format)
|
||
{
|
||
ColumnWidth = width;
|
||
}
|
||
|
||
public string Name { get; }
|
||
public string Caption { get; }
|
||
public string Format { get; }
|
||
|
||
/// <summary>
|
||
/// 单位：像素，为空时使用默认值
|
||
/// </summary>
|
||
public int? ColumnWidth { get; private set; }
|
||
|
||
public ExportableColumn Width(int? width)
|
||
{
|
||
ColumnWidth = width;
|
||
return this;
|
||
}
|
||
}
|
||
}
|