Npoi导出Excel操作类

最新推荐文章于 2023-06-02 00:17:41 发布
weixin_30633507
最新推荐文章于 2023-06-02 00:17:41 发布
阅读量70
点赞数
文章标签：测试
原文链接：http://www.cnblogs.com/iwenwen/archive/2013/06/09/3129065.html
版权
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.Data;
using System.ComponentModel;
using System.Web.Mvc;

namespace Util
{
    public class ExcelHelper
    {
        public static class ExportTool<T>
        {
            /// <summary>
            /// List&lt;T&gt;转化为Excel文件,并返回FileStreamResult
            /// </summary>
            /// <param name="list">需要转化的List&lt;T&gt;</param>
            /// <param name="headerList">Excel标题行的List列表</param>
            /// <param name="fileName">Excel的文件名</param>
            /// <returns></returns>
            public static FileStreamResult ExportListToExcel_MVCResult(IList<T> list, IList<String> headerList, String fileName)
            {
                FileStreamResult fsr = new FileStreamResult(ExportListToExcel(list, headerList, null), "application/ms-excel");
                fsr.FileDownloadName = HttpUtility.UrlEncode(fileName + ".xls");
                return fsr;
            }

            /// <summary>
            /// List&lt;T&gt;转化为Excel文件,并返回FileStreamResult
            /// </summary>
            /// <param name="list">需要转化的List&lt;T&gt;</param>
            /// <param name="headerList">Excel标题行的List列表</param>
            /// <param name="fileName">Excel的文件名</param>
            /// <param name="sortList">指定导出List&lt;T&gt中哪些属性,并按顺序排序</param>
            /// <returns></returns>
            public static FileStreamResult ExportListToExcel_MVCResult(IList<T> list, IList<String> headerList, String fileName, IList<String> sortList)
            {
                FileStreamResult fsr = new FileStreamResult(ExportListToExcel(list, headerList, sortList), "application/ms-excel");
                fsr.FileDownloadName = HttpUtility.UrlEncode(fileName + ".xls");
                return fsr;
            }

            public static MemoryStream ExportListToExcel(IList<T> list, IList<String> headerList, IList<String> sortList)
            {
                try
                {
                    //文件流对象
                    //FileStream file = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                    MemoryStream stream = new MemoryStream();

                    //打开Excel对象
                    HSSFWorkbook workbook = new HSSFWorkbook();

                    //Excel的Sheet对象
                    NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet("sheet1");

                    //set date format
                    CellStyle cellStyleDate = workbook.CreateCellStyle();
                    DataFormat format = workbook.CreateDataFormat();
                    cellStyleDate.DataFormat = format.GetFormat("yyyy年m月d日");

                    //使用NPOI操作Excel表
                    NPOI.SS.UserModel.Row row = sheet.CreateRow(0);
                    int count = 0;

                    PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));

                    //if (headerList != null && properties.Count != headerList.Count)
                    //    throw new Exception("集合的属性个数和标题行List的个数不一致");

                    //如果没有自定义的行首,那么采用反射集合的属性名做行首
                    if (headerList == null)
                    {
                        for (int i = 0; i < properties.Count; i++) //生成sheet第一行列名 
                        {
                            NPOI.SS.UserModel.Cell cell = row.CreateCell(count++);
                            cell.SetCellValue(String.IsNullOrEmpty(properties[i].DisplayName) ? properties[i].Name : properties[i].DisplayName);
                        }
                    }
                    else
                    {
                        for (int i = 0; i < headerList.Count; i++) //生成sheet第一行列名 
                        {
                            NPOI.SS.UserModel.Cell cell = row.CreateCell(count++);
                            cell.SetCellValue(headerList[i]);
                        }
                    }

