Java Code Examples for org.apache.poi.ss.usermodel.Cell#CELL_TYPE_FORMULA
The following examples show how to use
org.apache.poi.ss.usermodel.Cell#CELL_TYPE_FORMULA .
You can vote up the ones you like or vote down the ones you don't like,
and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: CellValueFormatter.java From CloverETL-Engine with GNU Lesser General Public License v2.1 | 6 votes |
@Override public String formatCellValue(Cell cell, FormulaEvaluator evaluator) { if (cell == null) { return ""; } int cellType = cell.getCellType(); if (cellType == Cell.CELL_TYPE_FORMULA) { if (evaluator == null) { return cell.getCellFormula(); } cellType = evaluator.evaluateFormulaCell(cell); } switch (cellType) { case Cell.CELL_TYPE_NUMERIC : return getFormattedNumberString(cell); case Cell.CELL_TYPE_STRING : return cell.getRichStringCellValue().getString(); case Cell.CELL_TYPE_BOOLEAN : return String.valueOf(cell.getBooleanCellValue()); case Cell.CELL_TYPE_BLANK : return ""; } throw new RuntimeException("Unexpected celltype (" + cellType + ")"); }
Example 2
Source File: ExcelImportServer.java From autopoi with Apache License 2.0 | 6 votes |
/** * 获取key的值,针对不同类型获取不同的值 * * @Author JEECG * @date 2013-11-21 * @param cell * @return */ private String getKeyValue(Cell cell) { if(cell==null){ return null; } Object obj = null; switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: obj = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: obj = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_NUMERIC: obj = cell.getNumericCellValue(); break; case Cell.CELL_TYPE_FORMULA: obj = cell.getCellFormula(); break; } return obj == null ? null : obj.toString().trim(); }
Example 3
Source File: ExcelImportServer.java From jeasypoi with Apache License 2.0 | 6 votes |
/** * 获取key的值,针对不同类型获取不同的值 * * @Author JueYue * @date 2013-11-21 * @param cell * @return */ private String getKeyValue(Cell cell) { Object obj = null; switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: obj = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: obj = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_NUMERIC: obj = cell.getNumericCellValue(); break; case Cell.CELL_TYPE_FORMULA: obj = cell.getCellFormula(); break; } return obj == null ? null : obj.toString().trim(); }
Example 4
Source File: SpreadsheetGetCellFormula.java From openbd-core with GNU General Public License v3.0 | 6 votes |
private cfData getAllFormulaForSheet( cfSession _session, cfSpreadSheetData spreadsheet ) throws cfmRunTimeException { cfArrayData array = cfArrayListData.createArray(1); Iterator<Row> rowIt = spreadsheet.getActiveSheet().rowIterator(); while ( rowIt.hasNext() ){ Row row = rowIt.next(); Iterator<Cell> cellIt = row.cellIterator(); while ( cellIt.hasNext() ){ Cell cell = cellIt.next(); if ( cell.getCellType() == Cell.CELL_TYPE_FORMULA ){ cfStructData s = new cfStructData(); s.setData( "formula", new cfStringData( cell.getCellFormula() ) ); s.setData( "row", new cfNumberData( row.getRowNum() + 1 ) ); s.setData( "column", new cfNumberData( cell.getColumnIndex() + 1 ) ); array.addElement( s ); } } } return array; }
Example 5
Source File: ExcelUtil.java From Leo with Apache License 2.0 | 6 votes |
private String getStrFromCell(Cell cell) { String res = ""; if (null==cell) { return ""; } // res=cell.getRichStringCellValue().toString(); switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: // 数字/日期 if (DateUtil.isCellDateFormatted(cell)){ res=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cell.getDateCellValue()); }else{ BigDecimal value = new BigDecimal(cell.getNumericCellValue()); String str = value.toString(); if(str.contains(".0"))str = str.replace(".0", ""); res=str; } break; case Cell.CELL_TYPE_STRING: // 字符串 res = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: // 布尔 Boolean booleanValue = cell.getBooleanCellValue(); res = booleanValue.toString(); break; case Cell.CELL_TYPE_BLANK: // 空值 res = ""; break; case Cell.CELL_TYPE_FORMULA: // 公式 res = cell.getCellFormula(); break; case Cell.CELL_TYPE_ERROR: // 故障 res = ""; break; default: System.out.println("未知类型"); break; } return res; }
Example 6
Source File: ExcelImportServer.java From easypoi with Apache License 2.0 | 6 votes |
/** * 获取key的值,针对不同类型获取不同的值 * * @Author JueYue * @date 2013-11-21 * @param cell * @return */ private String getKeyValue(Cell cell) { Object obj = null; switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: obj = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: obj = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_NUMERIC: obj = cell.getNumericCellValue(); break; case Cell.CELL_TYPE_FORMULA: obj = cell.getCellFormula(); break; } return obj == null ? null : obj.toString().trim(); }
Example 7
Source File: ImportExcel.java From Shop-for-JavaWeb with MIT License | 6 votes |
/** * 获取单元格值 * @param row 获取的行 * @param column 获取单元格列号 * @return 单元格值 */ public Object getCellValue(Row row, int column){ Object val = ""; try{ Cell cell = row.getCell(column); if (cell != null){ if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC){ val = cell.getNumericCellValue(); }else if (cell.getCellType() == Cell.CELL_TYPE_STRING){ val = cell.getStringCellValue(); }else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA){ val = cell.getCellFormula(); }else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){ val = cell.getBooleanCellValue(); }else if (cell.getCellType() == Cell.CELL_TYPE_ERROR){ val = cell.getErrorCellValue(); } } }catch (Exception e) { return val; } return val; }
Example 8
Source File: ExcelDataReader.java From micro-integrator with Apache License 2.0 | 5 votes |
/** * Extracts the value of a particular cell depending on its type * * @param cell A populated Cell instance * @return Value of the cell */ private Object extractCellValue(Cell cell) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: return cell.getNumericCellValue(); case Cell.CELL_TYPE_BLANK: case Cell.CELL_TYPE_FORMULA: case Cell.CELL_TYPE_STRING: return cell.getStringCellValue(); case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue(); default: return cell.getStringCellValue(); } }
Example 9
Source File: readExcelXLSX.java From Selenium with The Unlicense | 5 votes |
private static String cellToString(XSSFCell cell) { Object result; 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.toString(); }
Example 10
Source File: TestExcelFormulaDemo.java From poi with Apache License 2.0 | 5 votes |
public static void readSheetWithFormula() { try { FileInputStream file = new FileInputStream(new File( TestUtil.DOC_PATH + File.separator + EXCEL_NAME + Globals.SUFFIX_XLSX)); // Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); FormulaEvaluator evaluator = workbook.getCreationHelper() .createFormulaEvaluator(); // Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); // Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); // For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); // If it is formula cell, it will be evaluated otherwise no // change will happen switch (evaluator.evaluateInCell(cell).getCellType()) { 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; case Cell.CELL_TYPE_FORMULA: // Not again break; } } System.out.println(""); } file.close(); } catch (Exception e) { e.printStackTrace(); } }
Example 11
Source File: AbstractExcelExtractor.java From wandora with GNU General Public License v3.0 | 5 votes |
public Topic getCellTypeTopic(Cell cell, TopicMap tm) throws TopicMapException { int type = cell.getCellType(); String typeStr = "string"; switch(type) { case Cell.CELL_TYPE_BLANK: { typeStr = "blank"; break; } case Cell.CELL_TYPE_BOOLEAN: { typeStr = "boolean"; break; } case Cell.CELL_TYPE_ERROR: { typeStr = "error"; break; } case Cell.CELL_TYPE_FORMULA: { typeStr = "formula"; break; } case Cell.CELL_TYPE_NUMERIC: { typeStr = "numeric"; break; } case Cell.CELL_TYPE_STRING: { typeStr = "string"; break; } } Topic t = getOrCreateTopic(tm, EXCEL_CELL_TYPE_SI_PREFIX+"/"+typeStr, "Excel cell type "+typeStr); t.addType(getCellTypeTypeTopic(tm)); return t; }
Example 12
Source File: ReadExcelFormula.java From journaldev with MIT License | 5 votes |
public static void readExcelFormula(String fileName) throws IOException{ FileInputStream fis = new FileInputStream(fileName); //assuming xlsx file Workbook workbook = new XSSFWorkbook(fis); Sheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch(cell.getCellType()){ case Cell.CELL_TYPE_NUMERIC: System.out.println(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_FORMULA: System.out.println("Cell Formula="+cell.getCellFormula()); System.out.println("Cell Formula Result Type="+cell.getCachedFormulaResultType()); if(cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC){ System.out.println("Formula Value="+cell.getNumericCellValue()); } } } } }
Example 13
Source File: SsioIntegrationTest.java From sep4j with Apache License 2.0 | 5 votes |
private Object getStringOrDateValue(Cell cell) { if (cell == null) { return null; } if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { return null; } if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { return String.valueOf(cell.getBooleanCellValue()); } if (cell.getCellType() == Cell.CELL_TYPE_ERROR) { return null; } if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { return null; } if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { if (DateUtil.isCellDateFormatted(cell)) { return cell.getDateCellValue(); } else { double v = cell.getNumericCellValue(); return String.valueOf(v); } } if (cell.getCellType() == Cell.CELL_TYPE_STRING) { String s = cell.getStringCellValue(); return StringUtils.trimToNull(s); } return null; }
Example 14
Source File: Excel2XMLTransformer.java From equalize-xpi-modules with MIT License | 5 votes |
private String retrieveCellContent(Cell cell, Workbook wb, boolean evaluateFormulas, String formatting) { FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); DataFormatter formatter = new DataFormatter(true); String cellContent = null; int cellType = cell.getCellType(); switch(cellType) { case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_FORMULA: if (evaluateFormulas) { cellContent = formatter.formatCellValue(cell, evaluator); } else { // Display the formula instead cellContent = cell.getCellFormula(); } break; default: if(formatting.equalsIgnoreCase("excel")) { cellContent = formatter.formatCellValue(cell); } else if(formatting.equalsIgnoreCase("raw")) { // Display the raw cell contents switch (cellType) { case Cell.CELL_TYPE_NUMERIC: cellContent = Double.toString(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: cellContent = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: cellContent = Boolean.toString(cell.getBooleanCellValue()); break; } } break; } return cellContent; }
Example 15
Source File: XssfWCellImpl.java From xlsbeans with Apache License 2.0 | 5 votes |
public String getContents() { String contents = null; // IllegalStateException occurs , if illegal type defined... switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: contents = ""; break; case Cell.CELL_TYPE_BOOLEAN: contents = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: contents = String.valueOf(cell.getCellFormula()); break; case Cell.CELL_TYPE_FORMULA: contents = getCellFormulaContents(cell); break; case Cell.CELL_TYPE_NUMERIC: contents = getNumericContents(cell); break; case Cell.CELL_TYPE_STRING: contents = String.valueOf(cell.getStringCellValue()); break; default: contents = ""; break; } return contents; }
Example 16
Source File: ExcelTempletService.java From jeewx with Apache License 2.0 | 5 votes |
/** * 得到某个格子的值 已经对过时方法进行更新 * * @param cell * 格子对象 * @return 格子的值 */ public static String getCellValueString(Cell cell) { if (cell == null) { return null; } // 时间对象 特殊处理 int dataFormat = cell.getCellStyle().getDataFormat(); if (dataFormat == 14 || dataFormat == 178 || dataFormat == 180 || dataFormat == 181 || dataFormat == 182) { return getDateValue(cell); } String value = null; switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC : value = new DecimalFormat("0.##########").format(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING : // value = cell.getStringCellValue(); value = cell.getRichStringCellValue().toString(); break; case Cell.CELL_TYPE_FORMULA : value = String.valueOf(cell.getCellFormula()); break; case Cell.CELL_TYPE_BLANK : // value = String.valueOf(cell.getStringCellValue()); value = String.valueOf(cell.getRichStringCellValue().toString()); break; case Cell.CELL_TYPE_BOOLEAN : value = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR : value = String.valueOf(cell.getErrorCellValue()); break; } return value; }
Example 17
Source File: XssfWCellImpl.java From xlsbeans with Apache License 2.0 | 5 votes |
public String getContents() { String contents = null; // IllegalStateException occurs , if illegal type defined... switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: contents = ""; break; case Cell.CELL_TYPE_BOOLEAN: contents = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: contents = String.valueOf(cell.getCellFormula()); break; case Cell.CELL_TYPE_FORMULA: contents = getCellFormulaContents(cell); break; case Cell.CELL_TYPE_NUMERIC: contents = getNumericContents(cell); break; case Cell.CELL_TYPE_STRING: contents = String.valueOf(cell.getStringCellValue()); break; default: contents = ""; break; } return contents; }
Example 18
Source File: SpreadsheetGetCellValue.java From openbd-core with GNU General Public License v3.0 | 4 votes |
public cfData execute( cfSession _session, List<cfData> parameters ) throws cfmRunTimeException { cfSpreadSheetData spreadsheet = null; int rowNo, columnNo; /* * 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; }
Example 19
Source File: ExcelUtil.java From autopoi with Apache License 2.0 | 4 votes |
/** * 获取单元格的值 * @param cell * @return */ public static String getCellValue(Cell cell){ if(cell == null) return ""; if(cell.getCellType() == Cell.CELL_TYPE_STRING){ return cell.getStringCellValue(); }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){ return String.valueOf(cell.getBooleanCellValue()); }else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){ return cell.getCellFormula() ; }else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){ return String.valueOf(cell.getNumericCellValue()); } return ""; }
Example 20
Source File: ExcelUtil.java From SI with BSD 2-Clause "Simplified" License | 4 votes |
/** * 엑셀파일로부터 데이터를 읽어 리턴한다. * */ public static List getData(Workbook wb) { List excelList = new ArrayList(); int sheetNum = wb.getNumberOfSheets(); for (int k=0; k<sheetNum; k++) { Sheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); for (int r=0; r<rows; r++) { Row row = sheet.getRow(r); if (row != null) { int cells = row.getPhysicalNumberOfCells(); String[] excelRow = new String[cells]; for(int c=0; c<cells; c++) { Cell cell = row.getCell(c); if (cell != null) { String value = null; switch (cell.getCellType()) { case Cell.CELL_TYPE_FORMULA: value = cell.getCellFormula(); break; case Cell.CELL_TYPE_NUMERIC: value = "" + Integer.parseInt(String.valueOf(Math.round(cell.getNumericCellValue()))); break; case Cell.CELL_TYPE_STRING: value = "" + cell.getStringCellValue(); break; case Cell.CELL_TYPE_BLANK: value = "" + cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_ERROR: value = "" + cell.getErrorCellValue(); break; default: } excelRow[c] = value; } } excelList.add(excelRow); } } } return excelList; }