List of usage examples for org.apache.poi.ss.usermodel DateUtil getJavaDate
public static Date getJavaDate(double date)
From source file:ImporteerExcelsheet.java
public void importeerExcelsheet(String filePath) { try {/*w w w .jav a 2s .com*/ FileInputStream file = new FileInputStream(new File(filePath)); if (filePath.toLowerCase().indexOf(xlsx.toLowerCase()) != -1) { XSSFWorkbook wbXlsx = new XSSFWorkbook(file); sheet = wbXlsx.getSheetAt(0); Row row = sheet.getRow(3); Cell cell = row.getCell(1); try { // convert String dd-mm-yyyy naar Date yyyy-mm-dd String excelDatum1 = cell.getStringCellValue(); Date date = new SimpleDateFormat("dd-MM-yyyy").parse(excelDatum1); this.excelDatum = this.formatter.format(date); } catch (Exception e) { double date1 = cell.getNumericCellValue(); //convert excel double naar datum Date date2 = DateUtil.getJavaDate((double) date1); this.excelDatum = new SimpleDateFormat("yyyy-MM-dd").format(date2); } //Iterate through each rows one by one rowIterator = sheet.iterator(); } else { HSSFWorkbook wbXls = new HSSFWorkbook(file); sheet1 = wbXls.getSheetAt(0); Row row = sheet1.getRow(3); Cell cell = row.getCell(1); // convert String dd-mm-yyyy naar Date yyyy-mm-dd try { String excelDatum1 = cell.getStringCellValue(); Date date = new SimpleDateFormat("dd-MM-yyyy").parse(excelDatum1); this.excelDatum = this.formatter.format(date); } catch (Exception e) { double date1 = cell.getNumericCellValue(); //convert excel double naar datum Date date2 = DateUtil.getJavaDate((double) date1); this.excelDatum = new SimpleDateFormat("yyyy-MM-dd").format(date2); } //Iterate through each rows one by one rowIterator = sheet1.iterator(); } //Iterate through each rows one by one while (rowIterator.hasNext()) { Row row = rowIterator.next(); //skip first 5 rows if (row.getRowNum() == 0 || row.getRowNum() == 1 || row.getRowNum() == 2 || row.getRowNum() == 3 || row.getRowNum() == 4) { continue; } //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); //Check the cell type and format accordingly switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: // System.out.print(cell.getNumericCellValue() + "\t"); break; case Cell.CELL_TYPE_STRING: // System.out.print(cell.getStringCellValue() + "\t"); break; case Cell.CELL_TYPE_BLANK: // System.out.print(cell.getStringCellValue() + "\t"); break; case Cell.CELL_TYPE_FORMULA: // System.out.print(cell.getStringCellValue() + "\t"); break; } } // Tabel client int kaartnummer = (int) row.getCell(0).getNumericCellValue(); String naam = row.getCell(1).getStringCellValue(); String naamPartner = row.getCell(2).getStringCellValue(); String telefoonnummer = row.getCell(3).getStringCellValue(); String email = row.getCell(4).getStringCellValue(); String mobiel = row.getCell(5).getStringCellValue(); int aantalPersonen = (int) row.getCell(6).getNumericCellValue(); int aantalPersonenInDeNorm = (int) row.getCell(7).getNumericCellValue(); double gebruikInMaanden = (double) row.getCell(8).getNumericCellValue(); String idSoort = row.getCell(9).getStringCellValue(); //convert excel double naar datum double datumUitgifteId1 = row.getCell(10).getNumericCellValue(); if (datumUitgifteId1 == 0.0) { this.datumUitgifteId = null; } else { Date datumUitgifteId2 = DateUtil.getJavaDate((double) datumUitgifteId1); this.datumUitgifteId = new SimpleDateFormat("yyyy-MM-dd").format(datumUitgifteId2); } //////////////////////////////////////////////////////////////////////////////////// String idNummer = row.getCell(11).getStringCellValue(); String plaatsUitgifteId = row.getCell(12).getStringCellValue(); String adres = row.getCell(13).getStringCellValue(); String postcode = row.getCell(14).getStringCellValue(); String plaats = row.getCell(15).getStringCellValue(); String status = row.getCell(16).getStringCellValue(); // Tabel intake // kaartnummer gaat hier ook in de query String intaker = row.getCell(17).getStringCellValue(); double intakeDatum1 = row.getCell(18).getNumericCellValue(); //convert excel double naar datum Date intakeDatum2 = DateUtil.getJavaDate((double) intakeDatum1); String intakeDatum = new SimpleDateFormat("yyyy-MM-dd").format(intakeDatum2); ///////////////////////////////////////////////////////////////////////// double startDatumUitgifte1 = row.getCell(19).getNumericCellValue(); //convert excel double naar datum Date startDatumUitgifte2 = DateUtil.getJavaDate((double) startDatumUitgifte1); String startDatumUitgifte = new SimpleDateFormat("yyyy-MM-dd").format(startDatumUitgifte2); ////////////////////////////////////////////////////////////////////////////////// double datumHerintake1 = row.getCell(20).getNumericCellValue(); //convert excel double naar datum Date datumHerintake2 = DateUtil.getJavaDate((double) datumHerintake1); String datumHerintake = new SimpleDateFormat("yyyy-MM-dd").format(datumHerintake2); ///////////////////////////////////////////////////////////////////////////////// // Tabel Stopt double datumStopzetting1 = row.getCell(21).getNumericCellValue(); //convert excel numbers naar datum Date datumStopzetting2 = DateUtil.getJavaDate((double) datumStopzetting1); String datumStopzetting = new SimpleDateFormat("yyyy-MM-dd").format(datumStopzetting2); ////////////////////////////////////////////////////////////////////////////////////// String redenStopzetting = row.getCell(22).getStringCellValue(); // Tabel verwijzer String verwijzerNaam = row.getCell(23).getStringCellValue(); String verwijzersDoorContactpersoon1 = row.getCell(24).getStringCellValue(); String verwijzersDoorContactpersoon = verwijzersDoorContactpersoon1.toLowerCase(); String verwijzersDoorTelefoonnummer = row.getCell(25).getStringCellValue(); String verwijzersDoorEmail = row.getCell(26).getStringCellValue(); String verwijzersNaar = row.getCell(27).getStringCellValue(); String verwijzersNaarContactpersoon = row.getCell(28).getStringCellValue(); String verwijzersNaarTelefoonnummer = row.getCell(29).getStringCellValue(); String verwijzersNaarEmail = row.getCell(30).getStringCellValue(); // Uitgiftepunt gaat in de tabel intake String uitgiftepunt = row.getCell(31).getStringCellValue(); // PakketSoort gaat in de tabel client String pakketSoort = row.getCell(32).getStringCellValue(); // Query's om de excelsheet in de database te krijgen SQLExcelSheetInsert excelSQL = new SQLExcelSheetInsert(); SQLget getSQL = new SQLget(); // Losse query uit de tabel om bepaalde gegevens te krijgen om te controleren of die al bestaat of niet int Verwijzer = getSQL.getVerwijzernr(verwijzerNaam, verwijzersDoorContactpersoon); int checkKaartnr = getSQL.getCheckKaartnummer(kaartnummer); int checkUitgiftepunt = getSQL.getUitgiftepunt(uitgiftepunt); if (Verwijzer == 0) { excelSQL.insertExcelVerwijzer(verwijzerNaam, verwijzersDoorContactpersoon, verwijzersDoorTelefoonnummer, verwijzersDoorEmail, verwijzersNaar, verwijzersNaarContactpersoon, verwijzersNaarTelefoonnummer, verwijzersNaarEmail); } if (checkUitgiftepunt == 0) { int maxVolgorde = getSQL.getCheckVolgordeLijst(); excelSQL.insertUitgiftepunt(uitgiftepunt, maxVolgorde); } int Verwijzer2 = getSQL.getVerwijzernr(verwijzerNaam, verwijzersDoorContactpersoon); /// Wanneer er geen kaartnummer is die bekend gaat die door naar insert if (checkKaartnr == 0) { // Wanneer kaartnummer alles behalve 0 is insert die if (kaartnummer > 0) { excelSQL.insertExcelClient(kaartnummer, naam, naamPartner, telefoonnummer, email, mobiel, aantalPersonen, aantalPersonenInDeNorm, gebruikInMaanden, idSoort, this.datumUitgifteId, idNummer, plaatsUitgifteId, adres, postcode, plaats, status, pakketSoort, Verwijzer2); } else { break; } } else { excelSQL.updateExcelClient(kaartnummer, naam, naamPartner, telefoonnummer, email, mobiel, aantalPersonen, aantalPersonenInDeNorm, gebruikInMaanden, idSoort, this.datumUitgifteId, idNummer, plaatsUitgifteId, adres, postcode, plaats, status, pakketSoort, Verwijzer2); } int checkIntake = getSQL.getCheckIntake(intakeDatum, startDatumUitgifte, datumHerintake, kaartnummer); if (checkIntake == 0) { excelSQL.insertExcelIntake(intaker, intakeDatum, startDatumUitgifte, datumHerintake, kaartnummer, uitgiftepunt); int intakeId = getSQL.getIntakeId(kaartnummer); excelSQL.insertExcelStopt(datumStopzetting, redenStopzetting, intakeId); } // Kan upgedate worden aan een knop voor een query aan status in de table voedselpakket wel of niet opgehaald String status1 = null; String checkStatus = getSQL.getStatus(kaartnummer); this.pakketAantal = getSQL.getPakketAantal(kaartnummer); int intakeId = getSQL.getIntakeId(kaartnummer); int checkPakket = getSQL.getPakket(this.excelDatum, intakeId); if (checkPakket == 0) { if (checkStatus != null) { if (checkStatus.equals("Actief")) { int intakeId2 = getSQL.getIntakeId(kaartnummer); excelSQL.insertVoedselpakket(this.excelDatum, this.pakketAantal, status1, intakeId2, uitgiftepunt); } } } } file.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:apm.common.utils.excel.ImportExcel.java
License:Open Source License
/** * ??/*from w ww.jav a 2 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 = Dicts.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) { 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:at.mukprojects.exclycore.dao.XLSXReader.java
License:Open Source License
private ExclyDate readDate(Cell cell, int type) throws Exception { ExclyDate output = null;//from w w w . jav a2 s.com if (type == Cell.CELL_TYPE_STRING) { String data = cell.getStringCellValue(); if (isNumericGerman(data)) { Number number = NumberFormat.getNumberInstance(Locale.GERMAN).parse(data); output = new ExclyDate(DateUtil.getJavaDate(number.intValue())); } else if (isNumericUK(data)) { Number number = NumberFormat.getNumberInstance(Locale.UK).parse(data); output = new ExclyDate(DateUtil.getJavaDate(number.intValue())); } else if (data.equals("") || data.equals(" ") || data.trim().equals("-")) { output = new ExclyDateBlank(); } else { ExclyDate parsedDate = parse(cell.getStringCellValue()); output = parsedDate; } } else if (type == Cell.CELL_TYPE_BLANK) { output = new ExclyDateBlank(); } else if (type == Cell.CELL_TYPE_FORMULA) { int formulaType = cell.getCachedFormulaResultType(); output = readDate(cell, formulaType); } else if (DateUtil.isCellDateFormatted(cell)) { Date data = cell.getDateCellValue(); output = new ExclyDate(data); } else if (type == Cell.CELL_TYPE_NUMERIC) { double data = cell.getNumericCellValue(); output = new ExclyDate(DateUtil.getJavaDate(data)); } else if (type == Cell.CELL_TYPE_ERROR) { output = new ExclyDateError(); } else { log.warn("The reader was unable to find a valid parser for the cell [Row, Column] (" + cell.getRowIndex() + ", " + cell.getColumnIndex() + ")"); output = new ExclyDateError(); } return output; }
From source file:br.sp.telesul.service.ExportServiceImpl.java
@Override public List<Funcionario> readExcelDocument() { try {//from w ww . jav a2s . c o m List<Funcionario> funcionariosExcel = new ArrayList<>(); ClassLoader classLoader = Thread.currentThread().getContextClassLoader(); // FileInputStream fl = new FileInputStream(new File("C:\\Matriz1.xlsx")); Workbook wb = new XSSFWorkbook(classLoader.getResourceAsStream("Matriz1.xlsx")); Sheet firstSheet = wb.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); while (iterator.hasNext()) { Row nextRow = iterator.next(); int row = nextRow.getRowNum(); // System.out.println("Row start" + row); Iterator<Cell> cellIterator = nextRow.cellIterator(); Funcionario f = new Funcionario(); Formacao fm = new Formacao(); Idioma id = new Idioma(); int column = 0; while (cellIterator.hasNext()) { Cell nextCell = cellIterator.next(); int columnIndex = nextCell.getColumnIndex(); column = columnIndex; // System.out.println("Valor" + getCellValue(nextCell)); // System.out.println("Index: " + columnIndex); if (row > 0) { switch (columnIndex) { case 1: f.setArea((String) getCellValue(nextCell)); break; case 2: Date dt = new Date(); if (!getCellValue(nextCell).toString().isEmpty()) { try { dt = DateUtil.getJavaDate((Double) getCellValue(nextCell)); } catch (ClassCastException cce) { SimpleDateFormat formatter = new SimpleDateFormat("dd-MMM-yyyy"); dt = formatter.parse((String) getCellValue((nextCell))); } ; } f.setDtAdmissao(dt); break; case 3: f.setCargo((String) getCellValue(nextCell)); break; case 4: f.setNome((String) getCellValue(nextCell)); break; case 5: f.setGestor((String) getCellValue(nextCell)); break; case 9: fm.setNivel((String) getCellValue(nextCell)); break; case 10: fm.setCurso((String) getCellValue(nextCell)); break; case 11: fm.setInstituicaoo((String) getCellValue(nextCell)); break; case 12: String typeEnum = (String) getCellValue(nextCell); if (!typeEnum.isEmpty()) { id.setNome(Language.valueOf(typeEnum.trim())); } break; case 13: String typeEnumNivel = (String) getCellValue(nextCell); if (!typeEnumNivel.isEmpty()) { id.setNivel(Nivel.valueOf(typeEnumNivel.trim())); } break; } } } List<Formacao> listFm = new ArrayList<>(); listFm.add(fm); f.setFormacoes(listFm); List<Idioma> listId = new ArrayList<>(); listId.add(id); f.setIdiomas(listId); if (row > 0) { funcionariosExcel.add(f); } } wb.close(); // fl.close(); // for (Funcionario fc : funcionariosExcel) { // System.out.println(fc.getNome()); // } return funcionariosExcel; } catch (Exception e) { e.printStackTrace(); return null; } }
From source file:cn.afterturn.easypoi.excel.imports.CellValueService.java
License:Apache License
/** * ??/*from ww w. j a v a2s . com*/ * * @param cell * @param entity * @return */ private Object getCellValue(String classFullName, Cell cell, ExcelImportEntity entity) { if (cell == null) { return ""; } Object result = null; if ("class java.util.Date".equals(classFullName) || "class java.sql.Date".equals(classFullName) || ("class java.sql.Time").equals(classFullName) || ("class java.time.Instant").equals(classFullName) || ("class java.time.LocalDate").equals(classFullName) || ("class java.time.LocalDateTime").equals(classFullName) || ("class java.sql.Timestamp").equals(classFullName)) { //FIX: ?yyyyMMdd cell.getDateCellValue() ? if (CellType.NUMERIC == cell.getCellType() && DateUtil.isCellDateFormatted(cell)) { result = DateUtil.getJavaDate(cell.getNumericCellValue()); } else { String val = ""; try { val = cell.getStringCellValue(); } catch (Exception e) { cell.setCellType(CellType.STRING); val = cell.getStringCellValue(); } result = getDateData(entity, val); if (result == null) { return null; } } if (("class java.time.Instant").equals(classFullName)) { result = ((Date) result).toInstant(); } else if (("class java.time.LocalDate").equals(classFullName)) { result = ((Date) result).toInstant().atZone(ZoneId.systemDefault()).toLocalDate(); } else if (("class java.time.LocalDateTime").equals(classFullName)) { result = ((Date) result).toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime(); } else if (("class java.sql.Date").equals(classFullName)) { result = new java.sql.Date(((Date) result).getTime()); } else if (("class java.sql.Time").equals(classFullName)) { result = new Time(((Date) result).getTime()); } else if (("class java.sql.Timestamp").equals(classFullName)) { result = new Timestamp(((Date) result).getTime()); } } else { switch (cell.getCellType()) { case STRING: result = cell.getRichStringCellValue() == null ? "" : cell.getRichStringCellValue().getString(); break; case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { if ("class java.lang.String".equals(classFullName)) { result = formateDate(entity, cell.getDateCellValue()); } } else { result = readNumericCell(cell); } break; case BOOLEAN: result = Boolean.toString(cell.getBooleanCellValue()); break; case BLANK: break; case ERROR: break; case FORMULA: try { result = readNumericCell(cell); } catch (Exception e1) { try { result = cell.getRichStringCellValue() == null ? "" : cell.getRichStringCellValue().getString(); } catch (Exception e2) { throw new RuntimeException("???", e2); } } break; default: break; } } return result; }
From source file:cn.afterturn.easypoi.excel.imports.CellValueService.java
License:Apache License
/** * ??/*from w ww .j a va 2 s . co m*/ * * @param entity * @param value * @return * @author JueYue * 20131126 */ private Date getDateData(ExcelImportEntity entity, String value) { if (StringUtils.isNotEmpty(entity.getFormat()) && StringUtils.isNotEmpty(value)) { SimpleDateFormat format = new SimpleDateFormat(entity.getFormat()); try { return format.parse(value); } catch (ParseException e) { try { return DateUtil.getJavaDate(Double.parseDouble(value)); } catch (NumberFormatException ex) { LOGGER.error("?,?:{},:{}", entity.getFormat(), value); throw new ExcelImportException(ExcelImportEnum.GET_VALUE_ERROR); } } } return null; }
From source file:cn.mypandora.util.MyExcelUtil.java
License:Apache License
/** * @param workbook /*from w w w. j a va 2 s .c o m*/ * @param fieldNames ?? * @param sheetName ??? * @return */ private static List<Map<String, String>> execRead(Workbook workbook, String fieldNames, String... sheetName) { String[] strKey = fieldNames.split(","); List<Map<String, String>> listMap = new ArrayList<>(); int i = 1; try { Sheet sheet; if (sheetName.length == 0) { sheet = workbook.getSheetAt(0); } else { sheet = workbook.getSheet(sheetName[0]); } while (true) { Row row = sheet.getRow(i); if (row == null) { break; } Map<String, String> map = new HashMap<String, String>(); map.put("rowid", String.valueOf(row.getRowNum())); for (int keyIndex = 0; keyIndex < strKey.length; keyIndex++) { Cell cell; cell = row.getCell(keyIndex); String cellValue = ""; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: { // ?cell?Date if (DateUtil.isCellDateFormatted(cell)) { // Date?CellDate SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); cellValue = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue())); } // else { // ??Cell Integer num = new Integer((int) cell.getNumericCellValue()); cellValue = String.valueOf(num); } break; } case Cell.CELL_TYPE_STRING: cellValue = cell.getRichStringCellValue().getString(); break; case Cell.CELL_TYPE_BOOLEAN: System.out.println(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: System.out.println(cell.getCellFormula()); break; default: cellValue = " "; } } map.put(strKey[keyIndex], cellValue); } listMap.add(map); i++; } } catch (Exception e) { logger.debug("?" + i + "??"); throw new RuntimeException(e); } return listMap; }
From source file:cn.orignzmn.shopkepper.common.utils.excel.ImportExcel.java
License:Open Source License
/** * ??/* w w w . j a va 2 s. c o m*/ * @param cls * @param groups */ public <E> List<E> getDataList(Class<E> cls, Map<String, Object> inportInfo, int... groups) throws InstantiationException, IllegalAccessException { List<Object[]> annotationList = Lists.newArrayList(); // Get annotation field Field[] fs = cls.getDeclaredFields(); ExcelSheet esarr = cls.getAnnotation(ExcelSheet.class); String annTitle = ""; if (esarr == null) return Lists.newArrayList(); annTitle = esarr.value(); 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(); //??? if (!"".equals(annTitle)) { String title = StringUtils.trim(this.getCellValue(this.getRow(0), 0).toString()); if (!annTitle.equals(title)) { inportInfo.put("success", false); inportInfo.put("message", "??"); return 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.aistor.common.utils.excel.ImportExcel.java
License:Open Source License
/** * ??/*from w w w. ja v a 2 s . com*/ * @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+","+colunm+"] " + valType); try { if (valType == String.class) { 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.alkacon.opencms.excelimport.CmsExcelContent.java
License:Open Source License
/** * Reads the excel file row by row. Fills the excel import internal structure which is column wise.<p> * //from w w w . j a v a 2s . c o m * @param in the document input stream * * @throws IOException if something goes wring */ private void readExcelColumnContents(InputStream in) throws IOException { HSSFWorkbook excelWb = new HSSFWorkbook(in); HSSFSheet sheet = excelWb.getSheetAt(0); int rowsNumber = sheet.getPhysicalNumberOfRows(); if (rowsNumber > 0) { // loop over all rows from excel // do not read first column, because here are only user raw names for (int rowCounter = 1; rowCounter < sheet.getPhysicalNumberOfRows(); rowCounter++) { HSSFRow row = sheet.getRow(rowCounter); if ((row != null)) { // get number of rows in excel if ((rowCounter) > m_rowNumber) { m_rowNumber = rowCounter; } // loop over all columns in this row for (int columnCounter = 0; columnCounter < row.getLastCellNum(); columnCounter++) { CmsExcelColumn cmsExcelCol = (CmsExcelColumn) m_colContents.get(new Integer(columnCounter)); if (cmsExcelCol != null) { // read cell HSSFCell cell = row.getCell((short) columnCounter); if (cell != null) { String text = null; try { // read cell content from excel switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: case Cell.CELL_TYPE_ERROR: // ignore all blank or error cells break; case Cell.CELL_TYPE_NUMERIC: // check for date if (DateUtil.isCellDateFormatted(cell) || DateUtil.isValidExcelDate(cell.getNumericCellValue())) { // valid date Date date = DateUtil.getJavaDate(cell.getNumericCellValue()); text = new Long(date.getTime()).toString(); } else { // no valid date text = Double.toString(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: text = Boolean.toString(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_STRING: default: text = cell.getStringCellValue(); break; } // add to column list cmsExcelCol.addNewCellValue(rowCounter, text); m_colContents.put(new Integer(columnCounter), cmsExcelCol); } catch (Exception e) { if (LOG.isErrorEnabled()) { LOG.error(e.toString()); } } } else { // add to column list cmsExcelCol.addNewCellValue(rowCounter, ""); m_colContents.put(new Integer(columnCounter), cmsExcelCol); } } } } } } }