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

 

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页