java HSSFWorkbook生成excel

public class CwFundsChangeExportExcel {

	private  HSSFWorkbook wb = new HSSFWorkbook();

	private  HSSFSheet sheet = wb.createSheet();
	
	public static void main(String[] args) {}
	
	/**
	 * 
	 * @param wb
	 * @param type
	 * 			1:head 2 je
	 * @return
	 */
	public HSSFCellStyle setCellStyleHead(HSSFWorkbook wb,int type,int statu)
	{
		HSSFCellStyle cellStyle = wb.createCellStyle();
		// 创建单元格样式
		if(type != 2)
		{
			// 指定单元格居中对齐
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			// 指定单元格垂直居中对齐
			cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		}
		else
		{
			// 指定单元格居中对齐
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
			// 指定单元格垂直居中对齐
			cellStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT);
		}
		
		//设置单元格边框和颜色
		cellStyle.setBorderBottom((short)1);  
		cellStyle.setBorderLeft((short)1);  
		cellStyle.setBorderRight((short)1);  
		cellStyle.setBorderTop((short)1);
		HSSFFont font = wb.createFont();
		if(type == 1)
		{
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		}
		if(statu == 2)
		{
			font.setColor(HSSFColor.RED.index);
		}
		font.setFontName("宋体");
		cellStyle.setFont(font);
		//字体大小
		//font.setFontHeight((short) 200);
		cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
		
		// 指定当单元格内容显示不下时自动换行
		cellStyle.setWrapText(true);
		
