NPOI List数据源 导出excel

 List数据源生成HSSFWorkbook通用方法:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
public class WorkBook
    {
        public static HSSFWorkbook BuildSwitchData<T>(string SheetName, List<T> list, Dictionary<string, string> FiedNames)
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)wb.CreateSheet(SheetName); //创建工作表
            sheet.CreateFreezePane(0, 1); //冻结列头行
            HSSFRow row_Title = (HSSFRow)sheet.CreateRow(0); //创建列头行
            row_Title.HeightInPoints = 30.5F; //设置列头行高
            HSSFCellStyle cs_Title = (HSSFCellStyle)wb.CreateCellStyle(); //创建列头样式
            cs_Title.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
            cs_Title.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
            HSSFFont cs_Title_Font = (HSSFFont)wb.CreateFont(); //创建字体
            cs_Title_Font.IsBold = true; //字体加粗
            cs_Title_Font.FontHeightInPoints = 14; //字体大小
            cs_Title.SetFont(cs_Title_Font); //将字体绑定到样式
            #region 生成列头
            int ii = 0;
            foreach (string key in FiedNames.Keys)
            {
                HSSFCell cell_Title = (HSSFCell)row_Title.CreateCell(ii); //创建单元格
                cell_Title.CellStyle = cs_Title; //将样式绑定到单元格
                cell_Title.SetCellValue(key);
                sheet.SetColumnWidth(ii, 25 * 256);//设置列宽
                ii++;
            }
 
            #endregion
            //获取 实体类 类型对象
            Type t = typeof(T); // model.GetType();
            //获取 实体类 所有的 公有属性
            List<PropertyInfo> proInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();
            //创建 实体属性 字典集合
            Dictionary<string, PropertyInfo> dictPros = new Dictionary<string, PropertyInfo>();
            //将 实体属性 中要修改的属性名 添加到 字典集合中 键:属性名  值:属性对象
            proInfos.ForEach(p =>
            {
                if (FiedNames.Values.Contains(p.Name))
                {
                    dictPros.Add(p.Name, p);
                }
            });
 
            HSSFCellStyle cs_Content = (HSSFCellStyle)wb.CreateCellStyle(); //创建列头样式
            cs_Content.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
            cs_Content.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
            for (int i = 0; i < list.Count; i++)
            {
 
                HSSFRow row_Content = (HSSFRow)sheet.CreateRow(i + 1); //创建行
                row_Content.HeightInPoints = 20;
                int jj = 0;
                foreach (string proName in FiedNames.Values)
                {
                    if (dictPros.ContainsKey(proName))
                    {
                        HSSFCell cell_Conent = (HSSFCell)row_Content.CreateCell(jj); //创建单元格
                        cell_Conent.CellStyle = cs_Content;
 
                        //如果存在,则取出要属性对象
                        PropertyInfo proInfo = dictPros[proName];
                        //获取对应属性的值
                        object value = proInfo.GetValue(list[i], null); //object newValue = model.uName;
                        string cell_value = value == null ? "" : value.ToString();
                        cell_Conent.SetCellValue(cell_value);
                        jj++;
                    }
                }
            }
            return wb;
 
        }
    }

MVC控制器调用方法并以流的形式下载:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
public ActionResult ProcessRequest()
   {
       try
       {
           fangcanpyEntities db = new fangcanpyEntities();
           List<Models.order> list = db.order.AsNoTracking().ToList();//获取List数据 可自行获取
           Dictionary<string, string> FiedNames = new Dictionary<string, string>();
           FiedNames.Add("订单编号", "order_no");
           FiedNames.Add("下单时间", "add_time");
           FiedNames.Add("会员编号", "uid");
           FiedNames.Add("收件人", "accept_name");
           FiedNames.Add("电话号码", "mobile");
           FiedNames.Add("收件地址", "address");
           FiedNames.Add("订单金额", "order_amount");
           
           string sExportFileName = "";
           HSSFWorkbook wb = null;
           wb = WorkBook.BuildSwitchData<Models.order>("订单",list, FiedNames);//调用通用方法
           sExportFileName = "订单" + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";//设置下载文件的名称
           MemoryStream stream = new MemoryStream();
           wb.Write(stream);
           var buf = stream.ToArray();
           return File(buf, "pplication/vnd.ms-excel", sExportFileName);
 
       }
       catch
       {
           return Content("");
       }
   }

  

posted on   落叶子  阅读(1570)  评论(1编辑  收藏  举报

编辑推荐:
· SQL Server 2025 AI相关能力初探
· Linux系列:如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
阅读排行:
· 阿里最新开源QwQ-32B,效果媲美deepseek-r1满血版,部署成本又又又降低了!
· 单线程的Redis速度为什么快?
· SQL Server 2025 AI相关能力初探
· AI编程工具终极对决:字节Trae VS Cursor,谁才是开发者新宠?
· 展开说说关于C#中ORM框架的用法!

导航

< 2025年3月 >
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5
点击右上角即可分享
微信分享提示