ExcelUtil

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

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

源代码参考附件：

 

ExcelUtil.java:  导入核心代码

package com.lw.util;


import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
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.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;

/**
 * Excel组件
 * 
 * @author liwei
 * @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";

	
	public static void main(String[] args) {
		String file = "d://importStudentDatas.xls";
		String[] columnArray = new String[]{"code", "name", "birthDate"};
		Map<String, ColumnCheckTypeEnum> checkColumn = new HashMap<String, ColumnCheckTypeEnum>();
		checkColumn.put("code", ColumnCheckTypeEnum.CHECK_TYPE_PRICE_1);

		try {
			List<Student> list = ExcelUtil.excelToList(new File(file), 0, Student.class, columnArray, checkColumn);
			System.out.println(list.size());
			for (int i = 0; i < list.size(); i++) {
				Student stu = list.get(i);
				System.out.println("编码：" + stu.getCode() + " >>>>> 名称：" + stu.getName() + " >>>>> 生日：" + stu.getBirthDate());
			}
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ParseException e) {
			e.printStackTrace();
		}
		
	}

	
	/***
	 * 导入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 = ProDataUtil.getMySqlColumnDatas(sheetName, "scp");
		} catch (SQLException e) {
			throw new SQLException("导入excel出错，获取表信息错误！");
		}
		// 遍历数据库表对应的字段列信息
		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());
			}
			for(int k=0; k<columnDatas.size(); k++) {
				ColumnData cd = columnDatas.get(k);
				Object value = rowData.get(cd.getColumnLowName());

				String errMsg = "导入excel出错，错误位置>>：sheet【" + sheetName + "】中，第【"+ (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());
				// 非空校验
				if(ColumnCheckTypeEnum.CHECK_TYPE_NOTNULL.equals(checkType)) {
					if(value==null) {
						throw new InputMismatchException(errMsg + "此列为非空列，请检查excel数据是否为空！");
					}
				}
				// 整数类型校验
				else if(ColumnCheckTypeEnum.CHECK_TYPE_LONG.equals(checkType)) {
					try {
						Long.parseLong(value.toString());
					} catch (NumberFormatException e) {
						throw new NumberFormatException(errMsg + "此列为整数列，单元格应设置为文本类型，请检查excel数据是否整数数字或单元格是否为文本类型！" + e.getMessage());
					}
				}
				// 价格类型校验，取2位小数
				else if(ColumnCheckTypeEnum.CHECK_TYPE_PRICE.equals(checkType)) {
					try {
						String price = value.toString();
						new BigDecimal(price);
						if(price.indexOf(".")>-1 && price.split("\\.")[1].length()>2) {
							throw new InputMismatchException(errMsg + "此列为金额数值列，小数点后不超过2位小数，请检查excel数据是否合格！");
						}
					} catch (NumberFormatException e) {
						throw new NumberFormatException(errMsg + "此列为金额数值列，请检查excel数据是否合格！");
					}
				}
				// 价格类型校验，取4位小数
				else if(ColumnCheckTypeEnum.CHECK_TYPE_PRICE_1.equals(checkType)) {
					try {
						String price = value.toString();
						new BigDecimal(price);
						if(price.indexOf(".")>-1 && price.split("\\.")[1].length()>4) {
							throw new InputMismatchException(errMsg + "此列为金额数值列，小数点后不超过4位小数，请检查excel数据是否合格！");
						}
					} catch (NumberFormatException e) {
						throw new NumberFormatException(errMsg + "此列为金额数值列，请检查excel数据是否合格！");
					}
				}
				
				// 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".equalsIgnoreCase(cd.getJavaType())) {
					try {
//							field.setInt(o, Integer.parseInt(value.toString())); // Int to Integer 赋值时类型不匹配
						field.set(o, Integer.parseInt(value.toString()));
					} catch (NumberFormatException e) {
						throw new NumberFormatException(errMsg + "数据格式错误，无法将【"+value+"】转换为java.lang.Integer类型。" + e.getMessage());
					} catch (IllegalArgumentException e) {
						throw new IllegalArgumentException(errMsg + e.getMessage());
					} catch (IllegalAccessException e) {
						throw new IllegalAccessException(errMsg + e.getMessage());
					}
				}
			}
			list.add(o);
		}
		return list;
	}
	
	/***
	 * 读取单个sheet
	 * <p>导入Excel数据使用私有方法</p>
	 * @param sheet 单个sheet
	 * @param evaluator 解析公式结果
	 * @param columnArray 字段列数组
	 */
	private static List<Map<String, Object>> readSheet(Sheet sheet, FormulaEvaluator evaluator, String... columnArray) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		int firstRowNum = sheet.getFirstRowNum();
		int lastRowNum = sheet.getLastRowNum();
		// 从第二行开始读取数据
		for (int rowIndex = firstRowNum + 1; rowIndex <= lastRowNum; rowIndex++) {
			Row row = sheet.getRow(rowIndex);
			short firstCellNum = row.getFirstCellNum();
			// short maxColIx = row.getLastCellNum();
			Map<String, Object> rowMap = new HashMap<String, Object>();
			// 读取列的时候，按照设置好的字段列的数量长度循环读取
			for (short colIndex = firstCellNum; colIndex < columnArray.length; colIndex++) {
				Cell cell = row.getCell(new Integer(colIndex)+1); // 从第二列开始导入， 第一列序号列不做导入
				CellValue cellValue = evaluator.evaluate(cell);
				if (cellValue == null) {
					continue;
				}
				// 经过公式解析，最后只存在Boolean、Numeric和String三种数据类型，此外就是Error了
				// 其余数据类型，根据官方文档，完全可以忽略http://poi.apache.org/spreadsheet/eval.html
				switch (cellValue.getCellType()) {
					case Cell.CELL_TYPE_BOOLEAN:
						rowMap.put(columnArray[colIndex], cellValue.getBooleanValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						// 这里的日期类型会被转换为数字类型，需要判别后区分处理
						if (HSSFDateUtil.isCellDateFormatted(cell)) {
							rowMap.put(columnArray[colIndex], cell.getDateCellValue());
						} else {
							rowMap.put(columnArray[colIndex], cellValue.getNumberValue());
						}
						break;
					case Cell.CELL_TYPE_STRING:
						rowMap.put(columnArray[colIndex], cellValue.getStringValue());
						break;
					case Cell.CELL_TYPE_FORMULA:
						break;
					case Cell.CELL_TYPE_BLANK:
						break;
					case Cell.CELL_TYPE_ERROR:
						break;
					default:
						break;
				}
			}
			list.add(rowMap);
		}
		
		return list;
	}
	
	private static int getIndexOfArrayItem(String arrItem, String[] array) {
		int index = -1;
		for (int i = 0; i < array.length; i++) {
			if(array[i].equals(arrItem)) {
				index = i;
				break;
			}
		}
		return index;
	}
	
	/***
	 * 导出Excel数据
	 * @param sheetName sheet名称
	 * @param fileName 导出文件名
	 * @param list 要导出的数据
	 * @param response
	 * @throws IOException
	 */
	public static void exportDatas(String sheetName, String fileName, List<Map<String, Object>> list, HttpServletRequest request, HttpServletResponse response) throws IOException {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet(sheetName);
		int rowNum = 0;

		// 设置单元格样式(标题样式)
		HSSFCellStyle styleTitle = wb.createCellStyle();
		styleTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		styleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		styleTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		styleTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		// 对齐方式
		styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 字体
		HSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		styleTitle.setFont(font);
		
		// 设置单元格样式(数据值样式)
		HSSFCellStyle styleContent = wb.createCellStyle();
		styleContent.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		styleContent.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		styleContent.setBorderRight(HSSFCellStyle.BORDER_THIN);
		styleContent.setBorderTop(HSSFCellStyle.BORDER_THIN);
		styleContent.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		
		// 标题行
		HSSFRow rowTitle = sheet.createRow(rowNum++);
		
		int i = 0;
		HSSFCell cellH0 = rowTitle.createCell(i++);
		cellH0.setCellValue("序号");
		cellH0.setCellStyle(styleTitle);
		for(Map.Entry<String, Object> tmp : list.get(0).entrySet()) {
			HSSFCell cellH = rowTitle.createCell(i++);
			cellH.setCellValue(tmp.getKey());
			cellH.setCellStyle(styleTitle);
		}

		int cellOrder = 1;
		for (Map<String, Object> map : list) {
			HSSFRow row = sheet.createRow(rowNum++);
			int j = 0;
			// 序号
			HSSFCell cellC0 = row.createCell(j++);
			cellC0.setCellValue(cellOrder++);
			cellC0.setCellStyle(styleContent);
			
			for(Map.Entry<String, Object> entry : map.entrySet()) {
				HSSFCell cellC = row.createCell(j++);
				sheet.setColumnWidth(cellC.getColumnIndex(), 256 * (11 + 10));
				cellC.setCellValue(entry.getValue()+"");
				cellC.setCellStyle(styleContent);
			}
		}
		
		String agent = request.getHeader("User-Agent");
		// 火狐浏览器导出文件不会url解码
		if(StringUtil.isEmpty(agent)==false && agent.toLowerCase().indexOf("firefox")>-1) {
			response.setHeader("Content-disposition", "attachment;filename=\"" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1")+".xls\"");
		} else {
			response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName+".xls", "UTF-8"));
		}
		response.setContentType("application/vnd.ms-excel;charset=UTF-8");
		OutputStream out = response.getOutputStream();
		wb.write(out);
		out.flush();
		out.close();
	}
	
	
}

 

