NPOI导出EF的List数据

  

   

    

     

    
    

     

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

文章来源: zzzili.blog.csdn.net，作者：清雨小竹，版权归原作者所有，如需转载，请联系作者。

原文链接：zzzili.blog.csdn.net/article/details/84183953