Java Code Examples for org.apache.poi.ss.usermodel.Cell#getStringCellValue()

private Schema createSchema(Row headerRow, boolean validName) {
  SchemaBuilder.FieldAssembler<Schema> fa = SchemaBuilder.record(RECORD_NAME).fields();
  
  if(headerRow!=null) {
    Set<String> existNames = new HashSet<String>();
    int index = 0;
    
    int cellNumber = getCellNumber(headerRow);
    
    for (int i = 0; i < cellNumber; i++) {
        Cell cell = headerRow.getCell(i);
        String fieldName = validName ? (cell == null ? StringUtils.EMPTY : cell.getStringCellValue()) : (FIELD_PREFIX + (i));
        
        String finalName = NameUtil.correct(fieldName, index++, existNames);
        existNames.add(finalName);
        
        fa = fa.name(finalName).type(Schema.create(Schema.Type.STRING)).noDefault();
    }
  }
  
  return fa.endRecord();
}
 

@Override
public Object getValue(CsvColumnKey key, Row source) {
    final Cell cell = source.getCell(key.getIndex());
    if (cell != null) {
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BLANK:
                return null;
            case Cell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue();
            case Cell.CELL_TYPE_NUMERIC:
                return cell.getNumericCellValue();
            default:
                return cell.getStringCellValue();
        }
    }
    return null;
}
 

/**
 * 获取指定单元格的值
 * @param cell 指定单元格
 * @return 值
 */
public static String getColumnValue(Cell cell) {
    switch (cell.getCellTypeEnum()) {
        case BOOLEAN:
            return Boolean.toString(cell.getBooleanCellValue());
        case NUMERIC:
            return String.valueOf(cell.getNumericCellValue());
        case BLANK:
            return "";
        case FORMULA:
            return cell.getCellFormula();
        default:
            return cell.getStringCellValue();
    }

}
 

@SuppressWarnings("deprecation")
private static String getCellData(Cell cell) {
    String value = null;
    if (cell == null) {
        return null;
    }
    switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            value = cell.getStringCellValue();
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            value = cell.getCellFormula();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
            value = dataFormatter.formatCellValue(cell);
            break;
        case HSSFCell.CELL_TYPE_BLANK:
            value = null;
            break;
        case HSSFCell.CELL_TYPE_ERROR:
            value = "#ERROR#";
            break;
    }
    return value;
}
 

private String readCellAsString(final Cell cell) {
    if (cell == null) {
        return "";
    }

    switch (cell.getCellType()) {
        case CELL_TYPE_STRING:
            return cell.getStringCellValue();
        case CELL_TYPE_BLANK:
            return "";
        case CELL_TYPE_BOOLEAN:
            return Boolean.toString(cell.getBooleanCellValue());
        case CELL_TYPE_NUMERIC:
            final DataFormatter formatter = new DataFormatter();
            return formatter.formatCellValue(cell);
        default:
            throw new RuntimeException("unknown cell type " + cell.getCellType());
    }

}
 

@Override
protected void mapNames(Map<String, Integer> fieldNames) throws ComponentNotReadyException {
	if (fieldNames == null) {
		throw new NullPointerException("fieldNames");
	}

	Row row = sheet.getRow(metadataRow);
	int numberOfFoundFields = 0;

	for (int i = 0; i < row.getLastCellNum(); i++) {
		Cell cell = row.getCell(i);

		if (cell != null) {
			String cellValue = cell.getStringCellValue();

			if (fieldNames.containsKey(cellValue)) {// corresponding field in metadata found
				fieldNumber[numberOfFoundFields][XLS_NUMBER] = i;
				fieldNumber[numberOfFoundFields][CLOVER_NUMBER] = fieldNames.get(cellValue);
				numberOfFoundFields++;

				fieldNames.remove(cellValue);
			} else {
				logger.warn("There is no field \"" + cellValue + "\" in output metadata");
			}
		}
	}

	if (numberOfFoundFields < metadata.getNumFields()) {
		logger.warn("Not all fields found:");

		for (String fieldName : fieldNames.keySet()) {
			logger.warn(fieldName);
		}
	}
}
 

protected Object readValueFromCell(Cell cell) {
  if (cell == null) return null;
  Object cellValue = null;
  switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
      cellValue = cell.getBooleanCellValue();
      break;
    case Cell.CELL_TYPE_NUMERIC:
      cellValue = cell.getNumericCellValue();
      break;
    default:
      cellValue = cell.getStringCellValue();
  }
  return cellValue;
}
 

