技術點：
1.自定義
attribute屬性
2.通過反射取類及其屬性的attribute屬性值
3.NPOI包常用屬性及方法(我也僅僅知道用到過的，陌生的要麼見名知意，要麼百度查)
實現功能點：
List類物件的模板匯出，實用場景例子見最後程式碼塊
（emm...還是比較抽象，程式碼見?）
EXCEL匯出類DTO超類
public abstract class ExcelSuper
{
// 所有excel匯出類DTO必須要繼承的方法
// 限制比較弱，主要還是用來區分DTO用在何處
}
定義繼承匯出類DTO的特性說明類
/// <summary>
/// 匯出類 類特性
/// </summary>
[AttributeUsage(AttributeTargets.Class)]
public class ExcelExpClassAttribute : Attribute
{
public ExcelExpClassAttribute(int colSplit, int rowSplit, int leftmostColumn, int topRow)
{
this.colSplit = colSplit;
this.rowSplit = rowSplit;
this.leftmostColumn = leftmostColumn;
this.topRow = topRow;
}
/// <summary>
/// 凍結的列數
/// </summary>
public int colSplit { get; set; }
/// <summary>
/// 凍結的行數
/// 只凍結列時為0
/// </summary>
public int rowSplit { get; set; }
/// <summary>
/// 右邊區域可見的首列序號，從1開始計算
/// </summary>
public int leftmostColumn { get; set; }
/// <summary>
/// 邊區域可見的首行序號，從1開始計算，
/// 只凍結列時為0
/// </summary>
public int topRow { get; set; }
}
/// <summary>
/// 匯出類 屬性特性
/// </summary>
[AttributeUsage(AttributeTargets.Property)]
public class ExcelExpAttribute : Attribute
{
/// <summary>
/// 是否隱藏，與SortIndex搭配使用
/// </summary>
public bool IsHidden { get; set; } = false;
/// <summary>
/// 排序索引（保持連貫性、不可重複）
/// - 對應dataTable的列排序 [0,1...]
/// - 對應Excel的列索引 [0,1...]
/// </summary>
public int SortIndex { get; set; }
/// <summary>
/// Excel列名
/// </summary>
public string ColName { get; set; }
/// <summary>
/// 是否動態列
/// </summary>
public bool IsdynamicColName { get; set; }
/// <summary>
/// 是否合併行 -- 預留，後補
/// </summary>
public bool IsRowMerge { get; set; } = false;
/// <summary>
/// 合併行依據 -- 預留，後補
/// </summary>
public string RowMergeBasis { get; set; }
}
Excel幫助類
/// <summary>
/// Excel幫助類
/// </summary>
public static class ExcelHelper
{
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="workbook"></param>
/// <param name="dtSource"></param>
/// <param name="sheetNum"></param>
/// <param name="useAttributeColName"> 新增一行列名，取類自定義屬性列名稱：ExcelExpAttribute.ColName</param>
/// <returns></returns>
private static IWorkbook OutputExcel<T>(this IWorkbook workbook, IEnumerable<T> dtSource, int sheetNum, bool useAttributeColName = false)
{
// 讀取sheet
ISheet sheet = workbook.GetSheetAt(sheetNum);
int rowIndex = sheet.LastRowNum + 1;// 獲取寫入行初始值
if (useAttributeColName)
{
// 待補充
}
Type objType = typeof(T);
// 取類上的自定義特性
object[] objs = objType.GetCustomAttributes(typeof(ExcelExpClassAttribute), true);
foreach (object obj in objs)
{
ExcelExpClassAttribute attr = obj as ExcelExpClassAttribute;
if (attr != null)
{
sheet.CreateFreezePane(attr.colSplit, attr.rowSplit, attr.leftmostColumn, attr.topRow);// 設定凍結行、列
break;
}
}
// 迴圈新增資料
foreach (T item in dtSource)
{
IRow rowi = sheet.CreateRow(rowIndex);
// 設定自適應寬度,9為Excel列數，根據需要自已修改
for (int columnNum = 0; columnNum <= rowi.LastCellNum; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
sheet.SetColumnWidth(columnNum, columnWidth * 256);
}
// 取屬性上的自定義特性
foreach (PropertyInfo propInfo in objType.GetProperties())
{
object[] objAttrs = propInfo.GetCustomAttributes(typeof(ExcelExpAttribute), true);
if (objAttrs.Length > 0)
{
ExcelExpAttribute attr = objAttrs[0] as ExcelExpAttribute;
if (attr != null)
{
if (attr.IsHidden)
continue;
int colIndex = attr.SortIndex;
var name = propInfo.Name;// 例項名稱
var value = propInfo.GetValue(item);// 例項值
#region 判斷值型別並填充
var newCell = rowi.CreateCell(colIndex);
switch (propInfo.PropertyType.ToString())
{
case "System.String"://字串型別
newCell.SetCellValue(value == null ? "" : value.ToString());
break;
case "System.DateTime"://日期型別
DateTime.TryParse(value.ToString(), out DateTime dateV);
newCell.SetCellValue(dateV);
break;
case "System.Boolean"://布林型
bool.TryParse(value.ToString(), out bool boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int.TryParse(value.ToString(), out int intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮點型
case "System.Double":
double.TryParse(value.ToString(), out double doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值處理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
#endregion
}
}
}
rowIndex++;
}
return workbook;
}
/// <summary>
/// 匯出模板Excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="iEnumerable">資料來源</param>
/// <param name="fileFullPath">檔案全路徑（包含檔案及字尾名）</param>
/// <returns>檔案流</returns>
public static MemoryStream ExportToExcel<T>(IEnumerable<T> iEnumerable, string fileFullPath)
where T : ExcelSuper
{
// 開啟模板檔案並寫入
var workbook = GetIWorkbook(fileFullPath, out ExcelTypeEnum type);
if (type == ExcelTypeEnum.XLS)
{
workbook = (HSSFWorkbook)workbook.OutputExcel(iEnumerable, 0, false);
}
else
{
workbook = (XSSFWorkbook)workbook.OutputExcel(iEnumerable, 0, false);
}
try
{
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
// sheet.Dispose();
// workbook.Dispose();//一般只用寫這一個就OK了，他會遍歷並釋放所有資源，但當前版本有問題所以只釋放sheet
return ms;
}
}
catch (Exception ex)
{
throw ex;
}
}
#region Private method
private static IWorkbook GetIWorkbook(string fileFullPath, out ExcelTypeEnum type)
{
string filename = Path.GetFileNameWithoutExtension(fileFullPath);// 檔名稱
string extension = Path.GetExtension(fileFullPath);// 字尾名 帶點(.)
try
{
if (!File.Exists(fileFullPath))
{
throw new Exception($"模板：{filename + extension}不存在");
}
FileStream fs = new FileStream(fileFullPath, FileMode.Open, FileAccess.ReadWrite);
if (".xls".Equals(extension.ToLower()))
{
type = ExcelTypeEnum.XLS;
return new HSSFWorkbook(fs);// Excel2003以前版本
}
else
{
type = ExcelTypeEnum.XLSX;
return new XSSFWorkbook(fs);// Excel2007後的版本
}
}
catch (Exception ex)
{
throw new Exception("獲取檔案錯誤：" + ex);
}
}
#endregion
enum ExcelTypeEnum
{
XLS,
XLSX
}
}
這部分要講的點其實挺多的，關鍵就是EXCEL匯出所用到的資料來源是強型別的。
可以看出來list其實是EF的QueryabletoList()後的類集合，作為資料來源存在；// 比較喜歡強型別，直接點出來的屬性讓人放心ヾ(•ω•`)o裡面的DTO
DesWeeklyReportExcExp繼承ExcelSuper，特性分別加在類及屬性上。
public class XXXXController : CoreController
{
// 控制器內部
[HttpPost]
public ActionResult export()
{
// 控制器介面
var list = op
.GetPagedQuery(PageModel)
.Select(s => new DesWeeklyReportExcExp
{
col1 = s.Project.ProjName,
col2 = s.ColAttROPDate1?.ToString("yyyy.MM.dd"),
col3 = (s.ColAttROPDate2 == null ? "無" : s.ColAttROPDate2.Value.ToString("yyyy.MM.dd"))
+ "/"
+ (s.ColAttROPDate3 == null ? "無" : s.ColAttROPDate3.Value.ToString("yyyy.MM.dd")),
col4 = s.ColAttROPDate4?.ToString("yyyy.MM.dd")
}).ToList();
string filePath = Server.MapPath("~/download/[這是模板名稱].xlsx");
string filename = Path.GetFileNameWithoutExtension(filePath);// 檔名稱
string extension = Path.GetExtension(filePath);// 字尾名 帶點(.)
string fileDownloadName = filename + extension;
var fs = ExcelHelper.ExportToExcel(list, filePath).ToArray();
return File(fs, "application/ms-excel", fileDownloadName);
}
}
[ExcelExpClassAttribute(2, 0, 2, 0)]
public class DesWeeklyReportExcExp : ExcelSuper
{
/// <summary>
/// 列1
/// </summary>
[ExcelExp(SortIndex = 0, ColName = "列1")]
public string col1 { get; set; }
/// <summary>
/// 列2
/// </summary>
[ExcelExp(SortIndex = 0, ColName = "列2")]
public string col2 { get; set; }
/// <summary>
/// 列3
/// </summary>
[ExcelExp(SortIndex = 0, ColName = "列3")]
public string col3 { get; set; }
/// <summary>
/// 列4
/// </summary>
[ExcelExp(SortIndex = 0, ColName = "列4")]
public string col4 { get; set; }
}
部分拙見，大部分還需要補充，比如設定合併列，比如資料來源支援DataTable匯出等等，還有現有的程式碼可能不夠完善，看到的多多提下寶貴意見吧?