List of usage examples for org.apache.poi.ss.usermodel CellValue getNumberValue
public double getNumberValue()
From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java
License:Open Source License
private void td(final HSSFCell cell, StringBuffer buf) { int colspan = 1; if (colIndex == mergeStart) { // First cell in the merging region - set colspan. colspan = mergeEnd - mergeStart + 1; } else if (colIndex == mergeEnd) { // Last cell in the merging region - no more skipped cells. mergeStart = -1;/*www .j a v a 2s . c om*/ mergeEnd = -1; return; } else if (mergeStart != -1 && mergeEnd != -1 && colIndex > mergeStart && colIndex < mergeEnd) { // Within the merging region - skip the cell. return; } //KLO 05022018 //buf.append("<td "); buf.append("<td height=\"15\" "); if (colspan > 1) { buf.append("colspan='").append(colspan).append("' "); } if (cell == null) { buf.append("/>"); return; } buf.append("style='"); final HSSFCellStyle style = cell.getCellStyle(); // Text alignment switch (style.getAlignment()) { case CellStyle.ALIGN_LEFT: buf.append("text-align: left; "); break; case CellStyle.ALIGN_RIGHT: buf.append("text-align: right; "); break; case CellStyle.ALIGN_CENTER: buf.append("text-align: center; "); break; default: break; } // Font style, size and weight final HSSFFont font = style.getFont(book); if (font == null) return; if (font.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD) { buf.append("font-weight: bold; "); } if (font.getItalic()) { buf.append("font-style: italic; "); } if (font.getUnderline() != HSSFFont.U_NONE) { buf.append("text-decoration: underline; "); } buf.append("font-size: ").append(Math.floor(font.getFontHeightInPoints() * 0.8)).append("pt; "); // Cell background and font colours final short[] backRGB = style.getFillForegroundColorColor().getTriplet(); final HSSFColor foreColor = palette.getColor(font.getColor()); if (foreColor != null) { final short[] foreRGB = foreColor.getTriplet(); if (foreRGB[0] != 0 || foreRGB[1] != 0 || foreRGB[2] != 0) { buf.append("color: rgb(").append(foreRGB[0]).append(',').append(foreRGB[1]).append(',') .append(foreRGB[2]).append(");"); } } if (backRGB[0] != 0 || backRGB[1] != 0 || backRGB[2] != 0) { buf.append("background-color: rgb(").append(backRGB[0]).append(',').append(backRGB[1]).append(',') .append(backRGB[2]).append(");"); } // Border if (style.getBorderTop() != HSSFCellStyle.BORDER_NONE) { buf.append("border-top-style: solid; "); } if (style.getBorderRight() != HSSFCellStyle.BORDER_NONE) { buf.append("border-right-style: solid; "); } if (style.getBorderBottom() != HSSFCellStyle.BORDER_NONE) { buf.append("border-bottom-style: solid; "); } if (style.getBorderLeft() != HSSFCellStyle.BORDER_NONE) { buf.append("border-left-style: solid; "); } buf.append("'>"); String val = ""; try { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: // POI does not distinguish between integer and double, thus: final double original = cell.getNumericCellValue(), rounded = Math.round(original); if (Math.abs(rounded - original) < 0.00000000000000001) { val = String.valueOf((int) rounded); } else { val = String.valueOf(original); } break; case HSSFCell.CELL_TYPE_FORMULA: final CellValue cv = evaluator.evaluate(cell); if (cv == null) return; switch (cv.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: buf.append(cv.getBooleanValue()); break; case Cell.CELL_TYPE_NUMERIC: buf.append(cv.getNumberValue()); break; case Cell.CELL_TYPE_STRING: buf.append(cv.getStringValue()); break; case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_ERROR: break; default: break; } break; default: // Neither string or number? Could be a date. try { val = sdf.format(cell.getDateCellValue()); } catch (final Exception e1) { } } } catch (final Exception e) { val = e.getMessage(); } if ("null".equals(val)) { val = ""; } if (pix.containsKey(rowIndex)) { if (pix.get(rowIndex).containsKey(colIndex)) { for (final HSSFPictureData pic : pix.get(rowIndex).get(colIndex)) { buf.append("<img alt='Image in Excel sheet' src='data:"); buf.append(pic.getMimeType()); buf.append(";base64,"); try { buf.append(new String(Base64.encodeBase64(pic.getData()), "US-ASCII")); } catch (final UnsupportedEncodingException e) { throw new RuntimeException(e); } buf.append("'/>"); } } } if (isCode(val) && this.URL != null) { val = getHyperLink(val); } buf.append(val); buf.append("</td>"); }
From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java
License:Open Source License
private String getCellData(final HSSFCell cell) { if (cell == null) return null; int colspan = 1; if (colIndex == mergeStart) { // First cell in the merging region - set colspan. colspan = mergeEnd - mergeStart + 1; } else if (colIndex == mergeEnd) { // Last cell in the merging region - no more skipped cells. mergeStart = -1;//from w w w . j a v a2 s.c o m mergeEnd = -1; return null; } else if (mergeStart != -1 && mergeEnd != -1 && colIndex > mergeStart && colIndex < mergeEnd) { // Within the merging region - skip the cell. return null; } //StringBuffer buf = new StringBuffer(); String val = ""; try { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: // POI does not distinguish between integer and double, thus: final double original = cell.getNumericCellValue(), rounded = Math.round(original); if (Math.abs(rounded - original) < 0.00000000000000001) { val = String.valueOf((int) rounded); } else { val = String.valueOf(original); } break; case HSSFCell.CELL_TYPE_FORMULA: final CellValue cv = evaluator.evaluate(cell); if (cv == null) return null; switch (cv.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: out.append(cv.getBooleanValue()); break; case Cell.CELL_TYPE_NUMERIC: out.append(cv.getNumberValue()); break; case Cell.CELL_TYPE_STRING: out.append(cv.getStringValue()); break; case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_ERROR: break; default: break; } break; default: // Neither string or number? Could be a date. try { val = sdf.format(cell.getDateCellValue()); } catch (final Exception e1) { } } } catch (final Exception e) { val = e.getMessage(); } if ("null".equals(val)) { val = ""; } return val; }
From source file:hu.webhejj.commons.io.table.excel.ExcelRowValueConverter.java
License:Apache License
public <T> T getValue(Row row, int column, Class<T> valueType) { if (row == null) { return null; }// ww w .j a v a 2s . c om Cell cell = row.getCell(column); if (cell == null) { return null; } CellValue cellValue = getCellValue(row, cell, column); if (cellValue == null) { return null; } switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BLANK: return null; case Cell.CELL_TYPE_BOOLEAN: if (String.class.isAssignableFrom(valueType)) { return (T) Boolean.toString(cellValue.getBooleanValue()); } else if (Integer.class.isAssignableFrom(valueType)) { return (T) Integer.valueOf(cellValue.getBooleanValue() ? 1 : 0); } else if (Long.class.isAssignableFrom(valueType)) { return (T) Long.valueOf(cellValue.getBooleanValue() ? 1L : 0L); } else { throw new ClassCastException( "Can't convert " + cellValue.getBooleanValue() + " to " + valueType.getName()); } case Cell.CELL_TYPE_STRING: String stringValue = cellValue.getStringValue(); if (CompareUtils.isEmpty(stringValue)) { return null; } if ("null".equals(stringValue)) { return null; } if (String.class.isAssignableFrom(valueType)) { return (T) stringValue; } else if (Integer.class.isAssignableFrom(valueType)) { return (T) Integer.valueOf(stringValue); } else if (Long.class.isAssignableFrom(valueType)) { return (T) Long.valueOf(stringValue); } else if (valueType.isEnum()) { return (T) Enum.valueOf((Class<? extends Enum>) valueType, stringValue); } else if (BigDecimal.class.isAssignableFrom(valueType)) { return (T) (CompareUtils.isEmpty(stringValue) ? null : new BigDecimal(stringValue)); } else if (Boolean.class.isAssignableFrom(valueType)) { return (T) Boolean.valueOf("true".equalsIgnoreCase(stringValue) || (!CompareUtils.isEmpty(stringValue) && !"0".equals(stringValue))); } else { throw new ClassCastException("Can't convert " + stringValue + " to " + valueType.getName()); } case Cell.CELL_TYPE_NUMERIC: if (String.class.isAssignableFrom(valueType)) { Format format = formatter.createFormat(cell); if (format == null) { // TODO: do this without creating a BigDecimal each time return (T) new BigDecimal(cellValue.getNumberValue()).toString(); } else { return (T) format.format(cellValue.getNumberValue()); } } else if (Integer.class.isAssignableFrom(valueType)) { return (T) Integer.valueOf((int) cellValue.getNumberValue()); } else if (Long.class.isAssignableFrom(valueType)) { return (T) Long.valueOf((int) cellValue.getNumberValue()); } else { throw new ClassCastException( "Can't convert " + cellValue.getNumberValue() + " to " + valueType.getName()); } case Cell.CELL_TYPE_ERROR: FormulaError error = FormulaError.forInt(cell.getErrorCellValue()); if (FormulaError.NA.equals(error)) { return null; } else { // System.err.format(" Cell[%d,%d] error code %s\n", r.getRowNum(), column, error); return null; // throw new RuntimeException(String.format("Cell[%d,%d] error code %s", r.getRowNum(), column, error)); } } throw new IllegalArgumentException("Don't know how to convert cell of type " + cellValue.getCellType()); }
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(); }/*from ww w. j a va2 s. c o m*/ } 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 ww w .ja v a 2 s . com*/ 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:opn.greenwebs.FXMLDocumentController.java
private Object getCellData(File fSheet, int row, int coll) { try {//from w w w . ja v a 2 s .c o m XSSFWorkbook book = new XSSFWorkbook(fSheet); FormulaEvaluator eval = book.getCreationHelper().createFormulaEvaluator(); XSSFSheet xSheet = book.getSheet("Digital Version"); Cell celled = xSheet.getRow(row).getCell(coll); if (celled != null) { CellValue cellval = eval.evaluate(celled); if (cellval == null) { //System.out.println("cellval is null at line 918 " + fSheet.getAbsolutePath() + " " + row + " " + coll); return ""; } else { switch (cellval.getCellType()) { case Cell.CELL_TYPE_BLANK: logger.info("got a blank"); return ""; case Cell.CELL_TYPE_BOOLEAN: logger.info("got a boolean"); return cellval.getBooleanValue(); case Cell.CELL_TYPE_ERROR: return cellval.getErrorValue(); case Cell.CELL_TYPE_FORMULA: return cellval.getStringValue(); case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(celled)) { return HSSFDateUtil.getJavaDate(cellval.getNumberValue()); } else { return cellval.getNumberValue(); } case Cell.CELL_TYPE_STRING: return cellval.getStringValue(); default: return ""; } } } } catch (IOException | InvalidFormatException ex) { Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex); } return ""; }
From source file:opn.greenwebs.FXMLDocumentController.java
private List<ItemDB> createListFromTable(File fStock) { List<ItemDB> lstMain = new ArrayList<>(); logger.log(Level.INFO, "into the breach {0}", fStock.getAbsolutePath()); try (FileInputStream fis = new FileInputStream(fStock); XSSFWorkbook book = new XSSFWorkbook(fis)) { XSSFSheet sheet = book.getSheet("Digital Version"); logger.log(Level.INFO, "we have a sheet {0}", sheet.getSheetName()); FormulaEvaluator evaluator2 = book.getCreationHelper().createFormulaEvaluator(); List lstItem;//from w ww.j av a2 s . c o m List<XSSFTable> lstTables = sheet.getTables(); logger.log(Level.SEVERE, "we have a table? {0}", lstTables.size()); if (!lstTables.isEmpty()) { logger.log(Level.INFO, "the table is not empty"); for (int j = lstTables.get(0).getStartCellReference().getRow(); j <= lstTables.get(0) .getEndCellReference().getRow(); j++) { ItemDB itmLine = new ItemDB(); lstItem = new ArrayList(); logger.log(Level.INFO, "we have some rows"); if (j > lstTables.get(0).getStartCellReference().getRow()) { Row row = sheet.getRow(j); if (row != null) { Iterator<Cell> cellIt = row.cellIterator(); logger.log(Level.INFO, "we have some cells {0}", cellIt.hasNext()); while (cellIt.hasNext()) { Cell cell = cellIt.next(); CellValue cellvalue = evaluator2.evaluate(cell); if (cellvalue == null) { lstItem.add(""); } else { switch (cellvalue.getCellType()) { case Cell.CELL_TYPE_STRING: lstItem.add(cellvalue.getStringValue()); break; case Cell.CELL_TYPE_NUMERIC: lstItem.add(cellvalue.getNumberValue()); break; case Cell.CELL_TYPE_BOOLEAN: lstItem.add(cellvalue.getBooleanValue()); case Cell.CELL_TYPE_BLANK: lstItem.add(""); break; case Cell.CELL_TYPE_FORMULA: lstItem.add("You found a formula"); break; default: } } } logger.log(Level.ALL, "lstItem is {0} and {1}", new Object[] { lstItem.size(), lstItem.toString() }); itmLine.setDblQty(Double.parseDouble(lstItem.get(0).toString())); logger.log(Level.INFO, "setting the quantity to {0}", lstItem.get(0).toString()); itmLine.setStrMfr(lstItem.get(1).toString()); itmLine.setStrSKU(lstItem.get(2).toString()); itmLine.setStrDescrip(lstItem.get(3).toString()); itmLine.setStrSupplier(lstItem.get(4).toString()); itmLine.setStrSupPart(lstItem.get(5).toString()); logger.log(Level.INFO, "setting the suppart to {0}", lstItem.get(5)); if (lstItem.size() > 6) { if (!lstItem.get(6).toString().isEmpty()) { itmLine.setDblSalePrice(Double.parseDouble(lstItem.get(6).toString())); } } //System.out.println("Added item " + itmLine.getStrDescrip()); lstMain.add(itmLine); } } } } } catch (IOException ex) { logger.info(ex.getLocalizedMessage()); } logger.log(Level.INFO, "returning {0}", lstMain.size()); return lstMain; }
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetGetCellValue.java
License:Open Source License
public cfData execute(cfSession _session, List<cfData> parameters) throws cfmRunTimeException { cfSpreadSheetData spreadsheet = null; int rowNo, columnNo; /*//from w ww .ja va2 s.c o m * Collect up the parameters */ spreadsheet = (cfSpreadSheetData) parameters.get(2); rowNo = parameters.get(1).getInt() - 1; columnNo = parameters.get(0).getInt() - 1; if (rowNo < 0) throwException(_session, "row must be 1 or greater (" + rowNo + ")"); if (columnNo < 0) throwException(_session, "column must be 1 or greater (" + columnNo + ")"); /* * Find the cell in question */ Sheet sheet = spreadsheet.getActiveSheet(); Row row = sheet.getRow(rowNo); if (row == null) row = sheet.createRow(rowNo); Cell cell = row.getCell(columnNo); if (cell == null) cell = row.createCell(columnNo); FormulaEvaluator evaluator = spreadsheet.getWorkBook().getCreationHelper().createFormulaEvaluator(); if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) return cfBooleanData.getcfBooleanData(cell.getBooleanCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) return new cfNumberData(cell.getNumericCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) return cfStringData.EMPTY_STRING; else if (cell.getCellType() == Cell.CELL_TYPE_STRING) return new cfStringData(cell.getStringCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: return cfBooleanData.getcfBooleanData(cellValue.getBooleanValue()); case Cell.CELL_TYPE_NUMERIC: return new cfNumberData(cellValue.getNumberValue()); case Cell.CELL_TYPE_STRING: return new cfStringData(cellValue.getStringValue()); default: return cfStringData.EMPTY_STRING; } } else return cfStringData.EMPTY_STRING; }
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetQueryRead.java
License:Open Source License
public cfData execute(cfSession _session, cfArgStructData argStruct) throws cfmRunTimeException { cfSpreadSheetData spreadsheet = (cfSpreadSheetData) getNamedParam(argStruct, "spreadsheet", null); if (spreadsheet == null) throwException(_session, "spreadsheet object not given"); //How many columns are we using int[] columnsToUse; String columns = getNamedStringParam(argStruct, "columns", null); if (columns != null) { int x = 0; List<Integer> numbers = tagUtils.getNumberListSorted(columns); columnsToUse = new int[numbers.size()]; Iterator<Integer> numbersIT = numbers.iterator(); while (numbersIT.hasNext()) columnsToUse[x++] = (numbersIT.next() - 1); } else {/*from w w w.j a va2 s .c o m*/ int maxColumns = SheetUtility.getMaxColumn(spreadsheet.getActiveSheet()); columnsToUse = new int[maxColumns]; for (int x = 0; x < maxColumns; x++) columnsToUse[x] = x; } //Figure out the columns String columnLabels[] = null; int startRow = 0; String columnnames = getNamedStringParam(argStruct, "columnnames", null); int headerRow = getNamedIntParam(argStruct, "headerrow", -1); FormulaEvaluator evaluator = spreadsheet.getWorkBook().getCreationHelper().createFormulaEvaluator(); if (columnnames != null) { columnLabels = (columnnames.split("\\s*,\\s*")); if (columnLabels.length != columnsToUse.length) throwException(_session, "The COLUMNNAMES does not match the number of columns"); } else if (headerRow > -1) { headerRow = headerRow - 1; Row row = spreadsheet.getActiveSheet().getRow(headerRow); if (row == null) throwException(_session, "The HEADERROW does not exist"); columnLabels = new String[columnsToUse.length]; for (int c = 0; c < columnsToUse.length; c++) { Cell cell = row.getCell(columnsToUse[c]); if (cell == null) columnLabels[c] = ""; else columnLabels[c] = cell.toString(); } startRow = headerRow + 1; } else { columnLabels = new String[columnsToUse.length]; for (int x = 0; x < columnLabels.length; x++) columnLabels[x] = "Column " + (columnsToUse[x] + 1); } //Create the query cfQueryResultData queryData = new cfQueryResultData(columnLabels, "SpreadSheet"); List<Map<String, cfData>> vResults = new ArrayList<Map<String, cfData>>(); int sheetNo = getNamedIntParam(argStruct, "sheet", -1); if (sheetNo != -1) spreadsheet.setActiveSheet(sheetNo); Sheet sheet = spreadsheet.getActiveSheet(); Row row; Cell cell; cfData cfdata; String rowsSt = getNamedStringParam(argStruct, "rows", null); if (rowsSt != null) { List<Integer> rows = tagUtils.getNumberListSorted(rowsSt); Iterator<Integer> rowsIT = rows.iterator(); while (rowsIT.hasNext()) { int r = rowsIT.next() - 1; Map<String, cfData> hm = new FastMap<String, cfData>(); if ((row = sheet.getRow(r)) == null) continue; for (int c = 0; c < columnsToUse.length; c++) { cell = row.getCell(columnsToUse[c]); if (cell == null) cfdata = new cfStringData(""); else { if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) cfdata = cfBooleanData.getcfBooleanData(cell.getBooleanCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cfdata = new cfNumberData(cell.getNumericCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) cfdata = new cfStringData(""); else if (cell.getCellType() == Cell.CELL_TYPE_STRING) cfdata = new cfStringData(cell.getStringCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: cfdata = cfBooleanData.getcfBooleanData(cellValue.getBooleanValue()); break; case Cell.CELL_TYPE_NUMERIC: cfdata = new cfNumberData(cellValue.getNumberValue()); break; case Cell.CELL_TYPE_STRING: cfdata = new cfStringData(cellValue.getStringValue()); break; default: cfdata = cfStringData.EMPTY_STRING; break; } } else cfdata = new cfStringData(""); } hm.put(columnLabels[c], cfdata); } vResults.add(hm); } } else { /* * Read __ALL__ the rows associated with this spreadsheet */ for (int r = startRow; r < sheet.getLastRowNum() + 1; r++) { Map<String, cfData> hm = new FastMap<String, cfData>(); if ((row = sheet.getRow(r)) == null) continue; for (int c = 0; c < columnsToUse.length; c++) { cell = row.getCell(columnsToUse[c]); if (cell == null) cfdata = new cfStringData(""); else { if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) cfdata = cfBooleanData.getcfBooleanData(cell.getBooleanCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cfdata = new cfNumberData(cell.getNumericCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) cfdata = new cfStringData(""); else if (cell.getCellType() == Cell.CELL_TYPE_STRING) cfdata = new cfStringData(cell.getStringCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: cfdata = cfBooleanData.getcfBooleanData(cellValue.getBooleanValue()); break; case Cell.CELL_TYPE_NUMERIC: cfdata = new cfNumberData(cellValue.getNumberValue()); break; case Cell.CELL_TYPE_STRING: cfdata = new cfStringData(cellValue.getStringValue()); break; default: cfdata = cfStringData.EMPTY_STRING; break; } } else cfdata = new cfStringData(""); } hm.put(columnLabels[c], cfdata); } vResults.add(hm); } } queryData.populateQuery(vResults); return queryData; }
From source file:org.databene.formats.xls.XLSUtil.java
License:Open Source License
public static Object resolveCellValue(Cell cell, String emptyMarker, String nullMarker, Converter<String, ?> stringPreprocessor) { if (cell == null) return null; switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: return convertString(cell, emptyMarker, nullMarker, stringPreprocessor); case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) return cell.getDateCellValue(); else/*from w ww .j av a2s .co m*/ return mapNumberType(cell.getNumericCellValue()); case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue(); case Cell.CELL_TYPE_BLANK: case Cell.CELL_TYPE_ERROR: return cell.getRichStringCellValue().getString(); case Cell.CELL_TYPE_FORMULA: FormulaEvaluator evaluator = createFormulaEvaluator(cell); CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case HSSFCell.CELL_TYPE_STRING: return convertString(cellValue, emptyMarker, stringPreprocessor); case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) return HSSFDateUtil.getJavaDate(cellValue.getNumberValue()); else return mapNumberType(cellValue.getNumberValue()); case Cell.CELL_TYPE_BOOLEAN: return cellValue.getBooleanValue(); case HSSFCell.CELL_TYPE_BLANK: case HSSFCell.CELL_TYPE_ERROR: return null; default: throw new IllegalStateException("Unexpected cell type: " + cellValue.getCellType()); // CELL_TYPE_FORMULA is not supposed to be encountered here } default: throw new ConfigurationError("Not a supported cell type: " + cell.getCellType()); } }