excel工具类
import com.iport.framework.util.ValidateUtil; import org.apache.commons.lang3.StringUtils; import org.apache.log4j.Logger; import org.apache.poi.POIXMLDocument; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.poifs.filesystem.POIFSFileSystem; 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.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.IOException; import java.io.InputStream; import java.io.PushbackInputStream; import java.lang.reflect.Field; import java.sql.Time; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.*; /** * Created by admin on 2016/8/26. */ public class PoiUtil { private static Logger logger = Logger.getLogger(PoiUtil.class); public static <T> List<T> getExcelData(String mapFilePath, Class<T> cla, InputStream inputStream) throws Exception{ List<T> dataList = new ArrayList<T>(); //获取配置数据 JaxbUtil jaxb = new JaxbUtil(TemplateExcelMap.class); TemplateExcelMap template = jaxb.fromPath(mapFilePath, false); List<ExcelMap> mapList = template.getColumnList(); Map<String,String> nameFieldMap = new HashMap<String,String>(); Map<Integer,Field> IndexFieldMap = new HashMap<Integer,Field>(); Map<Integer,ExcelMap> mapMap = new HashMap<Integer,ExcelMap>(); Map<String,ExcelMap> strMap = new HashMap<String,ExcelMap>(); for(ExcelMap m : mapList){ nameFieldMap.put(m.getColumnName().trim(), m.getFieldName()); strMap.put(m.getColumnName().trim(), m); } Workbook book = getWorkbook(inputStream); Sheet sheet = book.getSheetAt(0); Row row0 = sheet.getRow(0); int celNum = row0.getPhysicalNumberOfCells(); //头部数据获取映射 for(int i=0; i<celNum; i++){ Cell cell = row0.getCell(i); String headDesc = cell.getStringCellValue(); if(!ValidateUtil.isEmpty(headDesc)){ if(nameFieldMap.containsKey(headDesc.trim())){ String fieldName = nameFieldMap.get(headDesc.trim()); IndexFieldMap.put(i, getField(fieldName, cla)); mapMap.put(i, strMap.get(headDesc.trim())); } } } long s = System.currentTimeMillis(); int rowNum = sheet.getPhysicalNumberOfRows(); Set<Integer> keys = IndexFieldMap.keySet(); if(keys.size() > 0){ for(int i=1;i<rowNum;i++){ Row row = sheet.getRow(i); T vo = (T)cla.newInstance(); for(Integer columIndex : keys){ Cell cel = row.getCell(columIndex); Field field = IndexFieldMap.get(columIndex); ExcelMap mapping = mapMap.get(columIndex); Object o = getCellValue(cel, field.getType(), mapping); //try{ field.set(vo,o); // }catch (Exception e){ // e.printStackTrace(); //} } dataList.add(vo); } } long e = System.currentTimeMillis(); logger.debug("数据导入解析耗时："+(e-s)); return dataList; } public static Workbook getWorkbook(InputStream inputStream) throws IOException, InvalidFormatException { Workbook book = null; if (!(inputStream.markSupported())) { inputStream = new PushbackInputStream(inputStream, 8); } if (POIFSFileSystem.hasPOIFSHeader(inputStream)) { book = new HSSFWorkbook(inputStream); } else if (POIXMLDocument.hasOOXMLHeader(inputStream)) { book = new XSSFWorkbook(OPCPackage.open(inputStream)); } return book; } private static Field getField(String filedName, Class cla) throws Exception{ Field field = cla.getDeclaredField(filedName); if(field!=null){ field.setAccessible(true); } return field; }; /** * 获取单元格内容 * @param cell * @param fieldClass * @param mapping * @return * @throws Exception */ private static Object getCellValue(Cell cell, Class fieldClass, ExcelMap mapping) throws Exception{ if (cell == null) { return null; } Object result = null; if ("class java.util.Date".equals(fieldClass.toString()) || ("class java.sql.Time").equals(fieldClass.toString())) { if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { result = cell.getDateCellValue(); } else { cell.setCellType(Cell.CELL_TYPE_STRING); result = getDateData(mapping, cell.getStringCellValue()); } if ( result != null && ("class java.sql.Time").equals(fieldClass.toString())) { result = new Time(((Date) result).getTime()); } } else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { double doubleVal = cell.getNumericCellValue(); if("class java.lang.Double".equals(fieldClass.toString())){ result = doubleVal; }else{ long longVal = Math.round(cell.getNumericCellValue()); if(Double.parseDouble(longVal + ".0") == doubleVal){ try{ result = (int) longVal; }catch (Exception e){ result = longVal; } }else { result = doubleVal; } } } else if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) { result = cell.getBooleanCellValue(); } else { result = cell.getStringCellValue(); if(ValidateUtil.isEmpty((String)result)){ result = null; } } return result; } /** * 获取日期类型数据 * * @Author JueYue * @date 2013年11月26日 * @param entity * @param value * @return */ private static Date getDateData(ExcelMap entity, String value) { String formatStr = entity.getFormat(); if(!ValidateUtil.isEmpty(formatStr)){ String[] formats = formatStr.split(";"); for(int i = 0; i<formats.length; i++){ try{ Date d = formartData(formats[i], value); if(d!=null){ return d; } }catch (Exception e){ logger.error(formats[i]+"日期转换"+value+"异常" + e.getMessage()); } } } return null; } private static Date formartData(String formatStr, String value){ if (StringUtils.isNotEmpty(formatStr) && StringUtils.isNotEmpty(value)) { SimpleDateFormat format = new SimpleDateFormat(formatStr); try { return format.parse(value); } catch (ParseException e) { throw new RuntimeException("Excel 值获取失败"); } } return null; } }