使用 POI导出到Excel

//字体背景颜色 边框的设置
http://blog.csdn.net/sinat_34093604/article/details/53432545

http://blog.csdn.net/hantiannan/article/details/5312133

private String productEcel(List<BiUserAction> list) throws Exception {

		// 创建工作表和标题
		HSSFWorkbook workbook = null;
		try {
			workbook = new HSSFWorkbook();
		} catch (Exception e) {
			e.printStackTrace();
		}
		int rowIndex=0;
		HSSFSheet sheet = workbook.createSheet("贝壳奖励统计"); // 创建工作区
		/*
		HSSFRow row_title = sheet.createRow(0); // 创建一行引用对象
		HSSFFont title_font = workbook.createFont(); // 创建标题的字体

		title_font.setFontHeightInPoints((short) 8);
		title_font.setFontHeight((short) HSSFFont.BOLDWEIGHT_NORMAL);
		title_font.setColor((short) (HSSFFont.COLOR_NORMAL));

		HSSFCellStyle title_style = workbook.createCellStyle();// 创建样式
		title_style.setFont(title_font);
		title_style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

		//合并 第一个的  0 到 3  的单元格
		Region region=new Region(0,(short)0,0,(short)3);
		sheet.addMergedRegion(region);

		HSSFCell cell_title = row_title.createCell((short) 0);// 创建单元格引用对象
		cell_title.setCellStyle(title_style);
		cell_title.setCellValue("贝壳奖励统计");*/

		// 创建数据表头
		String titles[] = { "序号", "奖励项目", "贝壳总数", "经验总数"};


		//定义字体
		HSSFFont celltbnamefont = workbook.createFont();
		celltbnamefont.setFontHeightInPoints((short) 12);  //字体大小
		celltbnamefont.setColor((short) (HSSFFont.COLOR_NORMAL));  //颜色
		celltbnamefont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //粗体

		//定义列的样式 
		HSSFCellStyle items_style = workbook.createCellStyle();
		items_style.setAlignment((short) HSSFCellStyle.ALIGN_CENTER); //设置对其方式
		items_style.setFont(celltbnamefont);
		items_style.setWrapText(true); //设置自动换行

		HSSFRow row = sheet.createRow((short) rowIndex);

		//加入 标题
		for (int i = 0; i < titles.length; i++) {
			HSSFCell cell = row.createCell(i,Cell.CELL_TYPE_STRING); //设置 列类型
			if (i == 1 || i == 3 || i == 2 || i == 5) {
				sheet.setColumnWidth(i, 5335);
			}else{
				//sheet.setColumnWidth((short) i, (short) 3335);
				sheet.setColumnWidth(i, 3335);
			}
			cell.setCellValue(titles[i]);
			cell.setCellStyle(items_style);
		}

		//定义 date 的数据样式
		HSSFCellStyle datestyle = workbook.createCellStyle();
		HSSFDataFormat df = workbook.createDataFormat();
		datestyle.setAlignment((short) HSSFCellStyle.ALIGN_LEFT);
		datestyle.setDataFormat(df.getFormat("yyyy-mm-dd hh:mm:ss"));

		//定义 int 的数据样式
		HSSFCellStyle intdatestyle = workbook.createCellStyle();
		intdatestyle.setAlignment((short) HSSFCellStyle.ALIGN_LEFT);

		//定义 float 的数据样式
		HSSFCellStyle floatdatestyle = workbook.createCellStyle();
		floatdatestyle.setAlignment((short) HSSFCellStyle.ALIGN_LEFT);
		df = workbook.createDataFormat();
		floatdatestyle.setDataFormat(df.getFormat("#.##"));

		//定义 long 的数据样式
		HSSFCellStyle longdatestyle = workbook.createCellStyle();
		longdatestyle.setAlignment((short) HSSFCellStyle.ALIGN_LEFT);


		HSSFCell cell;
		Iterator<BiUserAction> it =list.iterator();
		while (it.hasNext()) {
			HSSFRow dataRow = sheet.createRow((short) (++rowIndex));
			BiUserAction userAction = it.next();

			cell = dataRow.createCell(0,Cell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(intdatestyle);
			cell.setCellValue(rowIndex);

			cell = dataRow.createCell(1,Cell.CELL_TYPE_STRING);
			String ruleName = userAction.getRuleName();
			cell.setCellValue(ruleName);

			cell = dataRow.createCell(2,Cell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(longdatestyle);
			cell.setCellValue(userAction.getCredits());

			cell = dataRow.createCell(3,Cell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(longdatestyle);
			cell.setCellValue(userAction.getExperience());
		}
		HSSFRow dataRow = sheet.createRow((short) (++rowIndex));
		cell = dataRow.createCell(0,Cell.CELL_TYPE_STRING);
		cell.setCellStyle(intdatestyle);
		cell.setCellValue("合计:");

		cell = dataRow.createCell(1,Cell.CELL_TYPE_STRING);
		cell.setCellValue("");

		//添加 公式
		cell = dataRow.createCell(2,Cell.CELL_TYPE_NUMERIC);
		cell.setCellStyle(longdatestyle);
		cell.setCellFormula("SUM(" + getColLetter(2) + sheet.getRow(2).getCell(2).getRowIndex() + ":" + getColLetter(2) +sheet.getRow(sheet.getLastRowNum()).getCell(2).getRowIndex() + ")");
		//添加 公式
		cell = dataRow.createCell(3,Cell.CELL_TYPE_NUMERIC);
		cell.setCellStyle(longdatestyle);
		cell.setCellFormula("SUM(" + getColLetter(3) + sheet.getRow(2).getCell(3).getRowIndex() + ":" + getColLetter(3) +sheet.getRow(sheet.getLastRowNum()).getCell(3).getRowIndex() + ")");


		String workDir = this.getServlet().getServletContext().getRealPath("/");

		String workddd = workDir.replaceAll("\\\\", "/");

		Calendar calendar = Calendar.getInstance();
		String month = calendar.get(Calendar.YEAR) + "/"+ (calendar.get(Calendar.MONTH) + 1);
		String filePath = "newsxls/adxls/" + month + "/";
		File f = new File(workDir + filePath);
		if (!f.isDirectory()) {
			f.mkdirs();
		}

		String fileNameCode = java.util.UUID.randomUUID().toString();
		String completeFilePath = workddd + filePath + fileNameCode + ".xls";
		FileOutputStream fileOut = new FileOutputStream(completeFilePath);
		this.getRequest().getSession(true).setAttribute("completeFilePath",
				completeFilePath);

		workbook.write(fileOut);
		fileOut.flush();
		fileOut.close();
		return completeFilePath;
	}

	 /**
	  * 将列的索引换算成ABCD字母，这个方法要在插入公式时用到。
	  * @param colIndex 列索引。
	  * @return ABCD字母。
	  */
	 private String getColLetter(int colIndex)
	 {
	  String ch = "";
	     if (colIndex  < 26)
	         ch = "" + (char)((colIndex) + 65);
	     else
	        ch = "" + (char)((colIndex) / 26 + 65 - 1) + (char)((colIndex) % 26 + 65);
	     return ch;
	 }