ExcelUtil导入导出

说明：数据库使用mysql，导入的字段类型只能是有varchar，Date 类型

火狐导出文件时做特殊设置，不能用url编码

ExcelUtil.java：

package com.lw.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.EnumSet;
import java.util.HashMap;
import java.util.InputMismatchException;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.io.FilenameUtils;
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.HSSFFont;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;


/**
 * Excel组件
 * 
 * @author david
 * @version 1.0
 * @since 1.0
 */
public class ExcelUtil {

	/**
	 * Excel 2003
	 */
	private final static String XLS = "xls";
	/**
	 * Excel 2007
	 */
	private final static String XLSX = "xlsx";

	/***
	 * 导入Excel数据
	 * 
	 * <li>1、读取excel数据</li>
	 * <li>2、校验数据的合法性（日期，金额，字符长度（和数据库结构比较））</li>
	 * <li>3、合法数据绑定到bean对象中（反射）</li>
	 * <li>4、得到数据层面校验通过的bean对象集合，</li>
	 * 
	 * @param file
	 *            导入数据文件
	 * @param entityClass
	 *            bean对象类型bean.class
	 * @param sheetIndex
	 *            sheet索引
	 * @param columnArray
	 *            字段列数组 （需要导入的字段数组）
	 * @param checkColumn
	 *            需要校验格式的字段列Map
	 * @throws IOException
	 * @throws NoSuchFieldException
	 * @throws SecurityException
	 * @throws InstantiationException
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws ParseException
	 */
	public static <T> List<T> excelToList(File file, Integer sheetIndex, Class<T> entityClass, String[] columnArray,
			Map<String, ColumnCheckTypeEnum> checkColumn) throws IOException, SecurityException, NoSuchFieldException,
					InstantiationException, SQLException, IllegalAccessException, ParseException {
		List<T> list = new ArrayList<T>();
		Workbook workbook = null;
		if (XLS.equalsIgnoreCase(FilenameUtils.getExtension(file.getName()))) {
			workbook = new HSSFWorkbook(new FileInputStream(file));
		} else if (XLSX.equalsIgnoreCase(FilenameUtils.getExtension(file.getName()))) {
			workbook = new XSSFWorkbook(new FileInputStream(file));
		} else {
			throw new IOException("导入excel出错，不支持文件类型！");
		}
		if (sheetIndex == null) {
			sheetIndex = 0;
		}
		if ((sheetIndex + 1) > workbook.getNumberOfSheets()) {
			throw new IndexOutOfBoundsException("导入excel出错，指定sheet索引越界！");
		}
		// sheet中要导出的列
		if (columnArray == null || columnArray.length < 1) {
			throw new NullPointerException("导入excel出错，导入列设置错误！");
		}

		// 拿到sheet
		Sheet sheet = workbook.getSheetAt(sheetIndex);
		String sheetName = sheet.getSheetName(); // sheetName 使用表名称

		// 解析公式结果
		FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
		// 每个sheet中的数据
		List<Map<String, Object>> dataList = readSheet(sheet, evaluator, columnArray);

		// 数据库表对应的字段列信息
		List<ColumnData> columnDatas = null;
		try {
			columnDatas = DBDataUtil.getMySqlColumnDatas(sheetName, "dbName");
		} catch (SQLException e) {
			throw new SQLException("导入excel出错，获取表信息错误！");
		}
		// 不在数据库中的列
		List<String> outColumnDatas = new ArrayList<String>();
		List<String> columnList = new ArrayList<String>();
		for (int i = 0; i < columnDatas.size(); i++) {
			columnList.add(columnDatas.get(i).getColumnLowName());
		}
		for (int i = 0; i < columnArray.length; i++) {
			if (!columnList.contains(columnArray[i])) {
				outColumnDatas.add(columnArray[i]);
			}
		}

		// 遍历数据库表对应的字段列信息
		for (int j = 0; j < dataList.size(); j++) {
			// 拿到每一行的数据
			Map<String, Object> rowData = dataList.get(j);
			T o = null;
			try {
				o = (T) entityClass.newInstance();
			} catch (IllegalAccessException e) {
				throw new IllegalAccessException("导入excel出错，错误信息：" + e.getMessage());
			}
			if (checkColumn == null) {
				checkColumn = new HashMap<String, ColumnCheckTypeEnum>();
			}
			// 1、循环列（数据库中包含）设置，依次设置每一列
			for (int k = 0; k < columnDatas.size(); k++) {
				ColumnData cd = columnDatas.get(k);
				Object value = rowData.get(cd.getColumnLowName());

				String errMsg = "导入excel出错，错误位置>>：第【" + (j + 1 + 1) + "】行，第【"
						+ (getIndexOfArrayItem(cd.getColumnLowName(), columnArray) + 1 + 1) + "】列。错误信息：";
				// 1、先根据设置，校验自定义校验的列
				if (checkColumn.containsKey(cd.getColumnLowName()) == false || ColumnCheckTypeEnum.CHECK_TYPE_NOTNULL
						.equals(checkColumn.get(cd.getColumnLowName())) == false) {
					if (value == null) {
						continue;
					}
				}
				ColumnCheckTypeEnum checkType = checkColumn.get(cd.getColumnLowName());
				customColumnCheck(value, errMsg, checkType);

				// 2、自定义校验完成后，进行常规校验
				// 通过属性名称获取属性，把值设置到属性里面
				Field field = entityClass.getDeclaredField(cd.getColumnLowName());
				field.setAccessible(true); // 设置属性可访问， private
				if ("java.lang.String".equalsIgnoreCase(cd.getJavaType())) {
					if (value.toString().length() > Integer.parseInt(cd.getDataMaxLength())) {
						throw new SQLException(errMsg + "当前数据长度不能超过【" + cd.getDataMaxLength() + "】，当前文本长度【" + value.toString().length() + "】");
					}
					try {
						field.set(o, value.toString());
					} catch (IllegalArgumentException e) {
						throw new IllegalArgumentException(errMsg + e.getMessage());
					} catch (IllegalAccessException e) {
						throw new IllegalAccessException(errMsg + e.getMessage());
					}
				} else if ("java.util.Date".equalsIgnoreCase(cd.getJavaType())) {
					try {
						field.set(o, (Date) value);
					} catch (ClassCastException e) {
						throw new ClassCastException(errMsg + "数据格式错误，无法将【" + value + "】转换为java.util.Date类型日期格式。" + e.getMessage());
					} catch (IllegalArgumentException e) {
						throw new IllegalArgumentException(errMsg + e.getMessage());
					} catch (IllegalAccessException e) {
						throw new IllegalAccessException(errMsg + e.getMessage());
					}
				} else if ("java.lang.Integer".equalsI