List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getBooleanCellValue
@Override public boolean getBooleanCellValue()
From source file:com.essa.main.ReadExcel.java
public static void main(String[] args) { try {//from www . j a v a 2 s . c om InputStream input = new BufferedInputStream(new FileInputStream("sample.xls")); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); System.out.println("\n"); Iterator cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) System.out.print(cell.getNumericCellValue() + " "); else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) System.out.print(cell.getStringCellValue() + " "); else if (HSSFCell.CELL_TYPE_BOOLEAN == cell.getCellType()) System.out.print(cell.getBooleanCellValue() + " "); else if (HSSFCell.CELL_TYPE_BLANK == cell.getCellType()) System.out.print("BLANK "); else System.out.print("Unknown cell type"); } } } catch (IOException ex) { ex.printStackTrace(); } }
From source file:com.example.selenium.util.ApachePOIXLSReader.java
private static Object cellToObject(HSSFCell cell) { Object result;//from www .ja v a2 s. c o m switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: result = cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: result = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: result = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_FORMULA: result = cell.getCellFormula(); break; default: throw new RuntimeException("Unknown Cell Type"); } return result; }
From source file:com.flexive.extractor.ExcelExtractor.java
License:Open Source License
private void processSheet(HSSFSheet sheet) { try {//from w ww. j a v a 2s . c o m // Use the HFFS functions for the number of rows & columns int rowCount = sheet.getPhysicalNumberOfRows(); int colCount = sheet.getRow(0).getPhysicalNumberOfCells(); HSSFRow row; HSSFCell cell; String cellValue; for (int i = 0; i < rowCount; i++) { row = sheet.getRow(i); for (short j = 0; j < colCount; j++) { cell = row.getCell(j); if (cell != null) { try { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_BOOLEAN: cellValue = String.valueOf(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: cellValue = String.valueOf(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: // Doesnt make much sense to index a cell formula cellValue = ""; break; case HSSFCell.CELL_TYPE_ERROR: cellValue = String.valueOf(cell.getErrorCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: cellValue = ""; break; default: cellValue = cell.getStringCellValue(); } } catch (Exception exc) { cellValue = ""; } writer.write(FxSharedUtils.getBytes(cellValue)); } } } } catch (Exception eN) { System.out.println("Error reading sheet:" + eN.toString()); } }
From source file:com.frameworkset.platform.cms.searchmanager.extractors.CmsExtractorMsExcel.java
License:Open Source License
/** * Extracts the text from the Excel table content.<p> * //from w ww . ja va 2s . c om * @param in the document input stream * @return the extracted text * @throws IOException if something goes wring */ protected String extractTableContent(InputStream in) throws IOException { HSSFWorkbook excelWb = new HSSFWorkbook(in); StringBuffer result = new StringBuffer(4096); int numberOfSheets = excelWb.getNumberOfSheets(); for (int i = 0; i < numberOfSheets; i++) { HSSFSheet sheet = excelWb.getSheetAt(i); int numberOfRows = sheet.getPhysicalNumberOfRows(); if (numberOfRows > 0) { if (CmsStringUtil.isNotEmpty(excelWb.getSheetName(i))) { // append sheet name to content if (i > 0) { result.append("\n\n"); } result.append(excelWb.getSheetName(i).trim()); result.append(":\n\n"); } Iterator rowIt = sheet.rowIterator(); while (rowIt.hasNext()) { HSSFRow row = (HSSFRow) rowIt.next(); if (row != null) { boolean hasContent = false; Iterator it = row.cellIterator(); while (it.hasNext()) { HSSFCell cell = (HSSFCell) it.next(); String text = null; try { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: case HSSFCell.CELL_TYPE_ERROR: // ignore all blank or error cells break; case HSSFCell.CELL_TYPE_NUMERIC: text = Double.toString(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BOOLEAN: text = Boolean.toString(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_STRING: default: text = cell.getStringCellValue(); break; } } catch (Exception e) { // ignore this cell } if (CmsStringUtil.isNotEmpty(text)) { result.append(text.trim()); result.append(' '); hasContent = true; } } if (hasContent) { // append a newline at the end of each row that has content result.append('\n'); } } } } } return result.toString(); }
From source file:com.glaf.base.modules.todo.TodoXlsReader.java
License:Apache License
public List<Todo> readXls(java.io.InputStream inputStream) { List<Todo> todos = new java.util.ArrayList<Todo>(); HSSFWorkbook wb = null;/*from w w w . j av a2 s. co m*/ try { wb = new HSSFWorkbook(inputStream); } catch (Exception ex) { throw new RuntimeException(ex); } HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = sheet.getRow(1); Map<Integer, String> keyMap = new java.util.HashMap<Integer, String>(); Map<String, Object> dataMap = new java.util.HashMap<String, Object>(); int cells = row.getPhysicalNumberOfCells(); for (int colIndex = 0; colIndex < cells; colIndex++) { HSSFCell cell = row.getCell(colIndex); keyMap.put(colIndex, cell.getStringCellValue()); } Set<String> keys = new HashSet<String>(); for (int rowIndex = 2; rowIndex < sheet.getPhysicalNumberOfRows(); rowIndex++) { HSSFRow rowx = sheet.getRow(rowIndex); if (rowx == null) { continue; } // System.out.println(); dataMap.clear(); for (int colIndex = 0; colIndex < cells; colIndex++) { String fieldName = keyMap.get(colIndex); HSSFCell cell = rowx.getCell(colIndex); if (cell == null) { continue; } Object cellValue = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: break; case HSSFCell.CELL_TYPE_BOOLEAN: cellValue = cell.getBooleanCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: cellValue = cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: if (StringUtils.isNotEmpty(cell.getRichStringCellValue().getString())) { cellValue = cell.getRichStringCellValue().getString(); } break; default: if (StringUtils.isNotEmpty(cell.getStringCellValue())) { cellValue = cell.getStringCellValue(); } break; } if (cellValue != null) { dataMap.put(fieldName, cellValue); if ("id".equals(fieldName)) { cellValue = cellValue.toString().substring(0, cellValue.toString().indexOf(".")); dataMap.put(fieldName, cellValue); } } } if (dataMap.get("code") != null) { String id = ParamUtils.getString(dataMap, "id"); if (!keys.contains(ParamUtils.getString(dataMap, "code"))) { if (id != null && StringUtils.isNotEmpty(id)) { Todo model = new Todo(); Tools.populate(model, dataMap); if (ParamUtils.getInt(dataMap, "limitDay") > 0) { model.setLimitDay(ParamUtils.getInt(dataMap, "limitDay")); } todos.add(model); keys.add(model.getCode()); } } } } return todos; }
From source file:com.glaf.core.todo.util.TodoXlsReader.java
License:Apache License
public List<Todo> readXls(java.io.InputStream inputStream) { List<Todo> todos = new java.util.ArrayList<Todo>(); HSSFWorkbook wb = null;//from w w w .j ava 2 s .c o m try { wb = new HSSFWorkbook(inputStream); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = sheet.getRow(1); Map<Integer, String> keyMap = new java.util.HashMap<Integer, String>(); Map<String, Object> dataMap = new java.util.HashMap<String, Object>(); int cells = row.getPhysicalNumberOfCells(); for (int colIndex = 0; colIndex < cells; colIndex++) { HSSFCell cell = row.getCell(colIndex); keyMap.put(colIndex, cell.getStringCellValue()); } int sortNo = 1; Set<String> keys = new HashSet<String>(); for (int rowIndex = 2; rowIndex < sheet.getPhysicalNumberOfRows(); rowIndex++) { HSSFRow rowx = sheet.getRow(rowIndex); if (rowx == null) { continue; } // System.out.println(); dataMap.clear(); for (int colIndex = 0; colIndex < cells; colIndex++) { String fieldName = keyMap.get(colIndex); HSSFCell cell = rowx.getCell(colIndex); if (cell == null) { continue; } Object cellValue = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: break; case HSSFCell.CELL_TYPE_BOOLEAN: cellValue = cell.getBooleanCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: cellValue = cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: if (StringUtils.isNotEmpty(cell.getRichStringCellValue().getString())) { cellValue = cell.getRichStringCellValue().getString(); } break; default: if (StringUtils.isNotEmpty(cell.getStringCellValue())) { cellValue = cell.getStringCellValue(); } break; } if (cellValue != null) { dataMap.put(fieldName, cellValue); // System.out.print("\t" + fieldName + "=" + cellValue); } } if (dataMap.get("code") != null) { String id = ParamUtils.getString(dataMap, "id"); Todo model = new Todo(); dataMap.remove("id"); Tools.populate(model, dataMap); if (!keys.contains(model.getCode())) { model.setSortNo(sortNo++); if (id != null) { model.setId(Long.parseLong(id)); } if (ParamUtils.getDouble(dataMap, "limitDay") > 0) { model.setLimitDay(ParamUtils.getInt(dataMap, "limitDay")); } todos.add(model); keys.add(model.getCode()); } } } } catch (Exception ex) { throw new RuntimeException(ex); } finally { if (wb != null) { try { wb.close(); wb = null; } catch (IOException e) { } } } return todos; }
From source file:com.isotrol.impe3.idx.oc.extractors.ExtractorMsExcel.java
License:Open Source License
/** * Extracts the text from the Excel table content.<p> * //from w w w . j a v a2s.c om * @param in the document input stream * @return the extracted text * @throws IOException if something goes wring * @deprecated */ protected String extractTableContent(InputStream in) throws IOException { HSSFWorkbook excelWb = new HSSFWorkbook(in); StringBuffer result = new StringBuffer(4096); int numberOfSheets = excelWb.getNumberOfSheets(); for (int i = 0; i < numberOfSheets; i++) { HSSFSheet sheet = excelWb.getSheetAt(i); int numberOfRows = sheet.getPhysicalNumberOfRows(); if (numberOfRows > 0) { if (excelWb.getSheetName(i) != null && !excelWb.getSheetName(i).trim().equals("")) { // append sheet name to content if (i > 0) { result.append("\n\n"); } result.append(excelWb.getSheetName(i).trim()); result.append(":\n\n"); } Iterator<?> rowIt = sheet.rowIterator(); while (rowIt.hasNext()) { HSSFRow row = (HSSFRow) rowIt.next(); if (row != null) { boolean hasContent = false; Iterator<?> it = row.cellIterator(); while (it.hasNext()) { HSSFCell cell = (HSSFCell) it.next(); String text = null; try { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: case HSSFCell.CELL_TYPE_ERROR: // ignore all blank or error cells break; case HSSFCell.CELL_TYPE_NUMERIC: text = Double.toString(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BOOLEAN: text = Boolean.toString(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_STRING: default: text = cell.getStringCellValue(); break; } } catch (Exception e) { // ignore this cell } if ((text != null) && (text.length() != 0)) { result.append(text.trim()); result.append(' '); hasContent = true; } } if (hasContent) { // append a newline at the end of each row that has content result.append('\n'); } } } } } return result.toString(); }
From source file:com.jitendrasinghnz.excelreadutility.ExcelReadStringArrayXSL.java
License:Open Source License
public String convertCellToString(HSSFCell cell) { int type;/*from www . ja v a 2 s . c o m*/ if (cell == null) { type = Cell.CELL_TYPE_BLANK; } else { type = cell.getCellType(); } Object result; switch (type) { case Cell.CELL_TYPE_STRING: result = cell.getStringCellValue(); break; case Cell.CELL_TYPE_NUMERIC: result = cell.getNumericCellValue(); break; case Cell.CELL_TYPE_BLANK: result = ""; break; case Cell.CELL_TYPE_BOOLEAN: result = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_ERROR: result = cell.getErrorCellValue(); break; case Cell.CELL_TYPE_FORMULA: result = cell.getCellFormula(); break; default: throw new RuntimeException("There are no support fot the cell type"); } return result.toString(); }
From source file:com.learn.core.utils.HSSFReadWrite.java
License:Apache License
/** * Method main/*w w w .ja v a 2 s . c om*/ * * Given 1 argument takes that as the filename, inputs it and dumps the * cell values/types out to sys.out.<br> * * given 2 arguments where the second argument is the word "write" and the * first is the filename - writes out a sample (test) spreadsheet * see {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br> * * given 2 arguments where the first is an input filename and the second * an output filename (not write), attempts to fully read in the * spreadsheet and fully write it out.<br> * * given 3 arguments where the first is an input filename and the second an * output filename (not write) and the third is "modify1", attempts to read in the * spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col 3 to * "MODIFIED CELL" then writes it out. Hence this is "modify test 1". If you * take the output from the write test, you'll have a valid scenario. */ public static void main(String[] args) { if (args.length < 1) { System.err.println("At least one argument expected"); return; } String fileName = args[0]; try { if (args.length < 2) { try (HSSFWorkbook wb = HSSFReadWrite.readFile(fileName)) { System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out .println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } System.out.println("\nROW " + row.getRowNum() + " has " + row.getPhysicalNumberOfCells() + " cell(s)."); for (int c = 0; c < row.getLastCellNum(); c++) { HSSFCell cell = row.getCell(c); String value; if (cell != null) { switch (cell.getCellTypeEnum()) { case FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case STRING: value = "STRING value=" + cell.getStringCellValue(); break; case BLANK: value = "<BLANK>"; break; case BOOLEAN: value = "BOOLEAN value-" + cell.getBooleanCellValue(); break; case ERROR: value = "ERROR value=" + cell.getErrorCellValue(); break; default: value = "UNKNOWN value of type " + cell.getCellTypeEnum(); } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } } } } else if (args.length == 2) { if (args[1].toLowerCase(Locale.ROOT).equals("write")) { System.out.println("Write mode"); long time = System.currentTimeMillis(); HSSFReadWrite.testCreateSampleSheet(fileName); System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time"); } else { System.out.println("readwrite test"); try (HSSFWorkbook wb = HSSFReadWrite.readFile(fileName)) { try (FileOutputStream stream = new FileOutputStream(args[1])) { wb.write(stream); } } } } else if (args.length == 3 && args[2].equalsIgnoreCase("modify1")) { // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!" try (HSSFWorkbook wb = HSSFReadWrite.readFile(fileName)) { HSSFSheet sheet = wb.getSheetAt(0); for (int k = 0; k < 25; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } for (int k = 74; k < 100; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } HSSFRow row = sheet.getRow(39); HSSFCell cell = row.getCell(3); cell.setCellValue("MODIFIED CELL!!!!!"); try (FileOutputStream stream = new FileOutputStream(args[1])) { wb.write(stream); } } } } catch (Exception e) { e.printStackTrace(); } }
From source file:com.liferay.util.poi.XLSTextStripper.java
License:Open Source License
public XLSTextStripper(FileInputStream fis) { try {// w w w . j a va 2 s . c om StringBuffer sb = new StringBuffer(); HSSFWorkbook workbook = new HSSFWorkbook(fis); int numOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numOfSheets; i++) { HSSFSheet sheet = workbook.getSheetAt(i); Iterator rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { HSSFRow row = (HSSFRow) rowIterator.next(); Iterator cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { HSSFCell cell = (HSSFCell) cellIterator.next(); String cellStringValue = null; if (cell.getCellType() == 4) { boolean booleanValue = cell.getBooleanCellValue(); cellStringValue = Boolean.toString(booleanValue); } else if (cell.getCellType() == 0) { double doubleValue = cell.getNumericCellValue(); cellStringValue = Double.toString(doubleValue); } else if (cell.getCellType() == 1) { cellStringValue = cell.getStringCellValue(); } if (cellStringValue != null) { sb.append(cellStringValue); sb.append("\t"); } } sb.append("\n"); } } _text = sb.toString(); } catch (Exception e) { Logger.error(this, e.getMessage(), e); } }