public Relationship getRelationship(int rowNum, SpdxDocumentContainer container) throws SpreadsheetException {
	Row row = sheet.getRow(rowNum);
	if (row == null) {
		return null;
	}
	Cell relatedIdCell = row.getCell(RELATED_ID_COL);
	String relatedId = null;
	if (relatedIdCell != null && relatedIdCell.getStringCellValue() != null) {
		relatedId = relatedIdCell.getStringCellValue();
	}
	RelationshipType type = null;
	Cell relationshipCell = row.getCell(RELATIONSHIP_COL);
	if (relationshipCell != null && relationshipCell.getStringCellValue() != null) {
		type = RelationshipType.fromTag(relationshipCell.getStringCellValue().trim());
	}
	Cell commentCell = row.getCell(COMMENT_COL);
	String comment = null;
	if (commentCell != null && commentCell.getStringCellValue() != null) {
		comment = commentCell.getStringCellValue();
	}
	if (relatedId == null) {
		throw new SpreadsheetException("No related element ID for relationship");
	}
	SpdxElement element;
	try {
		element = container.findElementById(relatedId);
	} catch (InvalidSPDXAnalysisException e) {
		throw new SpreadsheetException("No element found for relationship with related ID "+relatedId);
	}	
	return new Relationship(element, type, comment);
}
 

@Test
public void testRowSpanXlsx() throws BirtException, IOException {

	debug = false;
	InputStream inputStream = runAndRenderReport("Issue27.rptdesign", "xlsx");
       assertNotNull(inputStream);
       try {
           XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
           assertNotNull(workbook);
   
           Sheet sheet = workbook.getSheetAt(0);
           int rangesValidated = 0;
           
           for( Row row : sheet ) {
           	for( Cell cell : row ) {
           		if(cell.getCellType() == Cell.CELL_TYPE_STRING) {
           			String cellValue = cell.getStringCellValue();
           			
           			Matcher matcher = pattern.matcher(cellValue);
           			if( matcher.matches() ) {
           				validateCellRange( matcher, cell );
           				++rangesValidated;
           			}
           		}
           	}
           }
           assertEquals( 12, rangesValidated );
       
       } finally {
           inputStream.close();
       }
}
 

private void loadColumnTitles(Sheet sheet, int maxPhysicalNumberOfRows) {
    if (maxPhysicalNumberOfRows > 0) {
        int row = options.getHeaderStart();
        Row firstRow = sheet.getRow(row);
        for (Cell cell : firstRow) {
            final int columnIndex = cell.getColumnIndex();
            caseSensitiveTitlePerColumnIndex.put(columnIndex, getTitleNameForMap(cell.getStringCellValue(), columnIndex));
            final String titleName = options.getCaseInsensitive()
                    ? cell.getStringCellValue().toLowerCase()
                    : cell.getStringCellValue();
            columnIndexPerTitle.put(titleName, columnIndex);
            titlePerColumnIndex.put(columnIndex, getTitleNameForMap(titleName, columnIndex));
        }
    }
}
 

public String getIdentifier(int rowNum) {
	Row row = sheet.getRow(rowNum);
	if (row == null) {
		return null;
	}
	Cell idCell = row.getCell(IDENTIFIER_COL);
	if (idCell == null) {
		return null;
	}
	return idCell.getStringCellValue();
}
 

protected String getCellFormatValue(Cell cell) {
    String cellValue;
    if (cell != null) {
        switch (cell.getCellType()) {
            case NUMERIC:
                cellValue = df.format(cell.getNumericCellValue());
                break;
            case FORMULA: {
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = sdf.format(date);
                } else {
                    cellValue = String.valueOf(cell.getStringCellValue());
                }
                break;
            }
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case ERROR:
            case BLANK:
            default:
                cellValue = BLANK;
        }
    } else {
        cellValue = BLANK;
    }
    return cellValue;
}
 

/**
 * 给每个Cell通过解析方式set值
 * 
 * @param cell
 * @param map
 */
private void setValueForCellByMap(Cell cell, Map<String, Object> map) throws Exception {
	int cellType = cell.getCellType();
	if (cellType != Cell.CELL_TYPE_STRING && cellType != Cell.CELL_TYPE_NUMERIC) {
		return;
	}
	String oldString;
	cell.setCellType(Cell.CELL_TYPE_STRING);
	oldString = cell.getStringCellValue();
	if (oldString != null && oldString.indexOf(START_STR) != -1 && !oldString.contains(FOREACH)) {
		// step 2. 判断是否含有解析函数
		String params = null;
		boolean isNumber = false;
		if (isNumber(oldString)) {
			isNumber = true;
			oldString = oldString.replace(NUMBER_SYMBOL, "");
		}
		while (oldString.indexOf(START_STR) != -1) {
			params = oldString.substring(oldString.indexOf(START_STR) + 2, oldString.indexOf(END_STR));

			oldString = oldString.replace(START_STR + params + END_STR, eval(params, map).toString());
		}
		// 如何是数值 类型,就按照数值类型进行设置
		if (isNumber && StringUtils.isNotBlank(oldString)) {
			cell.setCellValue(Double.parseDouble(oldString));
			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
		} else {
			cell.setCellValue(oldString);
		}
	}
	// 判断foreach 这种方法
	if (oldString != null && oldString.contains(FOREACH)) {
		addListDataToExcel(cell, map, oldString.trim());
	}

}
 

