POI兼容导出Excel(样式)

package ycl.learn.excel;

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Workbook;

public class ExcelStyle {

	/**
	 * data style
	 * @param workbook
	 * @return
	 */
	public static CellStyle createDataCellStyle(Workbook workbook){
		CellStyle dataCellStyle = workbook.createCellStyle();
		DataFormat format = workbook.createDataFormat(); 
		dataCellStyle.setDataFormat(format.getFormat("m/d/yy"));
		return dataCellStyle;
	}
	
	/**
	 * number style
	 * @param workbook
	 * @return
	 */
	public static CellStyle createNumberCellStyle(Workbook workbook){
		CellStyle numberCellStyle = workbook.createCellStyle();
		DataFormat format = workbook.createDataFormat(); 
		numberCellStyle.setDataFormat(format.getFormat("0.00"));
		return numberCellStyle;
	}
	
	/**
	 * text style
	 * @param workbook
	 * @return
	 */
	public static CellStyle createTextCellStyle(Workbook workbook){
		CellStyle numberCellStyle = workbook.createCellStyle();
		DataFormat format = workbook.createDataFormat(); 
		numberCellStyle.setDataFormat(format.getFormat("@"));
		return numberCellStyle;
	}
	
	/**
	 * append the cellStyle with font
	 * 
	 * @param workbook
	 * @param cellStyle
	 * @return
	 */
	public static CellStyle appendFontST(Workbook workbook,CellStyle cellStyle){
		Font font = workbook.createFont(); 
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		font.setFontHeight((short)250);
		font.setFontHeightInPoints((short)12);
		font.setFontName("宋体");
		cellStyle.setFont(font);
		cellStyle.setWrapText(true);
		return cellStyle;
	}
	
	/**
	 * append the cellStyle with color
	 * 
	 * @param workbook
	 * @param cellStyle
	 * @return
	 */
	public static CellStyle appendBackColor(CellStyle cellStyle){
		cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cellStyle.setFillBackgroundColor(HSSFColor.LIME.index);
		cellStyle.setFillForegroundColor(CellStyle.THICK_FORWARD_DIAG);
		return cellStyle;
	}
	
	/**
	 * append the cellStyle with border
	 * 
	 * @param cellStyle
	 * @return
	 */
	public static CellStyle appendBorder(CellStyle cellStyle){
		cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
		cellStyle.setBorderRight(CellStyle.BORDER_THIN);
		cellStyle.setBorderTop(CellStyle.BORDER_THIN);
		cellStyle.setLeftBorderColor(HSSFColor.GREEN.index);
		cellStyle.setRightBorderColor(HSSFColor.GREEN.index);
		cellStyle.setTopBorderColor(HSSFColor.GREEN.index);
		cellStyle.setBottomBorderColor(HSSFColor.GREEN.index);
		return cellStyle;
	}
	
	/**
	 * append the cellStyle with wrapText
	 * 
	 * @param cellStyle
	 * @return
	 */
	public static CellStyle appendWrapText(CellStyle cellStyle){
		cellStyle.setWrapText(true);
		return cellStyle;
	}
	
	/**
	 * append the cellStyle with align.
	 * 
	 * @param cellStyle
	 * @return
	 */
	public static CellStyle appendAlign(CellStyle cellStyle){
		cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		return cellStyle;
	} 
}

this is the excel cell style.

package ycl.learn.excel;

import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook; 
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import ycl.learn.excel.ExcelHeader.ColumnHeader;
import ycl.learn.excel.ExcelResult.ExcelErrorEnum;
import ycl.learn.excel.ExcelTypeConvert.HeaderType;

public class ExcelWriter {

	public static ExcelResult writerExcel(ExcelParameter parameter) {
		ExcelResult result = new ExcelResult();
		OutputStream os = parameter.getOutputStream();
		Boolean version2007 = parameter.getVerstion2007();
		String sheetName = parameter.getSheetName(); 
		Workbook workbook = null;
		try {
			if (version2007){
				workbook = new XSSFWorkbook(); 
			}else{
				workbook = new HSSFWorkbook(); 
			}
		} catch (Exception e) {
			result.addError(ExcelErrorEnum.SYSTEM_ERROR);
			return result;
		} 
		Sheet sheet=workbook.createSheet(sheetName); 
		sheet.setPrintGridlines(true);
		result = writerSheet(workbook,sheet,parameter);
		try {
			workbook.write(os);
		} catch (IOException e) {
			result.addError(ExcelErrorEnum.SYSTEM_ERROR);
			return result;
		}
		return result; 
	}

