c#开发数据导出为excel文件

我们经常需要将数据导出为excel文件。最常用的是导出datatable。另外就是导出list。目前一般excel操作常用NPOI开源组件，因为大家用过office com调用的都知道，其调用以及后期软件部署都要处理很多问题，相对比较麻烦。所以用NPOI来导出。

我自己在日常也用到导出，所以写了一个导出的类。exportListToExcel 是将list泛型对象导出为excel文件，但是返回的是byte[]。主要是为了方便asp.net输出。SetCellStyle函数是设置单元格样式。

另外既然是泛型，导出时需要动态获取值，经过网友比较动态DynamicMethod方法调用比直接反射取属性效率高，所以使用DynamicMethod这个类来取对象属性。后面又增加了datatable导出方法，附在后面，大同小异，datatable导出更简单。

public class ExportObjectsToExcel<T> where T : class
    {
        /// <summary>
        /// 导出列表对象到excel
        /// </summary>
        /// <param name="title">excel sheet title</param>
        /// <param name="columns">first row names to export</param>
        /// <param name="fields">property on object to export</param>
        /// <param name="list">object list to export</param>
        /// <returns>bytes content of excel file,can write out to xlsx excel file</returns>
        public byte[] exportListToExcel(string title, string[] columns, string[] fields, IList<T> list )
        {
            XSSFWorkbook workbookX = new XSSFWorkbook();
            
            ISheet sheet = workbookX.CreateSheet(title);
            ICellStyle cellStyle_14_Left = SetCellStyle(
                workbookX, 12,
                BorderStyle.Thin,
                HorizontalAlignment.Left,
                VerticalAlignment.Center
                ); //设计单元格格式

            int startRow = 0, startCol = 0;
            int fieldIndex = startCol + 0;
            IRow fieldRow = sheet.CreateRow(startRow++);
            fieldRow.Height = 400;
            for (int i = 0; i < columns.Length; i++)
            {
                string sColumnName = columns[i];
                ICell fieldCell = fieldRow.CreateCell(fieldIndex++);
                //列宽度
                sheet.SetColumnWidth(i, 13 * 256);
                if (i == columns.Length - 1)
                {
                    sheet.SetColumnWidth(i, 30 * 256);
                }
                //fieldCell.SetCellValue(sColumnName);
                //fieldCell.CellStyle = cellStyle_14_Left;
            }
            fieldRow = sheet.CreateRow(startRow++);
            fieldRow.Height = 400;
            fieldIndex = 0;
            for (int i = 0; i < columns.Length; i++)
            {
                string sColumnName = columns[i];
                ICell fieldCell = fieldRow.CreateCell(fieldIndex++);
                //列宽度
                sheet.SetColumnWidth(i, 40 * 256);
                if (i == columns.Length - 1)
                {
                    sheet.SetColumnWidth(i, 90 * 256);
                }
                fieldCell.SetCellValue(sColumnName);
                fieldCell.CellStyle = cellStyle_14_Left;
            }
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, columns.Length - 1));
            sheet.GetRow(0).GetCell(0).SetCellValue(title);
            sheet.GetRow(0).GetCell(0).CellStyle = cellStyle_14_Left;

            DynamicMethod<T> dynamicMethod = new DynamicMethod<T>();
            for (int i = 0; i < list.Count; i++)
            {
                IRow dataRow = sheet.CreateRow(startRow++);
                dataRow.Height = 400;
                for (int j = 0; j < fields.Length; j++)
                {
                    string fieldName = fields[j];
                    ICell newCell = dataRow.CreateCell(j);

                    object data1 = list[i];

                    object drValue = dynamicMethod.GetValue(data1, fieldName);
                    string DataType = "";
                    if (drValue!=null  )
                    {
                        DataType = drValue.GetType().ToString();
                    }
                    
                    switch (DataType)
                    {
                        case "System.String": //字符串类型
                            newCell.SetCellValue(TypeConvert.ToString(drValue));
                            newCell.CellStyle = cellStyle_14_Left; //格式化显示
                            break;
                        case "System.DateTime": //日期类型
                            DateTime dateV = TypeConvert.ToDateTime(drValue);

                            newCell.SetCellValue(dateV.ToString("yyyy-MM-dd"));

                            newCell.CellStyle = cellStyle_14_Left; //格式化显示
                            break;
                        case "System.Boolean": //布尔型
                            bool boolV = false;
                            bool.TryParse(TypeConvert.ToString(drValue), out boolV);
                            newCell.SetCellValue(boolV);
                            newCell.CellStyle = cellStyle_14_Left; //格式化显示
                            break;
                        case "System.Int16": //整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(TypeConvert.ToString(drValue), out intV);
                            TypeConvert.ToInt32(drValue);
                            newCell.SetCellValue(intV);
                            newCell.CellStyle = cellStyle_14_Left; //格式化显示
                            break;
                        case "System.Decimal": //浮点型
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(TypeConvert.ToString(drValue), out doubV);
                            newCell.SetCellValue(doubV.ToString("f6"));
                            newCell.CellStyle = cellStyle_14_Left; //格式化显示
                            break;
                        case "System.DBNull": //空值处理
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle_14_Left; //格式化显示
                            break;
                        default:
                            newCell.SetCellValue("");
                            newCell.CellStyle = cellStyle_14_Left; //格式化显示
                            break;
                    }
                }
            }

            MemoryStream stream = new System.IO.MemoryStream();
            workbookX.Write(stream);
            stream.Close();
            byte[] bytes=stream.ToArray();
            
            workbookX.Close();
            return bytes;
        }

