NPOI导出EF的List数据

Bertha 。 2023-10-18 11:21 34阅读 0赞

using Newtonsoft.Json;
    using NPOI.HSSF.UserModel;
    using NPOI.SS.UserModel;
    using NPOI.XSSF.UserModel;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Diagnostics;
    using System.IO;
    using System.Linq;
    using System.Web;
    
    namespace YFAPICommon.Libs
    {
        public class ExportToExcel
        {
            private string fileName = null; //文件名
            private NPOI.SS.UserModel.IWorkbook workbook = null;
            private FileStream fs = null;
            private bool disposed;
    
            public ExportToExcel(string fileName)
            {
                this.fileName = fileName;
                disposed = false;
            }
            public void Export(Dictionary<string, string> keys, object list)
            {
                ISheet sheet = null;
    
                fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook();
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook();
    
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet("Sheet1");
                }
                else
                {
                    return ;
                }
    
                写入列名
                IRow row = sheet.CreateRow(0);
                int k = 0;
                foreach(string key in keys.Keys)
                {
                    row.CreateCell(k).SetCellValue(keys[key]);
                    k++;
                }
    
                int count = 1;
                string jsonStr = JsonConvert.SerializeObject(list);
                List<Dictionary<string, object>> objList = JsonConvert.DeserializeObject<List<Dictionary<string, object>>>(jsonStr);
                //foreach (Dictionary<string, object> node in objList)
                for (int i = 0; i < objList.Count; i++)
                {
                    Dictionary<string, object> node = objList[i];
                    IRow datarow = sheet.CreateRow(count);
                    int j = 0;
                    foreach (string key in keys.Keys)
                    {
                        object value = null;
                        node.TryGetValue(key, out value);
                        Debug.WriteLine(value);
                        if(value != null)
                            datarow.CreateCell(j).SetCellValue(value.ToString());
                        j++;
                    }
                    count++;
                }
    
                workbook.Write(fs); //写入到excel
            }
    
        }
    }

调用方法

Dictionary<string, string> daochuKeyDic = new Dictionary<string, string>();
                daochuKeyDic.Add("UserName", "用户名");
                daochuKeyDic.Add("OrderNo", "订单号");
                daochuKeyDic.Add("AgentName", "代理商名称");
                daochuKeyDic.Add("OrderAmount", "订单金额");
                daochuKeyDic.Add("CreateTime", "创建时间");
                daochuKeyDic.Add("PayType", "支付类型");
                daochuKeyDic.Add("OrderState", "支付状态");
                daochuKeyDic.Add("PayTime", "支付时间");
                ExportToExcel helper = new ExportToExcel("C:\\Data\\data.xlsx");
                helper.Export(daochuKeyDic, list);