C# net8使用NPOI导出多个sheet工作簿的execl文件
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("异常"); } }