public String getHtmlValue(Cell cell) {
	if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType() || Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
		cell.setCellType(Cell.CELL_TYPE_STRING);
		return cell.getStringCellValue();
	} else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
		if (cell.getRichStringCellValue().numFormattingRuns() == 0) {
			return XmlEscapers.xmlContentEscaper().escape(cell.getStringCellValue());
		} else if (is07) {
			return getXSSFRichString((XSSFRichTextString) cell.getRichStringCellValue());
		} else {
			return getHSSFRichString((HSSFRichTextString) cell.getRichStringCellValue());
		}
	}
	return "";
}
 

@Override
public String verify() {
	try {
		if (sheet == null) {
			return "Worksheet for Annotations does not exist";
		}
		Row firstRow = sheet.getRow(firstRowNum);
		for (int i = 0; i < NUM_COLS; i++) {
			Cell cell = firstRow.getCell(i+firstCellNum);
			if (cell == null || 
					cell.getStringCellValue() == null ||
					!cell.getStringCellValue().equals(HEADER_TITLES[i])) {
				return "Column "+HEADER_TITLES[i]+" missing for Annotation worksheet";
			}
		}
		// validate rows
		boolean done = false;
		int rowNum = firstRowNum + 1;
		while (!done) {
			Row row = sheet.getRow(rowNum);
			if (row == null || row.getCell(firstCellNum) == null) {
				done = true;
			} else {
				String error = validateRow(row);
				if (error != null) {
					return error;
				}
				rowNum++;
			}
		}
		return null;
	} catch (Exception ex) {
		return "Error in verifying Annotations worksheet: "+ex.getMessage();
	}
}
 

public Object getCellValueByFetchingLastCachedValue(String fileLocation, String cellLocation) throws IOException {
    Object cellValue = new Object();

    FileInputStream inputStream = new FileInputStream(new File(fileLocation));
    Workbook workbook = new XSSFWorkbook(inputStream);

    Sheet sheet = workbook.getSheetAt(0);

    CellAddress cellAddress = new CellAddress(cellLocation);
    Row row = sheet.getRow(cellAddress.getRow());
    Cell cell = row.getCell(cellAddress.getColumn());

    if (cell.getCellType() == CellType.FORMULA) {
        switch (cell.getCachedFormulaResultType()) {
            case BOOLEAN:
                cellValue = cell.getBooleanCellValue();
                break;
            case NUMERIC:
                cellValue = cell.getNumericCellValue();
                break;
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            default:
                cellValue = null;
        }
    }

    workbook.close();
    return cellValue;
}
 

public Object getCellValueByEvaluatingFormula(String fileLocation, String cellLocation) throws IOException {
    Object cellValue = new Object();

    FileInputStream inputStream = new FileInputStream(new File(fileLocation));
    Workbook workbook = new XSSFWorkbook(inputStream);

    Sheet sheet = workbook.getSheetAt(0);
    FormulaEvaluator evaluator = workbook.getCreationHelper()
        .createFormulaEvaluator();

    CellAddress cellAddress = new CellAddress(cellLocation);
    Row row = sheet.getRow(cellAddress.getRow());
    Cell cell = row.getCell(cellAddress.getColumn());

    if (cell.getCellType() == CellType.FORMULA) {
        switch (evaluator.evaluateFormulaCell(cell)) {
            case BOOLEAN:
                cellValue = cell.getBooleanCellValue();
                break;
            case NUMERIC:
                cellValue = cell.getNumericCellValue();
                break;
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            default:
                cellValue = null;
        }
    }

    workbook.close();
    return cellValue;
}
 

private static StringWriter createCsv(Workbook workBook) throws IOException {

    Sheet sheet = workBook.getSheetAt(0);
    Iterator<Row> rowIterator = sheet.iterator();
    StringWriter stringWriter = new StringWriter();
    CSVWriter csvOutput = new CSVWriter(stringWriter);

    while (rowIterator.hasNext()) {
      Row row = rowIterator.next();
      int i = 0;
      String[] cellValues = new String[SHEET_CELL_SIZE];
      Iterator<Cell> cellIterator = row.cellIterator();
      while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();
        switch (cell.getCellType()) {
          case Cell.CELL_TYPE_STRING:
            cellValues[i] = cell.getStringCellValue();
            break;
          case Cell.CELL_TYPE_NUMERIC:
            cellValues[i] = String.valueOf(cell.getNumericCellValue());
            break;
          case Cell.CELL_TYPE_BLANK:
            cellValues[i] = "";
            break;
        }
        i = i + 1;
      }
      csvOutput.writeNext(cellValues);
    }
    csvOutput.close();
    return stringWriter;
  }
 

