poi操作excel

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hslf.model.Sheet;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;



public class ExportExcelUtil {
	
	private  HSSFWorkbook wb;
	private HSSFCellStyle styleTitle;
	private HSSFCellStyle styleLeft;
	private HSSFCellStyle styleCenter;
	private HSSFCellStyle styleRight;
	private HSSFFont fontTitle;
	private HSSFFont fontData;
    public   ExportExcelUtil() {
		this.init();
	}
	public byte[] getExcelByte(List<Map<String, Object>> dataList, List<Map<String, Object>>  colConfig) {
		// 第一步， 初始化
		this.init();
		//  在webbook中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = wb.createSheet("sheet1");
		
		// 设置单元格居中
	
		// 加粗居中样式
		
		// 设置表头
		HSSFRow row = sheet.createRow(0);
		for (int i = 0; i < colConfig.size(); i++) {
			  int length =oConvertUtils.getInt(String.valueOf(colConfig.get(i).get("width"))) ;
			//sheet.setColumnWidth(i, (short) (50 * length));
			HSSFCell cell = row.createCell(i);
			cell.setCellValue(colConfig.get(i).get("title").toString()); 
			cell.setCellStyle(styleTitle);
		}
		// 设置内容
		for (int i = 0; i < dataList.size(); i++) {
			HSSFRow row1 = sheet.createRow(i + 1);
			for (int j = 0; j < colConfig.size(); j++) {
				HSSFCell cell = row1.createCell(j);
				Object object= dataList.get(i).get(colConfig.get(j).get("field"));
				cell.setCellValue(object==null?"":object.toString() );
				cell.setCellStyle(this.getDataHSSFCellStyle(colConfig.get(j)));
			}
		}
		 //设置自动列宽
		  for (int i = 0; i < colConfig.size(); i++) {
              sheet.autoSizeColumn(i);
              sheet.setColumnWidth(i,sheet.getColumnWidth(i)*12/10);
          }
	 
		return toByteArray();
	}
	private void init(){
	     wb = new HSSFWorkbook();
	     //
	   fontTitle = wb.createFont();
	   fontTitle.setFontName("宋体");
	   fontTitle.setFontHeightInPoints((short)11); 
	   fontTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
	   
	   //
	   styleTitle = wb.createCellStyle();
	   styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	   styleTitle.setFont(fontTitle);
	   styleTitle.setFillForegroundColor( HSSFColor.SKY_BLUE.index);
	   styleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	   styleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
	   styleTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);  
	   styleTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);  
	   styleTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	   
	   //
	    fontData = wb.createFont();
	    fontData.setFontName("宋体");
	    fontData.setFontHeightInPoints((short)11); 
	    
	    
	    //
		styleLeft = wb.createCellStyle();
		styleLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		styleLeft.setFont(fontData);
		styleLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
		styleLeft.setBorderRight(HSSFCellStyle.BORDER_THIN);  
		styleLeft.setBorderTop(HSSFCellStyle.BORDER_THIN);  
		styleLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		//
		styleCenter = wb.createCellStyle();
		styleCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		styleCenter.setFont(fontData);
		styleCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
		styleCenter.setBorderRight(HSSFCellStyle.BORDER_THIN);  
		styleCenter.setBorderTop(HSSFCellStyle.BORDER_THIN);  
		styleCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		//
		styleRight = wb.createCellStyle();
		styleRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		styleRight.setFont(fontData);
		styleRight.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
		styleRight.setBorderRight(HSSFCellStyle.BORDER_THIN);  
		styleRight.setBorderTop(HSSFCellStyle.BORDER_THIN);  
		styleRight.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	
  }
  private HSSFCellStyle getDataHSSFCellStyle(Map<String, Object> parms){
	 String align= parms.get("align").toString();
	 HSSFCellStyle hssfCellStyle=null;
	 switch (align) {
	 case "left":
		 hssfCellStyle=this.styleLeft;
		break;
	 case "center":
		 hssfCellStyle=this.styleCenter;
			break;
	 case "right":
		 hssfCellStyle=this.styleRight;
			break;
	 default:
		 hssfCellStyle=this.styleLeft;
		break;
	}
	 return hssfCellStyle;
  }
public HSSFWorkbook getWb() {
	return wb;
}
public void setWb(HSSFWorkbook wb) {
	this.wb = wb;
}
public HSSFCellStyle getStyleTitle() {
	return styleTitle;
}
public void setStyleTitle(HSSFCellStyle styleTitle) {
	this.styleTitle = styleTitle;
}
public HSSFCellStyle getStyleLeft() {
	return styleLeft;
}
public void setStyleLeft(HSSFCellStyle styleLeft) {
	this.styleLeft = styleLeft;
}
public HSSFCellStyle getStyleCenter() {
	return styleCenter;
}
public void setStyleCenter(HSSFCellStyle styleCenter) {
	this.styleCenter = styleCenter;
}
public HSSFCellStyle getStyleRight() {
	return styleRight;
}
public void setStyleRight(HSSFCellStyle styleRight) {
	this.styleRight = styleRight;
}
public HSSFFont getFontTitle() {
	return fontTitle;
}
public void setFontTitle(HSSFFont fontTitle) {
	this.fontTitle = fontTitle;
}
public HSSFFont getFontData() {
	return fontData;
}
public void setFontData(HSSFFont fontData) {
	this.fontData = fontData;
}
public byte[] toByteArray(){
	ByteArrayOutputStream os = new ByteArrayOutputStream();
	try {
		wb.write(os);
	} catch (IOException e) {
		e.printStackTrace();
	}
	return os.toByteArray();
}

}