private ICellStyle SetCellStyle(IWorkbook wk, int fontSize, BorderStyle borderStyle, HorizontalAlignment horAli, VerticalAlignment verAli)
        {
            ICellStyle cellStyle = wk.CreateCellStyle();
            //设置单元格上下左右边框线  
            cellStyle.BorderTop = borderStyle;
            cellStyle.BorderBottom = borderStyle;
            cellStyle.BorderLeft = borderStyle;
            cellStyle.BorderRight = borderStyle;
            //文字水平和垂直对齐方式  
            cellStyle.Alignment = horAli;
            cellStyle.VerticalAlignment = verAli;
            IFont font = wk.CreateFont();
            font.FontHeight = fontSize;
            cellStyle.SetFont(font);
            //是否换行  

            cellStyle.WrapText = true;  //自动换行
            //缩小字体填充  
            //cellStyle.ShrinkToFit = true; 
            return cellStyle;
        }
    }

    public class DynamicMethod<T>
    {
        internal static Func<object, string, object> GetValueDelegate;

        public object GetValue(object instance, string memberName)
        {
            return GetValueDelegate(instance, memberName);
        }

        static DynamicMethod()
        {
            GetValueDelegate = GenerateGetValue();
        }

        private static Func<object, string, object> GenerateGetValue()
        {
            var type = typeof(T);
            var instance = Expression.Parameter(typeof(object), "instance");
            var memberName = Expression.Parameter(typeof(string), "memberName");
            var nameHash = Expression.Variable(typeof(int), "nameHash");
            var calHash = Expression.Assign(nameHash, Expression.Call(memberName, typeof(object).GetMethod("GetHashCode")));
            var cases = new List<SwitchCase>();
            foreach (var propertyInfo in type.GetProperties())
            {
                var property = Expression.Property(Expression.Convert(instance, typeof(T)), propertyInfo.Name);
                var propertyHash = Expression.Constant(propertyInfo.Name.GetHashCode(), typeof(int));

                cases.Add(Expression.SwitchCase(Expression.Convert(property, typeof(object)), propertyHash));
            }
            var switchEx = Expression.Switch(nameHash, Expression.Constant(null), cases.ToArray());
            var methodBody = Expression.Block(typeof(object), new[] { nameHash }, calHash, switchEx);

            return Expression.Lambda<Func<object, string, object>>(methodBody, instance, memberName).Compile();
        }
    }

datatable的导出方法。