/**
 * To prepare data
 *
 * @param cell
 * @param rowIndex
 * @param columnIndex
 * @param firstRowCache
 * @return Returns the data that the cell needs to replace
 */
private String prepareData(Cell cell, int rowIndex, int columnIndex, Map<String, Set<Integer>> firstRowCache) {
    if (!CellType.STRING.equals(cell.getCellTypeEnum())) {
        return null;
    }
    String value = cell.getStringCellValue();
    if (StringUtils.isEmpty(value)) {
        return null;
    }
    StringBuilder preparedData = new StringBuilder();
    AnalysisCell analysisCell = null;

    int startIndex = 0;
    int length = value.length();
    int lastPrepareDataIndex = 0;
    out: while (startIndex < length) {
        int prefixIndex = value.indexOf(FILL_PREFIX, startIndex);
        if (prefixIndex < 0) {
            break out;
        }
        if (prefixIndex != 0) {
            char prefixPrefixChar = value.charAt(prefixIndex - 1);
            if (prefixPrefixChar == IGNORE_CHAR) {
                startIndex = prefixIndex + 1;
                continue;
            }
        }
        int suffixIndex = -1;
        while (suffixIndex == -1 && startIndex < length) {
            suffixIndex = value.indexOf(FILL_SUFFIX, startIndex + 1);
            if (suffixIndex < 0) {
                break out;
            }
            startIndex = suffixIndex + 1;
            char prefixSuffixChar = value.charAt(suffixIndex - 1);
            if (prefixSuffixChar == IGNORE_CHAR) {
                suffixIndex = -1;
            }
        }
        if (analysisCell == null) {
            analysisCell = initAnalysisCell(rowIndex, columnIndex);
        }
        String variable = value.substring(prefixIndex + 1, suffixIndex);
        if (StringUtils.isEmpty(variable)) {
            continue;
        }
        int collectPrefixIndex = variable.indexOf(COLLECTION_PREFIX);
        if (collectPrefixIndex > -1) {
            if (collectPrefixIndex != 0) {
                analysisCell.setPrefix(variable.substring(0, collectPrefixIndex));
            }
            variable = variable.substring(collectPrefixIndex + 1);
            if (StringUtils.isEmpty(variable)) {
                continue;
            }
            analysisCell.setCellType(WriteTemplateAnalysisCellTypeEnum.COLLECTION);
        }
        analysisCell.getVariableList().add(variable);
        if (lastPrepareDataIndex == prefixIndex) {
            analysisCell.getPrepareDataList().add(StringUtils.EMPTY);
        } else {
            String data = convertPrepareData(value.substring(lastPrepareDataIndex, prefixIndex));
            preparedData.append(data);
            analysisCell.getPrepareDataList().add(data);
            analysisCell.setOnlyOneVariable(Boolean.FALSE);
        }
        lastPrepareDataIndex = suffixIndex + 1;
    }
    return dealAnalysisCell(analysisCell, value, rowIndex, lastPrepareDataIndex, length, firstRowCache,
        preparedData);
}
 

/** Retrieves the proper Java type instance based on the Excel CellTypeEnum */
private Object getCellValue(Cell cell) {
  Object value;

  // Empty cells are null, instead of BLANK
  if (cell == null) {
    return null;
  }

  switch (cell.getCellTypeEnum()) {
    case STRING:
      value = cell.getStringCellValue();
      break;
    case NUMERIC:
      if (isCellDateFormatted(cell)) {
        try {
          // Excel dates are LocalDateTime, stored without timezone.
          // Interpret them as UTC to prevent ambiguous DST overlaps which happen in other
          // timezones.
          setUserTimeZone(LocaleUtil.TIMEZONE_UTC);
          Date dateCellValue = cell.getDateCellValue();
          value = formatUTCDateAsLocalDateTime(dateCellValue);
        } finally {
          resetUserTimeZone();
        }
      } else {
        value = cell.getNumericCellValue();
      }
      break;
    case BOOLEAN:
      value = cell.getBooleanCellValue();
      break;
    case FORMULA:
      value = getTypedFormulaValue(cell);
      break;
    default:
      value = null;
      break;
  }
  return value;
}