C#数据导出到Excel

教授开发了一个nb的导出excel类，很是方便，分享下，留着以后用

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HPSF;
using NPOI.SS.Util;
using NPOI.HSSF.UserModel.Contrib;
using System.Data;
using System.Reflection;
using System.ComponentModel;

namespace XXXXManage.Common
{
    public class MultiSheet
    {
        public string SheetName { get; set; }
        public string Description { get; set; }
        public Dictionary<string, int> TopTitle { get; set; }
        public Dictionary<string, string> DicTitle { get; set; }
        public DataTable Data { get; set; }
    }

    public static class ExcelHelper
    {
        public static DataTable ToDataTable<T>(this IList<T> data)
        {
            PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
            DataTable table = new DataTable();
            for (int i = 0; i < props.Count; i++)
            {
                PropertyDescriptor prop = props[i];
                table.Columns.Add(prop.Name);
            }
            object[] values = new object[props.Count];
            foreach (T item in data)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    values[i] = props[i].GetValue(item);
                }
                table.Rows.Add(values);
            }
            return table;
        }

        public static DataTable GetData(Stream stream)
        {
            return GetData(stream, null);
        }
        public static DataTable GetData(Stream stream, string sheetName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook(stream);
            Sheet sheet = string.IsNullOrEmpty(sheetName) ? workbook.GetSheetAt(0) : workbook.GetSheet(sheetName);
            List<string> cols = new List<string>();
            int colIdx = 0;
            while (sheet.GetRow(0).GetCell(colIdx) != null)
            {
                cols.Add(sheet.GetRow(0).GetCell(colIdx++).StringCellValue.Trim());
            }

            DataTable dt = new DataTable();
            foreach (string colName in cols)
            {
                dt.Columns.Add(colName, typeof(string));
            }
            int end = sheet.LastRowNum;
            int col = dt.Columns.Count;
            for (int i = 1; i <= end; i++)
            {
                DataRow dr = dt.NewRow();
                for (int j = 0; j < col; j++)
                {
                    Cell cell = sheet.GetRow(i).GetCell(j);
                    dr[j] = cell == null ? string.Empty : cell.StringCellValue.Trim();
                }
                dt.Rows.Add(dr);
            }

            return dt;
        }
        public static MemoryStream CreateExcel<T>(Dictionary<string, string> dicTitle, List<T> data, string sheetName, string company, string subject)
        {
            return CreateExcel<T>(dicTitle, data, sheetName, company, subject, string.Empty);
        }
        public static MemoryStream CreateExcel<T>(Dictionary<string, string> dicTitle, List<T> data, string sheetName, string company, string subject, string description)
        {
            return CreateExcel<T>(dicTitle, data, sheetName, company, subject, description, null);
        }
        public static MemoryStream CreateExcel<T>(Dictionary<string, string> dicTitle, List<T> data, string sheetName, string company, string subject, string description, Dictionary<string, int> topTitle)
        {
            PropertyInfo[] properties = new PropertyInfo[dicTitle.Count];
            int idx = 0;
            foreach (KeyValuePair<string, string> kv in dicTitle)
            {
                PropertyInfo property = typeof(T).GetProperty(kv.Key);
                if (property == null)
                {
                    throw new Exception(string.Format("'{0}' not contains propertiy '{1}'", typeof(T).Name, kv.Key));
                }
                properties[idx++] = property;
            }

            HSSFWorkbook workbook = new HSSFWorkbook();
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = company;
            workbook.DocumentSummaryInformation = dsi;
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = subject;
            workbook.SummaryInformation = si;

            Sheet sheet = workbook.CreateSheet(sheetName);
            int r = 0;
            if (topTitle != null)
            {
                Row topRow = sheet.CreateRow(r);
                int topIdx = 0;
                foreach (var kv in topTitle)
                {
                    topRow.CreateCell(topIdx, CellType.STRING).SetCellValue(kv.Key);
                    sheet.AddMergedRegion(new CellRangeAddress(r, r, topIdx, topIdx + kv.Value - 1));
                    topIdx = topIdx + kv.Value;
                }

                r++;
            }
            if (!string.IsNullOrEmpty(description))
            {
                Row descRow = sheet.CreateRow(r);
                descRow.CreateCell(0, CellType.STRING).SetCellValue(description);
                sheet.AddMergedRegion(new CellRangeAddress(r, r, 0, dicTitle.Count - 1));
                r++;
            }
            Row row = sheet.CreateRow(r);
            idx = 0;
            foreach (var kv in dicTitle)
            {
                row.CreateCell(idx++, CellType.STRING).SetCellValue(kv.Value);
            }

            for (int i = 0; i < data.Count; i++)
            {
                row = sheet.CreateRow(i + 1 + r);
                for (int j = 0; j < properties.Length; j++)
                {
                    row.CreateCell(j, CellType.STRING).SetCellValue(properties[j].GetValue(data[i], null).ToString());
                }
            }

            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            return stream;
        }
        public static MemoryStream CreateExcel(List<MultiSheet> sheets, string company, string subject)
        {
            #region 有效性验证

            StringBuilder error = new StringBuilder();
            if (sheets == null || sheets.Count == 0)
            {
                error.Append(string.Format("不包含任何表单数据!"));
            }
            else
            {
                foreach (MultiSheet ms in sheets)
                {
                    if (ms.DicTitle == null || ms.DicTitle.Count == 0)
                    {
                        error.Append(string.Format("“{0}”中不包含任何列；\r\n", ms.SheetName));
                    }
                    else
                    {
                        foreach (KeyValuePair<string, string> kv in ms.DicTitle)
                        {
                            if (!ms.Data.Columns.Contains(kv.Key))
                            {
                                error.Append(string.Format("“{0}”中不包含“{1}”列；\r\n", ms.SheetName, kv.Key));
                            }
                        }
                    }
                }
            }
            if (error.Length > 0)
            {
                throw new Exception(error.ToString());
            }

            #endregion

            #region Excel文件信息

            HSSFWorkbook workbook = new HSSFWorkbook();
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = company;
            workbook.DocumentSummaryInformation = dsi;
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = subject;
            workbook.SummaryInformation = si;

            #endregion

            #region 写入每个Sheet

            foreach (MultiSheet ms in sheets)
            {
                Sheet sheet = workbook.CreateSheet(ms.SheetName);
                int rowIdx = 0;
                int colIdx = 0;
                if (ms.TopTitle != null)
                {
                    Row topRow = sheet.CreateRow(rowIdx);
                    int topIdx = 0;
                    foreach (var kv in ms.TopTitle)
                    {
                        topRow.CreateCell(topIdx, CellType.STRING).SetCellValue(kv.Key);
                        sheet.AddMergedRegion(new CellRangeAddress(rowIdx, rowIdx, topIdx, topIdx + kv.Value - 1));
                        topIdx = topIdx + kv.Value;
                    }

                    rowIdx++;
                }
                if (!string.IsNullOrEmpty(ms.Description))
                {
                    Row descRow = sheet.CreateRow(rowIdx);
                    descRow.CreateCell(0, CellType.STRING).SetCellValue(ms.Description);
                    sheet.AddMergedRegion(new CellRangeAddress(rowIdx, rowIdx, 0, ms.DicTitle.Count - 1));
                    rowIdx++;
                }
                Row row = sheet.CreateRow(rowIdx);

                foreach (var kv in ms.DicTitle)
                {
                    row.CreateCell(colIdx++, CellType.STRING).SetCellValue(kv.Value);
                }

                for (int i = 0; i < ms.Data.Rows.Count; i++)
                {
                    row = sheet.CreateRow(i + 1 + rowIdx);
                    colIdx = 0;
                    foreach (string colName in ms.DicTitle.Keys)
                    {
                        string value = ms.Data.Rows[i][colName] == null ? string.Empty : ms.Data.Rows[i][colName].ToString();
                        row.CreateCell(colIdx++, CellType.STRING).SetCellValue(value);
                    }
                }
            }

            #endregion

            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            return stream;
        }
    }
}