		return cellStyle;
	}
	

	@SuppressWarnings("unchecked")
	public void createExcel(Map<String,String> headMap,List<Map<String,Object>> valueList,String url) {

		ExportExcel exportExcel = new ExportExcel(wb, sheet);

		// 设置列头
		HSSFRow row0 = sheet.createRow(0);
		
		//一列
		HSSFCell cell_00 = row0.createCell((short)0);
		//列头样式
		cell_00.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_00.setCellValue(new HSSFRichTextString("银行"));
		
		//二列
		HSSFCell cell_01 = row0.createCell((short)1);
		//列头样式
		cell_01.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_01.setCellValue(new HSSFRichTextString("期初"));
		
		//三列
		HSSFCell cell_02 = row0.createCell((short)2);
		//列头样式
		cell_02.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_02.setCellValue(new HSSFRichTextString());
		
		//四列
		HSSFCell cell_03 = row0.createCell((short)3);
		//列头样式
		cell_03.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_03.setCellValue(new HSSFRichTextString("收入"));
		
		//五
		HSSFCell cell_04 = row0.createCell((short)4);
		//列头样式
		cell_04.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_04.setCellValue(new HSSFRichTextString());
		
		//六
		HSSFCell cell_05 = row0.createCell((short)5);
		//列头样式
		cell_05.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_05.setCellValue(new HSSFRichTextString());
		
		//七
		HSSFCell cell_06 = row0.createCell((short)6);
		//列头样式
		cell_06.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_06.setCellValue(new HSSFRichTextString());
		
		//八
		HSSFCell cell_07 = row0.createCell((short)7);
		//列头样式
		cell_07.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_07.setCellValue(new HSSFRichTextString("支出"));
		
		//九
		HSSFCell cell_08 = row0.createCell((short)8);
		//列头样式
		cell_08.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_08.setCellValue(new HSSFRichTextString());
		
		//十
		HSSFCell cell_09 = row0.createCell((short)9);
		//列头样式
		cell_09.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_09.setCellValue(new HSSFRichTextString());
		
		//十一
		HSSFCell cell_010 = row0.createCell((short)10);
		//列头样式
		cell_010.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_010.setCellValue(new HSSFRichTextString());
		
		//十二
		HSSFCell cell_011 = row0.createCell((short)11);
		//列头样式
		cell_011.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_011.setCellValue(new HSSFRichTextString("转款"));
		
		//十三
		HSSFCell cell_012 = row0.createCell((short)12);
		//列头样式
		cell_012.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_012.setCellValue(new HSSFRichTextString("期末"));
		
		//十四
		HSSFCell cell_013 = row0.createCell((short)13);
		//列头样式
		cell_013.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_013.setCellValue(new HSSFRichTextString());
		
		/** 列头 第二行 */
		// 设置列头
		HSSFRow row1 = sheet.createRow(1);
		
		//一列
		HSSFCell cell_10 = row1.createCell((short)0);
		//列头样式
		cell_10.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_10.setCellValue(new HSSFRichTextString());
		
		//二列
		HSSFCell cell_11 = row1.createCell((short)1);
		//列头样式
		cell_11.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_11.setCellValue(new HSSFRichTextString("活期"));
		
		//三列
		HSSFCell cell_12 = row1.createCell((short)2);
		//列头样式
		cell_12.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_12.setCellValue(new HSSFRichTextString("保证金"));
		
		//四列
		HSSFCell cell_13 = row1.createCell((short)3);
		//列头样式
		cell_13.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_13.setCellValue(new HSSFRichTextString("托收/贴现"));
		
		//五
		HSSFCell cell_14 = row1.createCell((short)4);
		//列头样式
		cell_14.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_14.setCellValue(new HSSFRichTextString("往来"));
		
		//六
		HSSFCell cell_15 = row1.createCell((short)5);
		//列头样式
		cell_15.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_15.setCellValue(new HSSFRichTextString("其他"));
		
		//七
		HSSFCell cell_16 = row1.createCell((short)6);
		//列头样式
		cell_16.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_16.setCellValue(new HSSFRichTextString("收入合计"));
		
		//八
		HSSFCell cell_17 = row1.createCell((short)7);
		//列头样式
		cell_17.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_17.setCellValue(new HSSFRichTextString("兑付"));
		
		//九
		HSSFCell cell_18 = row1.createCell((short)8);
		//列头样式
		cell_18.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_18.setCellValue(new HSSFRichTextString("往来"));
		
		//十
		HSSFCell cell_19 = row1.createCell((short)9);
		//列头样式
		cell_19.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_19.setCellValue(new HSSFRichTextString("其他"));
		
		//十一
		HSSFCell cell_110 = row1.createCell((short)10);
		//列头样式
		cell_110.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_110.setCellValue(new HSSFRichTextString("支出合计"));
		
		//十二
		HSSFCell cell_111 = row1.createCell((short)11);
		//列头样式
		cell_111.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_111.setCellValue(new HSSFRichTextString());
		
		//十三
		HSSFCell cell_112 = row1.createCell((short)12);
		//列头样式
		cell_112.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_112.setCellValue(new HSSFRichTextString("活期"));
		
		//十四
		HSSFCell cell_113 = row1.createCell((short)13);
		//列头样式
		cell_113.setCellStyle(setCellStyleHead(wb,1,0));
		//VALUE
		cell_113.setCellValue(new HSSFRichTextString("保证金"));
		
		//银行名称 合并单元格
		sheet.addMergedRegion(new Region(0, (short)0, 1, (short) 0));
		//期初合并单元格
		sheet.addMergedRegion(new Region(0, (short)1, 0, (short) 2));
		//收入合并单元格
		sheet.addMergedRegion(new Region(0, (short)3, 0, (short) 6));
		//支出合并单元格
		sheet.addMergedRegion(new Region(0, (short)7, 0, (short) 10));
		//转款合并单元格
		sheet.addMergedRegion(new Region(0, (short)11, 1, (short) 11));
		//期末合并单元格
		sheet.addMergedRegion(new Region(0, (short)12, 0, (short) 13));
		

		//遍历value值
		for (int i = 0 ; i < valueList.size(); i++)
		{
			HSSFRow _row = sheet.createRow(i+2);
			Map<String,Object> valMap = valueList.get(i);
			
			Iterator itHead = headMap.keySet().iterator();
			String head = "";
			int j = 0;
			while(itHead.hasNext())
			{
				head = itHead.next() + "";
				//日期类型
				if(head.indexOf("dt") != -1)
				{
					HSSFCell cell12 = _row.createCell((short) j);   
			        cell12.setCellValue(new HSSFRichTextString(valMap.get(head)+""));   
			        cell12.setCellStyle(setCellStyleHead(wb,3,0));  
				}
				else if(head.indexOf("sl") != -1)
				{
					HSSFCell cell12 = _row.createCell((short) j);   
					int statu = 0;
					if(null == valMap.get(head) || "".equals(valMap.get(head)+""))
			        {
			        	cell12.setCellValue(new HSSFRichTextString());
			        }
					else
					{
						if((valMap.get(head)+"").indexOf("-")!=-1)
						{
							statu = 2;
						}
						cell12.setCellValue(new HSSFRichTextString(DataConvertUtils.formatNumber(valMap.get(head)+"",2)));
					}
					
			        cell12.setCellStyle(setCellStyleHead(wb,2,statu)); 
				}
				else if(head.indexOf("je") != -1)
				{
					// 金额格式化   
			        HSSFCell cell12 = _row.createCell((short) j);
			        int statu = 0;
			        
			        BigDecimal je = new BigDecimal(valMap.get(head)+"");
			        
			        //最后一行合计 不用处理
			        if(i != valueList.size()-1)
			        {
			        	if(head.equals("tsjes_je") || head.equals("wljes_je") || head.equals("otherjes_je")
			        			|| head.equals("sumjes_je") || head.equals("cdjez_je") || head.equals("wljez_je") 
			        			|| head.equals("otherjez_je") || head.equals("sumjez_je") || head.equals("sumzk_je"))
			        	{
				        	if(je.compareTo(new BigDecimal("0")) == 0)
					        {
					        	cell12.setCellValue(new HSSFRichTextString());
					        }
					        else
					        {
					        	cell12.setCellValue(new HSSFRichTextString(DataConvertUtils.formatMoney(valMap.get(head)+"",2)));	
					        }
			        	}
			        	else
			        	{
			        		cell12.setCellValue(new HSSFRichTextString(DataConvertUtils.formatMoney(valMap.get(head)+"",2)));
			        	}
			        }
			        else
			        {
			        	cell12.setCellValue(new HSSFRichTextString(DataConvertUtils.formatMoney(valMap.get(head)+"",2)));
			        }
			       
		        	if((valMap.get(head)+"").indexOf("-") != -1)
		        	{
		        		statu = 2;
		        	}
		        	
			           
			        cell12.setCellStyle(setCellStyleHead(wb,2,statu));
				}
				
				else
				{
					HSSFCell cell = _row.createCell((short)j);
					//列头样式
					cell.setCellStyle(setCellStyleHead(wb,3,0));
					//VALUE
					cell.setCellValue(new HSSFRichTextString(valMap.get(head)+""));
				}
				j++;
			}
		}
		
		
		for (int i = 0; i < headMap.size(); i++) {
			//自适应宽度
			sheet.autoSizeColumn((short)i);
			//sheet.setColumnWidth((short)i, (short)3000);
		}
		exportExcel.outputExcel(url);

	}
	
	
}