	private static ExcelResult writerSheet(Workbook workbook, Sheet sheet, ExcelParameter parameter) {
		ColumnHeader[] chs = parameter.getChs();
		List<Map<String, String>> dataMapList = parameter.getDataMapList();
		String title = parameter.getTitle();
		ExcelResult result = new ExcelResult();
		int rowindex = 0;
		if(title!=null){
			Row row = sheet.createRow(rowindex);
			CellRangeAddress region = new CellRangeAddress(0,0,0,chs.length-1);
			CellStyle cellStyle = ExcelStyle.createDataCellStyle(workbook);
			cellStyle = ExcelStyle.appendBorder(cellStyle);
			cellStyle = ExcelStyle.appendAlign(cellStyle);
			cellStyle = ExcelStyle.appendFontST(workbook, cellStyle);
			for(int i=region.getFirstColumn();i<region.getLastColumn()+1;i++){
				Cell cellregion=row.getCell(i);
				if( cellregion==null){   
					cellregion=row.createCell(i);   
					cellregion.setCellValue("");   
				} 
				//sheet.setDefaultColumnStyle(i, cellStyle);
				cellregion.setCellStyle(cellStyle); 
			}
			
			Cell cell = row.createCell(0);
			cell.setCellValue(title);
			//sheet.setDefaultColumnStyle(0, cellStyle);
			cell.setCellStyle(cellStyle);
			sheet.addMergedRegion(region);//merge cells
			rowindex++;
		}
		Row row = sheet.createRow(rowindex);
		for(int i=0;i<chs.length;i++){ 
			sheet.createFreezePane(0, 2, 0, 2);// freeze cells
			Cell cell = row.createCell(i);
			cell.setCellValue(chs[i].getName());
		}
		
		for(int j=0;j<dataMapList.size();j++){
			row = sheet.createRow(j+rowindex+1);
			for(int k=0;k<chs.length;k++){
				Cell dataCell = row.createCell(k);
				Map<String, String> dataMap = dataMapList.get(j);
				String value = dataMap.get(chs[k].getName());
				if(value == null){
					result.addError(ExcelErrorEnum.HEADER_NOT_MATCH);
				} 
				Object convertValue = chs[k].getHeaderType().getConvertValue(value); 
				if(chs[k].getHeaderType().equals(HeaderType.BOOLEAN)){
					Boolean realValue  = (Boolean)convertValue;
					dataCell.setCellValue(realValue); 
				}else if(chs[k].getHeaderType().equals(HeaderType.DATE)){
					Date realValue  = (Date)convertValue;  
					dataCell.setCellValue(realValue);
					dataCell.setCellType(Cell.CELL_TYPE_NUMERIC);
					CellStyle cellStyle=ExcelStyle.createDataCellStyle(workbook);
					cellStyle = ExcelStyle.appendBorder(cellStyle);
					dataCell.setCellStyle(cellStyle);
				}else if(chs[k].getHeaderType().equals(HeaderType.DOUBLE)){
					Double realValue = (Double)convertValue;
					dataCell.setCellValue(realValue);
					dataCell.setCellType(Cell.CELL_TYPE_NUMERIC);
					CellStyle cellStyle=ExcelStyle.createNumberCellStyle(workbook);
					cellStyle = ExcelStyle.appendBorder(cellStyle);
					dataCell.setCellStyle(cellStyle);
				}else if(chs[k].getHeaderType().equals(HeaderType.TEXT)){
					dataCell.setCellValue((String)convertValue);
					CellStyle cellStyle=ExcelStyle.createTextCellStyle(workbook);
					cellStyle = ExcelStyle.appendBorder(cellStyle);
					dataCell.setCellStyle(cellStyle);
				}else if(chs[k].getHeaderType().equals(HeaderType.STRING)){
					dataCell.setCellValue((String)convertValue);
				}else{
					dataCell.setCellValue((String)convertValue);
				} 
			}
		}
		return result;
	}
}

this is the excel writer, add the cell style, and merge cells and freeze cells. very beautiful function.