调用方式

List<MultiSheet> sheets = new List<MultiSheet> { 
                new MultiSheet{
                    SheetName = "车款关联信息",
                    Data = new List<CarStyleRelationViewModels>().ToDataTable<CarStyleRelationViewModels>(),
                    Description = null,
                    TopTitle = null,
                    DicTitle = new Dictionary<string,string>{
                        {"CarStyleName","本系统车款"},
                        {"RME_CarModelName","RME车款"}
                    }
                },
                new MultiSheet{
                    SheetName = "本系统车系车型车款对应表",
                    Data = dsaCarStyle.ToList().ToDataTable<DSACarStyleViewModel>(),
                    Description = null,
                    TopTitle = null,
                    DicTitle = new Dictionary<string,string>{
                        {"CarSeries","车系"},
                        {"CarType","车型"},
                        {"CarStyle","车款"}
                    }
                }, 
                new MultiSheet{
                    SheetName = "RME车型车款对应表",
                    Data = rmeCarModel.ToList().ToDataTable<DictionaryViewModel>(),
                    Description = null,
                    TopTitle = null,
                    DicTitle = new Dictionary<string,string>{
                        {"key","RME车型"},
                        {"value","RME车款"}
                    }
                }            
            };

ExcelHelper.CreateExcel(sheets, "XXX", "车款关联信息").GetBuffer()