List of usage examples for org.apache.poi.ss.usermodel Cell getDateCellValue
Date getDateCellValue();
From source file:com.miraisolutions.xlconnect.data.ColumnBuilder.java
License:Open Source License
public Column buildDateTimeColumn() { Date[] colValues = new Date[values.size()]; boolean[] missing = new boolean[values.size()]; Iterator<CellValue> it = values.iterator(); Iterator<Cell> jt = cells.iterator(); int counter = 0; while (it.hasNext()) { CellValue cv = it.next();//from www. ja va2 s . co m Cell cell = jt.next(); if (cv == null) { missing[counter] = true; } else { switch (detectedTypes.get(counter)) { case Boolean: missing[counter] = true; addWarning("Cell " + CellUtils.formatAsString(cells.get(counter)) + " cannot be converted from Boolean to DateTime - returning NA"); break; case Numeric: if (forceConversion) { if (DateUtil.isValidExcelDate(cv.getNumberValue())) { colValues[counter] = DateUtil.getJavaDate(cv.getNumberValue()); } else { missing[counter] = true; addWarning("Cell " + CellUtils.formatAsString(cells.get(counter)) + " cannot be converted from Numeric to DateTime - returning NA"); } } else { missing[counter] = true; } break; case String: if (forceConversion) { try { colValues[counter] = Workbook.dateTimeFormatter.parse(cv.getStringValue(), dateTimeFormat); } catch (Exception e) { missing[counter] = true; addWarning("Cell " + CellUtils.formatAsString(cells.get(counter)) + " cannot be converted from String to DateTime - returning NA"); } } else { missing[counter] = true; } break; case DateTime: colValues[counter] = cell.getDateCellValue(); break; default: throw new IllegalArgumentException("Unknown data type detected!"); } } ++counter; } return new Column(colValues, missing, DataType.DateTime); }
From source file:com.movielabs.availslib.AvailSS.java
License:Open Source License
/** * Add a sheet from an Excel spreadsheet to a spreadsheet object * @param wb an Apache POI workbook object * @param sheet an Apache POI sheet object * @return created sheet object/*w ww . ja v a 2s .c o m*/ */ private AvailsSheet addSheetHelper(Workbook wb, Sheet sheet) throws Exception { AvailsSheet as = new AvailsSheet(this, sheet.getSheetName()); // int qq = 0; for (Row row : sheet) { // qq++; int len = row.getLastCellNum(); if (len < 0) continue; String[] fields = new String[len]; for (int i = 0; i < len; i++) // XXX: don't want nulls fields[i] = ""; for (Cell cell : row) { int idx = cell.getColumnIndex(); int type = cell.getCellType(); switch (type) { case 0: // Numeric double v = cell.getNumericCellValue(); if (v < 0.5) { // XXX hack: assume TotalRunTime java.util.Date d = cell.getDateCellValue(); fields[idx] = String.format("%02d:%02d:%02d", d.getHours(), d.getMinutes(), d.getSeconds()); //System.out.println("run=" + tmp); } else { fields[idx] = cell.toString(); } break; case 1: // String case 3: // Blank fields[idx] = cell.getStringCellValue().trim(); break; default: //logger.warn("Cell[" + i + "," + idx + "]: invalid type (" + type + ")"); fields[idx] = cell.toString(); break; } } /* cell */ if (as.isAvail(fields)) as.addRow(fields, row.getRowNum() + 1); } /* row */ sheets.add(as); return as; }
From source file:com.mycompany.chartproject.ExcelReader.java
public Map<String, Double> getPieChartData(String repo) { Map<String, Double> map = new HashMap<>(); try {// www.j a v a 2 s.c o m 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 w ww . ja va 2 s . c om*/ 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.gannaraputakehomeexam.ReadingFromExcel.java
public List getSongsListFromExcel() { List songList = new ArrayList(); FileInputStream fis = null;//from w ww . j a v a 2 s . c o m try { fis = new FileInputStream(FILE_PATH); /* Use XSSF for xlsx format, for xls use HSSF */ Workbook workbook = new XSSFWorkbook(fis); int numberOfSheets = workbook.getNumberOfSheets(); /* looping over each workbook sheet */ for (int i = 0; i < numberOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); Iterator rowIterator = sheet.iterator(); /* iterating over each row */ while (rowIterator.hasNext()) { SongsList song = new SongsList(); Row row = (Row) rowIterator.next(); Iterator cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = (Cell) cellIterator.next(); /* checking if the cell is having a String value . */ if (Cell.CELL_TYPE_STRING == cell.getCellType()) { /* Cell with index 1 contains Album name */ if (cell.getColumnIndex() == 1) { song.setAlbumname(cell.getStringCellValue()); } /* Cell with index 2 contains Genre */ if (cell.getColumnIndex() == 2) { song.setGenre(cell.getStringCellValue()); } /* Cell with index 3 contains Artist name */ if (cell.getColumnIndex() == 3) { song.setArtist(cell.getStringCellValue()); } } /* checking if the cell is having a numeric value */ else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { /* Cell with index 0 contains Sno */ if (cell.getColumnIndex() == 0) { song.setSno((int) cell.getNumericCellValue()); } /* Cell with index 5 contains Critic score. */ else if (cell.getColumnIndex() == 5) { song.setCriticscore((int) cell.getNumericCellValue()); } /* Cell with index 4 contains Release date */ else if (cell.getColumnIndex() == 4) { Date dateValue = null; if (DateUtil.isCellDateFormatted(cell)) { dateValue = cell.getDateCellValue(); } song.setReleasedate(dateValue); } } } /* end iterating a row, add all the elements of a row in list */ songList.add(song); } } fis.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return songList; }
From source file:com.mycompany.gayamtakehomeexam.ReadfromExcel.java
public List getSongsListFromExcel() { List songList = new ArrayList(); FileInputStream fis = null;/*from ww w. ja v a 2 s. c o m*/ try { fis = new FileInputStream(FILE_PATH); /* Use XSSF for xlsx format, for xls use HSSF */ Workbook workbook = new XSSFWorkbook(fis); int numberOfSheets = workbook.getNumberOfSheets(); /* looping over each workbook sheet */ for (int i = 0; i < numberOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); Iterator rowIterator = sheet.iterator(); /* iterating over each row */ while (rowIterator.hasNext()) { Song song = new Song(); Row row = (Row) rowIterator.next(); Iterator cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = (Cell) cellIterator.next(); /* checking if the cell is having a String value . */ if (Cell.CELL_TYPE_STRING == cell.getCellType()) { /* Cell with index 1 contains Album name */ if (cell.getColumnIndex() == 1) { song.setAlbumname(cell.getStringCellValue()); } /* Cell with index 2 contains Genre */ if (cell.getColumnIndex() == 2) { song.setGenre(cell.getStringCellValue()); } /* Cell with index 3 contains Artist name */ if (cell.getColumnIndex() == 3) { song.setArtist(cell.getStringCellValue()); } } /* checking if the cell is having a numeric value */ else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { /* Cell with index 0 contains Sno */ if (cell.getColumnIndex() == 0) { song.setSno((int) cell.getNumericCellValue()); } /* Cell with index 5 contains Critic score. */ else if (cell.getColumnIndex() == 5) { song.setCriticscore((int) cell.getNumericCellValue()); } /* Cell with index 4 contains Release date */ else if (cell.getColumnIndex() == 4) { Date dateValue = null; if (DateUtil.isCellDateFormatted(cell)) { dateValue = cell.getDateCellValue(); } song.setReleasedate(dateValue); } } } /* end iterating a row, add all the elements of a row in list */ songList.add(song); } } fis.close(); } catch (FileNotFoundException e) { } catch (IOException e) { } return songList; }
From source file:com.nc.common.utils.ExcelUtil.java
License:Open Source License
/** * <pre>/*from www .j a va 2s.c o m*/ * 1. : POI Util * 2. : Excel ? ?(*.xls, *.xlsx ? ) * </pre> * * @method Name : readExcel * @param strFullFilePath, serviceType * @return List<Map<String, Object>> * @throws Exception * */ @SuppressWarnings("deprecation") public static List<Map<String, Object>> readExcel(String strFullFilePath, String serviceType) throws Exception { String tmpFile = strFullFilePath; File wbfile = new File(tmpFile); Workbook wb = null; FileInputStream file = null; List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); Map<String, Object> map = null; try { file = new FileInputStream(wbfile); wb = new HSSFWorkbook(file); /* WorkbookFactory.create(file); Version change */ /* Sheet ? , ? ?? */ /* for (int sheetIdx=0; sheetIdx<wb.getNumberOfSheets(); sheetIdx++) { */ for (int sheetIdx = 0; sheetIdx < 1; sheetIdx++) { /* 1 ? */ Sheet sheet = wb.getSheetAt(sheetIdx); /* ?? ? ?, */ /* row ? */ int cellCount = 0; for (int rowIdx = sheet.getFirstRowNum() + 1; rowIdx <= sheet.getLastRowNum(); rowIdx++) { Row row = sheet.getRow(rowIdx); cellCount = row.getLastCellNum(); map = new HashMap<String, Object>(); if (row != null) { // cell ? for (int cellIdx = 0; cellIdx < cellCount; cellIdx++) { Cell cell = row.getCell(cellIdx); if (cell != null) { int cellType = cell.getCellType(); String value = ""; // WBS ? ? ?? if (serviceType.equals("order")) { switch (cellType) { case HSSFCell.CELL_TYPE_FORMULA: //?? value = cell.getStringCellValue();//cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC://? if (HSSFDateUtil.isCellDateFormatted(cell)) { SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd"); value = formatter.format(cell.getDateCellValue()); } else { cell.setCellType(Cell.CELL_TYPE_STRING); value = cell.getStringCellValue(); } break; case HSSFCell.CELL_TYPE_STRING: //? value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BLANK: // value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_ERROR: //BYTE value = cell.getErrorCellValue() + ""; break; default: ; } } else { switch (cellType) { case HSSFCell.CELL_TYPE_FORMULA: //?? value = cell.getStringCellValue();//cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC://? value = cell.getNumericCellValue() + ""; break; case HSSFCell.CELL_TYPE_STRING: //? value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BLANK: // value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_ERROR: //BYTE value = cell.getErrorCellValue() + ""; break; default: } } map.put("colName" + cellIdx, value); } else { map.put("colName" + cellIdx, ""); } } list.add(map); } } } } catch (Exception e) { e.printStackTrace(); if (log.isDebugEnabled()) { log.debug( "=========================================================================================="); log.debug("= Excel File Reading ... Error : [{}] =", e); log.debug( "=========================================================================================="); } throw new NCException("ExcelUtil > readExcel ?"); } finally { /* ? ?? */ file.close(); wb.close(); } return list; }
From source file:com.ncc.excel.ExcelUtil.java
License:Apache License
public static String getCellValue(Cell cell) { // private String getCellValue(Cell cell){ String str = null;// www . ja v a 2 s . co m if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: //0 //?? 1. 2. if (HSSFDateUtil.isCellDateFormatted(cell)) { //?? str = DateUtil.formatDateForExcelDate(cell.getDateCellValue()); } else { double dValue = cell.getNumericCellValue(); //E if (String.valueOf(dValue).contains("E")) { str = new DecimalFormat("#").format(dValue); } else { str = String.valueOf(dValue); } } break; case Cell.CELL_TYPE_STRING: //1 str = cell.getStringCellValue(); break; case Cell.CELL_TYPE_FORMULA: //2 ? str = String.valueOf(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_BLANK: //3 str = ""; break; case Cell.CELL_TYPE_BOOLEAN: //4 str = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: //5 str = ""; break; default: str = null; break; } } return str; }
From source file:com.ncc.excel.ExcelUtil2.java
License:Apache License
public static String getCellValue(Cell cell) { // private String getCellValue(Cell cell){ String str = null;/* www. j av a2 s. c o m*/ if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: //0 //?? 1. 2. if (HSSFDateUtil.isCellDateFormatted(cell)) { //?? str = DateUtil.formatDateForExcelDate(cell.getDateCellValue()); } else { double dValue = cell.getNumericCellValue(); //E if (String.valueOf(dValue).contains("E")) { str = new DecimalFormat("#").format(dValue); } else { str = String.valueOf(dValue); } } break; case Cell.CELL_TYPE_STRING: //1 str = cell.getStringCellValue(); break; case Cell.CELL_TYPE_FORMULA: //2 ? str = String.valueOf(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_BLANK: //3 str = ""; break; case Cell.CELL_TYPE_BOOLEAN: //4 str = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: //5 str = ""; break; default: str = null; break; } } return str; }
From source file:com.opendoorlogistics.core.tables.io.PoiIO.java
License:Open Source License
private static String getTextValue(Cell cell, int treatAsCellType) { if (cell == null) { return null; }/*from ww w . jav a 2s. c om*/ switch (treatAsCellType) { case Cell.CELL_TYPE_STRING: return cell.getRichStringCellValue().getString(); case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); if (date != null) { Calendar cal = Calendar.getInstance(); cal.setTime(date); @SuppressWarnings("deprecation") int year = date.getYear(); if (year == -1) { // equivalent to 1899 which is the first data .. assume its a time String s = ODL_TIME_FORMATTER.format(date); return s; } // System.out.println(year); } return cell.getDateCellValue().toString(); } else { String ret = Double.toString(cell.getNumericCellValue()); if (ret.endsWith(".0")) { ret = ret.substring(0, ret.length() - 2); } return ret; } case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue() ? "T" : "F"; case Cell.CELL_TYPE_FORMULA: return cell.getCellFormula(); case Cell.CELL_TYPE_BLANK: return null; } return ""; }