1. 创建一个 实体类
public class ExeclDataResource { /// <summary> /// 保存到Sheet页的名称 /// </summary> public string SheetName { get; set; } /// <summary> /// 标题所在行 /// </summary> public int TitleIndex { get; set; } /// <summary> /// 每个Sheet的数据 /// </summary> public List<object> SheetDataResource { get; set; } /// <summary> /// 每个Sheet的列 /// </summary> public Dictionary<string, string> dicColumns { get; set; } }
2.根据execl内容动态生成文件
public static class ExcelUtilHelper { /// <summary> /// 多个Sheet 导出 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="execlDatas"></param> /// <returns></returns> public static IWorkbook DataToHssfWorkbook(List<ExeclDataResource> execlDatas) { HSSFWorkbook _workbook = new HSSFWorkbook(); if (execlDatas==null&& execlDatas.Count==0) { return _workbook; } // 标题及内容单元格样式 var headCellStyle = CreateCellStyle(_workbook, true); var contentCellStyle = CreateCellStyle(_workbook, false); //每循环一次。就生成一个Sheet foreach (var sheetResource in execlDatas) { if (sheetResource.SheetDataResource==null||sheetResource.SheetDataResource.Count==0) { break; } //创建一个页签 ISheet sheet = _workbook.CreateSheet(sheetResource.SheetName); //确定当前这一页多少列--取决于当前sheet页的数据 //获取实体属性名 PropertyInfo[] properties = sheetResource.SheetDataResource[0].GetType().GetProperties(); //排除多余的属性 foreach (var property in properties) { foreach (var notmapped in property.CustomAttributes) { if (notmapped.AttributeType.Name== "NotMappedAttribute") { var ss1 = properties.ToList(); var ss2 = ss1.Where(x => x.Name == property.Name).FirstOrDefault(); ss1.Remove(ss2); properties = ss1.ToArray(); } } } //properties = properties.Where(x => x.CustomAttributes.Where(z => z.AttributeType.Name != "NotMapped")); // 每列列宽字典 var dic = new Dictionary<int, int>(); //确定表头在哪一行生成 int titleIndex = 0; if (sheetResource.TitleIndex>=0) { titleIndex = sheetResource.TitleIndex - 1; } //基于当前创建Sheet页表头 IRow titleRow = sheet.CreateRow(titleIndex); //表头创建 Dictionary<string, int> columns = new Dictionary<string, int>(); for (int i = 0; i < sheetResource.dicColumns.Count(); i++) { ICell cell = titleRow.CreateCell(i); cell.SetCellValue(sheetResource.dicColumns.ToList()[i].Value.ToString()); cell.CellStyle = headCellStyle; dic.Add(i, Encoding.Default.GetBytes(cell.StringCellValue).Length * 260 + 600); columns.Add(sheetResource.dicColumns.ToList()[i].Value, i); } //读取数据 for (int i = 0; i < sheetResource.SheetDataResource.Count(); i++) { IRow row = sheet.CreateRow(i + titleIndex+1); // 行高,避免自动换行的内容将行高撑开 row.HeightInPoints = 20f; for (int j = 0; j < properties.Length; j++) { //如果属性不存在 if (!sheetResource.dicColumns.ContainsKey(properties[j].Name)) { continue; } object[] entityValues = new object[properties.Length]; entityValues[j] = properties[j].GetValue(sheetResource.SheetDataResource[i]); int index = columns[sheetResource.dicColumns[properties[j].Name]]; ICell cell = row.CreateCell(index); cell.SetCellValue(entityValues[j]?.ToString()); cell.CellStyle = contentCellStyle; int length = Encoding.Default.GetBytes(cell.StringCellValue).Length * 256 + 600; length = length > 15000 ? 15000 : length; // 若比已存在列宽更宽则替换,Excel限制最大宽度为15000 if (dic[j] < length) { dic[j] = length; } } } for (int i = 0; i < sheetResource.dicColumns.Count; i++) { sheet.SetColumnWidth(i, dic[i]); } } return _workbook; } /// <summary> /// 单元格样式 /// </summary> /// <param name="workbook"></param> /// <param name="isHead"></param> /// <returns></returns> private static ICellStyle CreateCellStyle(IWorkbook workbook, bool isHead) { var cellStyle = workbook.CreateCellStyle(); var font = workbook.CreateFont(); font.IsBold = isHead; // 粗体 cellStyle.SetFont(font); if (isHead) { cellStyle.Alignment = HorizontalAlignment.Center; // 水平居中 cellStyle.VerticalAlignment = VerticalAlignment.Center; // 垂直居中 } cellStyle.BorderTop = BorderStyle.Thin; cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; cellStyle.WrapText = true;//内容自动换行,避免存在换行符的内容合并成单行 return cellStyle; } }
1. 主程序多个Sheet 以及文件生成
//标题 Dictionary<string, string> dct = new Dictionary<string, string>(); dct.add("Name","名称"); dct.add("Age","年龄"); List<object> objlist = new List<object>(); string sql="select * from student"; var resource1 = _context.Database.SqlQuery<student>(sql).ToList(); objlist.AddRange(resource1); List <ExeclDataResource> dataResourceslist = new List<ExeclDataResource>() { new ExeclDataResource() { SheetName="门店报表", TitleIndex=1, SheetDataResource = objlist, dicColumns=dct }, new ExeclDataResource() { SheetName="门店报表2", TitleIndex=1, SheetDataResource = objlist,//数据源数据库返回数据 dicColumns=dct//头部列 } }; IWorkbook workbook = ExcelUtilHelper.DataToHssfWorkbook(dataResourceslist); using (FileStream fs = new FileStream(NewFilePath, FileMode.Create, FileAccess.Write)) { workbook.Write(fs); fs.Close(); fs.Dispose(); }