NOPI导出Excel 自定义列名
NOPI 做Excel 导出确实很方便 ，但是一直在用没好好研究。
在网上没找到自定义Columns的方法 ，于是乎自己就在原来的方法上简单地改改。
想用的童鞋们可以直接拿去用！
1 /// 数据大于65536时使用 2 /// </summary> 3 /// <param name="dt">数据源</param> 4 /// <param name="Columns">列名</param> 5 /// <returns></returns> 6 public static byte[] ExportToExcel_Columns(DataTable dt, ArrayList Columns) 7 { 8 DataColumnCollection str = dt.Columns; 9 if (str.Count == 0) return null; 10 HSSFWorkbook hssfworkbook; 11 hssfworkbook = new HSSFWorkbook(); 12 ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1"); 13 List<NPOI.SS.UserModel.ISheet> sheetList = new List<NPOI.SS.UserModel.ISheet>(); 14 sheetList.Add(sheet1); 15 16 int rows = dt.Rows.Count + 1; 17 int p = rows % 65535 == 0 ? rows / 65535 : (rows / 65535) + 1; 18 for (int i = 1; i < p; i++) 19 { 20 ISheet sheet = hssfworkbook.CreateSheet("sheet" + (i + 1).ToString()); 21 sheetList.Add(sheet); 22 } 23 LargeDataExport_Columns(hssfworkbook, sheetList, dt, Columns); 24 MemoryStream file = new MemoryStream(); 25 hssfworkbook.Write(file); 26 file.Close(); 27 return file.ToArray(); 28 } 29 30 private static void LargeDataExport_Columns(NPOI.HSSF.UserModel.HSSFWorkbook hssfworkbook, List<NPOI.SS.UserModel.ISheet> sheetCollection, DataTable dt, ArrayList Columns) 31 { 32 // DataColumnCollection str = dt.Columns; //定义表头，原来是从datatable中获取的columns 33 34 for (int i = 0; i < sheetCollection.Count; i++) 35 { 36 ISheet sheet1 = sheetCollection[i]; 37 38 if (i == 0) 39 { 40 IRow headerRow = sheet1.CreateRow(0); 41 for (int m = 0, len = Columns.Count; m < len; m++) 42 { 43 ICell curCell = headerRow.CreateCell(m); 44 headerRow.Height = 150 * 3; 45 ICellStyle style = hssfworkbook.CreateCellStyle(); 46 style.FillPattern = FillPattern.SolidForeground; 47 style.FillForegroundColor = HSSFColor.Grey25Percent.LightOrange.Index; 48 IFont font = hssfworkbook.CreateFont(); 49 font.FontHeightInPoints = 10; 50 font.Color = HSSFColor.White.Index; //HSSFColor.WHITE.index; 51 style.SetFont(font); 52 curCell.CellStyle = style; 53 curCell.SetCellValue(Columns[m].ToString()); 54 sheet1.SetColumnWidth(m, 400 * 10); 55 } 56 } 57 58 for (int j = i * 65535; j < (i + 1) * 65535; j++) 59 { 60 if (j > dt.Rows.Count - 1) 61 break; 62 IRow row = sheet1.CreateRow(j - 65535 * i + 1); 63 row.Height = 120 * 3; 64 65 for (int k = 0; k < dt.Columns.Count; k++) 66 { 67 ICell rowCell = row.CreateCell(k); 68 rowCell.SetCellValue(dt.Rows[j][k].ToString()); 69 } 70 } 71 } 72 }
程序调用：
1 /// <summary> 2 ///xxxx数据导出 3 /// </summary> 4 /// <param name="flg"></param> 5 /// <returns></returns> 6 public FileResult ExportExcel_ProfitDetails() 7 { 8 DataSet ds = new DataSet(); 9 //获取当前操作用户 10 string userRealName = ""; 11 if (!string.IsNullOrEmpty(Session["user"].ToString())) 12 { 13 userRealName = Session["user"].ToString(); 14 } 15 16 tempLog.Info(string.Format("用户：{0}正在做xxxx数据导出操作", userRealName)); 17 string date = Request["datetime"].ToString(); 18 ds = Automation.Common.DbHelperSQL.Query("ColligateExport"); 19 //创建Excel文件的对象 20 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); 21 System.IO.MemoryStream ms = new System.IO.MemoryStream(); 22 23 NPOIExcelHelper n = new NPOIExcelHelper(); 24 byte[] fileArr = null; 25 try 26 { 27 string[] a = new string[] { "ID", "应支付金额", "应支付日期", "支付期数","支付状态","公司收入","公司支出" 28 ,"支付类型","工单ID","收益明细ID", "转让债权价值","还款金额","还款日期","实际支付金额","实际本金","实际利息"}; 29 ArrayList alist = new ArrayList(); 30 alist.AddRange(a); 31 fileArr = NPOIExcelHelper.ExportToExcel_Columns(ds.Tables[0],alist); 32 } 33 catch (Exception ex) 34 { 35 throw new Exception(ex.Message); 36 } 37 string fileName = "xxxx数据" + System.DateTime.Now.ToString("yyyyMMddhhssmm"); 38 tempLog.Info(string.Format("导出xxxx数据的Excel文件名为：{0}", fileName)); 39 return File(fileArr, "application/vnd.ms-excel", fileName + ".xls"); 40 }
最后：使用时记得加上NOPI 引用哦！