ProDataUtil.java  获取数据库表信息

package com.lw.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProDataUtil {

	private static String url = "jdbc:mysql://192.168.1.1:3306/dbName?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull";
	private static String username = "root";
	private static String password = "root";
	private static String driverClassName = "com.mysql.jdbc.Driver";

	/**
	 * 查询表的字段，封装成List
	 * 
	 * @param tableName 表名
	 * @param schemaName 数据库名
	 * @return
	 * @throws SQLException
	 */
	public static List<ColumnData> getMySqlColumnDatas(String tableName, String schemaName) throws SQLException {
		String SQLColumns = " select COLUMN_NAME,DATA_TYPE,column_comment,numeric_scale,numeric_precision,character_maximum_length,ordinal_position from information_schema.COLUMNS where table_name =  '" + tableName + "'" + " and TABLE_SCHEMA='" + schemaName + "' " + " ORDER by  ordinal_position";
		Connection con = getConnection();
		PreparedStatement ps = con.prepareStatement(SQLColumns);
		List<ColumnData> columnList = new ArrayList<ColumnData>();
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			String name = rs.getString(1);
			String lowName = getColumnBeanName(name); // 将XXX_XXX转换成xxxXxx的格式
			String type = rs.getString(2).toUpperCase(); // mysql 区分大小写
			String javaType = getType(rs.getString(2), rs.getString(4), rs.getString(5)); // mysql 区分大小写
			String comment = rs.getString(3);
			String dataScale = rs.getString(4);
			String dataPrecision = rs.getString(5);
			String dataMaxLength = rs.getString(6);
			
			ColumnData cd = new ColumnData(name, lowName, type, javaType, comment, dataScale, dataPrecision, dataMaxLength);
			columnList.add(cd);
		}
		rs.close();
		ps.close();
		con.close();
		return columnList;
	}

	public static Connection getConnection() throws SQLException {
		try{
			Class.forName(driverClassName);
		}catch(Exception e){
			e.printStackTrace();
		}
		return DriverManager.getConnection(url, username, password);
	}

	private static String getColumnBeanName(String column) {
		String[] split = column.split("_");
		StringBuffer columnVal = new StringBuffer();
		if (split.length > 1) {
			for (int i = 0; i < split.length; i++) {
				String colVal = "";
				if (i == 0) {
					colVal = split[i].toLowerCase();
					columnVal.append(colVal);
				} else {
					colVal = split[i].substring(0, 1).toUpperCase() + split[i].substring(1, split[i].length()).toLowerCase();
					columnVal.append(colVal);
				}
			}
			columnVal.toString();
		} else {
			String colVal = column.toLowerCase();
			columnVal.append(colVal);
		}
		return columnVal.toString();
	}

	/***
	 * 获取java类型
	 * @param type 数据库数据类型
	 * @param dataScale 小数位数
	 * @param dataPrecision 数据精度
	 * @return
	 */
    private static String getType(String type, String dataScale, String dataPrecision){
    	type=type.toLowerCase();
    	if("char".equalsIgnoreCase(type) || "varchar".equalsIgnoreCase(type)|| "varchar2".equalsIgnoreCase(type)){
			return "java.lang.String";
		}else if("NUMBER".equalsIgnoreCase(type)|| "numeric".equalsIgnoreCase(type)){//
			if((dataScale!=null&&!dataScale.equals("")&&!dataScale.equals("0"))){
				if(dataPrecision!=null&&dataPrecision.equals("38")){
					return "java.math.BigDecimal";
				}else{
					return "java.lang.Double";
				}
			}else {
				if(dataPrecision!=null&&dataPrecision.equals("38")){
					return "java.math.BigDecimal";
				}else{
					return "java.lang.Long";
				}
			}
		}else if("decimal".equalsIgnoreCase(type)){//
			return "java.math.BigDecimal";
		}else if( "DATE".equalsIgnoreCase(type)){
			return "java.util.Date";
		}else if( "DATETIME".equalsIgnoreCase(type)){
			return "java.util.Date";
		}else if( "BLOB".equalsIgnoreCase(type)){
			return "java.sql.Blob";
		}else if( "CLOB".equalsIgnoreCase(type)){
			return "java.sql.Clob";
		}else if("int".equalsIgnoreCase(type)){
			return "java.lang.Integer";
		}else if("TINYINT".equalsIgnoreCase(type)){
			return "java.lang.Boolean";
		}else if("double".equalsIgnoreCase(type)){
			return "java.math.BigDecimal";
		}else if("datetime".equalsIgnoreCase(type)){
			return "java.util.Date";
		}
    	return null;
    }

	
}