.Net Execl 多个Sheet 导出

 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(); }

 

上一篇:canvas截图,html2canvas.js 截图整个div,canvas下载图片


下一篇:Linux:刚创建的普通用户不能使用Tab和上下左右键