List of usage examples for org.apache.poi.ss.usermodel FormulaEvaluator evaluateInCell
Cell evaluateInCell(Cell cell);
From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.importer.ImportWorkbook.java
License:Open Source License
/** * Initially reads the complete Excel workbook and calculates all formulaCells. After that, we can * be sure that no cell contains formulas any longer, but they all contain values (or nothing). *///w w w. j av a2 s .co m protected void calculateAllFormulas() { FormulaEvaluator evaluator = new HSSFFormulaEvaluator((HSSFWorkbook) getWb()); // iterate over all sheets, their rows, and all their cells for (int sheetNum = 0; sheetNum < getWb().getNumberOfSheets(); sheetNum++) { Sheet sheet = getWb().getSheetAt(sheetNum); for (Row row : sheet) { for (Cell cell : row) { if ((cell.getCellType() == Cell.CELL_TYPE_FORMULA) && (cell.getCellFormula() != null)) { try { evaluator.evaluateInCell(cell); } catch (Exception e) { getProcessingLog().error("Error calculating the formula in cell [" + ExcelImportUtilities.getCellRef(cell) + "] on sheet '" + sheet.getSheetName() + "', using cached value instead: " + e.getLocalizedMessage()); } } } } } }
From source file:foodbankyfs.FbMainFx.java
private List<String[]> saveSpreadsheetData() { // Copy spreadsheet contents into memory try {/*from w ww . j av a2s.c o m*/ // Initialize xls reading objects FileInputStream fileInputStream = new FileInputStream(spreadsheet); XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream); XSSFSheet worksheet = workbook.getSheet(FbConstants.SHEET_NAME); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); List<String[]> tmpData = new ArrayList(); // Save XSSF objects for rewrite wrksheet = worksheet; wrkbook = workbook; // Iterate through all rows in the sheet for (int rowNum = FbConstants.DATA_ROW_START_INDEX; rowNum < worksheet.getLastRowNum(); rowNum++) { // Initialize array that will store cell contents String values[] = new String[FbConstants.NUMBER_OF_COLUMNS]; XSSFRow row = worksheet.getRow(rowNum); // Iterate through cells in each row and store values to an array for (int cellNum = 0; cellNum < FbConstants.NUMBER_OF_COLUMNS; cellNum++) { XSSFCell cell = row.getCell(cellNum, Row.CREATE_NULL_AS_BLANK); String value = ""; if (cell != null) { if (cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) { evaluator.evaluateInCell(cell); } // If cell type is numeric convert the number value to a string if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { double tmpVal = cell.getNumericCellValue(); value = String.format("%.0f", tmpVal); } if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) { value = cell.getStringCellValue().toLowerCase(); } } // If a cell row has an empty ID do not include it in data if (cellNum == 0 && value.equals("")) { break; } // Initialize value to 0 if cell is empty if (value.isEmpty()) { // If value is from email or notes field then put empty instead if (cellNum == FbConstants.EMAIL_FIELD || cellNum == FbConstants.NOTES_FIELD) { value = "empty"; } else { value = "0"; } } // Store value in array values[cellNum] = value; } // Store array of values in list tmpData.add(values); } return tmpData; } catch (IOException e) { System.err.println(e); } return null; }
From source file:helpers.Excel.ExcelDataFormat.java
public OneExcelSheet marshalAsStructure(Iterator<Row> sheet, FormulaEvaluator evaluator) { logger.info("Evaluating formulas."); evaluator.evaluateAll();//from w ww. j a v a 2 s . c o m logger.info("Done..."); OneExcelSheet onesheet = new OneExcelSheet(); ArrayList<String> headers = null; for (Iterator<Row> rowIterator = sheet; rowIterator.hasNext();) { Row row = rowIterator.next(); if (headers == null) { headers = new ArrayList<String>(); int coln = 0; for (Iterator<Cell> cellIterator = row.cellIterator(); cellIterator.hasNext();) { try { Cell cell = cellIterator.next(); logger.info("Header:" + cell.getStringCellValue()); String headn = cell.getStringCellValue().replace(" ", ""); headers.add(headn); OneExcelColumn col = new OneExcelColumn(headn, coln); onesheet.columns.add(col); } catch (Exception e) { logger.error("Unable to decode cell header. Ex=" + e.getMessage(), e); } coln++; } } else { ArrayList<Object> newrow = new ArrayList<Object>(); onesheet.data.add(newrow); int coln = 0; //for (Iterator<Cell> cellIterator = row.cellIterator(); cellIterator.hasNext();) for (int cn = 0; cn < row.getLastCellNum(); cn++) { Cell cell = row.getCell(cn, Row.CREATE_NULL_AS_BLANK); //Cell cell=cellIterator.next(); //logger.info("Cell type:"+cell.getCellType()); switch (evaluator.evaluateInCell(cell).getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { //logger.info(cell.getCellType()+"="+cell.getDateCellValue()); newrow.add(cell.getDateCellValue()); if (onesheet.columns.size() > coln) onesheet.columns.get(coln).columnTypes[9]++; } else { //logger.info(cell.getCellType()+"="+cell.getNumericCellValue()); newrow.add(cell.getNumericCellValue()); if (onesheet.columns.size() > coln) onesheet.columns.get(coln).columnTypes[cell.getCellType()]++; } break; case HSSFCell.CELL_TYPE_FORMULA: int value = evaluator.evaluateFormulaCell(cell); value = cell.getCachedFormulaResultType(); newrow.add(value); if (onesheet.columns.size() > coln) onesheet.columns.get(coln).columnTypes[0]++; break; default: //logger.info(cell.getCellType()+"="+cell.getStringCellValue()); String cellstr = new String(cell.getStringCellValue().getBytes(), Charset.forName("UTF-8")); newrow.add(cellstr); if (onesheet.columns.size() > coln) onesheet.columns.get(coln).columnTypes[cell.getCellType()]++; break; } coln++; } } } return onesheet; }
From source file:ia_app.PastPerformancesPanel.java
public PastPerformancesPanel() throws FileNotFoundException, IOException { initComponents();//from w w w . ja va2 s. co m FileInputStream fis = new FileInputStream(new File("testing.xls")); HSSFWorkbook wb = new HSSFWorkbook(fis); HSSFSheet sheet = wb.getSheetAt(0); FormulaEvaluator forEval = wb.getCreationHelper().createFormulaEvaluator(); this.text = ""; for (Row row : sheet) { for (Cell cell : row) { switch (forEval.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: this.text = (text + cell.getNumericCellValue() + "\t"); break; case Cell.CELL_TYPE_STRING: this.text = (text + cell.getStringCellValue() + "\t"); break; } } this.text = (text + "\n"); } this.jTextArea1.setText(text); }
From source file:ia_app.StatsPanel.java
/** * Creates new form StatsPanel//from ww w.j a v a2 s .c om */ public StatsPanel() throws FileNotFoundException, IOException { initComponents(); FileInputStream fis = new FileInputStream(new File("games.xls")); HSSFWorkbook wb = new HSSFWorkbook(fis); HSSFSheet sheet = wb.getSheetAt(0); FormulaEvaluator forEval = wb.getCreationHelper().createFormulaEvaluator(); Row row1 = sheet.getRow(1); for (Cell cell : row1) { switch (forEval.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: this.makes += cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: break; } } this.jTextFieldMakes.setText(makes + ""); Row row2 = sheet.getRow(2); for (Cell cell : row2) { switch (forEval.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: this.attempts += cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: break; } } this.jTextFieldAttempts.setText(attempts + ""); this.jTextFieldFGPct.setText("58.3%"); Row row3 = sheet.getRow(3); for (Cell cell : row3) { switch (forEval.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: this.threeMakes += cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: break; } } Row row4 = sheet.getRow(4); for (Cell cell : row4) { switch (forEval.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: this.threeAttempts += cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: break; } } this.jTextField3Pct.setText("80%"); /*for(Row column : sheet){ for(Cell cell : column){ switch(forEval.evaluateInCell(cell).getCellType()){ case Cell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue() + "\t\t"); break; case Cell.CELL_TYPE_STRING: break; } } } */ }
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; }//from w w w .ja v a 2s . c om } System.out.println(); } fis.close(); }
From source file:jexcelcompi.JExcelCompi.java
public void ejemplo() throws FileNotFoundException, IOException { FileInputStream fis = new FileInputStream(new File("hoja2.xls")); HSSFWorkbook wb = new HSSFWorkbook(fis); HSSFSheet sheet = wb.getSheetAt(0);//from ww w . j ava 2 s. c o m FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator(); int x = 0; for (Row row : sheet) { for (Cell cell : row) { switch (formulaEvaluator.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: { System.out.println(cell.getNumericCellValue() + "\t\t"); } //case Cell.CELL_TYPE_STRING: //System.out.println(cell.getStringCellValue()+"\t\tcadena"); x++; } } System.out.println(); } }
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 w w w . jav a 2 s . c o 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); } }
From source file:net.sf.ahtutils.report.util.DataUtil.java
public static Object getCellValue(Cell cell) { Object value = new Object(); // Prevent a NullPointerException if (cell != null) { if (cell.getHyperlink() != null) { Workbook workbook = new XSSFWorkbook(); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Hyperlink link = cell.getHyperlink(); String address = link.getAddress(); if (logger.isTraceEnabled()) { logger.trace("Found a Hyperlink to " + cell.getHyperlink().getAddress() + " in cell " + cell.getRowIndex() + "," + cell.getColumnIndex()); }/*from w ww . j ava 2 s .c o m*/ cell = evaluator.evaluateInCell(cell); } // Depending on the cell type, the value is read using Apache POI methods switch (cell.getCellType()) { // String are easy to handle case Cell.CELL_TYPE_STRING: logger.trace("Found string " + cell.getStringCellValue()); value = cell.getStringCellValue(); break; // Since date formatted cells are also of the numeric type, this needs to be processed case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); DateFormat df = SimpleDateFormat.getDateInstance(); logger.trace("Found date " + df.format(date)); value = date; } else { logger.trace("Found general number " + cell.getNumericCellValue()); value = cell.getNumericCellValue(); } break; } } else { logger.trace("Found cell with NULL value"); } return value; }
From source file:org.apache.metamodel.excel.ExcelUtils.java
License:Apache License
private static String getFormulaCellValue(Workbook wb, Cell cell) { // first try with a cached/precalculated value try {/*from w w w .j a va 2 s . co m*/ double numericCellValue = cell.getNumericCellValue(); // TODO: Consider not formatting it, but simple using // Double.toString(...) return _numberFormat.format(numericCellValue); } catch (Exception e) { if (logger.isInfoEnabled()) { logger.info("Failed to fetch cached/precalculated formula value of cell: " + cell, e); } } // evaluate cell first, if possible try { if (logger.isInfoEnabled()) { logger.info("cell({},{}) is a formula. Attempting to evaluate: {}", new Object[] { cell.getRowIndex(), cell.getColumnIndex(), cell.getCellFormula() }); } final FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); // calculates the formula and puts it's value back into the cell final Cell evaluatedCell = evaluator.evaluateInCell(cell); return getCellValue(wb, evaluatedCell); } catch (RuntimeException e) { logger.warn("Exception occurred while evaluating formula at position ({},{}): {}", new Object[] { cell.getRowIndex(), cell.getColumnIndex(), e.getMessage() }); // Some exceptions we simply log - result will be then be the // actual formula if (e instanceof FormulaParseException) { logger.error("Parse exception occurred while evaluating cell formula: " + cell, e); } else if (e instanceof IllegalArgumentException) { logger.error("Illegal formula argument occurred while evaluating cell formula: " + cell, e); } else { logger.error("Unexpected exception occurred while evaluating cell formula: " + cell, e); } } // last resort: return the string formula return cell.getCellFormula(); }