List of usage examples for org.apache.poi.ss.usermodel FormulaEvaluator evaluate
CellValue evaluate(Cell cell);
From source file:eu.esdihumboldt.hale.io.xls.XLSUtil.java
License:Open Source License
/** * Extract the text from a given cell. Formulas are evaluated, for blank or * error cells <code>null</code> is returned * /*from www .j av a 2 s. c o m*/ * @param cell the cell * @param evaluator the formula evaluator * @return the cell text */ public static String extractText(Cell cell, FormulaEvaluator evaluator) { if (cell == null) return null; if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { // do this check here as the evaluator seems to return null on a // blank return null; } CellValue value = evaluator.evaluate(cell); switch (value.getCellType()) { case Cell.CELL_TYPE_BLANK: return null; case Cell.CELL_TYPE_BOOLEAN: return String.valueOf(value.getBooleanValue()); case Cell.CELL_TYPE_NUMERIC: // number formatting double number = value.getNumberValue(); if (number == Math.floor(number)) { // it's an integer return String.valueOf((int) number); } return String.valueOf(value.getNumberValue()); case Cell.CELL_TYPE_STRING: return value.getStringValue(); case Cell.CELL_TYPE_FORMULA: // will not happen as we used the evaluator case Cell.CELL_TYPE_ERROR: // fall through default: return null; } }
From source file:excel.Reader.java
public void print() { System.out.println("START PRINT"); SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd"); int columnWidth = 15; FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); Sheet sheet = wb.getSheetAt(0);/*from w w w . j a va 2s . c om*/ for (Row row : sheet) { //System.out.print("r"); for (Cell cell : row) { //CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex()); //System.out.print(cellRef.formatAsString()); //System.out.print(" - "); // System.out.print("c"); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: //System.out.print("s"); System.out.printf("%-" + columnWidth + "s", cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.print("d"); if (DateUtil.isCellDateFormatted(cell)) { System.out.printf("%-" + columnWidth + "s", df.format(cell.getDateCellValue())); } else { if ((cell.getNumericCellValue() % 1.0) != 0.0) System.out.printf("%-" + columnWidth + ".2f", cell.getNumericCellValue()); else System.out.printf("%-" + columnWidth + ".0f", cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: //System.out.print("b"); System.out.printf("%-" + columnWidth + "s", cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: CellValue val = evaluator.evaluate(cell); //System.out.print("f"); switch (val.getCellType()) { case Cell.CELL_TYPE_STRING: System.out.printf("%-" + columnWidth + "s", val.getStringValue()); break; case Cell.CELL_TYPE_NUMERIC: System.out.printf("%-" + columnWidth + ".2f", val.getNumberValue()); break; case Cell.CELL_TYPE_BOOLEAN: System.out.printf("%-" + columnWidth + "s", val.getBooleanValue()); break; default: System.out.printf("%-" + columnWidth + "s", ""); } break; default: System.out.print(""); } } System.out.println(); } }
From source file:fyp.POI.POI.java
public static void main(String[] args) throws Exception { FileInputStream fis = new FileInputStream(new File("FA_AAX.xlsx")); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sheet = wb.getSheetAt(0);//from w w w . ja va2 s . c o m FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); CellReference cr = new CellReference("C4"); Row row = sheet.getRow(cr.getRow()); Cell cell = row.getCell(cr.getCol()); CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: boolean truefalse = cellValue.getBooleanValue(); System.out.println("Boolean" + truefalse); break; case Cell.CELL_TYPE_NUMERIC: double NumericCheck = cellValue.getNumberValue(); if (NumericCheck % 1 == 0) { int Integer = (int) NumericCheck; System.out.println("Integer" + Integer); } else { double Dbl = NumericCheck; System.out.println("Double" + Dbl); } break; case Cell.CELL_TYPE_STRING: String str = cellValue.getStringValue(); System.out.println("String" + str.substring(3)); break; case Cell.CELL_TYPE_BLANK: System.out.println("Blank"); break; case Cell.CELL_TYPE_ERROR: break; } fis.close(); }
From source file:fyp.POI.POIFunction.java
public double poiToGetDbl(String FileAddress, String CellDetails, int SheetNum) throws NullPointerException { double result = 0.0; try {//from ww w .j ava 2 s .c o m FileInputStream fis = new FileInputStream(new File(FileAddress)); XSSFWorkbook wb = null; try { wb = new XSSFWorkbook(fis); } catch (IOException e) { System.out.print("Error detected: " + e); } XSSFSheet sheet = wb.getSheetAt(SheetNum); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); try { CellReference cr = new CellReference(CellDetails); Row row = sheet.getRow(cr.getRow()); Cell cell = row.getCell(cr.getCol()); //System.out.println("CELL "+ cell); checkNullNumeric(cell); CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BLANK: result = 0.0; break; case Cell.CELL_TYPE_ERROR: result = 0.0; break; case Cell.CELL_TYPE_NUMERIC: result = cellValue.getNumberValue(); break; } } catch (NullPointerException n) { result = 0.0; } fis.close(); } catch (IOException e) { System.out.print("Error detected: " + e); } return result; }
From source file:fyp.POI.POIFunction.java
public double poiToGetInt(String FileAddress, String CellDetails, int SheetNum) throws FileNotFoundException { double result = 0.0; try {/*from www . j av a 2s . c o m*/ FileInputStream fis = new FileInputStream(new File(FileAddress)); XSSFWorkbook wb = null; try { wb = new XSSFWorkbook(fis); } catch (IOException e) { System.out.print("Error detected: " + e); } XSSFSheet sheet = wb.getSheetAt(SheetNum); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); try { CellReference cr = new CellReference(CellDetails); Row row = sheet.getRow(cr.getRow()); Cell cell = row.getCell(cr.getCol()); checkNullNumeric(cell); CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_NUMERIC: result = cellValue.getNumberValue(); break; case Cell.CELL_TYPE_BLANK: result = 0; break; case Cell.CELL_TYPE_ERROR: result = 0; break; } } catch (NullPointerException n) { result = 0; } fis.close(); } catch (IOException e) { System.out.print("Error detected: " + e); } return (int) result; }
From source file:fyp.POI.POIFunction.java
public String poiToGetString(String FileAddress, String CellDetails, int SheetNum) throws FileNotFoundException { String result = "NOTFOUND"; try {//from w ww . java 2 s . com FileInputStream fis = new FileInputStream(new File(FileAddress)); XSSFWorkbook wb = null; try { wb = new XSSFWorkbook(fis); } catch (IOException e) { System.out.print("Error detected: " + e); } XSSFSheet sheet = wb.getSheetAt(SheetNum); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); try { CellReference cr = new CellReference(CellDetails); Row row = sheet.getRow(cr.getRow()); Cell cell = row.getCell(cr.getCol()); checkNullString(cell); CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_STRING: result = cellValue.getStringValue(); break; case Cell.CELL_TYPE_BLANK: result = " "; break; case Cell.CELL_TYPE_ERROR: result = " "; break; } } catch (NullPointerException n) { result = " "; } fis.close(); } catch (IOException e) { System.out.print("Error detected: " + e); } return result; }
From source file:icalendarconverter.ExcelConverter.java
public List<ScheduleClass> readExcel() throws FileNotFoundException, IOException { ArrayList<ScheduleClass> scheduleList = new ArrayList<>(); FileInputStream fis = new FileInputStream(pathFile); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sheet = wb.getSheetAt(0);//from www . j av a 2 s . c om Iterator<Row> rowIterator = sheet.iterator(); CellRangeAddress add; int colNoIdx = 0; ArrayList<String> dosen = new ArrayList<>(); ArrayList<Integer> idxDosen = new ArrayList<>(); ArrayList<Integer> colDosen = new ArrayList<>(); ArrayList<String> location = new ArrayList<>(); int idxNumber = 0; ArrayList<Integer> locationIdx = new ArrayList<>(); outerloop: for (int j = 0; j < sheet.getLastRowNum(); j++) { row = sheet.getRow(j); for (int f = 0; f < row.getLastCellNum(); f++) { Cell cell = row.getCell(f); if (cell.getCellType() == Cell.CELL_TYPE_STRING && cell.getStringCellValue().equalsIgnoreCase("No.")) { rowNoIdx = j; colNoIdx = cell.getColumnIndex(); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING && cell.getStringCellValue().equalsIgnoreCase("Nama Mata Kuliah")) { colMatkulIdx = cell.getColumnIndex(); break outerloop; } } } //System.out.println("col matkul = "+colMatkulIdx); System.out.println("sheet = " + sheet.getLastRowNum()); outerloop2: for (int i = 0; i < sheet.getLastRowNum(); i++) { outerloop: for (int j = 0; j < row.getLastCellNum(); j++) { row = sheet.getRow(i); if (row == null) { i = sheet.getLastRowNum(); break outerloop2; } Cell cell = row.getCell(j); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); if (cell.getColumnIndex() == colNoIdx && i > rowNoIdx + 3 && cell.getCellType() != Cell.CELL_TYPE_BLANK && evaluator.evaluate(cell).getCellType() != Cell.CELL_TYPE_NUMERIC) { i = sheet.getLastRowNum(); break outerloop2; } if (cell.getColumnIndex() == colNoIdx && i > rowNoIdx + 3 && cell.getCellType() == Cell.CELL_TYPE_BLANK) { i = i + 1; break outerloop; } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 1)) { if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { i = i + 1; break outerloop; } String delims = "[,. ]"; String[] sumary = cell.getStringCellValue().split(delims); for (int l = 0; l < sumary.length; l++) { if (sumary[l].equalsIgnoreCase("Mrt")) { sumary[l] = "3"; } if (sumary[l].equalsIgnoreCase("Okt")) { sumary[l] = "10"; } if (sumary[l].equalsIgnoreCase("`16")) { sumary[l] = "2016"; } } lc = LocalDate.of(Integer.parseInt(sumary[5]), Integer.parseInt(sumary[3]), Integer.parseInt(sumary[2])); // System.out.println("LC = "+lc); // sp = new SimpleDateFormat("EEEE, MMMM d, yyyy"); // String b = sumary[3] + "/" + sumary[2] + "/" + sumary[5]; // date = new Date(b); //System.out.println(sp.format(date)); } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 2)) { if (cell.getStringCellValue().equalsIgnoreCase("LIBUR")) { i = i + 1; break outerloop; } else { if (cell.getStringCellValue().equalsIgnoreCase("Shift 1") || cell.getStringCellValue().equalsIgnoreCase("Shift 2")) { CellReference cr = new CellReference(cell.getRowIndex() + 1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); String delimsJam = "[-]"; String[] arrJam = c.getStringCellValue().split(delimsJam); for (int k = 0; k < arrJam.length; k++) { arrJam[k] = arrJam[k].replace('.', ':'); } // indoFormatter = DateTimeFormatter // .ofLocalizedTime(FormatStyle.SHORT) // .withLocale(Locale.getDefault()); //System.out.println("I3 = " + i); lt = LocalTime.parse(arrJam[0]); //System.out.println(lt+"-"+lt.plusHours(2)); } else { String delimsJam = "[-]"; String[] arrJam = cell.getStringCellValue().split(delimsJam); for (int k = 0; k < arrJam.length; k++) { arrJam[k] = arrJam[k].replace('.', ':'); } // indoFormatter = DateTimeFormatter // .ofLocalizedTime(FormatStyle.SHORT) // .withLocale(Locale.getDefault()); //System.out.println("I3 = " + i); lt = LocalTime.parse(arrJam[0]); //System.out.println(lt+"-"+lt.plusHours(2)); } } } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == colMatkulIdx) { subject = cell.getStringCellValue(); //System.out.println("Subject = "+subject); } if (cell.getRowIndex() > rowNoIdx && cell.getColumnIndex() >= colMatkulIdx + 1 && cell.getColumnIndex() < row.getLastCellNum()) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { // location.add(String.valueOf((int)cell.getNumericCellValue())); // locationIdx.add(cell.getColumnIndex()); } if (cell.getCellType() == Cell.CELL_TYPE_STRING) { if (cell.getStringCellValue().contains(":")) { String[] splt = cell.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { dosen.add(splt2[l].trim()); location.add("Lab"); //System.out.println(splt2[l] + "= lab"); } } else { CellReference cr = new CellReference(1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); if (!cell.getStringCellValue().isEmpty()) { dosen.add(cell.getStringCellValue().trim()); location.add(String.valueOf((int) c.getNumericCellValue()).trim()); } //System.out.print(cell.getStringCellValue() + " Ruang =" + (int) c.getNumericCellValue() + " "); } } if (cell.getCellType() == Cell.CELL_TYPE_BLANK && cell.getRowIndex() > 2) { CellReference cr = new CellReference(cell.getRowIndex() - 1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); CellReference cr2 = new CellReference(1, cell.getColumnIndex()); Row row3 = sheet.getRow(cr2.getRow()); Cell c2 = row3.getCell(cr2.getCol()); if (c.getStringCellValue().contains(":")) { String[] splt = c.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { dosen.add("".trim()); location.add(""); //System.out.println(splt2[l] + "= lab"); } } else { if (!c.getStringCellValue().isEmpty()) { dosen.add(""); location.add(""); } //System.out.print(c.getStringCellValue() + " Ruang = " + (int) c2.getNumericCellValue() + " "); } } // scheduleList.add(new ScheduleClass(lc, lt, lt, subject, dosen.get(j), location.get(j))); } // System.out.println("lc = "+lc+",lt = "+lt+",subject = "+subject+",dosen = "+dosen.get(i)+",location = "+location.get(i)); // scheduleList.add(new ScheduleClass(lc, lt, lt, subject, dosen.get(j), location.get(j))); } for (int j = 0; j < dosen.size(); j++) { //System.out.println("lc = "+lc+",lt = "+lt+",subject = "+subject+",dosen = "+dosen.get(j)+",location = "+location.get(j)); scheduleList .add(new ScheduleClass(lc, lt, lt.plusHours(2), subject, dosen.get(j), location.get(j))); } dosen.clear(); location.clear(); } return mergeringList(scheduleList); }
From source file:icalendarconverter.ReadExcel.java
public static void main(String[] args) throws Exception { File src = new File( "C:\\Users\\Ariq\\Documents\\NetBeansProjects\\Skripsi-Jadwal-Mengawas-Ujian\\Contoh File\\Jadwal_Pengawas_ Ujian_Pak_ Pascal.xlsx"); //File src = new File("D:\\\\Skripsi\\\\Data Baru\\\\Daftar Dosen.xlsx"); FileInputStream fis = new FileInputStream(src); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sheet1 = wb.getSheetAt(0); Iterator<Row> rowIterator = sheet1.iterator(); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); // suppose your formula is in A3 CellReference cellReference = new CellReference("A3"); while (rowIterator.hasNext()) { row = (XSSFRow) rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); CellValue cellValue = evaluator.evaluate(cell); switch (evaluator.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: System.out.print((int) cell.getNumericCellValue() + " \t\t "); break; case Cell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue() + " \t\t "); break; }// w w w . j a v a2 s.c o m } System.out.println(); } fis.close(); }
From source file:mw.sqlitetool.ExcelHelper.java
public Object getCellValue(Cell cell) { if (cell == null || cell.getCellType() == cell.CELL_TYPE_BLANK) { return ""; } else if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) { return cell.getBooleanCellValue(); } else if (cell.getCellType() == cell.CELL_TYPE_ERROR) { return cell.getErrorCellValue(); } else if (cell.getCellType() == cell.CELL_TYPE_FORMULA) { FormulaEvaluator evaluator = _workbook.getCreationHelper().createFormulaEvaluator(); CellValue val = evaluator.evaluate(cell); if (val.getCellType() == cell.CELL_TYPE_BOOLEAN) { return val.getBooleanValue(); } else if (val.getCellType() == cell.CELL_TYPE_NUMERIC) { return val.getNumberValue(); } else if (val.getCellType() == cell.CELL_TYPE_STRING) { return val.getStringValue(); } else if (val.getCellType() == cell.CELL_TYPE_ERROR) { return val.getErrorValue(); }/*w ww . ja v a2s . com*/ } else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) { if (DateUtil.isCellDateFormatted(cell)) { return cell.getDateCellValue(); } else { return cell.getNumericCellValue(); } } else if (cell.getCellType() == cell.CELL_TYPE_STRING) { return cell.getStringCellValue(); } return ""; }
From source file:mx.edu.um.mateo.activos.dao.impl.ActivoDaoHibernate.java
License:Open Source License
@Override @SuppressWarnings("unchecked") public void sube(byte[] datos, Usuario usuario, OutputStream out, Integer codigoInicial) { Date inicio = new Date(); int idx = 5;/*from www. ja va 2s.co m*/ int i = 0; SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy"); SimpleDateFormat sdf2 = new SimpleDateFormat("dd/MM/yy"); SimpleDateFormat sdf3 = new SimpleDateFormat("dd-MM-yy"); MathContext mc = new MathContext(16, RoundingMode.HALF_UP); NumberFormat nf = NumberFormat.getInstance(); nf.setGroupingUsed(false); nf.setMaximumFractionDigits(0); nf.setMinimumIntegerDigits(5); Transaction tx = null; try { String ejercicioId = "001-2013"; Map<String, CentroCosto> centrosDeCosto = new HashMap<>(); Map<String, TipoActivo> tipos = new HashMap<>(); Query tipoActivoQuery = currentSession() .createQuery("select ta from TipoActivo ta " + "where ta.empresa.id = :empresaId " + "and ta.cuenta.id.ejercicio.id.idEjercicio = :ejercicioId " + "and ta.cuenta.id.ejercicio.id.organizacion.id = :organizacionId"); log.debug("empresaId: {}", usuario.getEmpresa().getId()); log.debug("ejercicioId: {}", ejercicioId); log.debug("organizacionId: {}", usuario.getEmpresa().getOrganizacion().getId()); tipoActivoQuery.setLong("empresaId", usuario.getEmpresa().getId()); tipoActivoQuery.setString("ejercicioId", ejercicioId); tipoActivoQuery.setLong("organizacionId", usuario.getEmpresa().getOrganizacion().getId()); List<TipoActivo> listaTipos = tipoActivoQuery.list(); for (TipoActivo tipoActivo : listaTipos) { tipos.put(tipoActivo.getCuenta().getId().getIdCtaMayor(), tipoActivo); } log.debug("TIPOS: {}", tipos); Query proveedorQuery = currentSession().createQuery( "select p from Proveedor p where p.empresa.id = :empresaId and p.nombre = :nombreEmpresa"); proveedorQuery.setLong("empresaId", usuario.getEmpresa().getId()); proveedorQuery.setString("nombreEmpresa", usuario.getEmpresa().getNombre()); Proveedor proveedor = (Proveedor) proveedorQuery.uniqueResult(); Query codigoDuplicadoQuery = currentSession() .createQuery("select a from Activo a where a.empresa.id = :empresaId and a.codigo = :codigo"); XSSFWorkbook workbook = new XSSFWorkbook(new ByteArrayInputStream(datos)); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet ccostoFantasma = wb.createSheet("CCOSTO-FANTASMAS"); int ccostoFantasmaRow = 0; XSSFSheet sinCCosto = wb.createSheet("SIN-CCOSTO"); int sinCCostoRow = 0; XSSFSheet codigoAsignado = wb.createSheet("CODIGO-ASIGNADO"); int codigoAsignadoRow = 0; XSSFSheet fechaInvalida = wb.createSheet("FECHA-INVALIDA"); int fechaInvalidaRow = 0; XSSFSheet sinCosto = wb.createSheet("SIN-COSTO"); int sinCostoRow = 0; //tx = currentSession().beginTransaction(); for (idx = 5; idx <= 5; idx++) { XSSFSheet sheet = workbook.getSheetAt(idx); int rows = sheet.getPhysicalNumberOfRows(); for (i = 2; i < rows; i++) { log.debug("Leyendo pagina {} renglon {}", idx, i); XSSFRow row = sheet.getRow(i); if (row.getCell(0) == null) { break; } String nombreGrupo = row.getCell(0).getStringCellValue().trim(); switch (row.getCell(0).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: nombreGrupo = row.getCell(0).toString().trim(); break; case XSSFCell.CELL_TYPE_STRING: nombreGrupo = row.getCell(0).getStringCellValue().trim(); break; } TipoActivo tipoActivo = tipos.get(nombreGrupo); if (tipoActivo != null) { String cuentaCCosto = row.getCell(2).toString().trim(); if (StringUtils.isNotBlank(cuentaCCosto)) { CentroCosto centroCosto = centrosDeCosto.get(cuentaCCosto); if (centroCosto == null) { Query ccostoQuery = currentSession().createQuery("select cc from CentroCosto cc " + "where cc.id.ejercicio.id.idEjercicio = :ejercicioId " + "and cc.id.ejercicio.id.organizacion.id = :organizacionId " + "and cc.id.idCosto like :idCosto"); ccostoQuery.setString("ejercicioId", ejercicioId); ccostoQuery.setLong("organizacionId", usuario.getEmpresa().getOrganizacion().getId()); ccostoQuery.setString("idCosto", "1.01." + cuentaCCosto); ccostoQuery.setMaxResults(1); List<CentroCosto> listaCCosto = ccostoQuery.list(); if (listaCCosto != null && listaCCosto.size() > 0) { centroCosto = listaCCosto.get(0); } if (centroCosto == null) { XSSFRow renglon = ccostoFantasma.createRow(ccostoFantasmaRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9).setCellValue(row.getCell(8).toString()); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); continue; } centrosDeCosto.put(cuentaCCosto, centroCosto); } String poliza = null; switch (row.getCell(4).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: poliza = row.getCell(4).toString(); poliza = StringUtils.removeEnd(poliza, ".0"); log.debug("POLIZA-N: {}", poliza); break; case XSSFCell.CELL_TYPE_STRING: poliza = row.getCell(4).getStringCellValue().trim(); log.debug("POLIZA-S: {}", poliza); break; } Boolean seguro = false; if (row.getCell(5) != null && StringUtils.isNotBlank(row.getCell(5).toString())) { seguro = true; } Boolean garantia = false; if (row.getCell(6) != null && StringUtils.isNotBlank(row.getCell(6).toString())) { garantia = true; } Date fechaCompra = null; if (row.getCell(7) != null) { log.debug("VALIDANDO FECHA"); XSSFCell cell = row.getCell(7); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: log.debug("ES NUMERIC"); if (DateUtil.isCellDateFormatted(cell)) { log.debug("ES FECHA"); fechaCompra = cell.getDateCellValue(); } else if (DateUtil.isCellInternalDateFormatted(cell)) { log.debug("ES FECHA INTERNAL"); fechaCompra = cell.getDateCellValue(); } else { BigDecimal bd = new BigDecimal(cell.getNumericCellValue()); bd = stripTrailingZeros(bd); log.debug("CONVIRTIENDO DOUBLE {} - {}", DateUtil.isValidExcelDate(bd.doubleValue()), bd); fechaCompra = HSSFDateUtil.getJavaDate(bd.longValue(), true); log.debug("Cal: {}", fechaCompra); } break; case Cell.CELL_TYPE_FORMULA: log.debug("ES FORMULA"); CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { fechaCompra = DateUtil.getJavaDate(cellValue.getNumberValue(), true); } } } } if (row.getCell(7) != null && fechaCompra == null) { String fechaCompraString; if (row.getCell(7).getCellType() == Cell.CELL_TYPE_STRING) { fechaCompraString = row.getCell(7).getStringCellValue(); } else { fechaCompraString = row.getCell(7).toString().trim(); } try { fechaCompra = sdf.parse(fechaCompraString); } catch (ParseException e) { try { fechaCompra = sdf2.parse(fechaCompraString); } catch (ParseException e2) { try { fechaCompra = sdf3.parse(fechaCompraString); } catch (ParseException e3) { // no se pudo convertir } } } } if (fechaCompra == null) { XSSFRow renglon = fechaInvalida.createRow(fechaInvalidaRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9).setCellValue(row.getCell(8).toString()); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); continue; } String codigo = null; switch (row.getCell(8).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: codigo = row.getCell(8).toString(); break; case XSSFCell.CELL_TYPE_STRING: codigo = row.getCell(8).getStringCellValue().trim(); break; } if (StringUtils.isBlank(codigo)) { codigo = "SIN CODIGO" + nf.format(codigoInicial); XSSFRow renglon = codigoAsignado.createRow(codigoAsignadoRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9).setCellValue("SIN CODIGO" + codigoInicial); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); codigoInicial++; } else { // busca codigo duplicado if (codigo.contains(".")) { codigo = codigo.substring(0, codigo.lastIndexOf(".")); log.debug("CODIGO: {}", codigo); } codigoDuplicadoQuery.setLong("empresaId", usuario.getEmpresa().getId()); codigoDuplicadoQuery.setString("codigo", codigo); Activo activo = (Activo) codigoDuplicadoQuery.uniqueResult(); if (activo != null) { XSSFRow renglon = codigoAsignado.createRow(codigoAsignadoRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9) .setCellValue(codigo + "-" + "SIN CODIGO" + nf.format(codigoInicial)); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); codigo = "SIN CODIGO" + nf.format(codigoInicial); codigoInicial++; } } String descripcion = null; if (row.getCell(9) != null) { switch (row.getCell(9).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: descripcion = row.getCell(9).toString(); descripcion = StringUtils.removeEnd(descripcion, ".0"); break; case XSSFCell.CELL_TYPE_STRING: descripcion = row.getCell(9).getStringCellValue().trim(); break; default: descripcion = row.getCell(9).toString().trim(); } } String marca = null; if (row.getCell(10) != null) { switch (row.getCell(10).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: marca = row.getCell(10).toString(); marca = StringUtils.removeEnd(marca, ".0"); break; case XSSFCell.CELL_TYPE_STRING: marca = row.getCell(10).getStringCellValue().trim(); break; default: marca = row.getCell(10).toString().trim(); } } String modelo = null; if (row.getCell(11) != null) { switch (row.getCell(11).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: modelo = row.getCell(11).toString(); modelo = StringUtils.removeEnd(modelo, ".0"); break; case XSSFCell.CELL_TYPE_STRING: modelo = row.getCell(11).getStringCellValue().trim(); break; default: modelo = row.getCell(11).toString().trim(); } } String serie = null; if (row.getCell(12) != null) { switch (row.getCell(12).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: serie = row.getCell(12).toString(); serie = StringUtils.removeEnd(serie, ".0"); break; case XSSFCell.CELL_TYPE_STRING: serie = row.getCell(12).getStringCellValue().trim(); break; default: serie = row.getCell(12).toString().trim(); } } String responsable = null; if (row.getCell(13) != null) { switch (row.getCell(13).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: responsable = row.getCell(13).toString(); responsable = StringUtils.removeEnd(responsable, ".0"); break; case XSSFCell.CELL_TYPE_STRING: responsable = row.getCell(13).getStringCellValue().trim(); break; default: responsable = row.getCell(13).toString().trim(); } } String ubicacion = null; if (row.getCell(14) != null) { switch (row.getCell(14).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: ubicacion = row.getCell(14).toString(); ubicacion = StringUtils.removeEnd(ubicacion, ".0"); break; case XSSFCell.CELL_TYPE_STRING: ubicacion = row.getCell(14).getStringCellValue().trim(); break; default: ubicacion = row.getCell(14).toString().trim(); } } BigDecimal costo = null; switch (row.getCell(15).getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: costo = new BigDecimal(row.getCell(15).getNumericCellValue(), mc); log.debug("COSTO-N: {} - {}", costo, row.getCell(15).getNumericCellValue()); break; case XSSFCell.CELL_TYPE_STRING: costo = new BigDecimal(row.getCell(15).toString(), mc); log.debug("COSTO-S: {} - {}", costo, row.getCell(15).toString()); break; case XSSFCell.CELL_TYPE_FORMULA: costo = new BigDecimal( evaluator.evaluateInCell(row.getCell(15)).getNumericCellValue(), mc); log.debug("COSTO-F: {}", costo); } if (costo == null) { XSSFRow renglon = sinCosto.createRow(sinCostoRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9).setCellValue(row.getCell(8).toString()); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); continue; } Activo activo = new Activo(fechaCompra, seguro, garantia, poliza, codigo, descripcion, marca, modelo, serie, responsable, ubicacion, costo, tipoActivo, centroCosto, proveedor, usuario.getEmpresa()); this.crea(activo, usuario); } else { XSSFRow renglon = sinCCosto.createRow(sinCCostoRow++); renglon.createCell(0).setCellValue(sheet.getSheetName() + ":" + (i + 1)); renglon.createCell(1).setCellValue(row.getCell(0).toString()); renglon.createCell(2).setCellValue(row.getCell(1).toString()); renglon.createCell(3).setCellValue(row.getCell(2).toString()); renglon.createCell(4).setCellValue(row.getCell(3).toString()); renglon.createCell(5).setCellValue(row.getCell(4).toString()); renglon.createCell(6).setCellValue(row.getCell(5).toString()); renglon.createCell(7).setCellValue(row.getCell(6).toString()); renglon.createCell(8).setCellValue(row.getCell(7).toString()); renglon.createCell(9).setCellValue(row.getCell(8).toString()); renglon.createCell(10).setCellValue(row.getCell(9).toString()); renglon.createCell(11).setCellValue(row.getCell(10).toString()); renglon.createCell(12).setCellValue(row.getCell(11).toString()); renglon.createCell(13).setCellValue(row.getCell(12).toString()); renglon.createCell(14).setCellValue(row.getCell(13).toString()); renglon.createCell(15).setCellValue(row.getCell(14).toString()); renglon.createCell(16).setCellValue(row.getCell(15).toString()); continue; } } else { throw new RuntimeException( "(" + idx + ":" + i + ") No se encontro el tipo de activo " + nombreGrupo); } } } //tx.commit(); log.debug("################################################"); log.debug("################################################"); log.debug("TERMINO EN {} MINS", (new Date().getTime() - inicio.getTime()) / (1000 * 60)); log.debug("################################################"); log.debug("################################################"); wb.write(out); } catch (IOException | RuntimeException e) { //if (tx != null && tx.isActive()) { //tx.rollback(); //} log.error("Hubo problemas al intentar pasar datos de archivo excel a BD (" + idx + ":" + i + ")", e); throw new RuntimeException( "Hubo problemas al intentar pasar datos de archivo excel a BD (" + idx + ":" + i + ")", e); } }