ExcelUtil.java

package cn.timer.api.utils;

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.web.multipart.MultipartFile;

public class ExcelUtil<T> {

	/**
	 * HSSFWorkbook excel 的文档对象 HSSFSheet excel 的表单 HSSFRow excel 的行 HSSFCell excel
	 * 的格子单元
	 */

	/**
	 * 导出xls文件
	 * 
	 * @param title   sheet标题
	 * @param headers 第一行标题
	 * @param dataset 集合数据/表单内容
	 * @param out
	 * @param pattern
	 */
	public void exportExcel(String title, String[] headers, Collection<T> dataset, OutputStream out, String pattern) {

		// 创建工作簿
		// SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 创建SHEET页/表格页，title-表格标题
		HSSFSheet sheet = (HSSFSheet) workbook.createSheet(title);
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth((short) 15);
		// 创建行，从0开始
		HSSFRow row = (HSSFRow) sheet.createRow(0);
		// 创建单元格样式对象(设置样式)
		HSSFCellStyle ztStyle = workbook.createCellStyle();
		// 创建字体对象
		ztStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
		ztStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
		ztStyle.setFillForegroundColor((short) 42);// 前景填充色
		// ztStyle.setFillForegroundColor(IndexedColors.YELLOW.index);//前景填充色-黄色
		ztStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 设置前景填充样式

		// 边框
		ztStyle.setBorderBottom(BorderStyle.THIN);// 下边框
		ztStyle.setBorderLeft(BorderStyle.THIN);// 左边框
		ztStyle.setBorderRight(BorderStyle.THIN);// 右边框
		ztStyle.setBorderTop(BorderStyle.THIN); // 上边框

		HSSFCellStyle namestyle = workbook.createCellStyle();
		namestyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
		namestyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
		namestyle.setBorderBottom(BorderStyle.THIN);// 下边框
		namestyle.setBorderLeft(BorderStyle.THIN);// 左边框
		namestyle.setBorderRight(BorderStyle.THIN);// 右边框
		namestyle.setBorderTop(BorderStyle.THIN); // 上边框

		for (short i = 0; i < headers.length; i++) {
			// 创建单元格
			HSSFCell cell = row.createCell(i);
			row.setHeightInPoints(30); // 设置行高
			sheet.createFreezePane(0, 1);// 冻结第1行
			cell.setCellValue(headers[i]);// 设置每一列的值
			cell.setCellStyle(ztStyle);// 样式应用到该单元格上
			/**
			 * 四个参数的含义：
			 * 
			 * ａ表示要冻结的列数；
			 * 
			 * ｂ表示要冻结的行数；
			 * 
			 * ｃ表示右边区域[可见]的首列序号；
			 * 
			 * ｄ表示下边区域[可见]的首行序号；
			 */
			// sheet.createFreezePane(a,b,c,d);

		}
		// 遍历集合数据，产生数据行
		Iterator<T> it = dataset.iterator();
		int index = 0;
		while (it.hasNext()) {
			index++;
			row = sheet.createRow(index);// 创建第 index 行
			T t = it.next();
			// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
			Field[] fields = t.getClass().getDeclaredFields();
			for (short i = 0; i < fields.length; i++) {
				HSSFCell cell = row.createCell(i);
				Field field = fields[i];
				String fieldName = field.getName();
				// 动态拼接getXxx获取属性值方法 如：TUserLand类 loginname 动态获取 -》 getLoginname 这个方法名
				String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
				try {
					Class<? extends Object> tclass = t.getClass();
					Method getMethod = tclass.getMethod(getMethodName, new Class[] {});
					Object value = getMethod.invoke(t, new Object[] {});
					String textValue = null;
					if (value instanceof Date) {
						Date date = (Date) value;
						SimpleDateFormat sdf = new SimpleDateFormat(pattern);
						textValue = sdf.format(date);
					} else if (fieldName.length() >= 4
							&& "Time".equalsIgnoreCase(fieldName.substring(fieldName.length() - 4))) {
						textValue = DateFormatUtils.getStringByTimestampAndParm((Long) value, "YYYY-MM-DD");
					} else {
						// 其它数据类型都当作字符串简单处理
						textValue = value != null ? value.toString() : "";
					}
					// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
					if (textValue != null) {
						Pattern p = Pattern.compile("^//d+(//.//d+)?$");// 正则表达式
						Matcher matcher = p.matcher(textValue);
						if (matcher.matches()) {
							// 是数字当作double处理
							cell.setCellValue(Double.parseDouble(textValue));
						} else {
							cell.setCellValue(textValue);
						}
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					// 清理资源
				}
				row.setHeightInPoints(25);// 设置行高为25(猜测单位为px)
				cell.setCellStyle(namestyle); // 渲染单元格
			}
		}
		try {
			workbook.write(out);
			workbook.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	@SuppressWarnings("unused")
	public Collection<T> importExcel(File file, Collection<T> dataset) throws Exception {

		// 装载流
		POIFSFileSystem fs = new POIFSFileSystem(((MultipartFile) file).getInputStream());
		HSSFWorkbook hw = new HSSFWorkbook(fs);
		// 获取第一个sheet页
		HSSFSheet sheet = hw.getSheetAt(0);
		// 创建容器
		//List<Map<String, Object>> ret = new ArrayList<Map<String, Object>>();
		
		dataset = new ArrayList<T>(sheet.getLastRowNum()-1);
		Iterator<T> it = dataset.iterator();
		
		// 遍历行 从下标第一行开始（去除标题）
		for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
			T t = it.next();
			Field[] fields = t.getClass().getDeclaredFields();
			
			
			HSSFRow row = sheet.getRow(i);
			if (row != null) {
				// 装载obj
				for (int k = 0; k < row.getLastCellNum(); k++) {
					HSSFCell cell = row.getCell(k);
					Field field = fields[k];
					String fieldName = field.getName();
					//动态拼接getXxx获取属性值方法 如：TUserLand类 loginname 动态获取 -》 setLoginname 这个方法名
					String getMethodName = "set"+ fieldName.substring(0, 1).toUpperCase()+ fieldName.substring(1);
				
					Class<? extends Object> tclass = t.getClass();
					Method getMethod = tclass.getMethod(getMethodName,new Class[] {});
					getMethod.invoke(t, ExcelUtil.cellFormat(cell));
					String textValue = null;
				}
				//ret.add(dataObj(dataset, row));
			}
		}
		hw.close();
		return dataset;

	}
	
	@SuppressWarnings("deprecation")
	private static String cellFormat(HSSFCell cell){
		String result = new String();
		if (null != cell) {
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_NUMERIC: // 数字
				String cellValue = new DecimalFormat("0").format(cell.getNumericCellValue());
				if (cellValue.lastIndexOf("E") > -1) {
					cell.setCellValue(new DecimalFormat("#").format(cell.getNumericCellValue()));
				}
				String str = String.valueOf(cell);
				if (str.indexOf(".0") != -1 || str.indexOf("-") != -1) {
					if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
						SimpleDateFormat sdf = null;
						if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
								.getBuiltinFormat("h:mm")) {
							sdf = new SimpleDateFormat("HH:mm");
						} else {// 日期
							sdf = new SimpleDateFormat("yyyyMMdd");
						}
						Date date = cell.getDateCellValue();
						result = sdf.format(date);
						cell.setCellValue(result);
					} else if (cell.getCellStyle().getDataFormat() == 176) {
						// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
						double value = cell.getNumericCellValue();
						Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
						result = DateFormatUtils.tranDate(date, "yyyyMMdd");
						cell.setCellValue(result);
					} else if (cell.getCellStyle().getDataFormat() == 14
							|| cell.getCellStyle().getDataFormat() == 31) {
						double value = cell.getNumericCellValue();
						Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
						result = DateFormatUtils.tranDate(date, "yyyyMMdd");
						cell.setCellValue(result);
					}
					System.out.println(result);
				}

				break;
			case HSSFCell.CELL_TYPE_STRING: // 字符串
//                     System.out.print(cell.getStringCellValue()     
//                             + "   ");     
				break;
			case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
//                     System.out.println(cell.getBooleanCellValue()     
//                             + "   ");     
				break;
			case HSSFCell.CELL_TYPE_FORMULA: // 公式
//                     System.out.print(cell.getCellFormula() + "   ");     
				break;
			case HSSFCell.CELL_TYPE_BLANK: // 空值
//                     System.out.println(" ");     
				break;
			case HSSFCell.CELL_TYPE_ERROR: // 故障
//                     System.out.println(" ");     
				break;
			default:
//                     System.out.print("未知类型   ");     
				break;
			}
			
			return result;
		}
		
		return null;
		
	}
	
	
	
	

	@SuppressWarnings("unused")
	private static Map<String, Object> dataObj(Object obj, HSSFRow row) throws Exception {
		Class<?> rowClazz = obj.getClass();
		Field[] fields = FieldUtils.getAllFields(rowClazz);
		if (fields == null || fields.length < 1) {
			return null;
		}
		// 容器
		Map<String, Object> map = new HashMap<String, Object>();
		// 注意excel表格字段顺序要和obj字段顺序对齐 （如果有多余字段请另作特殊下标对应处理）
		for (int j = 0; j < fields.length; j++) {
			map.put(fields[j].getName(), getVal(row.getCell(j)));
		}
		return map;
	}

	@SuppressWarnings("deprecation")
	public static String getVal(HSSFCell hssfCell) {
		if (hssfCell != null) {
			hssfCell.setCellType(Cell.CELL_TYPE_STRING);
			return hssfCell.getStringCellValue();
		} else {
			return "";
		}
	}

}