List of usage examples for org.apache.poi.ss.usermodel DateUtil isCellDateFormatted
public static boolean isCellDateFormatted(Cell cell)
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 {//from www . ja v a 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 . ja v a2 s . co m*/ */ 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 . ja v a 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);// ww w .ja 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:ru.icc.cells.ssdc.DataLoader.java
License:Apache License
private String extractCellValue(Cell excelCell) { String value = null;/*from w w w .j av a 2 s . c o m*/ switch (excelCell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(excelCell)) value = "DATE"; // TODO - ? else value = Double.toString(excelCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: value = excelCell.getRichStringCellValue().getString(); break; case Cell.CELL_TYPE_BOOLEAN: value = Boolean.toString(excelCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: //value = excelCell.getCellFormula(); value = extractCellFormulaValue(excelCell); break; case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_ERROR: break; } return value; }
From source file:ru.icc.cells.ssdc.DataLoader.java
License:Apache License
private void fillCell(CCell cell, Cell excelCell) { String rawTextualContent = null; CellType cellType = null;/* w w w. j a va 2 s . c om*/ String text = null; if (withoutSuperscript) { if (hasSuperscriptText(excelCell)) { text = getNotSuperscriptText(excelCell); } else { text = getText(excelCell); } } else { text = getText(excelCell); } cell.setText(text); rawTextualContent = getFormatCellValue(excelCell); cell.setRawText(rawTextualContent); switch (excelCell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(excelCell)) { //rawTextualContent = "DATE"; // TODO - ? cellType = CellType.DATE; } else { cellType = CellType.NUMERIC; } break; case Cell.CELL_TYPE_STRING: cellType = CellType.STRING; break; case Cell.CELL_TYPE_BOOLEAN: cellType = CellType.BOOLEAN; break; case Cell.CELL_TYPE_FORMULA: cellType = CellType.FORMULA; break; case Cell.CELL_TYPE_BLANK: cellType = CellType.BLANK; break; case Cell.CELL_TYPE_ERROR: cellType = CellType.ERROR; break; } cell.setId(this.cellCount); cell.setCellType(cellType); int height = excelCell.getRow().getHeight(); cell.setHeight(height); int width = excelCell.getSheet().getColumnWidth(excelCell.getColumnIndex()); cell.setWidth(width); CellStyle excelCellStyle = excelCell.getCellStyle(); CStyle cellStyle = cell.getStyle(); fillCellStyle(cellStyle, excelCellStyle); String reference = new CellReference(excelCell).formatAsString(); cell.setProvenance(reference); this.cellCount++; }
From source file:ru.spb.nicetu.tableviewer.server.XlsToHtml.java
License:Apache License
private void printSheetContent(Sheet sheet) { printColumnHeads();/*from w w w . j av a 2 s . com*/ 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;/*from w ww. 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:step.datapool.excel.ExcelFunctions.java
License:Open Source License
/** * Konvertiert unterschiedliche Formate in Strings. * * @param cell Excel Zelle// w w w. jav a2 s . c om * @param evaluator FormulaEvaluator * @return Wert der Zelle als String */ public static String getCellValueAsString(Cell cell, FormulaEvaluator evaluator) { boolean isFormulaPatched = false; String initialFormula = null; int chkTyp = cell.getCellType(); if (chkTyp == Cell.CELL_TYPE_FORMULA) { initialFormula = cell.getCellFormula(); // Some formula have to be changed before they can be evaluated in POI String formula = FormulaPatch.patch(initialFormula); if (!formula.equals(initialFormula)) { isFormulaPatched = true; cell.setCellFormula(formula); evaluator.notifySetFormula(cell); } } try { int typ = evaluateFormulaCell(cell, evaluator); if (typ == -1) typ = cell.getCellType(); switch (typ) { case Cell.CELL_TYPE_NUMERIC: /* Datum und Zeit (sind auch Zahlen) */ if (DateUtil.isCellDateFormatted(cell)) { Date dat = cell.getDateCellValue(); GregorianCalendar cal = new GregorianCalendar(); cal.setTime(dat); /* * In Excel beginnt die Zeitrechnung am 01.01.1900. Ein Datum ist immer als * double gespeichert. Dabei ist der Teil vor dem Dezimalpunkt das Datum * und der Teil nach dem Dezimalpunkt die Zeit (z.B. 1.5 entspricht 01.01.1900 12:00:00). * Falls der Tag 0 angegeben ist wird der Datumsanteil mit 31.12.1899 zurueck- * gegeben. Erhalten wir also ein Jahr kleiner als 1900, dann haben wir eine * Zeit. */ if (cal.get(Calendar.YEAR) < 1900) { // Zeitformat SimpleDateFormat STD_TIM = new SimpleDateFormat("kk:mm:ss"); return STD_TIM.format(dat); } SimpleDateFormat STD_DAT = new SimpleDateFormat("dd.MM.yyyy"); return STD_DAT.format(dat); // Datumsformat } else { /* int, long, double Formate */ double dbl = cell.getNumericCellValue(); int tryInt = (int) dbl; long tryLong = (long) dbl; if (tryInt == dbl) { return new Integer(tryInt).toString(); // int-Format } else if (tryLong == dbl) { return new Long(tryLong).toString(); // long-Format } // return new Double(dbl).toString(); // double-Format String numberValueString = new Double(dbl).toString(); // double-Format // always use decimal format try { // scale 14 to solve problem like value 0.22 --> 0.219999999999997 BigDecimal roundedBigDecimal = new BigDecimal(numberValueString).setScale(14, RoundingMode.HALF_UP); // use constructor BigDecimal(String)! String customValueString = getCustomDecimalFormat().format(roundedBigDecimal); if (!customValueString.equals(numberValueString)) { logger.debug("getCellValusAsString: Changing string value of double '{}' to '{}'", numberValueString, customValueString); numberValueString = customValueString; // bigdecimal-format } } catch (Exception e) { logger.error("An error occurred trying to convert the cell value number to decimal format " + numberValueString, e); } return numberValueString; } case Cell.CELL_TYPE_BOOLEAN: return Boolean.toString(cell.getBooleanCellValue()); case Cell.CELL_TYPE_FORMULA: /* Dieser Fall wird jetzt nie eintreffen, da im Falle einer Formel neu die * Berechnung zurueckgegeben wurde, die dann einen eigenen Typ hat. */ return cell.getCellFormula(); case Cell.CELL_TYPE_STRING: return cell.getRichStringCellValue().getString(); case Cell.CELL_TYPE_BLANK: return ""; case Cell.CELL_TYPE_ERROR: switch (cell.getErrorCellValue()) { case 1: return "#NULL!"; case 2: return "#DIV/0!"; case 3: return "#VALUE!"; case 4: return "#REF!"; case 5: return "#NAME?"; case 6: return "#NUM!"; case 7: return "#N/A"; default: return "#ERR!"; } default: return "ERROR: unknown Format"; } } finally { if (isFormulaPatched) { cell.setCellFormula(initialFormula); evaluator.notifySetFormula(cell); } } }
From source file:swift.selenium.WebHelper.java
License:Open Source License
/** * This method reads and returns data from each cell of a provided worksheet * //from w w w . j a v a2 s .c o m * @param reqValue * @param reqSheet * @param rowIndex * @param inputHashTable * @return * @throws IOException */ @SuppressWarnings("null") public static String getCellData(String reqValue, HSSFSheet reqSheet, int rowIndex, HashMap<String, Object> inputHashTable) throws IOException { HSSFCell reqCell = null; Object actualvalue = null; String req = ""; DataFormatter fmt = new DataFormatter(); if (inputHashTable.isEmpty() == true) { inputHashTable = getValueFromHashMap(reqSheet); } HSSFRow rowActual = reqSheet.getRow(rowIndex); if (inputHashTable.get(reqValue) == null) { TransactionMapping.report.setStrMessage("Column " + reqValue + " not Found. Please Check input Sheet"); TransactionMapping.pauseFun("Column " + reqValue + " not Found. Please Check input Sheet"); } else { actualvalue = inputHashTable.get(reqValue);//rowHeader.getCell(colIndex).toString(); if (actualvalue != null) { int colIndex = Integer.parseInt(actualvalue.toString()); reqCell = rowActual.getCell(colIndex); //TM 27-04-2015: Updated the code for formula in cells if (reqCell == null) { System.out.println(reqValue + " is Null"); } else { HSSFWorkbook wb = reqCell.getSheet().getWorkbook(); //TM-05/05/2015: Get workbook instance from the worksheet HSSFFormulaEvaluator.evaluateAllFormulaCells(wb); //TM-05/05/2015: To refresh all the formulas in the worksheet FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); CellValue cellValue = evaluator.evaluate(reqCell); int type = 0; if (cellValue != null) { type = cellValue.getCellType(); } else { type = reqCell.getCellType(); } switch (type) { case HSSFCell.CELL_TYPE_BLANK: req = ""; break; case HSSFCell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(reqCell)) { SimpleDateFormat form = new SimpleDateFormat( Automation.configHashMap.get("DATEFORMAT").toString()); req = form.format(reqCell.getDateCellValue()); } else req = fmt.formatCellValue(reqCell, evaluator); break; case HSSFCell.CELL_TYPE_STRING: req = reqCell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BOOLEAN: req = Boolean.toString(reqCell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: req = "error"; break; } } } else { req = reqCell.getStringCellValue(); System.out.println("null"); } } return req; }