应用NPOI导出数据到Excel 转http://hi.baidu.com/zzm1219/blog/item/60b764221934e1b04723e850.html

Coordinator
Dec 12, 2011 at 1:54 AM

网上又很多用dataset or datatable的,今天借鉴一下,做一个用Ilist<T>导出的

总体如下:

 public static void ExportExcel<T>(IList<T> list, Stream excelStream, params string[] fields)
        {
            /*
             * 1.创建excel文件
             * 2.创建sheet
             * 3.插入Row,Cell
             */
            HSSFWorkbook excelWorkbook = CreateExcelFile();
            InsertRow(list, excelWorkbook, fields);
            SaveExcelFile(excelWorkbook, excelStream);

重点在第InsertRow这个方法:

public static void InsertRow<T>(IList<T> list, HSSFWorkbook excelWorkbook, params string[] fields)
        {
            int rowCount = 0;
            int sheetCount = 1;
            NPOI.SS.UserModel.Sheet newsheet = null;

            //循环数据源
            newsheet = excelWorkbook.CreateSheet();
            for (int i = 0; i < list.Count; i++)
            {
                rowCount++;
                //超出10000条数据 创建新的工作博
                if (rowCount == 100000)
                {
                    rowCount = 1;
                    sheetCount++;
                    newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount);
                }

                var newRow = newsheet.CreateRow(rowCount);
                InsertCell(list, list[i], newRow, newsheet, excelWorkbook, fields);
            }
        }

protected static void InsertCell<T>(IList<T> list, T data, NPOI.SS.UserModel.Row currentExcelRow, NPOI.SS.UserModel.Sheet excelSheet, HSSFWorkbook excelWorkBook, params string[] fields)
        {
            List<string> reportFields = new List<string>();
            PropertyInfo[] properties = typeof(T).GetProperties(); //获得T类型的所有属性

            #region  找出那些须要导出的属性
            if (fields.Length == 0)
            {
                foreach (var pi in properties)
                {
                    object[] attrs = pi.GetCustomAttributes(true); //获得T类型的所有带特性的属性

                    var needExport = attrs.FirstOrDefault<object>(x => x is FieldsAttribute);

                    if (needExport != null)
                    {
                        reportFields.Add(pi.Name);
                    }
                }
            }
            else
            {
                reportFields.AddRange(fields);
            }
            #endregion

            for (int cellIndex = 0; cellIndex < reportFields.Count; cellIndex++)
            {
                var cell = currentExcelRow.CreateCell(cellIndex);

                var pi = properties.First<PropertyInfo>(x => x.Name == reportFields[cellIndex]);

                var value = pi.GetValue(data, null);  //获得该实例,该属性的值

                #region  类型验证

                if (pi.PropertyType == typeof(int) || pi.PropertyType == typeof(int?)
                       || pi.PropertyType == typeof(Int16) || pi.PropertyType == typeof(Int16?)
                   || pi.PropertyType == typeof(Int32) || pi.PropertyType == typeof(Int32?)
                   || pi.PropertyType == typeof(Int64) || pi.PropertyType == typeof(Int64?)
                   || pi.PropertyType == typeof(long) || pi.PropertyType == typeof(long?)
                   || pi.PropertyType == typeof(decimal) || pi.PropertyType == typeof(decimal?)
                   || pi.PropertyType == typeof(float) || pi.PropertyType == typeof(float?)
                   || pi.PropertyType == typeof(double) || pi.PropertyType == typeof(double?)
                    || pi.PropertyType == typeof(uint) || pi.PropertyType == typeof(uint?)
                   || pi.PropertyType == typeof(UInt16) || pi.PropertyType == typeof(UInt16?)
                  || pi.PropertyType == typeof(UInt32) || pi.PropertyType == typeof(UInt32?)
                  || pi.PropertyType == typeof(UInt64) || pi.PropertyType == typeof(UInt64?)
                   || pi.PropertyType == typeof(ulong) || pi.PropertyType == typeof(ulong?)
                   || pi.PropertyType == typeof(short) || pi.PropertyType == typeof(short?)
                   || pi.PropertyType == typeof(ushort) || pi.PropertyType == typeof(ushort?)
                   )
                {
                    cell.SetCellValue(Convert.ToDouble(value));
                }
                else if (pi.PropertyType == typeof(bool) || pi.PropertyType == typeof(bool?))
                {
                    cell.SetCellValue(Convert.ToBoolean(value));
                }
                else if (pi.PropertyType == typeof(DateTime) || pi.PropertyType == typeof(DateTime?))
                {
                    cell.SetCellValue(Convert.ToDateTime(value));
                }
                else
                {
                    cell.SetCellValue(Convert.ToString(value));
                }

                #endregion
            }
        }

 

有兴趣的可以自己调试试试·