using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using NPOI.SS.UserModel;

using NPOI.XSSF.UserModel;

using System.IO;

using System.Reflection;

namespace WebApplication1.Controllers

{

    public class Info

    {

        public int Id { get; set; }

        public string Name { get; set; }

    }

    public class TestController : Controller

    {

        // GET: Test

        public ActionResult Index()

        {

            List<Info> list = new List<Info>();

            for (int i = ; i < ; i++)

            {

                Info info = new Info();

                info.Id = i + ;

                info.Name = "张三" + (i + );

                list.Add(info);

            }

            Dictionary<string, string> columnInfo = new Dictionary<string, string>();

            columnInfo.Add("Id","序号");

            columnInfo.Add("Name", "姓名");

            byte[] btyBytes=null;

            var a = ExportExcelTest<Info>(list, @"F://111.xlsx", ref btyBytes, columnInfo, );

            return View();

        }

        #region  NPOI大数据量多个sheet导出

        /// <summary>

        /// 大数据量多个sheet导出

        /// </summary>

        /// <typeparam name="T">数据源实体类</typeparam>

        /// <param name="objList">数据源</param>

        /// <param name="fileName">文件名称</param>

        /// <param name="btyBytes">导出数据流</param>

        /// <param name="columnInfo">显示列对应数据字典</param>

        /// <param name="listCount">每个sheet包含数据条数</param>

        /// <returns></returns>

        public static bool ExportExcelTest<T>(List<T> objList, string fileName, ref byte[] btyBytes,

Dictionary<string, string> columnInfo = null, int listCount = )

        {

            bool bResult = false;

            //在内存中生成一个Excel文件：

            XSSFWorkbook book = new XSSFWorkbook();

            if (objList != null && objList.Count > )

            {

                double sheetCount = Math.Ceiling((double)objList.Count / listCount);

                for (int i = ; i < sheetCount; i++)

                {

                    ISheet sheet = null;

                    sheet = book.CreateSheet("sheet" + i);

                    sheet.DefaultRowHeight =  * ;

                    List<T> list = new List<T>();

                    list = objList.Skip<T>(listCount * i).Take<T>(listCount).ToList();

                    int rowIndex = ;

                    int StartColIndex = ;

                    int colIndex = StartColIndex;

                    //创建表头样式

                    ICellStyle style = book.CreateCellStyle();

                    style.Alignment = HorizontalAlignment.CENTER;

                    style.WrapText = true;

                    IFont font = book.CreateFont();

                    font.FontHeightInPoints = ;

                    font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;

                    font.FontName = "简体中文";

                    style.SetFont(font);//HEAD 样式

                    Type myType = null;

                    myType = objList[].GetType();

                    //根据反射从传递进来的属性名信息得到要显示的属性

                    List<PropertyInfo> myPro = new List<PropertyInfo>();

                    PropertyInfo[] properties = myType.GetProperties();

                    #region 定义表头

                    int m = ;

                    if (columnInfo != null)

                    {

                        var rowheader = sheet.CreateRow();

                        rowheader.Height = rowheader.Height =  * ;

                        foreach (string cName in columnInfo.Keys)

                        {

                            PropertyInfo p = myType.GetProperty(cName);

                            if (p != null)

                            {

                                myPro.Add(p);

                                rowheader.CreateCell(m).SetCellValue(columnInfo[cName]);

                                m++;

                            }

                        }

                    }

                    #endregion

                    #region 定义表体并赋值

                    //如果没有找到可用的属性则结束

                    if (myPro.Count == ) { return bResult; }

                    foreach (T obj in list)

                    {

                        int n = ;

                        if (sheet != null)

                        {

                            rowIndex++;

                            var sheetrow = sheet.CreateRow(rowIndex);

                            sheetrow.Height = sheetrow.Height =  * ;

                            foreach (PropertyInfo p in myPro)

                            {

                                dynamic val = p.GetValue(obj, null) ?? "";

                                string valtype = val.GetType().ToString();

                                if (valtype.ToLower().IndexOf("decimal", StringComparison.Ordinal) > -)

                                {

                                    val = Convert.ToDouble(val);

                                }

                                else if (valtype.ToLower().IndexOf("datetime", StringComparison.Ordinal) > -)

                                {

                                    val = val.ToString("yyyy-MM-dd HH:mm:ss");

                                    if (val.Equals("0001-01-01 00:00:00"))

                                    {

                                        val = "";

                                    }

                                }

                                sheetrow.CreateCell(n).SetCellValue(val);

                                n++;

                            }

                        }

                    }

                    #endregion

                }

            }

            else

            {

                //在工作薄中建立工作表

                XSSFSheet sheet = book.CreateSheet() as XSSFSheet;

                sheet.SetColumnWidth(,  * );

                if (sheet != null) sheet.CreateRow().CreateCell().SetCellValue("暂无数据！");

            }

          var  fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);

            book.Write(fs);

            //try

            //{

            //    HttpResponse rs = System.Web.HttpContext.Current.Response;

            //    rs.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");

            //    rs.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);

            //    rs.ContentType = "application/vnd.ms-excel";

            //    //application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

            //    using (MemoryStream ms = new MemoryStream())

            //    {

            //        book.Write(ms);

            //        btyBytes = ms.GetBuffer();

            //        rs.AddHeader("Content-Length", btyBytes.Length.ToString());

            //        rs.BinaryWrite(ms.GetBuffer());

            //        ms.Flush();

            //    }

            //}

            //catch (SystemException ex)

            //{

            //    throw ex;

            //}

            //catch (ApplicationException ex)

            //{

            //    throw ex;

            //}

            return bResult;

        }

        #endregion

    }

}