poi excel导入工具类

poi excel导入工具类ImportUtil

import java.beans.BeanInfo;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Date;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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 [e-mail]
 * @version 2.0
 * @since 2019-11-19
 *
 */
public class ImportUtil {

	private final static String excel2003L = ".xls"; // 2003- 版本的excel
	private final static String excel2007U = ".xlsx"; // 2007+ 版本的excel
	private static final String DATE_FORMAT = "yyyy-MM-dd";
	
	/**
	 * 获取IO流中的数据，组装成List<T>对象
	 * @param in	输入流
	 * @param fileName	文件名称，根据后缀判断excel版本
	 * @param fields	导入字段名称
	 * @param fieldsClazz	导入字段类型，目前只支持基本类型
	 * @param clazz		bean的class
	 * @return	class对应的实例对象List集合
	 * @throws Exception
	 */
	public static <T> List<T> getExcel(InputStream in, String fileName, String[] fields, Class<?>[] fieldsClazz, Class<T> clazz) throws Exception {
		List<T> list = new ArrayList<>();
		if(fields == null || fieldsClazz == null || fields.length != fieldsClazz.length){
			throw new Exception("需导入的列设置错误！");
		}
		Map<String, Class<?>> fieldMap = new HashMap<>();
		for (int i = 0, j = fields.length; i < j; i++) {
			fieldMap.put(fields[i], fieldsClazz[i]);
		}
		// 创建Excel工作薄
		Workbook work = getWorkbook(in, fileName);
		if (null == work) {
			throw new Exception("Excel工作薄为空！");
		}
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;

		sheet = work.getSheetAt(0);
		if (sheet == null) {
			return list;
		}
		
		// 遍历当前sheet中的所有行
		for (int j = sheet.getFirstRowNum(), y = sheet.getLastRowNum(); j <= y; j++) {
			row = sheet.getRow(j);
			if (row == null || row.getFirstCellNum() == j) {
				continue;
			}
			
			// 遍历所有的列
			Map<String, Object> fieldDatas = new HashMap<>();
			for (int k = row.getFirstCellNum(), z = row.getLastCellNum(); k < z; k++) {
				cell = row.getCell(k);
				if(cell == null){
					continue;
				}
				Object value = getCellValue(cell);
				if(value == null){
					continue;
				}
				fieldDatas.put(fields[k], value);					
			}
			// bean添加到集合
			list.add(cashToBean(fieldMap, fieldDatas, clazz));			
		}
		return list;
	}
	
	/**
	 * 将读取的行数据转为实例对象
	 * @param fieldsClazz	字段名称和字段类型集合
	 * @param fieldDatas	行数据
	 * @param clazz			实例对象的class
	 * @return
	 * @throws Exception
	 */
	private static <T> T cashToBean(Map<String, Class<?>> fieldsClazz, Map<String, Object> fieldDatas, Class<T> clazz)
		throws Exception{
		T entry = clazz.newInstance();
		BeanInfo beanInfo = Introspector.getBeanInfo(clazz);
		PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
		for (PropertyDescriptor property : propertyDescriptors) {
			String fieldName = property.getName();
			if (fieldDatas.containsKey(fieldName)) {
				Method setter = property.getWriteMethod();
				Object _value = cashFieldClass(fieldsClazz.get(fieldName), fieldDatas.get(fieldName));
				setter.invoke(entry, _value);
			}
		}
		return entry;
	}

	/**
	 * 属性类型转换
	 * @param fieldsClazz	字段名称和字段类型集合
	 * @param fieldData		行数据
	 * @param fieldName		字段名称
	 * @return
	 * @throws ParseException
	 */
	private static Object cashFieldClass(Class<?> fieldClazz, Object value) throws ParseException {
		Object _value = value;
		if(!fieldClazz.equals(value.getClass())){
			String valueStr = value.toString();
			if(fieldClazz.equals(Long.class)){
				_value = Long.valueOf(valueStr);
			}else if(fieldClazz.equals(Integer.class)){
				_value = Integer.valueOf(valueStr);
			}else if(fieldClazz.equals(Date.class)){
				_value = new SimpleDateFormat(DATE_FORMAT).parse(valueStr);
			}else if(fieldClazz.equals(BigDecimal.class)){
				_value = new BigDecimal(valueStr);
			}else if(fieldClazz.equals(Boolean.class)){
				_value = Boolean.valueOf(valueStr);
			}else if(fieldClazz.equals(Double.class)){
				_value = Double.valueOf(valueStr);
			}else if(fieldClazz.equals(Float.class)){
				_value = Float.valueOf(valueStr);
			}
		}
		return _value;
	}

	/**
	 * 根据文件后缀，自适应上传文件的版本
	 * 
	 * @param inStr
	 * @param fileName
	 * @return
	 * @throws Exception
	 */
	public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
		Workbook wb = null;
		String fileType = fileName.substring(fileName.lastIndexOf(".")).toLowerCase();
		if (excel2003L.equals(fileType)) {
			wb = new HSSFWorkbook(inStr); // 2003-
		} else if (excel2007U.equals(fileType)) {
			wb = new XSSFWorkbook(inStr); // 2007+
		} else {
			throw new Exception("解析的文件格式有误！");
		}
		return wb;
	}

	/**
	 * 对表格中数值进行格式化
	 * 
	 * @param cell
	 * @return
	 */
	public static Object getCellValue(Cell cell) {
		Object value = null;
		DecimalFormat df = new DecimalFormat("0"); // 格式化number String字符
		SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT); // 日期格式化
		DecimalFormat df2 = new DecimalFormat("0.000"); // 格式化数字
		
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:
			value = cell.getRichStringCellValue().getString();
			break;
		case Cell.CELL_TYPE_NUMERIC:
			if ("General".equals(cell.getCellStyle().getDataFormatString())) {
				value = df.format(cell.getNumericCellValue());
			} else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
				value = sdf.format(cell.getDateCellValue());
			} else {
				value = df2.format(cell.getNumericCellValue());
			}
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			value = cell.getBooleanCellValue();
			break;
		case Cell.CELL_TYPE_BLANK:
			value = "";
			break;
		default:
			break;
		}
		return value;
	}
	
}

 

 示例代码如下：

    /**
     * 持仓划转导入
     * @param upload
     * @param request
     * @param response
     */
    @RequestMapping("/modules/manage/artPos/import.htm")
    public void importPos(@RequestParam(value = "upload") MultipartFile upload,
    		HttpServletRequest request , HttpServletResponse response)throws Exception{
    	String[] fields = {"fromPhone", "toPhone", "artCode", "transferNumber", "transferPrice", "remark"};
    	Class<?>[] fieldsClazz = {String.class, String.class, String.class, Integer.class, BigDecimal.class, String.class};
    	List<ArtPositionTransferModel> list = ImportUtil.getExcel(upload.getInputStream(), 
    			upload.getOriginalFilename(), fields, fieldsClazz, ArtPositionTransferModel.class);
    	artPositionTransferService.saveBatch(list);
    	ServletUtils.success(response);
    }