List of usage examples for org.apache.poi.ss.usermodel Cell getBooleanCellValue
boolean getBooleanCellValue();
From source file:com.lushapp.common.excel.ExcelUtil.java
License:Apache License
/** * //from w w w .j a va2 s .c o 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; }
From source file:com.maxl.java.aips2xml.Aips2Xml.java
License:Open Source License
static String getAnyValue(Cell part) { if (part != null) { switch (part.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: return part.getBooleanCellValue() + ""; case Cell.CELL_TYPE_NUMERIC: return part.getNumericCellValue() + ""; case Cell.CELL_TYPE_STRING: return part.getStringCellValue() + ""; case Cell.CELL_TYPE_BLANK: return "BLANK"; case Cell.CELL_TYPE_ERROR: return "ERROR"; case Cell.CELL_TYPE_FORMULA: return "FORMEL"; }// w w w .j a va 2s.c o m } return ""; }
From source file:com.mimp.controllers.reporte.java
private static void copyRow(Sheet worksheet, int sourceRowNum, int destinationRowNum) { // Coge la fila antigua y nueva Row newRow = worksheet.getRow(destinationRowNum); Row sourceRow = worksheet.getRow(sourceRowNum); //Si existe una fila en el detino, pasa todas las filas 1 ms abajo antes de crear la nueva columna if (newRow != null) { worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1); } else {/* www .j av a 2 s. c o m*/ newRow = worksheet.createRow(destinationRowNum); } // Hace un loop entre las celdas de cada columna para aadir una por una a la nueva for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Copia la antigua y nueva celda Cell oldCell = sourceRow.getCell(i); Cell newCell = newRow.createCell(i); // Si la anterior celda es null, evalua la siguiente celda defrente if (oldCell == null) { newCell = null; continue; } // Usa el estilo de la celda antigua newCell.setCellStyle(oldCell.getCellStyle()); // Establece el tipo de valor de la celda newCell.setCellType(oldCell.getCellType()); // Establece el valor de la celda switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: 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; } } }
From source file:com.miraisolutions.xlconnect.data.ColumnBuilder.java
License:Open Source License
protected CellValue getCachedCellValue(Cell cell) { int valueType = cell.getCellType(); if (valueType == Cell.CELL_TYPE_FORMULA) { valueType = cell.getCachedFormulaResultType(); }//from ww w . j ava2s . c o m switch (valueType) { case Cell.CELL_TYPE_BLANK: return null; case Cell.CELL_TYPE_BOOLEAN: if (cell.getBooleanCellValue()) { return CellValue.TRUE; } else { return CellValue.FALSE; } case Cell.CELL_TYPE_NUMERIC: return new CellValue(cell.getNumericCellValue()); case Cell.CELL_TYPE_STRING: return new CellValue(cell.getStringCellValue()); case Cell.CELL_TYPE_ERROR: return CellValue.getError(cell.getErrorCellValue()); default: String msg = String.format("Could not extract value from cell with cached value type %d", valueType); throw new RuntimeException(msg); } }
From source file:com.mto.excel.model.MergeWorkbook.java
License:Open Source License
private void addRow(Row row, int cellOffset) { Row newRow = sheet.createRow(rowOffset); rowOffset++;/*from w w w .j a va 2 s. co m*/ for (int i = cellOffset; i < row.getLastCellNum(); i++) { Cell c = row.getCell(i); if (c == null) { continue; } Cell newCell = newRow.createCell(i, c.getCellType()); switch (c.getCellType()) { case CELL_TYPE_STRING: newCell.setCellValue(helper.createRichTextString(c.getStringCellValue())); break; case CELL_TYPE_BOOLEAN: newCell.setCellValue(c.getBooleanCellValue()); break; case CELL_TYPE_NUMERIC: newCell.setCellValue(c.getNumericCellValue()); break; default: return; } } }
From source file:com.mum.processexceldata.ReadExcel.java
public static void main(String args[]) { try {/*from w w w. ja v a 2 s .c o m*/ FileInputStream file = new FileInputStream( new File("C:\\Users\\demodem\\Downloads\\Calling_Codes.xls")); System.out.println("File:" + file); // HSSFWorkbook book = new HSSFWorkbook(file); //Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(file); //Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); //System.out.println("sheet :"+sheet); //Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: System.out.print(cell.getBooleanCellValue() + "\t\t"); break; case Cell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue() + "\t\t"); break; case Cell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue() + "\t\t"); break; } } System.out.println(""); } file.close(); FileOutputStream out = new FileOutputStream(new File("C:\\Users\\demodem\\Downloads\\test.xls")); workbook.write(out); out.close(); } catch (Exception e) { System.out.println("Error in reading the file."); } }
From source file:com.murilo.excel.ExcelHandler.java
private String stringrizeCell(Cell x) { switch (x.getCellType()) { case Cell.CELL_TYPE_BLANK: return ""; case Cell.CELL_TYPE_BOOLEAN: return String.valueOf(x.getBooleanCellValue()); case Cell.CELL_TYPE_NUMERIC: return String.valueOf(x.getNumericCellValue()); case Cell.CELL_TYPE_STRING: return x.getStringCellValue(); case Cell.CELL_TYPE_FORMULA: switch (x.getCachedFormulaResultType()) { case Cell.CELL_TYPE_NUMERIC: return String.valueOf(x.getNumericCellValue()); case Cell.CELL_TYPE_STRING: return x.getStringCellValue(); case Cell.CELL_TYPE_BLANK: return ""; }//from w w w .ja va 2 s .com } return null; }
From source file:com.mycompany.chartproject.ExcelReader.java
public Map<String, Double> getPieChartData(String repo) { Map<String, Double> map = new HashMap<>(); try {/*from ww w. j a v a 2 s . c om*/ String fileName = "src/main/resources/Stabilityfinal.xlsx"; String test = fileName; //String fileName2 = "src/main/resources/Series.xlsx"; //String test2 = fileName2; FileInputStream file = new FileInputStream(new File(test)); //Get the workbook instance for XLS file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first sheet from the workbook XSSFSheet sheet = workbook.getSheet(repo); //Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); int total = -1; int success = 0; int failure = 0; int unstable = 0; int aborted = 0; while (rowIterator.hasNext()) { ++total; Row row = rowIterator.next(); //For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { System.out.println(cell.getDateCellValue() + "\t\t"); } else { System.out.print(cell.getNumericCellValue() + "\t\t"); } break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(cell.getBooleanCellValue() + "\t\t"); break; case Cell.CELL_TYPE_STRING: if (cell.getStringCellValue().equalsIgnoreCase("SUCCESS")) { ++success; } else if (cell.getStringCellValue().equalsIgnoreCase("FAILURE")) { ++failure; } else if (cell.getStringCellValue().equalsIgnoreCase("UNSTABLE")) { ++unstable; } else if (cell.getStringCellValue().equalsIgnoreCase("ABORTED")) { ++aborted; } System.out.print(cell.getStringCellValue() + "\t\t"); break; } } System.out.println(""); file.close(); FileOutputStream out = new FileOutputStream(new File(fileName)); workbook.write(out); out.close(); } System.out.println("Total " + total); System.out.println("no. Successful " + success); System.out.println("no. Failures " + failure); System.out.println("no. Unstable " + unstable); int green = ((success * 100 / total)); double passedPercentage = (double) green / 100; System.out.println("Passed: " + passedPercentage); int red = ((failure * 100 / total)); double failedPercentage = (double) red / 100; System.out.println("Failed: " + failedPercentage); int orange = ((unstable * 100 / total)); double unstablePercentage = (double) orange / 100; System.out.println("Unstable: " + unstablePercentage); int abort = ((aborted * 100 / total)); double abortedPercentage = (double) abort / 100; System.out.println("Aborted: " + abortedPercentage); map.put("Failed", failedPercentage); map.put("Unstable", unstablePercentage); map.put("Passed", passedPercentage); map.put("Aborted", abortedPercentage); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return map; }
From source file:com.mycompany.chartproject.ExcelReader.java
public List<ChartSeries> getSeriesChartData(String repo) { List<ChartSeries> cs = new ArrayList<>(); try {/*from www .ja v a 2 s . co m*/ String fileName = "src/main/resources/Series.xlsx"; String test = fileName; //String fileName2 = "src/main/resources/Series.xlsx"; //String test2 = fileName2; FileInputStream file = new FileInputStream(new File(test)); //Get the workbook instance for XLS file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first sheet from the workbook XSSFSheet sheet = workbook.getSheet(repo); //Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); ChartSeries chartSeries = null; while (rowIterator.hasNext()) { chartSeries = new ChartSeries(); Row row = rowIterator.next(); if (row.getRowNum() == 0) { row = rowIterator.next(); } //For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: //System.out.println("numeric"); switch (cell.getColumnIndex()) { case 1: chartSeries.setTotal((int) cell.getNumericCellValue()); break; case 2: chartSeries.setPassed((int) cell.getNumericCellValue()); break; case 3: chartSeries.setFailed((int) cell.getNumericCellValue()); break; case 4: chartSeries.setSkipped((int) cell.getNumericCellValue()); break; } System.out.println(cell.getDateCellValue() + "\t\t"); System.out.print(cell.getNumericCellValue() + "\t\t"); break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(cell.getBooleanCellValue() + "\t\t"); break; case Cell.CELL_TYPE_STRING: chartSeries.setDate(cell.getStringCellValue()); System.out.print(cell.getStringCellValue() + "\t\t"); break; } } System.out.println(""); cs.add(chartSeries); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return cs; }
From source file:com.mycompany.javaapplicaton3.LerArquivo.java
public static void main(String args[]) { logger.info("Hello World!"); try {//from ww w . ja va2s .com //File excel = new File ("C:/Users/lprates/Documents/arquivo2013.xlsx"); //FileInputStream fis = new FileInputStream(excel); OPCPackage pkg = OPCPackage.open("C:/Users/lprates/Documents/arquivo2013.xlsx"); XSSFWorkbook myWorkBook = new XSSFWorkbook(pkg); // Return first sheet from the XLSX workbook XSSFSheet mySheet = myWorkBook.getSheetAt(0); // Get iterator to all the rows in current sheet Iterator<Row> rowIterator = mySheet.iterator(); // Traversing over each row of XLSX file while (rowIterator.hasNext()) { Row row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue() + "\t"); break; case Cell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue() + "\t"); break; case Cell.CELL_TYPE_BOOLEAN: System.out.print(cell.getBooleanCellValue() + "\t"); break; default: System.out.print("Nada"); } } System.out.println(""); } } catch (Exception ex) { logger.error(ex.toString()); } }