poi使用导出excel样式工具类--jerry出品

package com.poi.excle;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

public class ExcelStyleUtil {
	
	private static ExcelStyleUtil getExcelUtil = null;
	
	public static ExcelStyleUtil get(){
		
		if(null == getExcelUtil){
			getExcelUtil = new ExcelStyleUtil();
		}
		return getExcelUtil;
	}
	
	
	/**
	 * @Description  设置第几行内容
	 * @param cell 行对象
	 * @param row 列对象
	 * @param sheet 
	 * @param content 内容
	 * @param Height 高度
	 * @return
	 */
	public ExcelStyleUtil rowContent(HSSFCell cell,HSSFRow row,HSSFSheet sheet,String content,int Height){
		cell.setCellValue(content);
		row.setHeightInPoints(Height);
		return get();
	}
	
	
	
	/**
	 * @Description  设置单元格宽度5000
	 * @param column 多少列
	 * @param sheet
	 * @return
	 */
	public HSSFSheet setWidth(int column ,HSSFSheet sheet){
		for(int i=0;i<=column;i++){
			sheet.setColumnWidth(i, 5000);
		}
		return sheet;
	}
	
	/**
	 * @Description  设置合并单元格
	 * @param wbSheet
     * @param firstRow ：合并的开始行
     * @param lastRow：合并的结束行
     * @param firstCol: 合并的开始列
     * @param lastColL: 合并的结束列
	 * @return
	 */
	public HSSFSheet setMergeCell(HSSFSheet wbSheet,int firstRow, int lastRow, int firstCol, int lastCol){
		CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
		get().setCellBorder(cellRangeAddress, wbSheet);
        wbSheet.addMergedRegion(cellRangeAddress);
		return null;
	}
	
	/**
	 * @Description  设置边框线
	 * @param cellRangeAddress
	 * @param sheet
	 */
	public void setCellBorder(CellRangeAddress cellRangeAddress ,HSSFSheet sheet){
		RegionUtil.setBorderLeft(1, cellRangeAddress, sheet);  
		RegionUtil.setBorderBottom(1, cellRangeAddress, sheet);  
		RegionUtil.setBorderRight(1, cellRangeAddress, sheet);  
		RegionUtil.setBorderTop(1, cellRangeAddress, sheet); 
	}
	
	
	/**
     * @Description //标题样式 :加粗，垂直居中
	 * @param isBold 是否加粗
	 * @param FontISize 字体大小
     */
    public HSSFCellStyle setTitleStyle(HSSFWorkbook wb, boolean isBold, int FontISize) {
        // 标题样式（加粗，垂直居中）
    	HSSFCellStyle cellStyle = wb.createCellStyle();
        center(cellStyle);
        Font font = wb.createFont();
        font.setBold(isBold);   //加粗
        font.setFontHeightInPoints((short) FontISize);  //设置标题字体大小
        cellStyle.setFont(font);
        return cellStyle;
    }
    /**
     * @Description 样式居中 加边框
	 * @param cellStyle
	 * 
     */
    public HSSFCellStyle center(HSSFCellStyle cellStyle) {
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
//		setBackdropColor(cellStyle);
        return cellStyle;
    }
    
    /**
     * 
     * @Description 边框
     * @param cellStyle
     * @return
     */
    public ExcelStyleUtil setFrame(HSSFCellStyle cellStyle){
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
    	cellStyle.setBorderTop(BorderStyle.THIN);
    	return get();
    }

    /**
     * 
     * @Description 背景色灰色
     * @param cellStyle
     * @return
     */
    public ExcelStyleUtil setBackdropColor(HSSFCellStyle cellStyle){
    	cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);//水平居中 
    	cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//垂直居中
    	cellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);//垂直居中
    	return get();
    	
    }
    
    
    public static void main(String[] args) throws FileNotFoundException, IOException {

		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("table"); //创建table工作薄
		
		ExcelStyleUtil.get().setWidth(9, sheet);
		//标题
		HSSFRow row;
		row = sheet.createRow(0);//创建表格行
		HSSFCell cell;
		cell = row.createCell(0);//根据表格行创建单元格

		ExcelStyleUtil.get().rowContent(cell,row,sheet, "标题统计反馈表", 35).setMergeCell(sheet,0,0,0,9);		
		HSSFCellStyle titleStyle = ExcelStyleUtil.get().setTitleStyle(wb, true, 16);
		ExcelStyleUtil.get().setFrame(titleStyle);
		cell.setCellStyle(titleStyle);
		
		row = sheet.createRow(1);//创建表格行
		cell = row.createCell(0);//根据表格行创建单元格
		ExcelStyleUtil.get()
			.rowContent(cell,row,sheet, "填报单位：xxx运输厅                                                                                                                                                       填报时间：X年X月", 18)
			.setMergeCell(sheet,1,1,0,9);
		row.setHeightInPoints(18);
		HSSFCellStyle stylew = wb.createCellStyle();
		stylew = ExcelStyleUtil.get().center(stylew);
		cell.setCellStyle(stylew);
		
		Object[] datas = {"序号","列名1","列名2","列名3","列名4","列名5","列名6","列名7","列名8","列名9"};
		row = sheet.createRow(2);//创建表格行
		for(int i = 0; i < datas.length; i++){
			cell = row.createCell(i);//根据表格行创建单元格
//			row.setHeightInPoints(18);
//			cell.setCellValue(String.valueOf(datas[i]));
			ExcelStyleUtil.get().rowContent(cell,row,sheet, String.valueOf(datas[i]), 18);
			HSSFCellStyle style = wb.createCellStyle();
			ExcelStyleUtil.get().setFrame(style).setBackdropColor(style).center(style);
			style.setWrapText(true);
			cell.setCellStyle(style);
		}
		
//		//内容
		Object[][] datas3 = {{"序号测试","车牌号码测试","驾驶员测试","道路运输证号测试","从业资格证号测试","所属运输企业名称测试","装载货物测试",
		"报警时间测试","报警地点测试","报警原因测试"},{"序号测试","车牌号码测试","驾驶员测试","道路运输证号测试","从业资格证号测试",
		"所属运输企业名称测试","装载货物测试","报警时间测试","报警地点测试","报警原因测试"}};
		for(int i = 0; i < datas3.length; i++) {
			row = sheet.createRow(3+i);//创建表格行
			for(int j = 0; j < datas3[i].length; j++) {
				cell = row.createCell(j);//根据表格行创建单元格
				cell.setCellValue(String.valueOf(datas3[i][j]));
				HSSFCellStyle style = wb.createCellStyle();
				ExcelStyleUtil.get().setFrame(style);
				style.setWrapText(true);
				cell.setCellStyle(style);
				}
		}
		wb.write(new FileOutputStream("E:/demo.xls"));
		System.out.println("导出完成,已保存到E:/demo.xls");
	
	}
    
}