NOPI导出Excel

 NOPI导出Excel

 　　　　/// <summary>

         /// 导出的方法 Excel样式

         /// </summary>

         /// <param name="ds"></param>

         /// <returns></returns>

         public static byte[] ExportToExcelMultipleSheet(DataSet ds)

         {

             HSSFWorkbook hssfworkbook;

             hssfworkbook = new HSSFWorkbook();

             ISheet sheet1 = hssfworkbook.CreateSheet(ds.Tables[].TableName);

             List<NPOI.SS.UserModel.ISheet> sheetList = new List<NPOI.SS.UserModel.ISheet>();

             sheetList.Add(sheet1);

             for (int i = ; i < ds.Tables.Count; i++)

             {

                 ISheet sheet = hssfworkbook.CreateSheet(ds.Tables[i].TableName);

                 sheetList.Add(sheet);

             }

             LargeDataExportMultipleSheet(hssfworkbook, sheetList, ds);

             MemoryStream file = new MemoryStream();

             hssfworkbook.Write(file);

             file.Close();

             return file.ToArray();

         }

         /// <summary>

         /// 数据大于65536时使用

         /// </summary>

         /// <param name="dt"></param>

         /// <returns></returns>

         /// <summary>

         /// 数据大于65536时使用

         /// </summary>

         /// <param name="dt"></param>

         /// <returns></returns>

         public static byte[] ExportToExcel(DataTable dt)

         {

             DataColumnCollection str = dt.Columns;

             if (str.Count == ) return null;

             HSSFWorkbook hssfworkbook;

             hssfworkbook = new HSSFWorkbook();

             ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

             List<NPOI.SS.UserModel.ISheet> sheetList = new List<NPOI.SS.UserModel.ISheet>();

             sheetList.Add(sheet1);

             int rows = dt.Rows.Count + ;

             int p = rows %  ==  ? rows /  : (rows / ) + ;

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

             {

                 ISheet sheet = hssfworkbook.CreateSheet("sheet" + (i + ).ToString());

                 sheetList.Add(sheet);

             }

             LargeDataExport(hssfworkbook, sheetList, dt);

             MemoryStream file = new MemoryStream();

             hssfworkbook.Write(file);

             file.Close();

             return file.ToArray();

         }

         private static void LargeDataExport(NPOI.HSSF.UserModel.HSSFWorkbook hssfworkbook, List<NPOI.SS.UserModel.ISheet> sheetCollection, DataTable dt)

         {

             DataColumnCollection str = dt.Columns;

             for (int i = ; i < sheetCollection.Count; i++)

             {

                 ISheet sheet1 = sheetCollection[i];

                 if (i == )

                 {

                     IRow headerRow = sheet1.CreateRow();

                     for (int m = , len = str.Count; m < len; m++)

                     {

                         ICell curCell = headerRow.CreateCell(m);

                         headerRow.Height =  * ;

                         ICellStyle style = hssfworkbook.CreateCellStyle();

                         style.FillPattern = FillPattern.SolidForeground;

                         style.FillForegroundColor = HSSFColor.Grey25Percent.LightOrange.Index;

                         IFont font = hssfworkbook.CreateFont();

                         font.FontHeightInPoints = ;

                         font.Color = HSSFColor.White.Index; //HSSFColor.WHITE.index;

                         style.SetFont(font);

                         curCell.CellStyle = style;

                         curCell.SetCellValue(str[m].ToString());

                         sheet1.SetColumnWidth(m,  * );

                     }

                 }

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

                 {

                     if (j > dt.Rows.Count - )

                         break;

                     IRow row = sheet1.CreateRow(j -  * i + );

                     row.Height =  * ;

                     for (int k = ; k < dt.Columns.Count; k++)

                     {

                         ICell rowCell = row.CreateCell(k);

                         rowCell.SetCellValue(dt.Rows[j][k].ToString());

                     }

                 }

             }

         }

  =====================

113  调用方法：

 　　    /// <summary>

         /// 将数据导出到Excel中

         /// </summary>

         /// <returns></returns>

         public FileResult TXDebtExportExcel()

         {

          DataTable dt=new DataTable();

         dt =数据源；

   　　　 //创建Excel文件的对象

             NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();

             System.IO.MemoryStream ms = new System.IO.MemoryStream();

             NPOIExcelHelper n = new NPOIExcelHelper();

             byte[] fileArr = null;

             try

             {

                 fileArr = NPOIExcelHelper.ExportToExcel(dt);  //括号内的参数为数据源

             }

             catch (Exception ex)

             {

                 tempLog.Info(string.Format("用户:{0} 导出 数据 出现异常：{1}", userRealName, ex.Message));

             }

             string fileName = "数据" + System.DateTime.Now.ToString("yyyyMMddhhssmm");

             tempLog.Info(string.Format("用户:{0}导出数据成功!", userRealName));  //记录日志

             return File(fileArr, "application/vnd.ms-excel", fileName + ".xls");

         }

NOPI 下载地址 http://npoi.codeplex.com/releases/