List of usage examples for org.apache.poi.ss.usermodel Cell getDateCellValue
Date getDateCellValue();
From source file:org.wandora.application.tools.extractors.excel.AbstractExcelExtractor.java
License:Open Source License
protected String getCellValueAsString(Cell cell, int type) { if (cell != null) { switch (type) { case Cell.CELL_TYPE_ERROR: { return "ERROR" + cell.getErrorCellValue(); }/* www . jav a 2 s. com*/ case Cell.CELL_TYPE_BOOLEAN: { return "" + cell.getBooleanCellValue(); } case Cell.CELL_TYPE_NUMERIC: { if (DateUtil.isCellDateFormatted(cell)) { return dateFormat.format(cell.getDateCellValue()); } else { double value = cell.getNumericCellValue(); String formatString = cell.getCellStyle().getDataFormatString(); int formatIndex = cell.getCellStyle().getDataFormat(); return formatter.formatRawCellContents(value, formatIndex, formatString); } } case Cell.CELL_TYPE_STRING: { return cell.getRichStringCellValue().getString(); } } } return null; }
From source file:plugins.excel.client.util.ExcelReader.java
License:Microsoft Reference Source License
private Object getCellValue(Cell c, int targetType) { int cellType = c.getCellType(); Object val; try {//w w w.j a va 2 s .co m switch (cellType) { case (Cell.CELL_TYPE_STRING): case (Cell.CELL_TYPE_FORMULA): val = c.getStringCellValue(); switch (targetType) { case Types.BOOLEAN: return Boolean.parseBoolean((String) val); case Types.DOUBLE: return Double.parseDouble((String) val); case Types.INTEGER: return Integer.parseInt((String) val); case Types.VARCHAR: return (String) val; case Types.DATE: SimpleDateFormat sdf = new SimpleDateFormat(); try { return sdf.parse((String) val); } catch (ParseException e) { e.printStackTrace(); } } break; case (Cell.CELL_TYPE_NUMERIC): if (DateUtil.isCellDateFormatted(c)) { val = c.getDateCellValue(); switch (targetType) { case Types.BOOLEAN: return (((Date) val).getTime() > 0); case Types.DOUBLE: return (double) ((Date) val).getTime(); case Types.INTEGER: return ((Date) val).getTime(); case Types.VARCHAR: DateFormat df = new SimpleDateFormat("dd.MM.yyyy HH:mm:ss"); return df.format((Date) val); case Types.DATE: return (Date) val; } } else { val = c.getNumericCellValue(); switch (targetType) { case Types.BOOLEAN: return ((double) val > 0.0); case Types.DOUBLE: return (double) val; case Types.INTEGER: return (long) val; case Types.VARCHAR: return new Double((double) val).toString(); case Types.DATE: Date d = new Date(); d.setTime((long) val); return d; } } break; case (Cell.CELL_TYPE_ERROR): val = c.getErrorCellValue(); switch (targetType) { case Types.BOOLEAN: return ((int) val > 0); case Types.DOUBLE: return (double) val; case Types.INTEGER: return (int) val; case Types.VARCHAR: return new Integer((int) val).toString(); case Types.DATE: Date d = new Date(); d.setTime((long) val); return d; } break; case (Cell.CELL_TYPE_BOOLEAN): val = c.getBooleanCellValue(); switch (targetType) { case Types.BOOLEAN: return (boolean) val; case Types.DOUBLE: return (double) (((boolean) val ? 1 : 0)); case Types.INTEGER: return (int) (((boolean) val ? 1 : 0)); case Types.VARCHAR: return new Boolean((boolean) val).toString(); case Types.DATE: Date d = new Date(); d.setTime((long) (((boolean) val ? 1 : 0))); return d; } break; } } catch (IllegalStateException e) { Dialog.msgBox( "Could not import cell r:" + c.getRowIndex() + " c: " + c.getColumnIndex() + " because of data type errors in the sheet", "Import Excel File", Dialog.ERROR_MESSAGE); } return null; }
From source file:pruebaimportarexcel.excel.Excel.java
/** * Devuelve un objeto del tipo que contiene la celda. * * @return/*from w w w . j a va2 s. com*/ */ public static Object getCellValue(Cell cell) { Object result = null; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { result = cell.getDateCellValue(); } else { if (cell.getNumericCellValue() == (int) cell.getNumericCellValue()) { result = new Integer((int) cell.getNumericCellValue()); } else { result = new Double(cell.getNumericCellValue()); } } break; case Cell.CELL_TYPE_STRING: result = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: result = new Boolean(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: case Cell.CELL_TYPE_BLANK: case Cell.CELL_TYPE_ERROR: result = null; break; } } return result; }
From source file:resources.ministory.MinistoryFormItem.java
private Date getCellDate(Cell cell) { if (DateUtil.isCellDateFormatted(cell)) { return cell.getDateCellValue(); } else {//from w w w.j a va 2 s. c o m return new Date(); } }
From source file:ro.dabuno.office.integration.Xlsx2Word.java
public static void main(String[] args) throws Exception { log.info("starting app"); // Workbook wb = new XSSFWorkbook(new FileInputStream(args[0])); Workbook wb = new XSSFWorkbook(new FileInputStream("office-files/Input.xlsx")); DataFormatter formatter = new DataFormatter(); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i);/* w ww . j a v a 2 s .c o m*/ System.out.println(wb.getSheetName(i)); int j = 4; for (Row row : sheet) { System.out.println("rownum: " + row.getRowNum()); for (Cell cell : row) { CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex()); System.out.print(cellRef.formatAsString()); System.out.print(" - "); // get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc) String text = formatter.formatCellValue(cell); System.out.println(text); System.out.println("------------"); // Alternatively, get the value and format it yourself switch (cell.getCellTypeEnum()) { case STRING: System.out.println(cell.getRichStringCellValue().getString()); break; case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { System.out.println(cell.getDateCellValue()); } else { System.out.print(cellRef.formatAsString()); System.out.print(" - "); System.out.println((long) cell.getNumericCellValue()); } break; case BOOLEAN: System.out.println(cell.getBooleanCellValue()); break; case FORMULA: System.out.println(cell.getCellFormula()); break; case BLANK: System.out.println(); break; default: System.out.println(); } } j--; if (j == 0) { break; } } } XWPFDocument doc = new XWPFDocument(); XWPFParagraph p0 = doc.createParagraph(); XWPFRun r0 = p0.createRun(); r0.setBold(false); r0.setText("Domnule"); XWPFRun r00 = p0.createRun(); r00.setBold(true); r00.setText(" Ionescu Ion"); FileOutputStream out = new FileOutputStream("out/xlsx2word.docx"); doc.write(out); out.close(); }
From source file:ro.fortsoft.dataset.xls.XlsDataSet.java
License:Apache License
@Override public Object getObject(int fieldIndex) { Sheet sheet = workbook.getSheetAt(sheetIndex); Cell cell = sheet.getRow(cursorPosition).getCell(fieldIndex); if (cell == null) { return null; }// w w w.ja va 2 s . c om Class<?> valueClass = metaData.getFieldClass(fieldIndex); if (String.class.equals(valueClass)) { return cell.getStringCellValue(); } else if (Boolean.class.equals(valueClass)) { return cell.getBooleanCellValue(); } else if (Number.class.isAssignableFrom(valueClass)) { return cell.getNumericCellValue(); } else if (Date.class.isAssignableFrom(valueClass)) { return cell.getDateCellValue(); } throw new DataSetException("Cannot convert cell value of field '" + metaData.getFieldName(fieldIndex) + "' to '" + valueClass + "'"); }
From source file:ru.spb.nicetu.tableviewer.server.XlsToHtml.java
License:Apache License
private void printSheetContent(Sheet sheet) { printColumnHeads();//ww w . j a v a 2s. co m out.format("<tbody>%n"); Iterator<Row> rows = sheet.rowIterator(); int lastNum = -1; while (rows.hasNext()) { Row row = rows.next(); int curNum = row.getRowNum(); if (curNum - lastNum > 1) { for (int i = lastNum + 2; i <= curNum; i++) { out.format(" <tr>%n"); out.format(" <td class=%s>%d</td>%n", ROW_HEAD_CLASS, i); out.format(" <td colspan=%d style=\"%s\"> </td>%n", (endColumn - firstColumn + 1), styleSimpleContents(null, false)); out.format(" </tr>%n"); } } lastNum = curNum; out.format(" <tr>%n"); out.format(" <td class=%s>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1); for (int i = firstColumn; i < endColumn; i++) { String content = " "; String attrs = ""; CellStyle style = null; boolean isNumeric = false; if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) { Cell cell = row.getCell(i); if (cell != null) { style = cell.getCellStyle(); attrs = tagStyle(cell, style); CellFormat cf = CellFormat.getInstance(style.getDataFormatString()); CellFormatResult result = cf.apply(cell); content = result.text; if (content != null && !content.equals("") && (cell.getCellType() == Cell.CELL_TYPE_NUMERIC || cell.getCellType() == Cell.CELL_TYPE_FORMULA && cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC)) { if (DateUtil.isCellDateFormatted(cell)) { // Date if ("mmm-yy".equals(style.getDataFormatString())) { SimpleDateFormat sdfRus = new SimpleDateFormat("MMM.yy"); content = sdfRus.format(cell.getDateCellValue()); } else if ("h:mm".equals(style.getDataFormatString())) { SimpleDateFormat sdfRus = new SimpleDateFormat("HH:mm"); content = sdfRus.format(cell.getDateCellValue()); } else if (style.getDataFormatString() != null && style.getDataFormatString().contains("mm")) { SimpleDateFormat sdfRus = new SimpleDateFormat("dd.MM.yyyy HH:mm:ss"); content = sdfRus.format(cell.getDateCellValue()); } else { SimpleDateFormat sdfRus = new SimpleDateFormat("dd.MM.yyyy"); content = sdfRus.format(cell.getDateCellValue()); } } else { // Number if ("- 0".equals(content.trim())) content = " "; else content = "<nobr>" + content.replace(",", " ").replace(".", ",") + "</nobr>"; isNumeric = true; } } if (content == null || content.equals("")) content = " "; } } boolean isInRangeNotFirst = false; for (int j = 0; j < sheet.getNumMergedRegions(); j++) { CellRangeAddress rangeAddress = sheet.getMergedRegion(j); if (row.getRowNum() == rangeAddress.getFirstRow() && i == rangeAddress.getFirstColumn()) { if (rangeAddress.getLastRow() - row.getRowNum() > 0) attrs += " rowspan=" + (rangeAddress.getLastRow() - row.getRowNum() + 1); if (rangeAddress.getLastColumn() - i > 0) attrs += " colspan=" + (rangeAddress.getLastColumn() - i + 1); break; } else if (row.getRowNum() >= rangeAddress.getFirstRow() && row.getRowNum() <= rangeAddress.getLastRow() && i >= rangeAddress.getFirstColumn() && i <= rangeAddress.getLastColumn()) { isInRangeNotFirst = true; break; } } if (!isInRangeNotFirst) { out.format(" <td style=\"%s\" %s>%s</td>%n", styleSimpleContents(style, isNumeric), attrs, content); } } // columns out.format(" </tr>%n"); } // rows out.format("</tbody>%n"); }
From source file:Servelt.ExcelReader.java
private String cellToString(Cell cell) throws Exception { String data = null;/* ww w .j a v a 2 s.c o m*/ switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: data = cell.getRichStringCellValue().getString(); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { data = cell.getDateCellValue().toString(); } else { data = String.valueOf(cell.getNumericCellValue()); while (data.endsWith("0") && data.contains(".")) { data = data.substring(0, data.length() - 1); } if (data.endsWith(".")) data = data.substring(0, data.length() - 1); } break; case Cell.CELL_TYPE_BOOLEAN: data = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: data = cell.getCellFormula(); break; case Cell.CELL_TYPE_BLANK: data = ""; break; case Cell.CELL_TYPE_ERROR: throw new Exception("CELL_TYPE_ERROR"); } return data; }
From source file:sol.neptune.elisaboard.service.VPlanToHtml.java
License:Apache License
private void printSheetContent(Sheet sheet) { /* skip column heads */ //printColumnHeads(); out.format("<tbody>%n"); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next();//w ww. j av a 2 s . co m out.format(" <tr>%n"); /* skip first col*/ /* out.format(" <td class=%s>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1); */ for (int i = firstColumn; i < endColumn; i++) { String content = " "; String attrs = ""; CellStyle style = null; if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) { Cell cell = row.getCell(i); if (cell != null) { style = cell.getCellStyle(); attrs = tagStyle(cell, style); //Set the value that is rendered for the cell //also applies the format CellFormat cf = CellFormat.getInstance(style.getDataFormatString()); CellFormatResult result = cf.apply(cell); content = result.text; if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { final Date date = cell.getDateCellValue(); System.out.println("Date: " + date); System.out.println(new Date()); } if (content.equals("")) { content = " "; } } } out.format(" <td class=%s %s>%s</td>%n", styleName(style), attrs, content); } out.format(" </tr>%n"); } out.format("</tbody>%n"); }
From source file:standarapp.algorithm.ReadFileVector.java
public String lectureRegistry(String nameFile, String nameOut, int[] col, double percent, int rowBegin) { String answer = ""; int quantityFound = 0; workbook = Lecture.lectureXLSX(nameFile); sheet = workbook.getSheetAt(0);/*from w w w . ja va 2s . co m*/ for (Row row : sheet) { if (row.getRowNum() < rowBegin) { continue; } String[] cellsWI = new String[col.length + 1]; for (int i = 0; i < col.length; i++) { cellsWI[i] = ""; try { Cell cell = row.getCell(col[i]); if (cell.getCellType() == Cell.CELL_TYPE_STRING) { cellsWI[i] = deleteTrash(cell.getStringCellValue()); } else { cellsWI[i] = cell.getDateCellValue().toString(); cellsWI[col.length] = String.valueOf(cell.getDateCellValue().getMonth()); } if (i == 2) { cellsWI[i] = cell.getStringCellValue(); } } catch (Exception e) { } } registry.add(cellsWI); } System.out.println("Total: " + registry.size()); /*for (int i = 0; i < registry.size(); i++) { System.out.println(i +" Municipio: " + registry.get(i)[0] + " | Localidad: " + registry.get(i)[1] + " | Especie: " + registry.get(i)[2] + " | Fecha: " + registry.get(i)[3]); }*/ int rowCount = 0; int columnCount = 0; CellStyle cs = workbook.createCellStyle(); //Font font = workbook.createFont(); cs.setAlignment(HorizontalAlignment.CENTER); cs.setVerticalAlignment(VerticalAlignment.CENTER); cs.setBorderRight(BorderStyle.THIN); cs.setBorderLeft(BorderStyle.THIN); cs.setBorderBottom(BorderStyle.THIN); cs.setBorderTop(BorderStyle.THIN); sheet = workbook.createSheet(); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); sheet.setColumnWidth(0, 20); Row row = sheet.createRow(0); Cell encabezado = row.createCell(rowCount); encabezado.setCellValue("Base de datos coordenadas"); encabezado.setCellStyle(cs); CellRangeAddress region = new CellRangeAddress(0, 0, 0, 8); sheet.addMergedRegion(region); row = sheet.createRow(++rowCount); Cell cell = row.createCell(columnCount); cell.setCellValue("Especie"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Municipio"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Codigo Municipio"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Vereda"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Codigo Vereda"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Mes"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Ao"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Latitud"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Longitud"); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue("Fuente"); cell.setCellStyle(cs); for (int i = 0; i < registry.size(); i++) { try { String[] registro = registry.get(i); columnCount = -1; int cod_Mncp = 0; row = sheet.createRow(++rowCount); double levenstein = 0; double localidad_oficial = 0; double levensteinActual = 0; for (Integer codMunicipio : codigo_Municipio.keySet()) { if (registro[0].equals(codigo_Municipio.get(codMunicipio))) { cod_Mncp = codMunicipio; break; } try { double levenstein_local = FuzzySearch.ratio(registro[0], codigo_Municipio.get(codMunicipio)); if (levenstein_local >= levensteinActual) { cod_Mncp = codMunicipio; levensteinActual = levenstein_local; } if (levensteinActual == 100) { break; } } catch (Exception e) { } } for (Double cod_Loc : mncp_localidad.get(cod_Mncp).keySet()) { String loc = mncp_localidad.get(cod_Mncp).get(cod_Loc); if (registro[1].equals(loc)) { localidad_oficial = cod_Loc; levenstein = 101; } try { double levenstein_local = FuzzySearch.ratio(registro[1], loc); if (levenstein_local >= levenstein) { localidad_oficial = cod_Loc; levenstein = levenstein_local; } if (levenstein == 100) { break; } } catch (Exception e) { } } String mncp_oficial = codigo_Municipio.get(cod_Mncp); String loc_oficial = codigo_localidad.get(localidad_oficial); String especie = registro[2]; double locX = localidad_x.get(localidad_oficial); double locY = localidad_y.get(localidad_oficial); int year = 0; int month = 0; System.out.println(); try { year = Integer.parseInt(registro[3].split(" ")[5]); month = Integer.parseInt(registro[registro.length - 1]) + 1; } catch (Exception e) { year = Integer.parseInt(registro[3].substring(registro[3].length() - 4)); month = Integer .parseInt(registro[3].substring(registro[3].length() - 7, registro[3].length() - 5)); } quantityFound++; cell = row.createCell(++columnCount); cell.setCellValue(especie); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(mncp_oficial); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(cod_Mncp); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(loc_oficial); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(localidad_oficial); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(month); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(year); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(locY); cell.setCellStyle(cs); cell = row.createCell(++columnCount); cell.setCellValue(locX); cell.setCellStyle(cs); } catch (Exception e) { continue; } } sheet.setColumnWidth(0, 5800); sheet.setColumnWidth(1, 5800); sheet.setColumnWidth(2, 3000); sheet.setColumnWidth(3, 5800); sheet.setColumnWidth(4, 3000); sheet.setColumnWidth(5, 3000); sheet.setColumnWidth(6, 3000); sheet.setColumnWidth(7, 6400); sheet.setColumnWidth(8, 6400); answer = "Se generaron " + quantityFound + " vector(es)"; try (FileOutputStream outputStream = new FileOutputStream(nameOut)) { workbook.write(outputStream); } catch (IOException ex) { quantityFound = 0; answer = "Cerrar el archivo de entrada "; } return answer; }