List of usage examples for org.apache.poi.ss.usermodel Cell getStringCellValue
String getStringCellValue();
For numeric cells we throw an exception.
From source file:com.miraisolutions.xlconnect.Workbook.java
License:Open Source License
private DataFrame readData(Sheet sheet, int startRow, int startCol, int nrows, int ncols, boolean header, ReadStrategy readStrategy, DataType[] colTypes, boolean forceConversion, String dateTimeFormat, boolean takeCached, int[] subset) { DataFrame data = new DataFrame(); int[] colset; // Formula evaluator - only if we don't want to take cached values FormulaEvaluator evaluator = null;/*from w w w . ja v a 2 s.c om*/ if (!takeCached) { evaluator = workbook.getCreationHelper().createFormulaEvaluator(); evaluator.clearAllCachedResultValues(); } if (subset == null) { colset = new int[ncols]; for (int i = 0; i < ncols; i++) { colset[i] = i; } } else { colset = subset; } ColumnBuilder cb; switch (readStrategy) { case DEFAULT: cb = new DefaultColumnBuilder(nrows, forceConversion, evaluator, onErrorCell, missingValue, dateTimeFormat); break; case FAST: cb = new FastColumnBuilder(nrows, forceConversion, evaluator, onErrorCell, dateTimeFormat); break; default: throw new IllegalArgumentException("Unknown read strategy!"); } // Loop over columns for (int col : colset) { int colIndex = startCol + col; // Determine column header String columnHeader = null; if (header) { Cell cell = getCell(sheet, startRow, colIndex, false); // Check if there actually is a cell ... if (cell != null) { if (!takeCached) { CellValue cv = evaluator.evaluate(cell); if (cv != null) columnHeader = cv.getStringValue(); } else { columnHeader = cell.getStringCellValue(); } } } // If it was specified that there is a header but an empty(/non-existing) // cell or cell value is found, then use a default column name if (columnHeader == null) columnHeader = "Col" + (col + 1); // Prepare column builder for new set of rows cb.clear(); // Loop over rows Row r; for (int row = header ? 1 : 0; row < nrows; row++) { int rowIndex = startRow + row; // Cell cell = getCell(sheet, rowIndex, colIndex, false); Cell cell = ((r = sheet.getRow(rowIndex)) == null) ? null : r.getCell(colIndex); cb.addCell(cell); } DataType columnType = ((colTypes != null) && (colTypes.length > 0)) ? colTypes[col % colTypes.length] : cb.determineColumnType(); switch (columnType) { case Boolean: data.addColumn(columnHeader, cb.buildBooleanColumn()); break; case DateTime: data.addColumn(columnHeader, cb.buildDateTimeColumn()); break; case Numeric: data.addColumn(columnHeader, cb.buildNumericColumn()); break; case String: data.addColumn(columnHeader, cb.buildStringColumn()); break; default: throw new IllegalArgumentException("Unknown data type detected!"); } // ArrayList columnValues = cb.build(columnType); // data.addColumn(columnHeader, columnType, columnValues); // Copy warnings for (String w : cb.retrieveWarnings()) this.addWarning(w); } return data; }
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/*from w w w .j a v a2s . 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.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 v a 2s . c o 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 ww w . j a v a 2 s .co 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 ww .j a va 2s .co m*/ } return null; }
From source file:com.mycompany.bandaru_exam.ReadfromExcel.java
public List getAccountListFromExcel() { List accountList = new ArrayList(); FileInputStream fis = null;//from ww w . j av 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()) { Account account = new Account(); 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 0 contains first name */ if (cell.getColumnIndex() == 0) { account.setFirstName(cell.getStringCellValue()); } /* Cell with index 1 contains last name */ if (cell.getColumnIndex() == 1) { account.setLastName(cell.getStringCellValue()); } /* Cell with index 2 contains account number */ if (cell.getColumnIndex() == 2) { account.setAccNumber(cell.getStringCellValue()); } } /* checking if the cell is having a numeric value */ else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { /* Cell with index 3 contains account number */ if (cell.getColumnIndex() == 3) { account.setBalance(cell.getNumericCellValue()); } } } /* end iterating a row, add all the elements of a row in list */ accountList.add(account); } } fis.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } //Remove Headers accountList.remove(0); return accountList; }
From source file:com.mycompany.chartproject.ExcelReader.java
public Map<String, Double> getPieChartData(String repo) { Map<String, Double> map = new HashMap<>(); try {//from www. j ava2 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 ww w . j av a2s .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 ww w .j a v a2 s . c om*/ 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;/* ww w. j a v a2 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; }