npoi导出超过65000行excel分sheet页。

 直接上代码，引用对应的dll，就好啦，这边写在是在baseController 里，判断的是6W行换Sheet，逻辑很简单就不细说了， 

        public virtual FileResult NpoiExcel(DataTable dt, string title)        {            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();            int SheetNum = 1; int tempIndex = 1; //标示             NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet" + SheetNum);            NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0);            ICellStyle style = book.CreateCellStyle();            style.Alignment = HorizontalAlignment.CENTER;            style.VerticalAlignment = VerticalAlignment.CENTER;            for (int i = 0; i < dt.Columns.Count; i++)            {                ICell cell = headerrow.CreateCell(i);                cell.CellStyle = style;                cell.SetCellValue(dt.Columns[i].ColumnName);            }            //数据              for (int i = 0; i < dt.Rows.Count; i++)            {                IRow row1 = sheet.CreateRow(tempIndex);                for (int j = 0; j < dt.Columns.Count; j++)                {                    ICell cell = row1.CreateCell(j);                    cell.SetCellValue(dt.Rows[i][j].ToString());                }                if (tempIndex == 60000)                {                    SheetNum++;                    sheet = book.CreateSheet("Sheet" + SheetNum);//                    tempIndex = 0;                    headerrow = sheet.CreateRow(0);                    style = book.CreateCellStyle();                    style.Alignment = HorizontalAlignment.CENTER;                    style.VerticalAlignment = VerticalAlignment.CENTER;                    for (int m = 0; m < dt.Columns.Count; m++)                    {                        ICell cell = headerrow.CreateCell(m);                        cell.CellStyle = style;                        cell.SetCellValue(dt.Columns[m].ColumnName);                    }                }                tempIndex++;            }            MemoryStream ms = new MemoryStream();            book.Write(ms);            ms.Seek(0, SeekOrigin.Begin);            return File(ms, "application/vnd.ms-excel", string.Format("{0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));        }

顺便在贴个读取。

        /// <summary>        /// 读取Excel文件到DataSet中        /// </summary>        /// <param name="filePath">文件路径</param>        /// <returns></returns>        public virtual DataSet ExcelToDataSet(string filePath)        {            string connStr = "";            string fileType = System.IO.Path.GetExtension(filePath);            if (string.IsNullOrEmpty(fileType)) return null;            filePath = Server.MapPath("~" + filePath);            if (fileType == ".xls")                connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";            else                connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";            string sql_F = "Select * FROM [{0}]";            OleDbConnection conn = null;            OleDbDataAdapter da = null;            DataTable dtSheetName = null;            DataSet ds = new DataSet();            try            {                // 初始化连接，并打开                conn = new OleDbConnection(connStr);                conn.Open();                // 获取数据源的表定义元数据                                        string SheetName = "";                dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });                // 初始化适配器                da = new OleDbDataAdapter();                for (int i = 0; i < dtSheetName.Rows.Count; i++)                {                    SheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"];                    if (SheetName.Contains("$") && !SheetName.Replace("'", "").EndsWith("$"))                    {                        continue;                    }                    da.SelectCommand = new OleDbCommand(String.Format(sql_F, SheetName), conn);                    DataSet dsItem = new DataSet();                    da.Fill(dsItem, SheetName);                    ds.Tables.Add(dsItem.Tables[0].Copy());                }            }            catch (Exception ex)            {                Log(ex);            }            finally            {                // 关闭连接                if (conn.State == ConnectionState.Open)                {                    conn.Close();                    da.Dispose();                    conn.Dispose();                }            }            return ds;        }