List of usage examples for org.apache.poi.ss.usermodel Cell getRichStringCellValue
RichTextString getRichStringCellValue();
For numeric cells we throw an exception.
From source file:org.myorg.insertar.insertarSabanaTopComponent.java
public void procesaDatosEntrada(List sheetData) { ///* w ww . jav a 2 s. c o m*/ // Iterates the data and print it out to the console. // int cnt = 0; int fProblema = 0; int nTipos = 0; SimpleDateFormat formatDateJava = new SimpleDateFormat("dd-MM-yyyy"); HSSFRichTextString richTextString; // ........................................................................................... for (int i = 0; i < sheetData.size(); i++) { List list = (List) sheetData.get(i); System.out.println(i + " -> Tenemos Exel con " + list.size() + " columnas"); // ........................................................................................... if (cnt == 0) { // CARGAMOS LOS NOMBRES DE LOS CAMPOS for (int j = 0; j < list.size(); j++) { Cell cell = (Cell) list.get(j); // ............................................................ System.out.println("Tipo Nombre =" + cell.getCellType()); if (cell.getCellType() == Cell.CELL_TYPE_STRING) { richTextString = (HSSFRichTextString) cell.getRichStringCellValue(); this.nombres[j] = richTextString.getString(); System.out.println("Cargando Nombre =" + this.nombres[j]); } } this.nColumnas = list.size(); System.out.println("Numero de campos=" + this.nColumnas); } // ........................................................................................... if (cnt == 1) { // CARGAMOS LOS TIPOS DE LOS CAMPOS nTipos = list.size(); for (int j = 0; j < list.size(); j++) { Cell cell = (Cell) list.get(j); if (cell.getCellType() == Cell.CELL_TYPE_STRING) { richTextString = (HSSFRichTextString) cell.getRichStringCellValue(); this.tipos[j] = richTextString.getString(); } if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { this.tipos[j] = Double.toString(cell.getNumericCellValue()); System.out.println("Cargo this.tipos[" + j + "] = " + this.tipos[j]); } } } // ........................................................................................... if (cnt > 1) { // CARGAMOS LOS DATOS System.out.println("------------CARGAMOS LOS DATOS -----------"); if (nTipos != list.size()) { this.sLogTxt += "AVISO: FILA DATOS(" + (cnt - 2) + ") -> HAY UN PROBLEMA :EL NMERO DE CAMPOS DEFINIDOS Y EL QUE CONTIENE LA L?NEA DE DATOS NO COINCIDE (" + nTipos + "!=" + list.size() + ") \n"; // fProblema = 2 ; } if (fProblema != 2) { for (int j = 0; j < list.size(); j++) { Cell cell = (Cell) list.get(j); this.tablaDatos[cnt - 2][j] = ""; // ............................................................ if (this.tipos[j].equals("4.0")) { // es un tipo fecha SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); try { this.tablaDatos[cnt - 2][j] = String.valueOf(sdf.format(cell.getDateCellValue())); } catch (IllegalStateException e) { this.sLogTxt += "AVISO: FILA DATOS(" + (cnt - 2) + ") -> HAY UN PROBLEMA EL TIPO FECHA EN EL CAMPO: " + this.nombres[j] + "\n"; fProblema = 1; } } else { // ............................................................ if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { try { this.tablaDatos[cnt - 2][j] = Double.toString(cell.getNumericCellValue()); } catch (IllegalStateException e) { this.sLogTxt += "AVISO: FILA DATOS(" + (cnt - 2) + ") -> HAY UN PROBLEMA EL TIPO DE DATO NUMERICO EN EL CAMPO: " + this.nombres[j] + "\n"; fProblema = 1; } } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { // ............................................................ try { richTextString = (HSSFRichTextString) cell.getRichStringCellValue(); this.tablaDatos[cnt - 2][j] = richTextString.getString(); } catch (IllegalStateException e) { this.sLogTxt += "AVISO: FILA DATOS(" + (cnt - 2) + ") ->HAY UN PROBLEMA EL TIPO DE DATO TEXTO EN EL CAMPO: " + this.nombres[j] + "\n"; fProblema = 1; } } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { // ............................................................ try { this.tablaDatos[cnt - 2][j] = Boolean.toString(cell.getBooleanCellValue()); } catch (IllegalStateException e) { this.sLogTxt += "AVISO: FILA DATOS(" + (cnt - 2) + ") ->HAY UN PROBLEMA EL TIPO DE DATO BOOLEANO EN EL CAMPO: " + this.nombres[j] + "\n"; fProblema = 1; } } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { // ............................................................ this.tablaDatos[cnt - 2][j] = ""; } } } // ........................................................................................... } cnt++; } else { // Nos saltamos el procesamiento de esta lnea y seguimos contando. cnt++; fProblema = 0; } } // ........................................................................................... this.nDatos = cnt; System.out.println("----------- HE CARGADO " + this.nDatos + " REGISTROS ------------"); this.sLogTxt += "----------- HE CARGADO " + this.nDatos + " REGISTROS ------------" + "\n"; logAcciones.setText(this.sLogTxt); if (fProblema == 0) { JOptionPane.showMessageDialog(null, "\nHE CARGADO:" + this.nDatos + " REGISTROS", "INFORMACIN", JOptionPane.WARNING_MESSAGE); } else { JOptionPane.showMessageDialog(null, "\nSE HAN DETECTADO PROBLEMAS, SE CANCELA LA CARGA DE ARCHIVO EXEL", "AVISO", JOptionPane.WARNING_MESSAGE); } }
From source file:org.nekorp.workflow.desktop.control.imp.ProgramacionServicioWizardImp.java
License:Apache License
private List<String> procesarEncabezado(Row encabezado) { int index = 0; List<String> respuesta = new LinkedList<>(); for (Cell cell : encabezado) { if (index < encabezadoEsperado.length) { String value = cell.getRichStringCellValue().getString(); if (!StringUtils.equalsIgnoreCase(value, encabezadoEsperado[index])) { throw new IllegalArgumentException("el encabezado no es valido"); }/* w w w .jav a2 s . c o m*/ index = index + 1; } String content = cell.getRichStringCellValue().getString(); if (!StringUtils.isEmpty(content)) { respuesta.add(content); } else { respuesta.add("sin valor"); } } return respuesta; }
From source file:org.nekorp.workflow.desktop.control.imp.ProgramacionServicioWizardImp.java
License:Apache License
private String getStringValue(Cell cell) { if (cell.getCellType() == Cell.CELL_TYPE_STRING) { return cell.getRichStringCellValue().getString(); }/*from ww w . j av a 2 s. c o m*/ if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { Double raw = cell.getNumericCellValue(); DecimalFormat fm = new DecimalFormat("#.##"); String r = fm.format(raw); return r; } return null; }
From source file:org.nekorp.workflow.desktop.control.imp.ProgramacionServicioWizardImp.java
License:Apache License
private Long getLongValue(Cell cell) { if (cell.getCellType() == Cell.CELL_TYPE_STRING) { String raw = cell.getRichStringCellValue().getString(); try {//ww w . jav a 2s. c o m return Long.parseLong(raw); } catch (NumberFormatException e) { return null; } } if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { Double raw = cell.getNumericCellValue(); return raw.longValue(); } return null; }
From source file:org.obiba.onyx.core.etl.participant.impl.ParticipantReader.java
License:Open Source License
@SuppressWarnings("unchecked") private void initAttributeNameToColumnIndexMap(ExecutionContext context, Row headerRow) { if (headerRow == null) { AppointmentUpdateLog.addErrorLog(context, new AppointmentUpdateLog(new Date(), AppointmentUpdateLog.Level.ERROR, "Abort updating appointments: Reading file error: Null headerRow")); throw new IllegalArgumentException("Null headerRow"); }//w ww .j a va2 s. c om attributeNameToColumnIndexMap = new CaseInsensitiveMap(); Iterator<Cell> cellIter = headerRow.cellIterator(); while (cellIter.hasNext()) { Cell cell = cellIter.next(); if (cell != null) { if (cell.getCellType() != Cell.CELL_TYPE_STRING) { AppointmentUpdateLog.addErrorLog(context, new AppointmentUpdateLog(new Date(), AppointmentUpdateLog.Level.ERROR, "Abort updating appointments: Reading file error: Header row contains unexpected cell type")); throw new IllegalArgumentException("Header row contains unexpected cell type"); } String columnName = cell.getRichStringCellValue().getString(); if (columnName != null) { String attributeName = columnNameToAttributeNameMap.get(columnName); if (attributeName != null) { if (!attributeNameToColumnIndexMap.containsKey(attributeName)) { attributeNameToColumnIndexMap.put(attributeName, cell.getColumnIndex()); } else { AppointmentUpdateLog.addErrorLog(context, new AppointmentUpdateLog(new Date(), AppointmentUpdateLog.Level.ERROR, "Abort updating appointments: Reading file error: Duplicate column for field: " + attributeName)); throw new IllegalArgumentException("Duplicate column for field: " + attributeName); } } } } } log.info("attributeNameToColumnIndexMap: {}", attributeNameToColumnIndexMap); checkColumnsForMandatoryAttributesPresent(); }
From source file:org.obiba.onyx.core.io.support.ExcelReaderSupport.java
License:Open Source License
public static Boolean getBooleanValue(HSSFFormulaEvaluator evaluator, Cell cell) { Boolean rvalue = false;//www.j ava2 s. co m if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { evaluator.evaluate(cell); CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: rvalue = cellValue.getBooleanValue(); break; case Cell.CELL_TYPE_STRING: rvalue = Boolean.parseBoolean(cellValue.getStringValue()); break; } } else { switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: rvalue = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_STRING: rvalue = Boolean.parseBoolean(cell.getRichStringCellValue().getString()); break; } } if (rvalue == null) { throw new IllegalArgumentException("Unexpected cell type"); } return rvalue; }
From source file:org.obiba.onyx.core.io.support.ExcelReaderSupport.java
License:Open Source License
public static String getTextValue(HSSFFormulaEvaluator evaluator, Cell cell) { String rvalue = null;//from www . j a v a 2 s . c o m if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { CellValue cellValue = evaluator.evaluate(cell); if (cellValue.getCellType() == Cell.CELL_TYPE_STRING) { rvalue = cellValue.getStringValue(); } } else { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: rvalue = cell.getRichStringCellValue().getString(); break; case Cell.CELL_TYPE_NUMERIC: // If the cell type is NUMERIC, cast the value as a long and return it as a String. rvalue = (Long.valueOf((long) cell.getNumericCellValue())).toString(); break; case Cell.CELL_TYPE_BLANK: rvalue = ""; } } if (rvalue == null) { throw new IllegalArgumentException("Unexpected cell type"); } return rvalue; }
From source file:org.obiba.onyx.core.io.support.ExcelReaderSupport.java
License:Open Source License
public static boolean containsWhitespace(HSSFFormulaEvaluator evaluator, Cell cell) { boolean containsWhitespace = false; String textValue = null;/* w w w . j a va2s . co m*/ if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { CellValue cellValue = evaluator.evaluate(cell); if (cellValue.getCellType() == Cell.CELL_TYPE_STRING) { textValue = cellValue.getStringValue(); } } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { textValue = cell.getRichStringCellValue().getString(); } if (textValue != null) { if (textValue.trim().length() == 0) { containsWhitespace = true; } } return containsWhitespace; }
From source file:org.olat.search.service.document.file.ExcelOOXMLDocument.java
License:Apache License
private void extractContent(final StringBuilder buffy, final XSSFWorkbook document) { for (int i = 0; i < document.getNumberOfSheets(); i++) { final XSSFSheet sheet = document.getSheetAt(i); buffy.append(document.getSheetName(i)).append(' '); // Header(s), if present extractHeaderFooter(buffy, sheet.getFirstHeader()); extractHeaderFooter(buffy, sheet.getOddHeader()); extractHeaderFooter(buffy, sheet.getEvenHeader()); // Rows and cells for (final Object rawR : sheet) { final Row row = (Row) rawR; for (final Iterator<Cell> ri = row.cellIterator(); ri.hasNext();) { final Cell cell = ri.next(); if (cell.getCellType() == Cell.CELL_TYPE_FORMULA || cell.getCellType() == Cell.CELL_TYPE_STRING) { buffy.append(cell.getRichStringCellValue().getString()).append(' '); } else { final XSSFCell xc = (XSSFCell) cell; final String rawValue = xc.getRawValue(); if (rawValue != null) { buffy.append(rawValue).append(' '); }/*from ww w . ja v a 2 s. co m*/ } // Output the comment in the same cell as the content final Comment comment = cell.getCellComment(); if (comment != null) { buffy.append(comment.getString().getString()).append(' '); } } } // Finally footer(s), if present extractHeaderFooter(buffy, sheet.getFirstFooter()); extractHeaderFooter(buffy, sheet.getOddFooter()); extractHeaderFooter(buffy, sheet.getEvenFooter()); } }
From source file:org.opencities.berlin.uploaddata.service.Worker.java
/** * loop through all Cells and rows. Firstly, add correct keys to strings. * Secondly, parse corresponding value into correct json and add this * dataset to ckan via middleware.//from w w w . j a v a 2s. co m * * @param args * @throws Exception */ @SuppressWarnings("rawtypes") public String readXlsx() { String errormessage = ""; CKANGateway gw = new CKANGateway(ckan, key); HashMap<String, String> map = new HashMap<String, String>(); ArrayList<String> strings = new ArrayList<String>(); XSSFWorkbook workBook = null; try { workBook = new XSSFWorkbook(uploadFolder + "file.xlsx"); } catch (IOException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } int counter = 0; XSSFSheet sheet = workBook.getSheetAt(0); for (Row row : sheet) { for (Cell cell : row) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: String value = cell.getRichStringCellValue().getString(); // first row, add value to strings if (counter == 0) { if (!value.startsWith("resources:") && !value.startsWith("extras:")) map.put(value, null); strings.add(value); break; } if (strings.get(cell.getColumnIndex()).equalsIgnoreCase("tags") || strings.get(cell.getColumnIndex()).equalsIgnoreCase("groups")) { String[] tmp = value.split(","); String out = buildString(tmp); map.put(strings.get(cell.getColumnIndex()), out); } else if (strings.get(cell.getColumnIndex()).startsWith("resources:")) { String[] tmp = strings.get(cell.getColumnIndex()).split(":"); parseResource(tmp[1], value); } else if (strings.get(cell.getColumnIndex()).startsWith("extras:")) { String[] tmp = strings.get(cell.getColumnIndex()).split(":"); parseExtras(tmp[1], value); } else { map.put(strings.get(cell.getColumnIndex()), "\"" + value + "\""); } break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { // is a date; map = handleDate(map, strings, cell); } else { // is a number; map = handleNumber(map, strings, cell); } break; default: break; } } // finish extras and resources finishParseResource(); finishParseExtras(); map = checkEmptyValues(map); map = toLowerCase(map); // add resources and extras to map map.put("resources", resourceString); map.put("extras", extrasString); if (counter >= 1) { // add dataset to CKAN via middleware HashMap<String, HashMap> out = gw.createMetaDataSet(map); if (out == null) errormessage += String.valueOf(counter) + ","; } ++counter; resourceString = resetResourceString(); extrasString = resetExtrasString(); } if (errormessage.equalsIgnoreCase("")) return errormessage; else return errormessage.substring(0, errormessage.length() - 1); }