List of usage examples for org.apache.poi.ss.usermodel Cell getDateCellValue
Date getDateCellValue();
From source file:com.hurence.logisland.processor.excel.ExcelExtract.java
License:Apache License
/** * Handle row content and transform it into a {@link Record} * * @param row the {@link Row}/* www . j a v a2 s .c o m*/ * @return the transformed {@link Record} */ private Record handleRow(Row row, List<String> header) { Record ret = new StandardRecord().setTime(new Date()); int index = 0; for (Cell cell : row) { if (configuration.getFieldNames() != null && index >= configuration.getFieldNames().size()) { //we've reached the end of mapping. Go to next row. break; } if (configuration.getColumnsToSkip().contains(cell.getColumnIndex())) { //skip this cell. continue; } String fieldName = header != null ? header.get(cell.getColumnIndex()) : configuration.getFieldNames().get(index++); Field field; // Alternatively, get the value and format it yourself switch (cell.getCellTypeEnum()) { case STRING: field = new Field(fieldName, FieldType.STRING, cell.getStringCellValue()); break; case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { field = new Field(fieldName, FieldType.LONG, cell.getDateCellValue().getTime()); } else { field = new Field(fieldName, FieldType.DOUBLE, cell.getNumericCellValue()); } break; case BOOLEAN: field = new Field(fieldName, FieldType.BOOLEAN, cell.getBooleanCellValue()); break; case FORMULA: field = new Field(fieldName, FieldType.STRING, cell.getCellFormula()); break; default: //blank or unknown field = new Field(fieldName, FieldType.NULL, null); break; } ret.setField(field); } return ret; }
From source file:com.hust.zsuper.DealWithPatent.ExcelToMySQL.java
License:Open Source License
private static Object getCellValue(Cell cell) { switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { return cell.getDateCellValue(); } else {//from ww w. j a v a 2 s .c om return String.valueOf(cell.getNumericCellValue()); } case Cell.CELL_TYPE_STRING: default: return cell.getStringCellValue().trim(); } }
From source file:com.hust.zsuper.DealWithPatent.WorkhseetToMySQL.java
License:Open Source License
private String getStringValue(ExcelType type, Cell cell) { switch (type) { case DATE:// w ww . j av a 2 s .c o m return "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm").format(cell.getDateCellValue()) + "'"; case NUMERIC: return String.valueOf(cell.getNumericCellValue()); case BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); case STRING: return "'" + cell.getStringCellValue().replaceAll("'", "\\\\'") + "'"; default: return null; } }
From source file:com.ibm.db2j.GExcel.java
License:Open Source License
/** * Put the next row in the dvd row given in parameter. * Return SCAN_COMPLETED if there is no more row in the spreadsheet, or GOT_ROW if a row was successfully put in the dvd row. * //from w w w .j av a 2 s.c o m * Uses the attribute currentRow to save the previous row fetched. * * @param sheet * @param dvdr * @param numberOfLogicalColumnsInvolved * @param columnIndexes * @return SCAN_COMPLETED or GOT_ROW * @throws SQLException */ private int createNextRow(Sheet sheet, DataValueDescriptor[] dvdr) { boolean gotData = false; /* * Find the next row to return. * * currentRow should currently point to the last row returned. * If that's null, then start from first row. * Else, search for the next non-empty row (until we hit the end of the prescribed range). */ if (currentRow == null) currentRow = sheet.getRow(firstRowIndex + (firstRowIsMetaData ? 1 : 0)); else { int nextRowIndex = currentRow.getRowNum() + 1; currentRow = null; if (stopScanOnFirstEmptyRow) { currentRow = sheet.getRow(nextRowIndex); } else { while (currentRow == null && nextRowIndex <= lastRowIndex) { currentRow = sheet.getRow(nextRowIndex); nextRowIndex++; } } } /* * If we've run out of spreadsheet (currentRow == null) or gone out of the prescribed range, * then scan complete - return that. */ if (currentRow == null || currentRow.getRowNum() > lastRowIndex) { return SCAN_COMPLETED; } /* * Get the offset of the first column in the spreadsheet. * Note: this is used when iterating below, so that we can correctly relate * the actual column in the spreadsheet to the correct 'column' in the * DataValueDescriptor [] representing the row. */ int columnOffset = firstColumnIndex; //Figure out how many columns there are int numberOfColumns = lastColumnIndex - firstColumnIndex + 1; for (int i = 0; i < numberOfColumns; i++) { /* * Note: i is used to refer to the index of the DataValueDescriptor which represents * the actual spreadsheet column (at i + columnOffset) in the DataValueDescriptor[] * representing this row. */ Cell cell = currentRow.getCell(i + columnOffset); if (cell == null) { dvdr[i].setToNull(); } else { try { int cellValueType = cell.getCellType(); if (cellValueType == Cell.CELL_TYPE_FORMULA) cellValueType = cell.getCachedFormulaResultType(); switch (cellValueType) { case Cell.CELL_TYPE_STRING: dvdr[i].setValue(cell.getStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) dvdr[i].setValue(new java.sql.Date(cell.getDateCellValue().getTime())); else { cell.setCellType(Cell.CELL_TYPE_STRING); dvdr[i].setValue(cell.getStringCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: dvdr[i].setValue(cell.getBooleanCellValue()); break; default: dvdr[i].setToNull(); break; } //If a cell has data that is not null - then flag that we actually have data to return if (!dvdr[i].isNull()) gotData = true; } catch (Exception e) { dvdr[i].setToNull(); logger.logWarning(GDBMessages.DSWRAPPER_GEXCEL_MAP_LT_ERROR, "Excel cell [spreadsheet " + sheet.getSheetName() + "; row " + cell.getRow().getRowNum() + "; column " + cell.getColumnIndex() + "; value " + cell + "] could not be mapped into the logical table because of the column logical type: " + e); } } } if (!gotData && stopScanOnFirstEmptyRow) { logger.logInfo( "Ending GExcel table scan on first empty row (as no row limit was specified in the ending cell config constraint)"); return SCAN_COMPLETED; } return GOT_ROW; }
From source file:com.ibm.db2j.GExcel.java
License:Open Source License
/** * looks for the column definition and initializes the following attributes : * /*ww w . j a v a 2 s . c om*/ * - numberOfColumns * - columnIndexes * - columnNames * * If a column which contains no values is ignored. * * If firstRowIsMetaData is true, the column names will be extract from the first row of the spreadsheet. * Else, they will be automatically generated : COLUMN1, COLUMN2... * * @param sheet */ private void findColumns(Sheet sheet) { numberOfColumns = 0; columnIndexes = new ArrayList<Integer>(); columnNames = new ArrayList<String>(); Row firstRow = sheet.getRow(firstRowIndex); int columnLabelIndex = 1; if (firstRowIsMetaData) { //For each column for (int i = firstColumnIndex; i <= lastColumnIndex; ++i) { //Get the first cell in the column Cell cell = firstRow.getCell(i, Row.CREATE_NULL_AS_BLANK); columnIndexes.add(cell.getColumnIndex()); int cellType = cell.getCellType(); if (Cell.CELL_TYPE_FORMULA == cellType) { cellType = cell.getCachedFormulaResultType(); // System.out.println("cell type is now getCachedFormulaResultType() = " + cellType ); } //Build the column names depending on it's type switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: // case Cell.CELL_TYPE_FORMULA: // DO NOT USE: getCellFormula() !!! // System.out.println("cell type string" ); // Note: Javadoc on method getStringCellValue() states: // "get the value of the cell as a string - for numeric cells we throw an exception. For blank cells we return an empty string. // For formulaCells that are not string Formulas, we throw an exception" ++numberOfColumns; columnNames.add(cell.getStringCellValue().replaceAll("[\\ ]", "_")); // Note we should not have to do this in future... once defect is fixed break; case Cell.CELL_TYPE_NUMERIC: // System.out.println("cell type numeric " + // ( DateUtil.isCellDateFormatted( cell ) ? "date: " + cell.getDateCellValue().toString() : "num: " + cell.getNumericCellValue() ) ); ++numberOfColumns; columnNames.add(DateUtil.isCellDateFormatted(cell) ? cell.getDateCellValue().toString() : "" + cell.getNumericCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: // System.out.println("cell type boolean" ); ++numberOfColumns; columnNames.add("" + cell.getBooleanCellValue()); break; default: // System.out.println("cell type default" ); ++numberOfColumns; columnNames.add(DEFAULT_COLUMN_LABEL + "" + columnLabelIndex); break; } columnLabelIndex++; } } else { //For each column for (int i = firstColumnIndex; i <= lastColumnIndex; ++i) { //Get the first cell in the column Cell cell = firstRow.getCell(i, Row.CREATE_NULL_AS_BLANK); columnIndexes.add(cell.getColumnIndex()); columnNames.add(DEFAULT_COLUMN_LABEL + "" + columnLabelIndex++); } } }
From source file:com.infovity.iep.loader.util.SupplierLoaderUtil.java
public static ArrayList<String[]> getDataFromFile(File inputFile, String sheetName) { ArrayList<String[]> data = new ArrayList<String[]>(); File selectedFile = null;//from w w w . j av a2 s . c o m FileInputStream fis = null; ; XSSFWorkbook workbook = null; //inputFile = new File("C:\\Users\\INFOVITY-USER-029\\Desktop\\DataLoader\\Consolidated Supplier Data Capture Template v4.0.xlsx"); boolean sheetFound = false; XSSFSheet sheet = null; try { int sheetIndex = -1; fis = new FileInputStream(inputFile); workbook = new XSSFWorkbook(fis); int noOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < noOfSheets; i++) { sheet = workbook.getSheetAt(i); if (sheet.getSheetName().equals(sheetName)) { sheetFound = true; sheetIndex = i; selectedFile = inputFile; break; } } XSSFWorkbook myWorkBook; try { myWorkBook = new XSSFWorkbook(selectedFile); // Return first sheet from the XLSX workbook // XSSFSheet mySheet = myWorkBook.getSheetAt(0); // Get iterator to all the rows in current sheet Iterator<Row> rowIterator = sheet.iterator(); String query; String[] values = null; // Traversing over each row of XLSX file while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (rowHasData(row) && (row.getRowNum() >= 9)) { int endColumn = row.getLastCellNum(); int startColumn = row.getFirstCellNum(); // For each row, iterate through each columns values = new String[endColumn + 2]; for (int i = startColumn; i < endColumn; i++) { String cellValue; Cell cell = row.getCell(i); values[0] = Integer.toString(row.getRowNum() + 1); if (cell != null) { if (cell.getCellType() == cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(cell)) { DateFormat df = new SimpleDateFormat("yyyy/MM/dd"); Date cellDateValue = cell.getDateCellValue(); cellValue = df.format(cellDateValue); } else { cell.setCellType(cell.CELL_TYPE_STRING); cellValue = cell.getStringCellValue().replaceAll("'", ""); } if (!"".equals(cellValue) && cellValue != null) { values[i + 1] = cellValue; } else if (cellValue.isEmpty() || "".equals(cellValue)) { values[i + 1] = ""; } } else { values[i + 1] = ""; } } data.add(values); } } } catch (InvalidFormatException ex) { Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex); } } catch (IOException ex) { Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex); } finally { try { fis.close(); workbook.close(); } catch (IOException ex) { Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex); } } return data; }
From source file:com.jmc.jfxxlsdiff.util.POIXlsUtil.java
public static Object getCellValue(Cell cell) { Object cv = null;//from w ww . j a v a 2s. c o m switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: { break; } case Cell.CELL_TYPE_BOOLEAN: { cv = cell.getBooleanCellValue(); break; } case Cell.CELL_TYPE_ERROR: { cv = cell.getErrorCellValue(); break; } case Cell.CELL_TYPE_FORMULA: { cv = getFormulaValue(cell); break; } case Cell.CELL_TYPE_NUMERIC: { if (DateUtil.isCellDateFormatted(cell)) { // format in form of M/D/YY //Calendar cal = Calendar.getInstance(); //cal.setTime( DateUtil.getJavaDate( d ) ); //cv = cal.getTime(); cv = cell.getDateCellValue(); } else { cv = cell.getNumericCellValue(); } break; } case Cell.CELL_TYPE_STRING: { cv = cell.getStringCellValue(); break; } default: { logger.log(Level.WARNING, "Unexpected cell type = {0}", cell.getCellType()); break; } } return cv; }
From source file:com.kybelksties.excel.ExcelSheetTableModel.java
License:Open Source License
/** * Get the value contained in the cell./*from www .j ava2 s .co m*/ * * @param cell the examined cell * @return the value as Boolean, Numeric, String, Blank, Error or Formula */ public static Object getCellValue(Cell cell) { return cell == null ? "" : cell.getCellType() == Cell.CELL_TYPE_BOOLEAN ? cell.getBooleanCellValue() : cell.getCellType() == Cell.CELL_TYPE_NUMERIC ? (DateUtil.isCellDateFormatted(cell) ? cell.getDateCellValue() : cell.getNumericCellValue()) : cell.getCellType() == Cell.CELL_TYPE_STRING ? cell.getStringCellValue() : cell.getCellType() == Cell.CELL_TYPE_BLANK ? cell.getStringCellValue() : cell.getCellType() == Cell.CELL_TYPE_ERROR ? cell.getErrorCellValue() : cell.getCellType() == Cell.CELL_TYPE_FORMULA ? cell.getCachedFormulaResultType() : cell.getStringCellValue(); }
From source file:com.larasolution.serverlts.FileUploadHandler.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // tablename=request.getParameter(tablename) //process only if its multipart content FileOutputStream fos = new FileOutputStream("C:\\uploads\\data.csv"); String list = ""; List<List> allData = new ArrayList<List>(); List<String> parameters = new ArrayList<String>(); if (ServletFileUpload.isMultipartContent(request)) { try {//from w w w.java 2s. c o m StringBuilder data = new StringBuilder(); List<FileItem> multiparts = new ServletFileUpload(new DiskFileItemFactory()).parseRequest(request); System.out.println(multiparts); for (FileItem item : multiparts) { if (item.isFormField()) { parameters.add(item.getFieldName()); System.out.println(parameters); } if (!item.isFormField()) { String name = new File(item.getName()).getName(); item.write(new File(UPLOAD_DIRECTORY + File.separator + name)); //System.out.println(File.separator); // Get the workbook object for XLSX file XSSFWorkbook wBook = new XSSFWorkbook( new FileInputStream(UPLOAD_DIRECTORY + File.separator + name)); XSSFSheet zz = wBook.getSheetAt(0); FormulaEvaluator formulaEval = wBook.getCreationHelper().createFormulaEvaluator(); Row row; Cell cell; // Iterate through each rows from first sheet Iterator<Row> rowIterator = zz.iterator(); while (rowIterator.hasNext()) { row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: data.append(cell.getBooleanCellValue()).append(","); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { data.append( com.larasolution.modle.getDate.getDate5(cell.getDateCellValue())) .append(","); } else { data.append(cell.getNumericCellValue()).append(","); } break; case Cell.CELL_TYPE_STRING: data.append(cell.getStringCellValue()).append(","); break; case Cell.CELL_TYPE_BLANK: data.append("" + ","); break; case Cell.CELL_TYPE_FORMULA: Double value = Double.parseDouble(formulaEval.evaluate(cell).formatAsString()); data.append(String.format("%.2f", value)).append(","); break; default: data.append(cell).append(""); } } data.append("\r\n"); //String k = data.substring(0, data.length() - 3); //ls.add(k); // data.setLength(0); } fos.write(data.toString().getBytes()); fos.close(); // } } savetosql(); request.setAttribute("message", "successfully uploaded "); } catch (Exception ex) { request.setAttribute("message", "File Upload Failed due to " + ex); } } else { request.setAttribute("message", "Sorry this Servlet only handles file upload request"); } request.setAttribute("arrayfile", allData); request.setAttribute("names", parameters); RequestDispatcher disp = getServletContext().getRequestDispatcher("/FileUploadResult.jsp"); disp.forward(request, response); // System.out.println(allData.size()); // response.sendRedirect("send.jsp?arrayfile=" + list + ""); //request.getRequestDispatcher("/send.jsp?arrayfile='"+ls+"'").forward(request, response); }
From source file:com.lushapp.common.excel.ExcelUtil.java
License:Apache License
/** * /*from w ww . ja v a 2 s . co m*/ * excel * * @param inputstream : ? * @param pojoClass : (?) * @return */ public static Collection importExcelByIs(InputStream inputstream, Class pojoClass) { Collection dist = new ArrayList<Object>(); try { // Field filed[] = pojoClass.getDeclaredFields(); // Annotation??,map Map<String, Method> fieldSetMap = new HashMap<String, Method>(); Map<String, Method> fieldSetConvertMap = new HashMap<String, Method>(); // ? for (int i = 0; i < filed.length; i++) { Field f = filed[i]; // ?Annotation Excel excel = f.getAnnotation(Excel.class); // Annotationd? if (excel != null) { // AnnotationSetter String fieldname = f.getName(); String setMethodName = "set" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1); // method Method setMethod = pojoClass.getMethod(setMethodName, new Class[] { f.getType() }); // methodAnnotaion??key? // ??? ?? fieldSetMap.put(excel.exportName(), setMethod); if (excel.importConvert() == true) { // get/setXxxxConvert??? ?Entity? StringBuffer setConvertMethodName = new StringBuffer("convertSet"); setConvertMethodName.append(fieldname.substring(0, 1).toUpperCase()); setConvertMethodName.append(fieldname.substring(1)); Method getConvertMethod = pojoClass.getMethod(setConvertMethodName.toString(), new Class[] { String.class }); fieldSetConvertMap.put(excel.exportName(), getConvertMethod); } } } // FileFileInputStream; // // HSSFWorkbook book = new HSSFWorkbook(inputstream); // // HSSFSheet sheet = book.getSheetAt(0); // // ? Iterator<Row> row = sheet.rowIterator(); // Row title = row.next(); // Iterator<Cell> cellTitle = title.cellIterator(); // map Map titlemap = new HashMap(); // int i = 0; // while (cellTitle.hasNext()) { Cell cell = cellTitle.next(); String value = cell.getStringCellValue(); titlemap.put(i, value); i = i + 1; } // ??DateFormat // SimpleDateFormat sf; while (row.hasNext()) { // Row rown = row.next(); // Iterator<Cell> cellbody = rown.cellIterator(); // Object tObject = pojoClass.newInstance(); int k = 0; // ?? while (cellbody.hasNext()) { Cell cell = cellbody.next(); // String titleString = (String) titlemap.get(k); // ?Annotation?set if (fieldSetMap.containsKey(titleString)) { Method setMethod = (Method) fieldSetMap.get(titleString); // setter? Type[] ts = setMethod.getGenericParameterTypes(); // ??? String xclass = ts[0].toString(); // ? if (Cell.CELL_TYPE_STRING == cell.getCellType() && fieldSetConvertMap.containsKey(titleString)) { // ???String? fieldSetConvertMap.get(titleString).invoke(tObject, cell.getStringCellValue()); } else { if (xclass.equals("class java.lang.String")) { // Cell??String? cell.setCellType(Cell.CELL_TYPE_STRING); setMethod.invoke(tObject, cell.getStringCellValue()); } else if (xclass.equals("class java.util.Date")) { // update-start--Author:Quainty Date:20130523 for??(?Excel?) Date cellDate = null; if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { // ? cellDate = cell.getDateCellValue(); } else { // Cell.CELL_TYPE_STRING: ? yyyy-mm-dd hh:mm:ss ??(wait to do:?) cellDate = stringToDate(cell.getStringCellValue()); } setMethod.invoke(tObject, cellDate); //// -------------------------------------------------------------------------------------------- //String cellValue = cell.getStringCellValue(); //Date theDate = stringToDate(cellValue); //setMethod.invoke(tObject, theDate); //// -------------------------------------------------------------------------------------------- } else if (xclass.equals("class java.lang.Boolean")) { boolean valBool; if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) { valBool = cell.getBooleanCellValue(); } else {// Cell.CELL_TYPE_STRING valBool = cell.getStringCellValue().equalsIgnoreCase("true") || (!cell.getStringCellValue().equals("0")); } setMethod.invoke(tObject, valBool); } else if (xclass.equals("class java.lang.Integer")) { Integer valInt; if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { valInt = (new Double(cell.getNumericCellValue())).intValue(); } else {// Cell.CELL_TYPE_STRING valInt = new Integer(cell.getStringCellValue()); } setMethod.invoke(tObject, valInt); } else if (xclass.equals("class java.lang.Long")) { Long valLong; if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { valLong = (new Double(cell.getNumericCellValue())).longValue(); } else {// Cell.CELL_TYPE_STRING valLong = new Long(cell.getStringCellValue()); } setMethod.invoke(tObject, valLong); } else if (xclass.equals("class java.math.BigDecimal")) { BigDecimal valDecimal; if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { valDecimal = new BigDecimal(cell.getNumericCellValue()); } else {// Cell.CELL_TYPE_STRING valDecimal = new BigDecimal(cell.getStringCellValue()); } setMethod.invoke(tObject, valDecimal); } } } // k = k + 1; } dist.add(tObject); } } catch (Exception e) { e.printStackTrace(); return null; } return dist; }