List of usage examples for org.apache.poi.ss.usermodel Cell getBooleanCellValue
boolean getBooleanCellValue();
From source file:com.hust.zsuper.DealWithPatent.ExcelToMySQL.java
License:Open Source License
private String createInsert(final String tableName, final List<Entry<String, ExcelType>> types, final Row row) { //Iterate/* w w w. j a va 2 s . c o m*/ final FormulaEvaluator evaluator = row.getSheet().getWorkbook().getCreationHelper() .createFormulaEvaluator(); final Map<String, String> colVals = new HashMap<String, String>(); int columnCount = 0; for (Entry<String, ExcelType> sourceType : types) { if (isSet(sourceType)) { Cell cell = row.getCell(columnCount); if (cell != null) { cell = evaluator.evaluateInCell(cell); try { final String value; switch (sourceType.getValue()) { case DATE: value = "'" + new SimpleDateFormat("yyyy-MM-dd HH:mm").format(getCellValue(cell)) + "'"; colVals.put(sourceType.getKey(), value); break; case NUMERIC: value = String.valueOf(getCellValue(cell)); colVals.put(sourceType.getKey(), value); break; case BOOLEAN: value = String.valueOf(cell.getBooleanCellValue()); colVals.put(sourceType.getKey(), value); break; case STRING: value = String.valueOf(getCellValue(cell)).replaceAll("'", "\\\\'"); if (!value.isEmpty()) { colVals.put(sourceType.getKey(), "'" + value + "'"); } break; } } catch (Exception ex) { if (strict) { throw new RuntimeException("Failed to process cell value: " + getCellValue(cell) + ", of column:row " + columnCount + ":" + row.getRowNum() + ", expecting type: " + sourceType.getValue().toString(), ex); } } } } columnCount++; } return createInsertStatement(colVals, tableName); }
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 w w w . j av a 2 s .c o m*/ 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://from ww w . j a v a 2s . 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 ww w . j a v a 2s. 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 : * //from w w w . j a v a 2 s . c o m * - 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.jaredrummler.android.devices.Main.java
License:Apache License
private static String getStringCellValue(Cell cell) { switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue() ? "true" : "false"; case Cell.CELL_TYPE_NUMERIC: return Double.toString(cell.getNumericCellValue()); case Cell.CELL_TYPE_STRING: default:/*from w w w. j a v a 2 s. c o m*/ return cell.getStringCellValue(); } }
From source file:com.jmc.jfxxlsdiff.util.POIXlsUtil.java
public static Object getCellValue(Cell cell) { Object cv = null;//from w w w . j av 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 w ww .jav a 2 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.kybelksties.excel.ExcelSheetTableModel.java
License:Open Source License
/** * Insert a row at a given index.// w w w . j a va 2s . co m * * @param createAtIndex row-number of the cell at which to create a new row * @param sourceRow the row to insert */ public void insertRowAt(int createAtIndex, Row sourceRow) { Row newRow = getRow(createAtIndex); if (newRow != null) { // shift all rows >= createAtIndex up by one getSheet().shiftRows(createAtIndex, getSheet().getLastRowNum(), 1); } else { newRow = getSheet().createRow(createAtIndex); } // Loop through source columns to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell Cell oldCell = sourceRow.getCell(i); Cell newCell = newRow.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { continue; } // Copy style from old cell and apply to new cell CellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // If there is a cell comment, copy if (oldCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } // If there is a cell hyperlink, copy if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } // Set the cell data type newCell.setCellType(oldCell.getCellType()); // Set the cell data value switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } } // If there are are any merged regions in the source row, copy to new row for (int i = 0; i < getSheet().getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = getSheet().getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); getSheet().addMergedRegion(newCellRangeAddress); } } }
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 .j ava 2 s. 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); }