/// <summary>
        /// export datatable to excel file
        /// </summary>
        /// <param name="title">table title</param>
        /// <param name="dt">table</param>
        /// <param name="xlsFileName">export file name xlsx</param>
        public void exportDataTable(string title, DataTable dt, string xlsFileName)
        {
            string fileName = xlsFileName;
            if (fileName.ToLower().EndsWith(".xls"))
            {
                fileName = fileName + "x";
            }
            XSSFWorkbook workbookX = new XSSFWorkbook();

            FileStream stream = File.Create(fileName);
            {
                ISheet sheet = workbookX.CreateSheet(title);
                ICellStyle cellStyle_14_Left = SetCellStyle(
                    workbookX, 12,
                    BorderStyle.Thin,
                    HorizontalAlignment.Left,
                    VerticalAlignment.Center
                    ); //设计单元格格式

                int startRow = 0, startCol = 0;
                int fieldIndex = startCol + 0;
                IRow fieldRow = sheet.CreateRow(startRow++);
                fieldRow.Height = 400;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    string sColumnName = dt.Columns[i].ColumnName;
                    ICell fieldCell = fieldRow.CreateCell(fieldIndex++);
                    //列宽度
                    sheet.SetColumnWidth(i, 13 * 256);
                    if (i == dt.Columns.Count - 1)
                    {
                        sheet.SetColumnWidth(i, 30 * 256);
                    }
                    //fieldCell.SetCellValue(sColumnName);
                    //fieldCell.CellStyle = cellStyle_14_Left;
                }
                fieldRow = sheet.CreateRow(startRow++);
                fieldRow.Height = 400;
                fieldIndex = 0;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    string sColumnName = dt.Columns[i].ColumnName;
                    ICell fieldCell = fieldRow.CreateCell(fieldIndex++);
                    //列宽度
                    sheet.SetColumnWidth(i, 13 * 256);
                    if (i == dt.Columns.Count - 1)
                    {
                        sheet.SetColumnWidth(i, 30 * 256);
                    }
                    fieldCell.SetCellValue(sColumnName);
                    fieldCell.CellStyle = cellStyle_14_Left;
                }
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));
                sheet.GetRow(0).GetCell(0).SetCellValue(title);
                sheet.GetRow(0).GetCell(0).CellStyle = cellStyle_14_Left;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow dataRow = sheet.CreateRow(startRow++);
                    dataRow.Height = 400;
                    foreach (DataColumn column in dt.Columns)
                    {
                        ICell newCell = dataRow.CreateCell(column.Ordinal);

                        DataRow row = dt.Rows[i];

                        string drValue = TypeConvert.ToString(row[column]);

                        switch (column.DataType.ToString())
                        {
                            case "System.String": //字符串类型
                                newCell.SetCellValue(drValue);
                                newCell.CellStyle = cellStyle_14_Left; //格式化显示
                                break;
                            case "System.DateTime": //日期类型
                                DateTime dateV = TypeConvert.ToDateTime(row[column]);

                                newCell.SetCellValue(dateV.ToString("yyyy-MM-dd"));

                                newCell.CellStyle = cellStyle_14_Left; //格式化显示
                                break;
                            case "System.Boolean": //布尔型
                                bool boolV = false;
                                bool.TryParse(drValue, out boolV);
                                newCell.SetCellValue(boolV);
                                newCell.CellStyle = cellStyle_14_Left; //格式化显示
                                break;
                            case "System.Int16": //整型
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Byte":
                                int intV = 0;
                                int.TryParse(drValue, out intV);
                                TypeConvert.ToInt32(drValue);
                                newCell.SetCellValue(intV);
                                newCell.CellStyle = cellStyle_14_Left; //格式化显示
                                break;
                            case "System.Decimal": //浮点型
                            case "System.Double":
                                double doubV = 0;
                                double.TryParse(drValue, out doubV);
                                newCell.SetCellValue(doubV.ToString("f6"));
                                newCell.CellStyle = cellStyle_14_Left; //格式化显示
                                break;
                            case "System.DBNull": //空值处理
                                newCell.SetCellValue("");
                                newCell.CellStyle = cellStyle_14_Left; //格式化显示
                                break;
                            default:
                                newCell.SetCellValue("");
                                newCell.CellStyle = cellStyle_14_Left; //格式化显示
                                break;
                        }
                    }
                }
            }
            workbookX.Write(stream);
            stream.Close();
            workbookX.Close();
        }