                    //将数据导入到excel表中
                    for (int i = 0; i < list.Count; i++)
                    {
                        NPOI.SS.UserModel.Row rows = sheet.CreateRow(i + 1);
                        count = 0;

                        object value = null;
                        //如果自定义导出属性及排序字段为空,那么走反射序号的方式
                        if (sortList == null)
                        {
                            for (int j = 0; j < properties.Count; j++)
                            {

                                NPOI.SS.UserModel.Cell cell = rows.CreateCell(count++);
                                value = properties[j].GetValue(list[i]);
                                cell.SetCellValue(value == null ? String.Empty : value.ToString());
                            }
                        }
                        else
                        {
                            for (int j = 0; j < sortList.Count; j++)
                            {
                                NPOI.SS.UserModel.Cell cell = rows.CreateCell(count++);
                                value = properties[sortList[j]].GetValue(list[i]);
                                cell.SetCellValue(value == null ? String.Empty : value.ToString());
                            }
                        }
                    }


                    //保存excel文档
                    sheet.ForceFormulaRecalculation = true;

                    workbook.Write(stream);
                    workbook.Dispose();
                    stream.Seek(0, SeekOrigin.Begin);

                    return stream;
                }
                catch
                {
                    return new MemoryStream();
                }
            }

            /// <summary>
            /// 将DataSet数据集转换HSSFworkbook对象，并保存为Stream流
            /// </summary>
            /// <param name="ds"></param>
            /// <returns>返回数据流Stream对象</returns>
            public static MemoryStream ExportDatasetToExcel(DataSet ds)
            {
                try
                {
                    //文件流对象
                    //FileStream file = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                    MemoryStream stream = new MemoryStream();

                    //打开Excel对象
                    HSSFWorkbook workbook = new HSSFWorkbook();

                    //Excel的Sheet对象
                    NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet("sheet1");


                    var cellFont = workbook.CreateFont();
                    var cellStyle = workbook.CreateCellStyle();
                    //- 加粗，白色前景色
                    cellFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
                    //- 这个是填充的模式，可以是网格、花式等。如果需要填充单色，请使用：SOLID_FOREGROUND
                    //cellStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
                    //- 设置这个样式的字体，如果没有设置，将与所有单元格拥有共同字体！
                    cellStyle.SetFont(cellFont);
                    cellStyle.Alignment = HorizontalAlignment.CENTER;


                    //set date format
                    CellStyle cellStyleDate = workbook.CreateCellStyle();
                    DataFormat format = workbook.CreateDataFormat();
                    cellStyleDate.DataFormat = format.GetFormat("yyyy年m月d日");

                    //使用NPOI操作Excel表
                    NPOI.SS.UserModel.Row row = sheet.CreateRow(0);
                    int count = 0;
                    for (int i = 0; i < ds.Tables[0].Columns.Count; i++) //生成sheet第一行列名 
                    {
                        NPOI.SS.UserModel.Cell cell = row.CreateCell(count++);
                        cell.SetCellValue(ds.Tables[0].Columns[i].Caption);
                        cell.CellStyle = cellStyle;
                    }
                    //将数据导入到excel表中
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        NPOI.SS.UserModel.Row rows = sheet.CreateRow(i + 1);
                        count = 0;
                        for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                        {
                            NPOI.SS.UserModel.Cell cell = rows.CreateCell(count++);
                            Type type = ds.Tables[0].Rows[i][j].GetType();
                            if (type == typeof(int) || type == typeof(Int16)
                                || type == typeof(Int32) || type == typeof(Int64))
                            {
                                cell.SetCellValue((int)ds.Tables[0].Rows[i][j]);
                            }
                            else
                            {
                                if (type == typeof(float) || type == typeof(double) || type == typeof(Double))
                                {
                                    cell.SetCellValue((Double)ds.Tables[0].Rows[i][j]);
                                }
                                else
                                {
                                    if (type == typeof(DateTime))
                                    {
                                        cell.SetCellValue(((DateTime)ds.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd HH:mm"));
                                    }
                                    else
                                    {
                                        if (type == typeof(bool) || type == typeof(Boolean))
                                        {
                                            cell.SetCellValue((bool)ds.Tables[0].Rows[i][j]);
                                        }
                                        else
                                        {
                                            cell.SetCellValue(ds.Tables[0].Rows[i][j].ToString());
                                        }
                                    }
                                }
                            }
                        }
                    }

                    //保存excel文档
                    sheet.ForceFormulaRecalculation = true;

                    workbook.Write(stream);
                    workbook.Dispose();

                    return stream;
                }
                catch
                {
                    return new MemoryStream();
                }
            }
        }
    }
}