XSSFWorkbook：突破HSSFWorkbook的65535行局限

使用NPOI.XSSF导出，话不多说 直接上代码

导出封装方法，传入datatable数据集

/// <summary>
/// 大数据量
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static MemoryStream ExportDatasetToExcelXSSF(DataTable dt)
{try{//文件流对象MemoryStream stream = new MemoryStream();//打开Excel对象XSSFWorkbook workbook = new XSSFWorkbook();//Excel的Sheet对象NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1");//set date formatXSSFCellStyle cellStyleDate = (XSSFCellStyle)workbook.CreateCellStyle();//IDataFormat format = workbook.CreateDataFormat();//cellStyleDate.DataFormat = format.GetFormat("yyyy年m月d日");cellStyleDate.BorderBottom = BorderStyle.Thin;cellStyleDate.BorderLeft = BorderStyle.Thin;cellStyleDate.BorderRight = BorderStyle.Thin;cellStyleDate.BorderTop = BorderStyle.Thin;//使用NPOI操作Excel表NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);int count = 0;for (int i = 0; i < dt.Columns.Count; i++) //生成sheet第一行列名 {sheet.SetColumnWidth(i, dt.Columns[i].ColumnName.Length * 4 * 256);NPOI.SS.UserModel.ICell cell = row.CreateCell(count++);cell.SetCellValue(dt.Columns[i].ColumnName);cell.CellStyle = cellStyleDate;}//将数据导入到excel表中for (int i = 0; i < dt.Rows.Count; i++){NPOI.SS.UserModel.IRow rows = sheet.CreateRow(i + 1);count = 0;for (int j = 0; j < dt.Columns.Count; j++){NPOI.SS.UserModel.ICell cell = rows.CreateCell(count++);cell.CellStyle = cellStyleDate;Type type = dt.Rows[i][j].GetType();if (type == typeof(int) || type == typeof(Int16)|| type == typeof(Int32) || type == typeof(Int64)){cell.SetCellValue((int)dt.Rows[i][j]);}else{if (type == typeof(float) || type == typeof(double) || type == typeof(Double)){cell.SetCellValue((Double)dt.Rows[i][j]);}else{if (type == typeof(DateTime)){cell.SetCellValue(((DateTime)dt.Rows[i][j]).ToString("yyyy-MM-dd HH:mm"));}else{if (type == typeof(bool) || type == typeof(Boolean)){cell.SetCellValue((bool)dt.Rows[i][j]);}else{cell.SetCellValue(dt.Rows[i][j].ToString());}}}}}}//保存excel文档sheet.ForceFormulaRecalculation = true;workbook.Write(stream);return stream;}catch{return new MemoryStream();}
}

下载

/// <summary>
/// 导出Excle
/// </summary>
public void DownExcel()
{string BrowserType = Request.Browser.Type;string fileName = "数据导出" + DateTime.Now.ToString("yyMMddHHmmss") + ".xls";MemoryStream ms = InfoDownExcel();byte[] bytesInStream = ms.ToArray();ms.Close();Response.Clear();Response.Buffer = true;Response.Charset = "GB2312";if (BrowserType.Contains("Firefox")){Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);}else{Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8));}Response.ContentType = "application/ms-excel";Response.ContentEncoding = Encoding.GetEncoding("GB2312");Response.BinaryWrite(bytesInStream);Response.End();
}/// <summary>
/// 信息表格导出
/// </summary>
/// <returns></returns>
public MemoryStream InfoDownExcel()
{string queryJson = Request["queryJson"];DataSet ds = App.GetList(queryJson);DataTable dt = new DataTable();//定义行dt.Columns.Add("编号");dt.Columns.Add("名称");DataRow dr;for (int i = 0; i < ds.Tables[0].Rows.Count; i++){dr = dt.NewRow();dr[0] = ds.Tables[0].Rows[i]["code"];dr[1] = ds.Tables[0].Rows[i]["name"];dt.Rows.Add(dr);}MemoryStream ms = ExportTool.ExportDatasetToExcelXSSF(dt);return ms;
}