1、安裝NPOI 2.7.2
2、工具類DataTableTool.cs，將list 轉成DataTable
using System.Collections; using System.Data; using System.Reflection; namespace Common.Helper { public static class DataTableTool { /// <summary> /// 轉化一個DataTable /// </summary> /// <typeparam name="T"></typeparam> /// <param name="list"></param> /// <returns></returns> public static DataTable ToDataTable<T>(this IEnumerable<T> list) { //建立屬性的集合 List<PropertyInfo> pList = new List<PropertyInfo>(); //獲得反射的入口 Type type = typeof(T); DataTable dt = new DataTable(); //把所有的public屬性加入到集合 並新增DataTable的列 Array.ForEach<PropertyInfo>(type.GetProperties(), p => { pList.Add(p); dt.Columns.Add(p.Name, p.PropertyType); }); foreach (var item in list) { //建立一個DataRow例項 DataRow row = dt.NewRow(); //給row 賦值 pList.ForEach(p => row[p.Name] = p.GetValue(item, null)); //加入到DataTable dt.Rows.Add(row); } return dt; } /// <summary> /// DataTable 轉換為List 集合 /// </summary> /// <typeparam name="TResult">型別</typeparam> /// <param name="dt">DataTable</param> /// <returns></returns> public static List<T> ToList<T>(this DataTable dt) where T : class, new() { //建立一個屬性的列表 List<PropertyInfo> prlist = new List<PropertyInfo>(); //獲取TResult的型別例項 反射的入口 Type t = typeof(T); //獲得TResult 的所有的Public 屬性 並找出TResult屬性和DataTable的列名稱相同的屬性(PropertyInfo) 並加入到屬性列表 Array.ForEach<PropertyInfo>(t.GetProperties(), p => { if (dt.Columns.IndexOf(p.Name) != -1) prlist.Add(p); }); //建立返回的集合 List<T> oblist = new List<T>(); foreach (DataRow row in dt.Rows) { //建立TResult的例項 T ob = new T(); //找到對應的資料 並賦值 prlist.ForEach(p => { if (row[p.Name] != DBNull.Value) p.SetValue(ob, row[p.Name], null); }); //放入到返回的集合中. oblist.Add(ob); } return oblist; } /// <summary> /// 將集合類轉換成DataTable /// </summary> /// <param name="list">集合</param> /// <returns></returns> public static DataTable ToDataTableTow(IList list) { DataTable result = new DataTable(); if (list.Count > 0) { PropertyInfo[] propertys = list[0].GetType().GetProperties(); foreach (PropertyInfo pi in propertys) { Type colType = pi.PropertyType; if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>))) { colType = colType.GetGenericArguments()[0]; } result.Columns.Add(pi.Name, colType); } for (int i = 0; i < list.Count; i++) { ArrayList tempList = new ArrayList(); foreach (PropertyInfo pi in propertys) { object obj = pi.GetValue(list[i], null); tempList.Add(obj); } object[] array = tempList.ToArray(); result.LoadDataRow(array, true); } } return result; } /// <summary> /// 將泛型集合類轉換成DataTable /// </summary> /// <typeparam name="T">集合項型別</typeparam> /// <param name="list">集合</param> /// <returns>資料集(表)</returns> public static DataTable ToDataTable<T>(IList<T> list) { return ToDataTable<T>(list, null); } /// <summary> /// 將泛型集合類轉換成DataTable /// </summary> /// <typeparam name="T">集合項型別</typeparam> /// <param name="list">集合</param> /// <param name="propertyName">需要返回的列的列名</param> /// <returns>資料集(表)</returns> public static DataTable ToDataTable<T>(IList<T> list, params string[] propertyName) { List<string> propertyNameList = new List<string>(); if (propertyName != null) propertyNameList.AddRange(propertyName); DataTable result = new DataTable(); if (list.Count > 0) { PropertyInfo[] propertys = list[0].GetType().GetProperties(); foreach (PropertyInfo pi in propertys) { if (propertyNameList.Count == 0) { result.Columns.Add(pi.Name, pi.PropertyType); } else { if (propertyNameList.Contains(pi.Name)) result.Columns.Add(pi.Name, pi.PropertyType); } } for (int i = 0; i < list.Count; i++) { ArrayList tempList = new ArrayList(); foreach (PropertyInfo pi in propertys) { if (propertyNameList.Count == 0) { object obj = pi.GetValue(list[i], null); tempList.Add(obj); } else { if (propertyNameList.Contains(pi.Name)) { object obj = pi.GetValue(list[i], null); tempList.Add(obj); } } } object[] array = tempList.ToArray(); result.LoadDataRow(array, true); } } return result; } } }
3、execl生成類ExcelTool.cs
using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System.Data; namespace Common.Helper { public static class ExcelTool { /// <summary> /// 將excel中的資料匯入到DataTable中 /// </summary> /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param> /// <param name="fileName">檔名稱</param> /// <param name="sheetName">excel工作薄sheet的名稱</param> /// <returns>返回的DataTable</returns> public static DataTable ExcelStreamToDataTable(bool isFirstRowColumn, string fileName, Stream fs, string sheetName = "") { if (string.IsNullOrEmpty(fileName)) { throw new ArgumentNullException(fileName); } var data = new DataTable(); IWorkbook workbook = null; try { if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0) { workbook = new XSSFWorkbook(fs); } else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0) { workbook = new HSSFWorkbook(fs); } ISheet sheet = null; if (workbook != null) { //如果沒有找到指定的sheetName對應的sheet，則嘗試獲取第一個sheet if (sheetName == "") { sheet = workbook.GetSheetAt(0); } else { sheet = workbook.GetSheet(sheetName) ?? workbook.GetSheetAt(0); } } if (sheet == null) return data; var firstRow = sheet.GetRow(0); //一行最後一個cell的編號 即總的列數 int cellCount = firstRow.LastCellNum; int startRow; if (isFirstRowColumn) { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { var cell = firstRow.GetCell(i); var cellValue = cell.ToString(); if (cellValue == null) continue; var column = new DataColumn(cellValue); data.Columns.Add(column); } startRow = sheet.FirstRowNum + 1; } else { startRow = sheet.FirstRowNum; } //最後一列的標號 var rowCount = sheet.LastRowNum; for (var i = startRow; i <= rowCount; ++i) { var row = sheet.GetRow(i); //沒有資料的行預設是null if (row == null) continue; var dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { //同理，沒有資料的單元格都預設是null if (row.GetCell(j) != null) dataRow[j] = row.GetCell(j).ToString(); } data.Rows.Add(dataRow); } return data; } catch (IOException ioex) { throw new IOException(ioex.Message); } catch (Exception ex) { throw new Exception(ex.Message); } finally { if (fs != null) { fs.Close(); } } } /// <summary> /// 將excel中的資料匯入到DataTable中 /// </summary> /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param> /// <param name="fileName">檔案路徑</param> /// <param name="sheetName">excel工作薄sheet的名稱</param> /// <returns>返回的DataTable</returns> public static DataTable ExcelToDataTable(bool isFirstRowColumn, string fileName, string sheetName = "") { if (string.IsNullOrEmpty(fileName)) { throw new ArgumentNullException(fileName); } var data = new DataTable(); IWorkbook workbook = null; FileStream fs = null; try { fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0) { workbook = new XSSFWorkbook(fs); } else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0) { workbook = new HSSFWorkbook(fs); } ISheet sheet = null; if (workbook != null) { //如果沒有找到指定的sheetName對應的sheet，則嘗試獲取第一個sheet if (sheetName == "") { sheet = workbook.GetSheetAt(0); } else { sheet = workbook.GetSheet(sheetName) ?? workbook.GetSheetAt(0); } } if (sheet == null) return data; var firstRow = sheet.GetRow(0); //一行最後一個cell的編號 即總的列數 int cellCount = firstRow.LastCellNum; int startRow; if (isFirstRowColumn) { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { var cell = firstRow.GetCell(i); var cellValue = cell.StringCellValue; if (cellValue == null) continue; var column = new DataColumn(cellValue); data.Columns.Add(column); } startRow = sheet.FirstRowNum + 1; } else { startRow = sheet.FirstRowNum; } //最後一列的標號 var rowCount = sheet.LastRowNum; for (var i = startRow; i <= rowCount; ++i) { var row = sheet.GetRow(i); //沒有資料的行預設是null if (row == null) continue; var dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { //同理，沒有資料的單元格都預設是null if (row.GetCell(j) != null) dataRow[j] = row.GetCell(j).ToString(); } data.Rows.Add(dataRow); } return data; } catch (IOException ioex) { throw new IOException(ioex.Message); } catch (Exception ex) { throw new Exception(ex.Message); } finally { if (fs != null) { fs.Close(); } } } /// <summary> /// 將DataTable資料匯入到excel中 /// </summary> /// <param name="data">要匯入的資料</param> /// <param name="isColumnWritten">DataTable的列名是否要匯入</param> /// <param name="sheetName">要匯入的excel的sheet的名稱</param> /// <param name="fileName">資料夾路徑</param> /// <returns>匯入資料行數(包含列名那一行)</returns> public static int DataTableToExcel(DataTable[] dataArr, string[] sheetNameArr, bool isColumnWritten, string fileName) { if (dataArr == null) { throw new ArgumentNullException("data"); } if (dataArr.Length != sheetNameArr.Length) { throw new ArgumentNullException("data長度與sheetName不一致"); } if (string.IsNullOrEmpty(fileName)) { throw new ArgumentNullException(fileName); } IWorkbook workbook = null; if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0) { workbook = new XSSFWorkbook(); } else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0) { workbook = new HSSFWorkbook(); } FileStream fs = null; try { fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite); ISheet[] sheetArr = new ISheet[sheetNameArr.Length]; for(int n= 0; n < sheetArr.Length; n++) { sheetArr[n] = workbook.CreateSheet(sheetNameArr[n]); } var cellStyle = workbook.CreateCellStyle(); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); // var cellStyle1 = workbook.CreateCellStyle(); // cellStyle1.DataFormat = HSSFDataFormat.GetBuiltinFormat("yyyy年m月d日"); var cellStyle1 = workbook.CreateCellStyle(); var format = workbook.CreateDataFormat(); cellStyle1.DataFormat = (format.GetFormat("yyyy-mm-dd")); // cell.setCellStyle(cellStyle); int j; int count = 0 ; //寫入DataTable的列名，寫入單元格中 for(int m = 0; m < sheetArr.Length; m++) { var sheet = sheetArr[m]; DataTable data = dataArr[m]; if (isColumnWritten) { var row = sheet.CreateRow(0); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName); Type types = data.Columns[j].DataType; if (types.Name == "DateTime") { sheet.SetDefaultColumnStyle(j, cellStyle1); } else { sheet.SetDefaultColumnStyle(j, cellStyle); } } count = 1; } else { count = 0; } //遍歷迴圈datatable具體資料項 int i; for (i = 0; i < data.Rows.Count; ++i) { var row = sheet.CreateRow(count); for (j = 0; j < data.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString()); } ++count; } } //將檔案流寫入到excel workbook.Write(fs); return count; } catch (IOException ioex) { throw new IOException(ioex.Message); } catch (Exception ex) { throw new Exception(ex.Message); } finally { if (fs != null) { fs.Close(); } } } } }
4、使用
[HttpGet] public async Task<MessageModel<string>> ExportExcel(string key = "", int orderBy = 0) { try { string fileName = $"{DateTime.Now.ToString("yyyyMMdd_HHmss")}_{_user.ShopID}" + ".xlsx"; string folder = $"execl\\shopbak"; string urlFolder = $"execl/shopbak"; string folderpath = Path.Combine($"{_env.WebRootPath}\\{folder}\\"); string path = folderpath + fileName; if (!Directory.Exists(folderpath)) { Directory.CreateDirectory(folderpath); } //刪除舊檔案 foreach (string file in Directory.GetFiles(folderpath)) { try { FileInfo fileInfo = new FileInfo(file); DateTime localCreationTime = fileInfo.CreationTime; // 獲取檔案的本地建立時間 Console.WriteLine("檔案建立時間（本地）: " + localCreationTime.ToString()); //建立時間與當前時間比較，相差30秒以上則刪除 if (DateTime.Now.Subtract(localCreationTime).TotalSeconds > 60) { System.IO.File.Delete(file); } } catch(Exception ex) { _logger.LogError("刪除檔案失敗" + ex); } } DataTable[] dtArr = new DataTable[2]; string[] sheetNameArr = { "全部會員", "次卡會員"}; #region 全部會員 var result = await Get(page: 1, intPageSize: 5000, key: key, memberType: 0, orderBy: orderBy); //獲取資料 if (!result.success) { return Failed("查詢資料失敗"); } var list = result.response.data.Select(s => new { s.Name, s.Phone, s.Points, s.Birthday, s.Sex, s.CardNO, s.Detail, s.StoreCardName, s.StoreCardBalance, s.CreateTime }); DataTable dt = DataTableTool.ToDataTableTow(list.ToList()); dt.Columns["Name"].ColumnName = "名字"; dt.Columns["Phone"].ColumnName = "手機"; dt.Columns["Points"].ColumnName = "積分"; dt.Columns["Birthday"].ColumnName = "生日"; dt.Columns["Sex"].ColumnName = "性別"; dt.Columns["CardNO"].ColumnName = "會員卡號"; dt.Columns["Detail"].ColumnName = "詳情"; dt.Columns["StoreCardName"].ColumnName = "儲值卡"; dt.Columns["StoreCardBalance"].ColumnName = "儲值卡餘額"; dt.Columns["CreateTime"].ColumnName = "新增時間"; dtArr[0] = dt; #endregion #region 次卡 var timesResult = await Get(page: 1, intPageSize: 5000, key: key, memberType: 1, orderBy: orderBy); if (!timesResult.success) { return Failed("查詢資料失敗"); } var timesList = timesResult.response.data.Select(s => new { s.Name, s.Phone, s.Points, s.Birthday, s.Sex, s.CardNO, s.Detail, s.TimesCardName, s.TimesBusinessName, s.TimesCardRemainCount, s.CreateTime }); DataTable timesDT = DataTableTool.ToDataTableTow(timesList.ToList()); timesDT.Columns["Name"].ColumnName = "名字"; timesDT.Columns["Phone"].ColumnName = "手機"; timesDT.Columns["Points"].ColumnName = "積分"; timesDT.Columns["Birthday"].ColumnName = "生日"; timesDT.Columns["Sex"].ColumnName = "性別"; timesDT.Columns["CardNO"].ColumnName = "會員卡號"; timesDT.Columns["Detail"].ColumnName = "詳情"; timesDT.Columns["TimesCardName"].ColumnName = "次卡"; timesDT.Columns["TimesBusinessName"].ColumnName = "次卡專案"; timesDT.Columns["TimesCardRemainCount"].ColumnName = "剩餘次數"; timesDT.Columns["CreateTime"].ColumnName = "新增時間"; dtArr[1] = timesDT; #endregion int count = ExcelTool.DataTableToExcel(dtArr, sheetNameArr, true, path); if (System.IO.File.Exists(path)) { var imgUrl = $"{Request.Scheme}://{Request.Host}/{urlFolder}/{fileName}"; return Success(imgUrl, "成功"); } return Failed("匯出失敗"); } catch(Exception ex) { _logger.LogError("匯出execl異常" + ex); return Failed("異常"); } }