List of usage examples for org.apache.poi.ss.usermodel Cell getBooleanCellValue
boolean getBooleanCellValue();
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetGetCellValue.java
License:Open Source License
public cfData execute(cfSession _session, List<cfData> parameters) throws cfmRunTimeException { cfSpreadSheetData spreadsheet = null; int rowNo, columnNo; /*//from ww w. ja v a 2s. c om * Collect up the parameters */ spreadsheet = (cfSpreadSheetData) parameters.get(2); rowNo = parameters.get(1).getInt() - 1; columnNo = parameters.get(0).getInt() - 1; if (rowNo < 0) throwException(_session, "row must be 1 or greater (" + rowNo + ")"); if (columnNo < 0) throwException(_session, "column must be 1 or greater (" + columnNo + ")"); /* * Find the cell in question */ Sheet sheet = spreadsheet.getActiveSheet(); Row row = sheet.getRow(rowNo); if (row == null) row = sheet.createRow(rowNo); Cell cell = row.getCell(columnNo); if (cell == null) cell = row.createCell(columnNo); FormulaEvaluator evaluator = spreadsheet.getWorkBook().getCreationHelper().createFormulaEvaluator(); if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) return cfBooleanData.getcfBooleanData(cell.getBooleanCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) return new cfNumberData(cell.getNumericCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) return cfStringData.EMPTY_STRING; else if (cell.getCellType() == Cell.CELL_TYPE_STRING) return new cfStringData(cell.getStringCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: return cfBooleanData.getcfBooleanData(cellValue.getBooleanValue()); case Cell.CELL_TYPE_NUMERIC: return new cfNumberData(cellValue.getNumberValue()); case Cell.CELL_TYPE_STRING: return new cfStringData(cellValue.getStringValue()); default: return cfStringData.EMPTY_STRING; } } else return cfStringData.EMPTY_STRING; }
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetQueryRead.java
License:Open Source License
public cfData execute(cfSession _session, cfArgStructData argStruct) throws cfmRunTimeException { cfSpreadSheetData spreadsheet = (cfSpreadSheetData) getNamedParam(argStruct, "spreadsheet", null); if (spreadsheet == null) throwException(_session, "spreadsheet object not given"); //How many columns are we using int[] columnsToUse; String columns = getNamedStringParam(argStruct, "columns", null); if (columns != null) { int x = 0; List<Integer> numbers = tagUtils.getNumberListSorted(columns); columnsToUse = new int[numbers.size()]; Iterator<Integer> numbersIT = numbers.iterator(); while (numbersIT.hasNext()) columnsToUse[x++] = (numbersIT.next() - 1); } else {//from w ww .ja va 2 s. c o m int maxColumns = SheetUtility.getMaxColumn(spreadsheet.getActiveSheet()); columnsToUse = new int[maxColumns]; for (int x = 0; x < maxColumns; x++) columnsToUse[x] = x; } //Figure out the columns String columnLabels[] = null; int startRow = 0; String columnnames = getNamedStringParam(argStruct, "columnnames", null); int headerRow = getNamedIntParam(argStruct, "headerrow", -1); FormulaEvaluator evaluator = spreadsheet.getWorkBook().getCreationHelper().createFormulaEvaluator(); if (columnnames != null) { columnLabels = (columnnames.split("\\s*,\\s*")); if (columnLabels.length != columnsToUse.length) throwException(_session, "The COLUMNNAMES does not match the number of columns"); } else if (headerRow > -1) { headerRow = headerRow - 1; Row row = spreadsheet.getActiveSheet().getRow(headerRow); if (row == null) throwException(_session, "The HEADERROW does not exist"); columnLabels = new String[columnsToUse.length]; for (int c = 0; c < columnsToUse.length; c++) { Cell cell = row.getCell(columnsToUse[c]); if (cell == null) columnLabels[c] = ""; else columnLabels[c] = cell.toString(); } startRow = headerRow + 1; } else { columnLabels = new String[columnsToUse.length]; for (int x = 0; x < columnLabels.length; x++) columnLabels[x] = "Column " + (columnsToUse[x] + 1); } //Create the query cfQueryResultData queryData = new cfQueryResultData(columnLabels, "SpreadSheet"); List<Map<String, cfData>> vResults = new ArrayList<Map<String, cfData>>(); int sheetNo = getNamedIntParam(argStruct, "sheet", -1); if (sheetNo != -1) spreadsheet.setActiveSheet(sheetNo); Sheet sheet = spreadsheet.getActiveSheet(); Row row; Cell cell; cfData cfdata; String rowsSt = getNamedStringParam(argStruct, "rows", null); if (rowsSt != null) { List<Integer> rows = tagUtils.getNumberListSorted(rowsSt); Iterator<Integer> rowsIT = rows.iterator(); while (rowsIT.hasNext()) { int r = rowsIT.next() - 1; Map<String, cfData> hm = new FastMap<String, cfData>(); if ((row = sheet.getRow(r)) == null) continue; for (int c = 0; c < columnsToUse.length; c++) { cell = row.getCell(columnsToUse[c]); if (cell == null) cfdata = new cfStringData(""); else { if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) cfdata = cfBooleanData.getcfBooleanData(cell.getBooleanCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cfdata = new cfNumberData(cell.getNumericCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) cfdata = new cfStringData(""); else if (cell.getCellType() == Cell.CELL_TYPE_STRING) cfdata = new cfStringData(cell.getStringCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: cfdata = cfBooleanData.getcfBooleanData(cellValue.getBooleanValue()); break; case Cell.CELL_TYPE_NUMERIC: cfdata = new cfNumberData(cellValue.getNumberValue()); break; case Cell.CELL_TYPE_STRING: cfdata = new cfStringData(cellValue.getStringValue()); break; default: cfdata = cfStringData.EMPTY_STRING; break; } } else cfdata = new cfStringData(""); } hm.put(columnLabels[c], cfdata); } vResults.add(hm); } } else { /* * Read __ALL__ the rows associated with this spreadsheet */ for (int r = startRow; r < sheet.getLastRowNum() + 1; r++) { Map<String, cfData> hm = new FastMap<String, cfData>(); if ((row = sheet.getRow(r)) == null) continue; for (int c = 0; c < columnsToUse.length; c++) { cell = row.getCell(columnsToUse[c]); if (cell == null) cfdata = new cfStringData(""); else { if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) cfdata = cfBooleanData.getcfBooleanData(cell.getBooleanCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cfdata = new cfNumberData(cell.getNumericCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) cfdata = new cfStringData(""); else if (cell.getCellType() == Cell.CELL_TYPE_STRING) cfdata = new cfStringData(cell.getStringCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: cfdata = cfBooleanData.getcfBooleanData(cellValue.getBooleanValue()); break; case Cell.CELL_TYPE_NUMERIC: cfdata = new cfNumberData(cellValue.getNumberValue()); break; case Cell.CELL_TYPE_STRING: cfdata = new cfStringData(cellValue.getStringValue()); break; default: cfdata = cfStringData.EMPTY_STRING; break; } } else cfdata = new cfStringData(""); } hm.put(columnLabels[c], cfdata); } vResults.add(hm); } } queryData.populateQuery(vResults); return queryData; }
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.SheetUtility.java
License:Open Source License
public static void cloneCell(Cell cNew, Cell cOld) { cNew.setCellComment(cOld.getCellComment()); cNew.setCellStyle(cOld.getCellStyle()); cNew.setCellType(cOld.getCellType()); switch (cNew.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: { cNew.setCellValue(cOld.getBooleanCellValue()); break;//from w w w . ja va 2s . c o m } case Cell.CELL_TYPE_NUMERIC: { cNew.setCellValue(cOld.getNumericCellValue()); break; } case Cell.CELL_TYPE_STRING: { cNew.setCellValue(cOld.getStringCellValue()); break; } case Cell.CELL_TYPE_ERROR: { cNew.setCellValue(cOld.getErrorCellValue()); break; } case Cell.CELL_TYPE_FORMULA: { cNew.setCellFormula(cOld.getCellFormula()); break; } case Cell.CELL_TYPE_BLANK: { cNew.setCellValue(cOld.getNumericCellValue()); break; } } }
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.tags.cfSpreadSheetRead.java
License:Open Source License
private cfQueryResultData getQueryData(cfSpreadSheet tag, cfSession _Session) throws cfmRunTimeException { cfSpreadSheetData spreadsheet = getSpreadSheetData(tag, _Session); //How many columns are we using int[] columnsToUse; if (tag.containsAttribute("COLUMNS")) { int x = 0; List<Integer> numbers = tagUtils.getNumberListSorted(tag.getDynamic(_Session, "COLUMNS").getString()); columnsToUse = new int[numbers.size()]; Iterator<Integer> numbersIT = numbers.iterator(); while (numbersIT.hasNext()) columnsToUse[x++] = (numbersIT.next() - 1); } else {//from w ww. ja v a 2s . c om int maxColumns = SheetUtility.getMaxColumn(spreadsheet.getActiveSheet()); columnsToUse = new int[maxColumns]; for (int x = 0; x < maxColumns; x++) columnsToUse[x] = x; } //Figure out the columns String columnLabels[] = null; int startRow = 0; if (tag.containsAttribute("COLUMNNAMES")) { columnLabels = tag.getDynamic(_Session, "COLUMNNAMES").getString().split(","); if (columnLabels.length != columnsToUse.length) throw tag.newRunTimeException("The COLUMNNAMES does not match the number of columns"); } else if (tag.containsAttribute("HEADERROW")) { int headerRow = tag.getDynamic(_Session, "HEADERROW").getInt() - 1; Row row = spreadsheet.getActiveSheet().getRow(headerRow); if (row == null) throw tag.newRunTimeException("The HEADERROW does not exist"); columnLabels = new String[columnsToUse.length]; for (int c = 0; c < columnsToUse.length; c++) { Cell cell = row.getCell(columnsToUse[c]); if (cell == null) columnLabels[c] = ""; else columnLabels[c] = cell.toString(); } startRow = headerRow + 1; } else { columnLabels = new String[columnsToUse.length]; for (int x = 0; x < columnLabels.length; x++) { columnLabels[x] = "Column " + (columnsToUse[x] + 1); } } //Create the query cfQueryResultData queryData = new cfQueryResultData(columnLabels, "SpreadSheet"); List<Map<String, cfData>> vResults = new ArrayList<Map<String, cfData>>(); Sheet sheet = spreadsheet.getActiveSheet(); Row row; Cell cell; cfData cfdata; if (tag.containsAttribute("ROWS")) { List<Integer> rows = tagUtils.getNumberListSorted(tag.getDynamic(_Session, "ROWS").getString()); Iterator<Integer> rowsIT = rows.iterator(); while (rowsIT.hasNext()) { int r = rowsIT.next() - 1; Map<String, cfData> hm = new FastMap<String, cfData>(); if ((row = sheet.getRow(r)) == null) continue; for (int c = 0; c < columnsToUse.length; c++) { cell = row.getCell(columnsToUse[c]); if (cell == null) cfdata = new cfStringData(""); else { if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) cfdata = cfBooleanData.getcfBooleanData(cell.getBooleanCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cfdata = new cfNumberData(cell.getNumericCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) cfdata = new cfStringData(""); else if (cell.getCellType() == Cell.CELL_TYPE_STRING) cfdata = new cfStringData(cell.getStringCellValue()); else cfdata = new cfStringData(""); } hm.put(columnLabels[c], cfdata); } vResults.add(hm); } } else { /* * Read __ALL__ the rows associated with this spreadsheet */ for (int r = startRow; r < sheet.getLastRowNum() + 1; r++) { Map<String, cfData> hm = new FastMap<String, cfData>(); if ((row = sheet.getRow(r)) == null) continue; for (int c = 0; c < columnsToUse.length; c++) { cell = row.getCell(columnsToUse[c]); if (cell == null) cfdata = new cfStringData(""); else { if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) cfdata = cfBooleanData.getcfBooleanData(cell.getBooleanCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cfdata = new cfNumberData(cell.getNumericCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) cfdata = new cfStringData(""); else if (cell.getCellType() == Cell.CELL_TYPE_STRING) cfdata = new cfStringData(cell.getStringCellValue()); else cfdata = new cfStringData(""); } hm.put(columnLabels[c], cfdata); } vResults.add(hm); } } queryData.populateQuery(vResults); return queryData; }
From source file:org.aludratest.testcase.data.impl.TestConfigInfoHelper.java
License:Apache License
private boolean parseIgnoredCell(Row infoRow, int ignoreColumnIndex) { boolean ignored = false; if (aludraConfig.isIgnoreEnabled() && infoRow != null && ignoreColumnIndex >= 0) { Cell ignoreCell = infoRow.getCell(ignoreColumnIndex); if (ignoreCell != null) { if (ignoreCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { ignored = ignoreCell.getBooleanCellValue(); } else if (ignoreCell.getCellType() == Cell.CELL_TYPE_STRING) { String spec = ignoreCell.getStringCellValue(); if ("true".equals(spec)) { ignored = true;/* w w w . ja va 2 s.c o m*/ } else if (!(spec == null || "".equals(spec) || "false".equals(spec))) { throw new AutomationException( "Illegal value for '" + IGNORE_COLUMN_NAME + "' column: " + spec); } } } } return ignored; }
From source file:org.apache.metamodel.excel.ExcelUtils.java
License:Apache License
public static String getCellValue(Workbook wb, Cell cell) { if (cell == null) { return null; }//w ww . jav a 2 s.c o m final String cellCoordinate = "(" + cell.getRowIndex() + "," + cell.getColumnIndex() + ")"; final String result; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: result = null; break; case Cell.CELL_TYPE_BOOLEAN: result = Boolean.toString(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: String errorResult; try { byte errorCode = cell.getErrorCellValue(); FormulaError formulaError = FormulaError.forInt(errorCode); errorResult = formulaError.getString(); } catch (RuntimeException e) { logger.debug("Getting error code for {} failed!: {}", cellCoordinate, e.getMessage()); if (cell instanceof XSSFCell) { // hack to get error string, which is available String value = ((XSSFCell) cell).getErrorCellString(); errorResult = value; } else { logger.error("Couldn't handle unexpected error scenario in cell: " + cellCoordinate, e); throw e; } } result = errorResult; break; case Cell.CELL_TYPE_FORMULA: // result = cell.getCellFormula(); result = getFormulaCellValue(wb, cell); break; case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); if (date == null) { result = null; } else { result = DateUtils.createDateFormat().format(date); } } else { // TODO: Consider not formatting it, but simple using // Double.toString(...) result = _numberFormat.format(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: result = cell.getRichStringCellValue().getString(); break; default: throw new IllegalStateException("Unknown cell type: " + cell.getCellType()); } logger.debug("cell {} resolved to value: {}", cellCoordinate, result); return result; }
From source file:org.azkfw.datasource.excel.ExcelDatasourceBuilder.java
License:Apache License
private String toStringFromCell(final Cell aCell) { // ??? String string = ""; if (null != aCell) { switch (aCell.getCellType()) { case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_BOOLEAN: string = Boolean.toString(aCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: string = aCell.getCellFormula(); // string = cell.getStringCellValue();( break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(aCell)) { java.util.Date dt = aCell.getDateCellValue(); string = (new SimpleDateFormat("yyyy/MM/dd HH:mm:ss")).format(dt); } else { string = Double.toString(aCell.getNumericCellValue()); }/*from w ww . j a v a 2 s . co m*/ break; case Cell.CELL_TYPE_STRING: { string = aCell.getStringCellValue(); break; } case Cell.CELL_TYPE_ERROR: { break; } } } return string; }
From source file:org.azkfw.datasource.excel.ExcelDatasourceFactory.java
License:Apache License
private static String toStringFromCell(final Cell aCell) { // ??? String string = ""; if (null != aCell) { switch (aCell.getCellType()) { case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_BOOLEAN: string = Boolean.toString(aCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: string = aCell.getCellFormula(); // string = cell.getStringCellValue();( break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(aCell)) { java.util.Date dt = aCell.getDateCellValue(); string = (new SimpleDateFormat("yyyy/MM/dd HH:mm:ss")).format(dt); } else { string = Double.toString(aCell.getNumericCellValue()); }/* w w w. j a va 2 s . com*/ break; case Cell.CELL_TYPE_STRING: { string = aCell.getStringCellValue(); break; } case Cell.CELL_TYPE_ERROR: { break; } } } return string; }
From source file:org.badvision.outlaweditor.data.DataUtilities.java
License:Apache License
public static String getStringValueFromCell(Cell cell) { switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: return Boolean.toString(cell.getBooleanCellValue()); case Cell.CELL_TYPE_BLANK: return null; case Cell.CELL_TYPE_NUMERIC: return Double.toString(cell.getNumericCellValue()); case Cell.CELL_TYPE_STRING: return cell.getStringCellValue(); default://from w w w . j a v a2s.co m return "???"; } }
From source file:org.bbreak.excella.core.test.util.TestUtil.java
License:Open Source License
private static Object getCellValue(Cell cell) { String value = null;// www . jav a 2 s .c o m if (cell != null) { switch (cell.getCellTypeEnum()) { case BLANK: value = cell.getStringCellValue(); break; case BOOLEAN: value = String.valueOf(cell.getBooleanCellValue()); break; case ERROR: value = String.valueOf(cell.getErrorCellValue()); break; case NUMERIC: value = String.valueOf(cell.getNumericCellValue()); break; case STRING: value = cell.getStringCellValue(); break; case FORMULA: value = cell.getCellFormula(); default: value = ""; } } return value; }