Java tutorial
/* * Copyright 2002-2015 by bafeimao.net * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package net.bafeimao.umbrella.support.data.entity; import com.google.common.base.Converter; import com.google.common.base.Preconditions; import net.bafeimao.umbrella.annotation.IgnoreParsing; import net.bafeimao.umbrella.annotation.PrintExecutionTime; import org.apache.poi.ss.usermodel.*; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.lang.reflect.Field; import java.util.HashMap; import java.util.LinkedList; import java.util.Map; /** * Excel???? * * @author bafeimao */ public class ExcelEntityParser extends AbstractEntityParser { private static final Logger LOGGER = LoggerFactory.getLogger(ExcelEntityParser.class); private Map<String, Map<String, Integer>> sheetColumnIndexesMap = new HashMap<String, Map<String, Integer>>(); /** * Excel?Sheet???,?clazz * <p/> * TODO {@link PrintExecutionTime}?AOP? */ @Override @PrintExecutionTime public <E extends DataEntity> LinkedList<E> parse(Class<E> entityClass) throws EntityParseException { LinkedList<E> entities = null; InputStream inputStream = null; try { ExcelMapping mapping = entityClass.getAnnotation(ExcelMapping.class); String fileName = "e:\\" + mapping.file(); String sheetName = mapping.sheet(); inputStream = new FileInputStream(fileName); // FileInputStream?Excel? Workbook wb = WorkbookFactory.create(inputStream); // Workbook,?excelsheet if (wb != null && wb.getNumberOfSheets() > 0) { Sheet sheet = wb.getSheet(sheetName); // ??? if (sheet.getRow(0) != null) { entities = parse0(entityClass, sheet); } } } catch (Exception e) { LOGGER.error("{}", e); } finally { try { if (inputStream != null) inputStream.close(); } catch (IOException e) { LOGGER.error("{}", e); } } return entities; } @Override public void registerConverter(Class<?> dataType, Converter<?, ?> converter) { convertersByType.put(dataType, converter); } private <E extends DataEntity> LinkedList<E> parse0(Class<E> entityClass, Sheet sheet) throws EntityParseException { LinkedList<E> retList = new LinkedList<E>(); try { // ???(??) for (int rowNum = 2; rowNum <= sheet.getLastRowNum(); rowNum++) { // LOGGER.info("Parsing {} with data row #{} ...", entityClass.getSimpleName(), rowNum + 1); E instance = entityClass.newInstance(); for (Field field : getEntityFields(entityClass)) { if (field.isAnnotationPresent(IgnoreParsing.class)) { continue; } field.setAccessible(true); int colNum = getColumnIndex(sheet, field.getName()); if (colNum != -1) { Cell cell = sheet.getRow(rowNum).getCell(colNum); if (cell != null) { try { String cellValue = this.getCellValue(cell); // LOGGER.debug("Assigning {}.{} = {} ({}))", entityClass.getSimpleName() // , field.getName(), cellValue, field.getType().getSimpleName()); this.assignFieldValue(instance, field, cellValue); } catch (IllegalAccessException e) { LOGGER.error("{}", e); } catch (IllegalArgumentException e) { LOGGER.error("{}", e); } catch (NullPointerException e) { LOGGER.error("{}", e); } catch (ExceptionInInitializerError e) { LOGGER.error("{}", e); } catch (Exception e) { throw new EntityParseException("(" + "name:" + field.getName() // + ", type:" + fieldType.getSimpleName() // + ", value:" + cellValue + ", entity:" + instance.getClass().getSimpleName() + ")?:" + e.getMessage(), e); } } } } retList.add(instance); } } catch (Exception e) { LOGGER.error("{}", e); } return retList; } private void assignFieldValue(Object instance, Field field, String cellValue) throws Exception { Object fieldValue = null; if (field.getType().equals(String.class)) { fieldValue = cellValue; } else { Converter<String, ?> converter = getConverter(field); if (converter == null) { throw new Exception(String.format("No converter found for field : s%' with type :'s%'", field.getName(), field.getType().getSimpleName())); } fieldValue = converter.convert(cellValue); } field.set(instance, fieldValue); } private String getCellValue(Cell cell) { Preconditions.checkNotNull("cell"); Object retVal = null; switch (cell.getCellType()) { case Cell.CELL_TYPE_FORMULA: // Get the type of Formula switch (cell.getCachedFormulaResultType()) { case Cell.CELL_TYPE_STRING: retVal = cell.getStringCellValue(); break; case Cell.CELL_TYPE_NUMERIC: retVal = cell.getNumericCellValue(); break; default: } // retVal = formulaEval.evaluate(cell).formatAsString(); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) retVal = cell.getDateCellValue(); else retVal = cell.getNumericCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: retVal = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_STRING: retVal = cell.getStringCellValue(); break; default: retVal = null; } return retVal == null ? null : retVal.toString(); } private int getColumnIndex(Sheet sheet, String colName) { Map<String, Integer> columnIndexesMap = sheetColumnIndexesMap.get(sheet.getSheetName()); if (columnIndexesMap == null) { columnIndexesMap = new HashMap<String, Integer>(); Row titleRow = sheet.getRow(1); int colNum = titleRow.getLastCellNum(); for (int i = titleRow.getFirstCellNum(); i < colNum; i++) { if (titleRow.getCell(i) != null) { columnIndexesMap.put(titleRow.getCell(i).getStringCellValue(), i); } } sheetColumnIndexesMap.put(sheet.getSheetName(), columnIndexesMap); } Integer index = columnIndexesMap.get(colName); return index == null ? -1 : index; } }