poi根据数据创建导出excel

转载

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * 
 */
public class ExcelExportUtil {

	/**
	 * <p>
	 * 设置报表名称及样式
	 * </p>
	 *
	 * @param titleName
	 * @param sheet
	 * @param length
	 */
	public static void outputTitle(String titleName, Sheet sheet, int length) {
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, length));
		Row row = sheet.createRow(0);
		// 行高
		row.setHeightInPoints(40);
		Cell cell = row.createCell(0);
		cell.setCellValue(titleName);
		Workbook workbook = sheet.getWorkbook();
		// 居中对齐
		CellStyle centerHr = workbook.createCellStyle();
		centerHr.setVerticalAlignment(VerticalAlignment.CENTER);
		centerHr.setAlignment(HorizontalAlignment.CENTER);
		cell.setCellStyle(centerHr);
		// 设置字体
		Font f = workbook.createFont();
		f.setBold(true);
		f.setFontHeightInPoints((short) 16);
		cell.getCellStyle().setFont(f);
	}

	/**
	 * <p>
	 * 设置表头信息
	 * </p>
	 *
	 * @param headersInfo
	 * @param sheet
	 */
	public static void outputHeaders(String[] headersInfo, Sheet sheet) {
		Row row = sheet.createRow(1);
		row.setHeightInPoints(32);
		// 冻结行
		sheet.createFreezePane(0, row.getRowNum() + 1);
		Workbook workbook = sheet.getWorkbook();
		// 中文行样式
		CellStyle chinaRowFontStyle = workbook.createCellStyle();
		chinaRowFontStyle = workbook.createCellStyle();
		chinaRowFontStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		chinaRowFontStyle.setAlignment(HorizontalAlignment.CENTER);
		chinaRowFontStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		chinaRowFontStyle.setAlignment(HorizontalAlignment.CENTER);
		// 字体加粗
		Font f1 = workbook.createFont();
		f1.setBold(true);
		f1.setFontHeightInPoints((short) 10);
		chinaRowFontStyle.setFont(f1);
		for (int i = 0; i < headersInfo.length; i++) {
			sheet.setColumnWidth(i, 4000);
			Cell cell = row.createCell(i);
			cell.setCellValue(headersInfo[i]);
			cell.setCellStyle(chinaRowFontStyle);
		}
	}

	/**
	 * <p>
	 * 填充数据
	 * </p>
	 *
	 * @param headersInfo
	 * @param columnsInfo
	 * @param sheet
	 * @param rowIndex
	 */
	public static void outputColumns(String[] headersInfo, @SuppressWarnings("rawtypes") List columnsInfo, Sheet sheet,
			int rowIndex) {
		Row row;
		@SuppressWarnings("unused")
		int headerSize = headersInfo.length;
		Workbook workbook = sheet.getWorkbook();
		CellStyle defaultStyle = workbook.createCellStyle();
		defaultStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		defaultStyle.setAlignment(HorizontalAlignment.CENTER);
		Font f1 = workbook.createFont();
		f1.setFontHeightInPoints((short) 10);
		defaultStyle.setFont(f1);
		for (int i = 0; i < columnsInfo.size(); i++) {
			row = sheet.createRow(rowIndex + i);
			Object obj = columnsInfo.get(i);
			for (int j = 0; j < headersInfo.length; j++) {
				Object value = getFieldValueByName(headersInfo[j], obj);
				Cell cell = row.createCell(j);
				if (value != null) {
					if (value instanceof Date) {
						// 日期类型
						DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
						cell.setCellValue(format.format(value));
					} else {
						cell.setCellValue(value.toString());
					}
				} else {
					cell.setCellValue("");
				}
				cell.setCellStyle(defaultStyle);
			}
		}
	}

	/**
	 * <p>
	 * 取值
	 * </p>
	 *
	 * @param fieldName
	 * @param obj
	 * @return
	 */
	private static Object getFieldValueByName(String fieldName, Object obj) {
		String firstLetter = fieldName.substring(0, 1).toUpperCase();
		String getter = "get" + firstLetter + fieldName.substring(1);
		try {
			Method method = obj.getClass().getMethod(getter, new Class[] {});
			Object value = method.invoke(obj, new Object[] {});
			return value;
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * <p>
	 * 设置表头信息
	 * </p>
	 *
	 * @param headersInfo
	 * @param sheet
	 */
	public static void outputFirstHeaders(String[] headersInfo, Sheet sheet) {
		Row row = sheet.createRow(0);
		row.setHeightInPoints(32);
		sheet.createFreezePane(0, row.getRowNum() + 1);
		Workbook workbook = sheet.getWorkbook();
		// 中文行样式
		CellStyle chinaRowFontStyle = workbook.createCellStyle();
		chinaRowFontStyle = workbook.createCellStyle();
		chinaRowFontStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		chinaRowFontStyle.setAlignment(HorizontalAlignment.CENTER);
		chinaRowFontStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		chinaRowFontStyle.setAlignment(HorizontalAlignment.CENTER);
		// 字体加粗
		Font f1 = workbook.createFont();
		f1.setBold(true);
		f1.setFontHeightInPoints((short) 10);
		chinaRowFontStyle.setFont(f1);
		for (int i = 0; i < headersInfo.length; i++) {
			sheet.setColumnWidth(i, 4000);
			Cell cell = row.createCell(i);
			cell.setCellValue(headersInfo[i]);
			cell.setCellStyle(chinaRowFontStyle);
		}
	}

	/**
	 * <p>
	 * 设置表头信息
	 * </p>
	 *
	 * @param headersInfo
	 * @param sheet
	 */
	public static void outputSecondHeaders(String[] headersInfo, Sheet sheet) {
		Row row = sheet.createRow(1);
		row.setHeightInPoints(32);
		// 冻结行
		sheet.createFreezePane(0, row.getRowNum() + 2);
		Workbook workbook = sheet.getWorkbook();
		// 中文行样式
		CellStyle chinaRowFontStyle = workbook.createCellStyle();
		chinaRowFontStyle = workbook.createCellStyle();
		chinaRowFontStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		chinaRowFontStyle.setAlignment(HorizontalAlignment.CENTER);
		chinaRowFontStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		chinaRowFontStyle.setAlignment(HorizontalAlignment.CENTER);
		// 字体加粗
		Font f1 = workbook.createFont();
		f1.setBold(true);
		f1.setFontHeightInPoints((short) 10);
		chinaRowFontStyle.setFont(f1);
		for (int i = 0; i < headersInfo.length; i++) {
			sheet.setColumnWidth(i, 4000);
			Cell cell = row.createCell(i);
			cell.setCellValue(headersInfo[i]);
			cell.setCellStyle(chinaRowFontStyle);
		}
	}

}