NPOIExtensions.cs

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;

namespace TestProject
{
    public class ColumnDefination<T>
    {
        public string Caption { get; set; }

        public int Width { get; set; }

        public Func<T, object> Value { get; set; }
    }

    public static class NPOIExtensions
    {
        public static void FillObject<T>(this IRow row, T entity, Func<int, Expression<Func<T, object>>> mappings, HSSFFormulaEvaluator evaluator = null)
        {
            evaluator = evaluator ?? new HSSFFormulaEvaluator(row.Sheet.Workbook);

            for (var i = 0; i < row.Cells.Count; i++)
            {
                var expression = mappings(i);

                if (expression != null)
                {
                    row.Cells[i].FillProperty(entity, expression, evaluator);
                }
            }
        }

        public static void FillProperty<T>(this ICell cell, T entity, Expression<Func<T, object>> expression, HSSFFormulaEvaluator evaluator = null)
        {
            var propertyInfo = expression.GetPropertyAccessor();

            var propertyType = propertyInfo.PropertyType;

            var underlyingType = Nullable.GetUnderlyingType(propertyInfo.PropertyType);

            var nullable = false;

            if (underlyingType != null)
            {
                nullable = true;
                propertyType = underlyingType;
            }

            object value = null;
            var set = false;

            try
            {
                switch (cell.CellType)
                {
                    case CellType.STRING:
                        //value = Convert.ChangeType(cell.StringCellValue.Trim(), propertyInfo.PropertyType);
                        value = cell.StringCellValue.Trim();
                        set = true;
                        break;

                    case CellType.NUMERIC:
                        if (propertyType.IsAssignableFrom(typeof(DateTime)))
                        {
                            //value = Convert.ChangeType(cell.DateCellValue, propertyInfo.PropertyType);
                            value = cell.DateCellValue;
                            set = true;
                        }
                        else
                        {
                            value = Convert.ChangeType(cell.NumericCellValue, propertyType);
                            //value = cell.NumericCellValue;
                            set = true;
                        }
                        break;

                    case CellType.BOOLEAN:
                        value = cell.BooleanCellValue;
                        set = true;
                        break;

                    //case CellType.FORMULA:
                    //    evaluator = evaluator ?? new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                    //    break;
                }

                if (set)
                {
                    if (nullable)
                    {
                        propertyInfo.SetValue(entity, value);
                    }
                    else
                    {
                        propertyInfo.SetValue(entity, value, null);
                    }
                }
            }
            catch (Exception ex)
            {
                var message = string.Format("Cell[{0}, {1}] fill failed", cell.RowIndex, cell.ColumnIndex);
                throw new InvalidOperationException(message, ex);
            }

        }


        /// <summary>
        /// 安全的取出某個 NPOI Cell 的值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="cell"></param>
        /// <param name="defaultValue"></param>
        /// <param name="evaluator"></param>
        /// <returns></returns>
        public static T GetCellValue<T>(this ICell cell, T defaultValue, HSSFFormulaEvaluator evaluator = null) where T : IConvertible
        {
            var type = typeof(T);

            switch (cell.CellType)
            {
                case CellType.STRING:
                    return (T)Convert.ChangeType(cell.StringCellValue.Trim(), type);

                case CellType.NUMERIC:
                    if (defaultValue is DateTime)
                    {
                        return (T)Convert.ChangeType(cell.DateCellValue, type);
                    }

                    return (T)Convert.ChangeType(cell.NumericCellValue, type);

                case CellType.BOOLEAN:
                    return (T)Convert.ChangeType(cell.BooleanCellValue, type);

                case CellType.FORMULA:
                    evaluator = evaluator ?? new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                    return evaluator.EvaluateInCell(cell).GetCellValue(defaultValue);

                case CellType.ERROR:
                    var message = string.Format("Cell[{0}, {1}]", cell.RowIndex, cell.ColumnIndex);
                    throw new NotSupportedException(message);

                default:
                    return defaultValue;
            }
        }

        public static string GetCellString(this ICell cell, HSSFFormulaEvaluator evaluator = null)
        {
            return cell.GetCellValue(string.Empty, evaluator);
        }

        /// <summary>
        /// 將 IEnumerable&lt;T&gt; 匯出成 Excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="source"></param>
        /// <param name="sheetName"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public static Stream ExportExcel<T>(this IEnumerable<T> source, string sheetName, IEnumerable<ColumnDefination<T>> columns)
        {
            int rowIndex = 0;
            int cellIndex = 0;

            var workbook = new HSSFWorkbook();
            var sheet = workbook.CreateSheet(sheetName);

            #region 樣式

            var headerStyle = workbook.CreateCellStyle();
            headerStyle.Alignment = HorizontalAlignment.CENTER;
            headerStyle.VerticalAlignment = VerticalAlignment.CENTER;
            headerStyle.FillBackgroundColor = HSSFColor.BLACK.index;
            headerStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
            headerStyle.BorderTop = headerStyle.BorderLeft = headerStyle.BorderRight = headerStyle.BorderBottom = BorderStyle.THIN;

            var headerFont = workbook.CreateFont();
            headerFont.Color = HSSFColor.WHITE.index;
            headerFont.Boldweight = 1;
            headerStyle.SetFont(headerFont);

            var cellStyle = workbook.CreateCellStyle();
            cellStyle.Alignment = HorizontalAlignment.LEFT;
            cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
            cellStyle.BorderTop = cellStyle.BorderLeft = cellStyle.BorderRight = cellStyle.BorderBottom = BorderStyle.THIN;
            cellStyle.WrapText = true;

            #endregion

            #region 表頭

            var row = sheet.CreateRow(rowIndex++);
            foreach (var column in columns)
            {
                if (column.Width > 0)
                {
                    sheet.SetColumnWidth(cellIndex, column.Width * 256);
                }
                var cell = row.CreateCell(cellIndex++);
                cell.SetCellValue(column.Caption);
                cell.CellStyle = headerStyle;
            }
            var colorHeader = row.GetCell(cellIndex - 1);

            #endregion

            #region 表身

            foreach (var result in source)
            {
                row = sheet.CreateRow(rowIndex++);
                cellIndex = 0;

                foreach (var column in columns)
                {
                    var cell = row.CreateCell(cellIndex++);
                    cell.CellStyle = cellStyle;
                    var obj = column.Value.Invoke(result);
                    var value = obj == null ? string.Empty : obj.ToString();
                    cell.SetCellValue(value);
                }
            }

            #endregion

            var stream = new MemoryStream();
            workbook.Write(stream);
            stream.Seek(0, SeekOrigin.Begin);

            return stream;
        }
    }
}