NOPI导出Excel 自定义列名

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

NOPI 做Excel 导出确实很方便，但是一直在用没好好研究。

在网上没找到自定义Columns的方法，于是乎自己就在原来的方法上简单地改改。

想用的童鞋们可以直接拿去用！

         /// 数据大于65536时使用

         /// </summary>

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

         /// <param name="Columns">列名</param>

         /// <returns></returns>

         public static byte[] ExportToExcel_Columns(DataTable dt, ArrayList Columns)

         {

             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_Columns(hssfworkbook, sheetList, dt, Columns);

             MemoryStream file = new MemoryStream();

             hssfworkbook.Write(file);

             file.Close();

             return file.ToArray();

         }

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

         {

             // DataColumnCollection str = dt.Columns; //定义表头，原来是从datatable中获取的columns

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

             {

                 ISheet sheet1 = sheetCollection[i];

                 if (i == )

                 {

                     IRow headerRow = sheet1.CreateRow();

                     for (int m = , len = Columns.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(Columns[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());

                     }

                 }

             }

         }

程序调用：

         /// <summary>

         ///xxxx数据导出

         /// </summary>

         /// <param name="flg"></param>

         /// <returns></returns>

         public FileResult ExportExcel_ProfitDetails()

         {

             DataSet ds = new DataSet();

             //获取当前操作用户

             string userRealName = "";

             if (!string.IsNullOrEmpty(Session["user"].ToString()))

             {

                 userRealName = Session["user"].ToString();

             }

             tempLog.Info(string.Format("用户：{0}正在做xxxx数据导出操作", userRealName));

             string date = Request["datetime"].ToString();

             ds = Automation.Common.DbHelperSQL.Query("ColligateExport");

             //创建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

             {

                 string[] a = new string[] { "ID", "应支付金额", "应支付日期", "支付期数","支付状态","公司收入","公司支出"

                   ,"支付类型","工单ID","收益明细ID", "转让债权价值","还款金额","还款日期","实际支付金额","实际本金","实际利息"};

                 ArrayList alist = new ArrayList();

                 alist.AddRange(a);

                 fileArr =  NPOIExcelHelper.ExportToExcel_Columns(ds.Tables[],alist);

             }

             catch (Exception ex)

             {

                 throw new Exception(ex.Message);

             }

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

             tempLog.Info(string.Format("导出xxxx数据的Excel文件名为：{0}", fileName));

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

         }