Excel2007写入表格

package com.ucf.boss.utils;

import java.io.OutputStream;
import java.lang.reflect.Method;
import java.util.Date;
import java.util.List;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.util.HSSFColor;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.ucf.boss.enumtype.file.AbstractExcelEnum;
import com.ucf.platform.framework.core.util.DateUtils;

/**
 * 导出excel工具类
 * 
 * @author LiuHui
 *
 */
public class ExcelFileUtils {

	/**
	 * 创建excel
	 * 
	 * @param list
	 * @param excelEnums
	 * @return
	 */
	public static void createExcel(OutputStream os, List<?> list, AbstractExcelEnum[] excelEnums) throws Exception {
		XSSFWorkbook wb = new XSSFWorkbook();
		Sheet sheet = wb.createSheet();
		// 表头格式
		CellStyle headCellStyle = createExcelHeadStyle(wb);
		XSSFCellStyle cellStyle = wb.createCellStyle();
		// 创建表头
		createTabHeader(sheet, excelEnums, headCellStyle);
		if (null != list && list.size() > 0) {
			for (int i = 0; i < list.size(); i++) {
				// 创建每行的数据
				Row row = sheet.createRow(i + 1);
				Object vo = list.get(i);
				for (int j = 0; j < excelEnums.length; j++) {
					Cell cell = row.createCell(j);
					cell.setCellType(XSSFCell.CELL_TYPE_STRING);
					cell.setCellStyle(cellStyle);
					AbstractExcelEnum excelEnum = excelEnums[j];
					String fileName = excelEnum.getField();
					Object value = PropertyUtils.getProperty(vo, fileName);
					if (null != value) {
						// 如果是Date类型
						if (value.getClass().isAssignableFrom(Date.class)) {
							cell.setCellValue(DateUtils.format((Date) value, DateUtils.newFormat));
						} else {
							String valTrim = value.toString().trim();
							if (!"".equals(valTrim)) {
								// 如果返回的值可用枚举做转换
								if (null != excelEnum.getClazz()) {
									String cconvertedValue = convertValue(excelEnum, valTrim);
									if (StringUtils.isNotBlank(cconvertedValue)) {
										cell.setCellValue(cconvertedValue);
									} else {
										cell.setCellValue("");
									}
								} else {
									cell.setCellValue(valTrim);
								}
							} else {
								cell.setCellValue("");
							}
						}
					} else {
						cell.setCellValue("");
					}
				}
			}
		}
		wb.write(os);
	}

	/**
	 * 定义表头单元格样式
	 * 
	 * @param wb
	 * @return
	 */
	private static CellStyle createExcelHeadStyle(XSSFWorkbook wb) {
		CellStyle style = wb.createCellStyle();
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setFillForegroundColor(HSSFColor.BLUE_GREY.index); // 设置背景色
		style.setAlignment(CellStyle.ALIGN_CENTER);// 居中
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 设置垂直居中
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
		return style;
	}

	/**
	 * 根据编码获取枚举中对应的值
	 * 
	 * @param excelEnum
	 * @param valTrim
	 * @return
	 * @throws Exception
	 */
	private static String convertValue(AbstractExcelEnum excelEnum, String valTrim) throws Exception {
		Class<?> clazz = excelEnum.getClazz();
		if (!clazz.isEnum()) {
			throw new IllegalArgumentException("参数类型必须是枚举类型");
		}
		Method method = clazz.getMethod("values");
		Object obj = method.invoke(null);
		Object[] enumArr = (Object[]) obj;
		for (Object object : enumArr) {
			String va = PropertyUtils.getProperty(object, excelEnum.getCodeFieldName()).toString();
			if (valTrim.equals(va)) {
				return PropertyUtils.getProperty(object, excelEnum.getNameFieldName()).toString();
			}
		}
		return null;
	}

	/**
	 * 创建表头
	 * 
	 * @param sheet
	 * @param enums
	 * @author LiuHui
	 * @param headCellStyle
	 */
	private static void createTabHeader(Sheet sheet, AbstractExcelEnum[] enums, CellStyle headCellStyle) {
		Row row = sheet.createRow(0);
		row.setHeight((short) (1.5 * 256)); // 设置行高
		for (int i = 0; i < enums.length; i++) {
			Cell cell = row.createCell(i);
			cell.setCellType(XSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(headCellStyle);
			cell.setCellValue(enums[i].getColumn());
		}
	}
}