List of usage examples for org.apache.poi.ss.usermodel DateUtil getJavaDate
public static Date getJavaDate(double date)
From source file:com.asakusafw.testtools.excel.ExcelUtils.java
License:Apache License
/** * ?StringOption??/*w w w. j a va2 s. c o m*/ * @param cell ? * @return ? */ private StringOption getStringOption(HSSFCell cell) { String str; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: str = null; break; case Cell.CELL_TYPE_BOOLEAN: if (cell.getBooleanCellValue()) { str = "1"; } else { str = "0"; } break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { double d = cell.getNumericCellValue(); Date date = DateUtil.getJavaDate(d); str = dateTimeFormat.format(date); } else { double d = cell.getNumericCellValue(); str = Double.toString(d); str = str.replaceAll("\\.0*$", ""); } break; case Cell.CELL_TYPE_STRING: str = cell.getStringCellValue(); break; case Cell.CELL_TYPE_ERROR: case Cell.CELL_TYPE_FORMULA: default: String msg = createCellTypeMismatchExceptionMsg(cell, ""); throw new CellTypeMismatchException(msg); } StringOption stringOption = new StringOption(); stringOption.modify(str); return stringOption; }
From source file:com.asakusafw.testtools.excel.ExcelUtils.java
License:Apache License
/** * ?java.util.Data??//from w w w. j av a2 s . c o m * @param cell ? * @return ? */ private Date getDate(HSSFCell cell) { Date date; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: date = null; break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { double d = cell.getNumericCellValue(); date = DateUtil.getJavaDate(d); } else { String msg = createCellTypeMismatchExceptionMsg(cell, ""); throw new CellTypeMismatchException(msg); } break; case Cell.CELL_TYPE_STRING: String str = cell.getStringCellValue(); try { date = dateTimeFormat.parse(str); } catch (Exception e) { try { date = dateFormat.parse(str); } catch (Exception e2) { String msg = createCellTypeMismatchExceptionMsg(cell, ""); throw new CellTypeMismatchException(msg); } } break; case Cell.CELL_TYPE_BOOLEAN: case Cell.CELL_TYPE_ERROR: case Cell.CELL_TYPE_FORMULA: default: String msg = createCellTypeMismatchExceptionMsg(cell, ""); throw new CellTypeMismatchException(msg); } return date; }
From source file:com.cn.controller.OrderPlanController.java
/** * ?//from w ww . ja v a 2s . com * * @param fileName * @param planType * @return -1 -- ?, 0 -- ??, -2 -- ?, -3 -- ?? */ public int importData(String fileName, int planType) { InputStream inputStream = null; int templateType;//?: 0 -- ??? | 1 -- ??? | 2 -- ?SAP? try { File file = new File(fileName); inputStream = new FileInputStream(file); Sheet sheet; if (fileName.endsWith(".xls")) { HSSFWorkbook workbook = new HSSFWorkbook(inputStream); sheet = workbook.getSheetAt(0); } else if (fileName.endsWith(".xlsx")) { XSSFWorkbook workbook = new XSSFWorkbook(inputStream); sheet = workbook.getSheetAt(0); } else { logger.info("?Excel!"); return -2; } Row headRow = sheet.getRow(0); if (headRow.getPhysicalNumberOfCells() == 38) { templateType = 0; } else if (headRow.getPhysicalNumberOfCells() == 8) { templateType = 1; } else if (headRow.getPhysicalNumberOfCells() == 34) { templateType = 2; } else { logger.info("???!"); return -3; } ArrayList<OrderPlan> imports = new ArrayList<>(); for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) { Row row = sheet.getRow(i); if (null == row) { continue; } int cellNum = row.getPhysicalNumberOfCells(); //logger.info("count row num:" + sheet.getPhysicalNumberOfRows() + ",the row num is:" + i + ",count cell num is:" + cellNum); OrderPlan info = new OrderPlan(); // if (row.getCell(templateDataIndex[templateType][0]).getCellType() == Cell.CELL_TYPE_STRING) { info.setFinishTime(row.getCell(templateDataIndex[templateType][0]).getStringCellValue()); } else { if (DateUtil.isCellDateFormatted(row.getCell(templateDataIndex[templateType][0]))) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); Date date = DateUtil .getJavaDate(row.getCell(templateDataIndex[templateType][0]).getNumericCellValue()); info.setFinishTime(dateFormat.format(date)); } else { DecimalFormat df = new DecimalFormat("0"); info.setFinishTime( df.format(row.getCell(templateDataIndex[templateType][0]).getNumericCellValue())); } } // if (row.getCell(templateDataIndex[templateType][1]).getCellType() == Cell.CELL_TYPE_STRING) { info.setSendTime(row.getCell(templateDataIndex[templateType][1]).getStringCellValue()); } else { if (DateUtil.isCellDateFormatted(row.getCell(templateDataIndex[templateType][1]))) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); Date date = DateUtil .getJavaDate(row.getCell(templateDataIndex[templateType][1]).getNumericCellValue()); info.setSendTime(dateFormat.format(date)); } else { DecimalFormat df = new DecimalFormat("0"); info.setSendTime( df.format(row.getCell(templateDataIndex[templateType][1]).getNumericCellValue())); } } //? row.getCell(templateDataIndex[templateType][2]).setCellType(Cell.CELL_TYPE_STRING); info.setCarrierCode(row.getCell(templateDataIndex[templateType][2]).getStringCellValue()); //?? row.getCell(templateDataIndex[templateType][3]).setCellType(Cell.CELL_TYPE_STRING); info.setCarrierName(row.getCell(templateDataIndex[templateType][3]).getStringCellValue()); //??? row.getCell(templateDataIndex[templateType][4]).setCellType(Cell.CELL_TYPE_STRING); info.setPinMing(row.getCell(templateDataIndex[templateType][4]).getStringCellValue()); //? row.getCell(templateDataIndex[templateType][5]).setCellType(Cell.CELL_TYPE_STRING); info.setJianHao(row.getCell(templateDataIndex[templateType][5]).getStringCellValue()); //??? row.getCell(templateDataIndex[templateType][6]).setCellType(Cell.CELL_TYPE_STRING); info.setUnit(row.getCell(templateDataIndex[templateType][6]).getStringCellValue()); //? row.getCell(templateDataIndex[templateType][7]).setCellType(Cell.CELL_TYPE_NUMERIC); info.setCarCount((int) row.getCell(templateDataIndex[templateType][7]).getNumericCellValue()); imports.add(info); /* if (templateType == 0) { OrderPlan info = new OrderPlan(); // if (row.getCell(0).getCellType() == Cell.CELL_TYPE_STRING) { info.setFinishTime(row.getCell(0).getStringCellValue()); } else { if (DateUtil.isCellDateFormatted(row.getCell(0))) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); Date date = DateUtil.getJavaDate(row.getCell(0).getNumericCellValue()); info.setFinishTime(dateFormat.format(date)); } else { DecimalFormat df = new DecimalFormat("0"); info.setFinishTime(df.format(row.getCell(0).getNumericCellValue())); } } // if (row.getCell(1).getCellType() == Cell.CELL_TYPE_STRING) { info.setSendTime(row.getCell(1).getStringCellValue()); } else { if (DateUtil.isCellDateFormatted(row.getCell(1))) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); Date date = DateUtil.getJavaDate(row.getCell(1).getNumericCellValue()); info.setSendTime(dateFormat.format(date)); } else { DecimalFormat df = new DecimalFormat("0"); info.setSendTime(df.format(row.getCell(1).getNumericCellValue())); } } //? row.getCell(13).setCellType(Cell.CELL_TYPE_STRING); info.setCarrierCode(row.getCell(13).getStringCellValue()); //?? row.getCell(14).setCellType(Cell.CELL_TYPE_STRING); info.setCarrierName(row.getCell(14).getStringCellValue()); //??? row.getCell(16).setCellType(Cell.CELL_TYPE_STRING); info.setPinMing(row.getCell(16).getStringCellValue()); //? row.getCell(17).setCellType(Cell.CELL_TYPE_STRING); info.setJianHao(row.getCell(17).getStringCellValue()); //??? row.getCell(21).setCellType(Cell.CELL_TYPE_STRING); info.setUnit(row.getCell(21).getStringCellValue()); //? row.getCell(22).setCellType(Cell.CELL_TYPE_NUMERIC); info.setCarCount((int) row.getCell(22).getNumericCellValue()); imports.add(info); } else if (templateType == 1) { OrderPlan info = new OrderPlan(); // if (row.getCell(0).getCellType() == Cell.CELL_TYPE_STRING) { info.setFinishTime(row.getCell(0).getStringCellValue()); } else { if (DateUtil.isCellDateFormatted(row.getCell(0))) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); Date date = DateUtil.getJavaDate(row.getCell(0).getNumericCellValue()); info.setFinishTime(dateFormat.format(date)); } else { DecimalFormat df = new DecimalFormat("0"); info.setFinishTime(df.format(row.getCell(0).getNumericCellValue())); } } // if (row.getCell(1).getCellType() == Cell.CELL_TYPE_STRING) { info.setSendTime(row.getCell(1).getStringCellValue()); } else { if (DateUtil.isCellDateFormatted(row.getCell(1))) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); Date date = DateUtil.getJavaDate(row.getCell(1).getNumericCellValue()); info.setSendTime(dateFormat.format(date)); } else { DecimalFormat df = new DecimalFormat("0"); info.setSendTime(df.format(row.getCell(1).getNumericCellValue())); } } //? row.getCell(2).setCellType(Cell.CELL_TYPE_STRING); info.setCarrierCode(row.getCell(2).getStringCellValue()); //?? row.getCell(3).setCellType(Cell.CELL_TYPE_STRING); info.setCarrierName(row.getCell(3).getStringCellValue()); //??? row.getCell(4).setCellType(Cell.CELL_TYPE_STRING); info.setPinMing(row.getCell(4).getStringCellValue()); //? row.getCell(5).setCellType(Cell.CELL_TYPE_STRING); info.setJianHao(row.getCell(5).getStringCellValue()); //??? row.getCell(6).setCellType(Cell.CELL_TYPE_STRING); info.setUnit(row.getCell(6).getStringCellValue()); //? row.getCell(7).setCellType(Cell.CELL_TYPE_NUMERIC); info.setCarCount((int) row.getCell(7).getNumericCellValue()); imports.add(info); } else if (templateType == 2) { OrderPlan info = new OrderPlan(); // if (row.getCell(0).getCellType() == Cell.CELL_TYPE_STRING) { info.setFinishTime(row.getCell(0).getStringCellValue()); } else { if (DateUtil.isCellDateFormatted(row.getCell(0))) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); Date date = DateUtil.getJavaDate(row.getCell(0).getNumericCellValue()); info.setFinishTime(dateFormat.format(date)); } else { DecimalFormat df = new DecimalFormat("0"); info.setFinishTime(df.format(row.getCell(0).getNumericCellValue())); } } // if (row.getCell(1).getCellType() == Cell.CELL_TYPE_STRING) { info.setSendTime(row.getCell(1).getStringCellValue()); } else { if (DateUtil.isCellDateFormatted(row.getCell(1))) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); Date date = DateUtil.getJavaDate(row.getCell(1).getNumericCellValue()); info.setSendTime(dateFormat.format(date)); } else { DecimalFormat df = new DecimalFormat("0"); info.setSendTime(df.format(row.getCell(1).getNumericCellValue())); } } //? row.getCell(2).setCellType(Cell.CELL_TYPE_STRING); info.setCarrierCode(row.getCell(2).getStringCellValue()); //?? row.getCell(3).setCellType(Cell.CELL_TYPE_STRING); info.setCarrierName(row.getCell(3).getStringCellValue()); //??? row.getCell(4).setCellType(Cell.CELL_TYPE_STRING); info.setPinMing(row.getCell(4).getStringCellValue()); //? row.getCell(5).setCellType(Cell.CELL_TYPE_STRING); info.setJianHao(row.getCell(5).getStringCellValue()); //??? row.getCell(6).setCellType(Cell.CELL_TYPE_STRING); info.setUnit(row.getCell(6).getStringCellValue()); //? row.getCell(7).setCellType(Cell.CELL_TYPE_NUMERIC); info.setCarCount((int) row.getCell(7).getNumericCellValue()); imports.add(info); } */ } return batchAddData(planType, imports); } catch (FileNotFoundException ex) { logger.error("", ex); } catch (IOException ex) { logger.error("IO", ex); } finally { try { if (null != inputStream) { inputStream.close(); } } catch (IOException ex) { logger.error("?", ex); } } return -1; }
From source file:com.coul.common.excel.ImportExcel.java
License:Open Source License
/** * ??//from w w w . j a va 2 s. c o m * @param cls * @param groups */ public <E> List<E> getDataList(Class<E> cls, int... groups) throws InstantiationException, IllegalAccessException { List<Object[]> annotationList = Lists.newArrayList(); // Get annotation field Field[] fs = cls.getDeclaredFields(); for (Field f : fs) { ExcelField ef = f.getAnnotation(ExcelField.class); if (ef != null && (ef.type() == 0 || ef.type() == 2)) { if (groups != null && groups.length > 0) { boolean inGroup = false; for (int g : groups) { if (inGroup) { break; } for (int efg : ef.groups()) { if (g == efg) { inGroup = true; annotationList.add(new Object[] { ef, f }); break; } } } } else { annotationList.add(new Object[] { ef, f }); } } } // Get annotation method Method[] ms = cls.getDeclaredMethods(); for (Method m : ms) { ExcelField ef = m.getAnnotation(ExcelField.class); if (ef != null && (ef.type() == 0 || ef.type() == 2)) { if (groups != null && groups.length > 0) { boolean inGroup = false; for (int g : groups) { if (inGroup) { break; } for (int efg : ef.groups()) { if (g == efg) { inGroup = true; annotationList.add(new Object[] { ef, m }); break; } } } } else { annotationList.add(new Object[] { ef, m }); } } } // Field sorting Collections.sort(annotationList, new Comparator<Object[]>() { public int compare(Object[] o1, Object[] o2) { return new Integer(((ExcelField) o1[0]).sort()).compareTo(new Integer(((ExcelField) o2[0]).sort())); }; }); //log.debug("Import column count:"+annotationList.size()); // Get excel data List<E> dataList = Lists.newArrayList(); for (int i = this.getDataRowNum(); i < this.getLastDataRowNum(); i++) { E e = (E) cls.newInstance(); int column = 0; Row row = this.getRow(i); StringBuilder sb = new StringBuilder(); for (Object[] os : annotationList) { Object val = this.getCellValue(row, column++); if (val != null) { ExcelField ef = (ExcelField) os[0]; // If is dict type, get dict value if (StringUtils.isNotBlank(ef.dictType())) { //val = DictUtils.getDictValue(val.toString(), ef.dictType(), ""); //log.debug("Dictionary type value: ["+i+","+colunm+"] " + val); } // Get param type and type cast Class<?> valType = Class.class; if (os[1] instanceof Field) { valType = ((Field) os[1]).getType(); } else if (os[1] instanceof Method) { Method method = ((Method) os[1]); if ("get".equals(method.getName().substring(0, 3))) { valType = method.getReturnType(); } else if ("set".equals(method.getName().substring(0, 3))) { valType = ((Method) os[1]).getParameterTypes()[0]; } } //log.debug("Import value type: ["+i+","+column+"] " + valType); try { if (valType == String.class) { String s = String.valueOf(val.toString()); if (StringUtils.endsWith(s, ".0")) { val = StringUtils.substringBefore(s, ".0"); } else { val = String.valueOf(val.toString()); } } else if (valType == Integer.class) { val = Double.valueOf(val.toString()).intValue(); } else if (valType == Long.class) { val = Double.valueOf(val.toString()).longValue(); } else if (valType == Double.class) { val = Double.valueOf(val.toString()); } else if (valType == Float.class) { val = Float.valueOf(val.toString()); } else if (valType == Date.class) { val = DateUtil.getJavaDate((Double) val); } else { if (ef.fieldType() != Class.class) { val = ef.fieldType().getMethod("getValue", String.class).invoke(null, val.toString()); } else { val = Class .forName(this.getClass().getName().replaceAll( this.getClass().getSimpleName(), "fieldtype." + valType.getSimpleName() + "Type")) .getMethod("getValue", String.class).invoke(null, val.toString()); } } } catch (Exception ex) { log.info("Get cell value [" + i + "," + column + "] error: " + ex.toString()); val = null; } // set entity value if (os[1] instanceof Field) { Reflections.invokeSetter(e, ((Field) os[1]).getName(), val); } else if (os[1] instanceof Method) { String mthodName = ((Method) os[1]).getName(); if ("get".equals(mthodName.substring(0, 3))) { mthodName = "set" + StringUtils.substringAfter(mthodName, "get"); } Reflections.invokeMethod(e, mthodName, new Class[] { valType }, new Object[] { val }); } } sb.append(val + ", "); } dataList.add(e); log.debug("Read success: [" + i + "] " + sb.toString()); } return dataList; }
From source file:com.dataart.spreadsheetanalytics.engine.PoiWorkbookConverters.java
License:Apache License
public Date getDateCellValue() { return DateUtil.getJavaDate(getNumericCellValue()); }
From source file:com.ebay.xcelite.reader.BeanSheetReader.java
License:Apache License
private Object convertToFieldType(Object cellValue, Class<?> fieldType) { String value = String.valueOf(cellValue); if (fieldType == Double.class || fieldType == double.class) { return Double.valueOf(value); }/*from w w w .ja v a2s .c o m*/ if (fieldType == Integer.class || fieldType == int.class) { return Double.valueOf(value).intValue(); } if (fieldType == Short.class || fieldType == short.class) { return Double.valueOf(value).shortValue(); } if (fieldType == Long.class || fieldType == long.class) { return Double.valueOf(value).longValue(); } if (fieldType == Float.class || fieldType == float.class) { return Double.valueOf(value).floatValue(); } if (fieldType == Character.class || fieldType == char.class) { return value.charAt(0); } if (fieldType == Date.class) { return DateUtil.getJavaDate(Double.valueOf(value)); } return value; }
From source file:com.fengduo.spark.commons.file.excel.ImportExcel.java
License:Open Source License
/** * ??/*from w ww. jav a2 s.c om*/ * * @param cls * @param groups */ public <E> List<E> getDataList(Class<E> cls, int... groups) throws InstantiationException, IllegalAccessException { List<Object[]> annotationList = Lists.newArrayList(); // Get annotation field Field[] fs = cls.getDeclaredFields(); for (Field f : fs) { ExcelField ef = f.getAnnotation(ExcelField.class); if (ef != null && (ef.type() == 0 || ef.type() == 2)) { if (groups != null && groups.length > 0) { boolean inGroup = false; for (int g : groups) { if (inGroup) { break; } for (int efg : ef.groups()) { if (g == efg) { inGroup = true; annotationList.add(new Object[] { ef, f }); break; } } } } else { annotationList.add(new Object[] { ef, f }); } } } // Get annotation method Method[] ms = cls.getDeclaredMethods(); for (Method m : ms) { ExcelField ef = m.getAnnotation(ExcelField.class); if (ef != null && (ef.type() == 0 || ef.type() == 2)) { if (groups != null && groups.length > 0) { boolean inGroup = false; for (int g : groups) { if (inGroup) { break; } for (int efg : ef.groups()) { if (g == efg) { inGroup = true; annotationList.add(new Object[] { ef, m }); break; } } } } else { annotationList.add(new Object[] { ef, m }); } } } // Field sorting Collections.sort(annotationList, new Comparator<Object[]>() { public int compare(Object[] o1, Object[] o2) { return new Integer(((ExcelField) o1[0]).sort()).compareTo(new Integer(((ExcelField) o2[0]).sort())); }; }); // log.debug("Import column count:"+annotationList.size()); // Get excel data List<E> dataList = Lists.newArrayList(); for (int i = this.getDataRowNum(); i < this.getLastDataRowNum(); i++) { E e = (E) cls.newInstance(); int column = 0; Row row = this.getRow(i); StringBuilder sb = new StringBuilder(); for (Object[] os : annotationList) { Object val = this.getCellValue(row, column++); if (val != null) { ExcelField ef = (ExcelField) os[0]; // If is dict type, get dict value // if (StringUtils.isNotBlank(ef.dictType())) { // val = DictUtils.getDictValue(val.toString(), ef.dictType(), ""); // log.debug("Dictionary type value: ["+i+","+colunm+"] " + val); // } // Get param type and type cast Class<?> valType = Class.class; if (os[1] instanceof Field) { valType = ((Field) os[1]).getType(); } else if (os[1] instanceof Method) { Method method = ((Method) os[1]); if ("get".equals(method.getName().substring(0, 3))) { valType = method.getReturnType(); } else if ("set".equals(method.getName().substring(0, 3))) { valType = ((Method) os[1]).getParameterTypes()[0]; } } // log.debug("Import value type: ["+i+","+column+"] " + valType); try { if (valType == String.class) { String s = String.valueOf(val.toString()); if (StringUtils.endsWith(s, ".0")) { val = StringUtils.substringBefore(s, ".0"); } else { val = String.valueOf(val.toString()); } } else if (valType == Integer.class) { val = Double.valueOf(val.toString()).intValue(); } else if (valType == Long.class) { val = Double.valueOf(val.toString()).longValue(); } else if (valType == Double.class) { val = Double.valueOf(val.toString()); } else if (valType == Float.class) { val = Float.valueOf(val.toString()); } else if (valType == Date.class) { val = DateUtil.getJavaDate((Double) val); } else { if (ef.fieldType() != Class.class) { val = ef.fieldType().getMethod("getValue", String.class).invoke(null, val.toString()); } else { val = Class .forName(this.getClass().getName().replaceAll( this.getClass().getSimpleName(), "fieldtype." + valType.getSimpleName() + "Type")) .getMethod("getValue", String.class).invoke(null, val.toString()); } } } catch (Exception ex) { log.info("Get cell value [" + i + "," + column + "] error: " + ex.toString()); val = null; } // set entity value if (os[1] instanceof Field) { Reflections.invokeSetter(e, ((Field) os[1]).getName(), val); } else if (os[1] instanceof Method) { String mthodName = ((Method) os[1]).getName(); if ("get".equals(mthodName.substring(0, 3))) { mthodName = "set" + StringUtils.substringAfter(mthodName, "get"); } Reflections.invokeMethod(e, mthodName, new Class[] { valType }, new Object[] { val }); } } sb.append(val + ", "); } dataList.add(e); log.debug("Read success: [" + i + "] " + sb.toString()); } return dataList; }
From source file:com.funtl.framework.smoke.core.commons.excel.ImportExcel.java
License:Apache License
/** * ??/* w w w . jav a2 s .c o m*/ * * @param cls * @param groups */ public <E> List<E> getDataList(Class<E> cls, int... groups) throws InstantiationException, IllegalAccessException { List<Object[]> annotationList = Lists.newArrayList(); // Get annotation field Field[] fs = cls.getDeclaredFields(); for (Field f : fs) { ExcelField ef = f.getAnnotation(ExcelField.class); if (ef != null && (ef.type() == 0 || ef.type() == 2)) { if (groups != null && groups.length > 0) { boolean inGroup = false; for (int g : groups) { if (inGroup) { break; } for (int efg : ef.groups()) { if (g == efg) { inGroup = true; annotationList.add(new Object[] { ef, f }); break; } } } } else { annotationList.add(new Object[] { ef, f }); } } } // Get annotation method Method[] ms = cls.getDeclaredMethods(); for (Method m : ms) { ExcelField ef = m.getAnnotation(ExcelField.class); if (ef != null && (ef.type() == 0 || ef.type() == 2)) { if (groups != null && groups.length > 0) { boolean inGroup = false; for (int g : groups) { if (inGroup) { break; } for (int efg : ef.groups()) { if (g == efg) { inGroup = true; annotationList.add(new Object[] { ef, m }); break; } } } } else { annotationList.add(new Object[] { ef, m }); } } } // Field sorting Collections.sort(annotationList, new Comparator<Object[]>() { public int compare(Object[] o1, Object[] o2) { return new Integer(((ExcelField) o1[0]).sort()).compareTo(new Integer(((ExcelField) o2[0]).sort())); } ; }); //log.debug("Import column count:"+annotationList.size()); // Get excel data List<E> dataList = Lists.newArrayList(); for (int i = this.getDataRowNum(); i < this.getLastDataRowNum(); i++) { E e = (E) cls.newInstance(); int column = 0; Row row = this.getRow(i); StringBuilder sb = new StringBuilder(); for (Object[] os : annotationList) { Object val = this.getCellValue(row, column++); if (val != null) { ExcelField ef = (ExcelField) os[0]; // If is dict type, get dict value if (StringUtils.isNotBlank(ef.dictType())) { val = DictUtils.getDictValue(val.toString(), ef.dictType(), ""); //log.debug("Dictionary type value: ["+i+","+colunm+"] " + val); } // Get param type and type cast Class<?> valType = Class.class; if (os[1] instanceof Field) { valType = ((Field) os[1]).getType(); } else if (os[1] instanceof Method) { Method method = ((Method) os[1]); if ("get".equals(method.getName().substring(0, 3))) { valType = method.getReturnType(); } else if ("set".equals(method.getName().substring(0, 3))) { valType = ((Method) os[1]).getParameterTypes()[0]; } } //log.debug("Import value type: ["+i+","+column+"] " + valType); try { if (valType == String.class) { String s = String.valueOf(val.toString()); if (StringUtils.endsWith(s, ".0")) { val = StringUtils.substringBefore(s, ".0"); } else { val = String.valueOf(val.toString()); } } else if (valType == Integer.class) { val = Double.valueOf(val.toString()).intValue(); } else if (valType == Long.class) { val = Double.valueOf(val.toString()).longValue(); } else if (valType == Double.class) { val = Double.valueOf(val.toString()); } else if (valType == Float.class) { val = Float.valueOf(val.toString()); } else if (valType == Date.class) { val = DateUtil.getJavaDate((Double) val); } else { if (ef.fieldType() != Class.class) { val = ef.fieldType().getMethod("getValue", String.class).invoke(null, val.toString()); } else { val = Class .forName(this.getClass().getName().replaceAll( this.getClass().getSimpleName(), "fieldtype." + valType.getSimpleName() + "Type")) .getMethod("getValue", String.class).invoke(null, val.toString()); } } } catch (Exception ex) { log.info("Get cell value [" + i + "," + column + "] error: " + ex.toString()); val = null; } // set entity value if (os[1] instanceof Field) { Reflections.invokeSetter(e, ((Field) os[1]).getName(), val); } else if (os[1] instanceof Method) { String mthodName = ((Method) os[1]).getName(); if ("get".equals(mthodName.substring(0, 3))) { mthodName = "set" + StringUtils.substringAfter(mthodName, "get"); } Reflections.invokeMethod(e, mthodName, new Class[] { valType }, new Object[] { val }); } } sb.append(val + ", "); } dataList.add(e); log.debug("Read success: [" + i + "] " + sb.toString()); } return dataList; }
From source file:com.green.common.utils.excel.ImportExcel.java
License:Open Source License
/** * ??/*www . ja v a2s. c o m*/ * @param cls * @param groups */ public <E> List<E> getDataList(Class<E> cls, int... groups) throws InstantiationException, IllegalAccessException { List<Object[]> annotationList = Lists.newArrayList(); // Get annotation field Field[] fs = cls.getDeclaredFields(); for (Field f : fs) { ExcelField ef = f.getAnnotation(ExcelField.class); if (ef != null && (ef.type() == 0 || ef.type() == 2)) { if (groups != null && groups.length > 0) { boolean inGroup = false; for (int g : groups) { if (inGroup) { break; } for (int efg : ef.groups()) { if (g == efg) { inGroup = true; annotationList.add(new Object[] { ef, f }); break; } } } } else { annotationList.add(new Object[] { ef, f }); } } } // Get annotation method Method[] ms = cls.getDeclaredMethods(); for (Method m : ms) { ExcelField ef = m.getAnnotation(ExcelField.class); if (ef != null && (ef.type() == 0 || ef.type() == 2)) { if (groups != null && groups.length > 0) { boolean inGroup = false; for (int g : groups) { if (inGroup) { break; } for (int efg : ef.groups()) { if (g == efg) { inGroup = true; annotationList.add(new Object[] { ef, m }); break; } } } } else { annotationList.add(new Object[] { ef, m }); } } } // Field sorting Collections.sort(annotationList, new Comparator<Object[]>() { public int compare(Object[] o1, Object[] o2) { return new Integer(((ExcelField) o1[0]).sort()).compareTo(new Integer(((ExcelField) o2[0]).sort())); }; }); //log.debug("Import column count:"+annotationList.size()); // Get excel data List<E> dataList = Lists.newArrayList(); for (int i = this.getDataRowNum(); i < this.getLastDataRowNum(); i++) { E e = (E) cls.newInstance(); int column = 0; Row row = this.getRow(i); StringBuilder sb = new StringBuilder(); for (Object[] os : annotationList) { Object val = this.getCellValue(row, column++); if (val != null) { ExcelField ef = (ExcelField) os[0]; // If is dict type, get dict value if (StringUtils.isNotBlank(ef.dictType())) { val = DictUtils.getDictValue(val.toString(), ef.dictType(), ""); //log.debug("Dictionary type value: ["+i+","+colunm+"] " + val); } // Get param type and type cast Class<?> valType = Class.class; if (os[1] instanceof Field) { valType = ((Field) os[1]).getType(); } else if (os[1] instanceof Method) { Method method = ((Method) os[1]); if ("get".equals(method.getName().substring(0, 3))) { valType = method.getReturnType(); } else if ("set".equals(method.getName().substring(0, 3))) { valType = ((Method) os[1]).getParameterTypes()[0]; } } //log.debug("Import value type: ["+i+","+column+"] " + valType); try { if (valType == String.class) { String s = String.valueOf(val.toString()); if (StringUtils.endsWith(s, ".0")) { val = StringUtils.substringBefore(s, ".0"); } else { val = String.valueOf(val.toString()); } } else if (valType == Integer.class) { val = Double.valueOf(val.toString()).intValue(); } else if (valType == Long.class) { val = Double.valueOf(val.toString()).longValue(); } else if (valType == Double.class) { val = Double.valueOf(val.toString()); } else if (valType == Float.class) { val = Float.valueOf(val.toString()); } else if (valType == Date.class) { val = DateUtil.getJavaDate((Double) val); } else { if (ef.fieldType() != Class.class) { val = ef.fieldType().getMethod("getValue", String.class).invoke(null, val.toString()); } else { val = Class .forName(this.getClass().getName().replaceAll( this.getClass().getSimpleName(), "fieldtype." + valType.getSimpleName() + "Type")) .getMethod("getValue", String.class).invoke(null, val.toString()); } } } catch (Exception ex) { log.info("Get cell value [" + i + "," + column + "] error: " + ex.toString()); val = null; } // set entity value if (os[1] instanceof Field) { Reflections.invokeSetter(e, ((Field) os[1]).getName(), val); } else if (os[1] instanceof Method) { String mthodName = ((Method) os[1]).getName(); if ("get".equals(mthodName.substring(0, 3))) { mthodName = "set" + StringUtils.substringAfter(mthodName, "get"); } Reflections.invokeMethod(e, mthodName, new Class[] { valType }, new Object[] { val }); } } sb.append(val + ", "); } dataList.add(e); log.debug("Read success: [" + i + "] " + sb.toString()); } return dataList; }
From source file:com.hauldata.dbpa.file.book.XlsxSourceSheet.java
License:Apache License
private Object fromXLSX(Cell cell) { if (cell == null) { return null; }//from ww w. ja v a 2 s . c om switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: return null; case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue(); case Cell.CELL_TYPE_NUMERIC: double numericValue = cell.getNumericCellValue(); return DateUtil.isCellDateFormatted(cell) ? DateUtil.getJavaDate(numericValue) : (Double) numericValue; case Cell.CELL_TYPE_STRING: default: return cell.getStringCellValue(); } }