Java HSSFCell.getStringCellValue方法代碼示例

import org.apache.poi.hssf.usermodel.HSSFCell; //導入方法依賴的package包/類
private String getCellValue(HSSFCell cell){
	if(cell == null) return "";
	
	switch (cell.getCellType()) {
	case HSSFCell.CELL_TYPE_STRING: return cell.getStringCellValue();
	case HSSFCell.CELL_TYPE_BOOLEAN : return Boolean.toString(cell.getBooleanCellValue());
	case HSSFCell.CELL_TYPE_NUMERIC : 
		if(HSSFDateUtil.isCellDateFormatted(cell))
			return DateUtils.formatDateTime("yyyyMMdd", HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
		else
			return new BigDecimal(cell.getNumericCellValue()).toPlainString();
	case HSSFCell.CELL_TYPE_FORMULA : return "";
	case HSSFCell.CELL_TYPE_BLANK : return "";
	default:return "";
	}
}
 

import org.apache.poi.hssf.usermodel.HSSFCell; //導入方法依賴的package包/類
/**
 * 獲取EXCEL文件單元列值
 * 
 * @param row
 * @param point
 * @return
 */
private static String getCellValue(HSSFRow row, int point) {
	String reString = "";
	try {
		HSSFCell cell = row.getCell((short) point);
		if (cell.getCellType() == 1)
			reString = cell.getStringCellValue();
		else if (cell.getCellType() == 0) {
			reString = convert(cell.getNumericCellValue());
			BigDecimal bd = new BigDecimal(reString);
			reString = bd.toPlainString();
		} else {
			reString = "";
		}
		System.out.println(cell.getCellType() + ":" + cell.getCellFormula());
	} catch (Exception localException) {
	}
	return checkNull(reString);
}
 

import org.apache.poi.hssf.usermodel.HSSFCell; //導入方法依賴的package包/類
private boolean parseBooleanCell(HSSFCell cell) {
if (cell != null) {
    String value;
    try {
	cell.setCellType(Cell.CELL_TYPE_STRING);
	if (cell.getStringCellValue() != null) {
	    if (cell.getStringCellValue().trim().length() != 0) {
		emptyRow = false;
	    }
	} else {
	    return false;
	}
	value = cell.getStringCellValue().trim();
    } catch (Exception e) {
	cell.setCellType(Cell.CELL_TYPE_NUMERIC);
	double d = cell.getNumericCellValue();
	emptyRow = false;
	value = new Long(new Double(d).longValue()).toString();
    }
    if (StringUtils.equals(value, "1") || StringUtils.equalsIgnoreCase(value, "true")) {
	return true;
    }
}
return false;
   }
 

import org.apache.poi.hssf.usermodel.HSSFCell; //導入方法依賴的package包/類
private String parseStringCell(HSSFCell cell) {
if (cell != null) {
    try {
	cell.setCellType(Cell.CELL_TYPE_STRING);
	if (cell.getStringCellValue() != null) {
	    if (cell.getStringCellValue().trim().length() != 0) {
		emptyRow = false;
	    }
	} else {
	    return null;
	}
	// log.debug("string cell value: '"+cell.getStringCellValue().trim()+"'");
	return cell.getStringCellValue().trim();
    } catch (Exception e) {
	cell.setCellType(Cell.CELL_TYPE_NUMERIC);
	double d = cell.getNumericCellValue();
	emptyRow = false;
	// log.debug("numeric cell value: '"+d+"'");
	return (new Long(new Double(d).longValue()).toString());
    }
}
return null;
   }
 

import org.apache.poi.hssf.usermodel.HSSFCell; //導入方法依賴的package包/類
/**
 * Constructor.
 *
 * @param xlsFileName
 *          the file name of the xls to read
 */
public WorkbookReader(final String xlsFileName) {
  super(xlsFileName);

  HSSFRow firstRow = sheet.getRow(rowNumber++);

  int columnNumber = COLUMN_DEFAULT_LANG + 1;
  HSSFCell cell = null;
  while (((cell = firstRow.getCell(columnNumber)) != null)) {
    if (!cell.getStringCellValue().trim().isEmpty()) {
      String lang = cell.getStringCellValue();
      langColumnNumber.put(lang, columnNumber);
      columnNumber++;
    }
  }

}
 

import org.apache.poi.hssf.usermodel.HSSFCell; //導入方法依賴的package包/類
/**
 * Get next row in the sheet. Read rows between second to last row.
 *
 * @return the {@link WorkbookRowDTO}.
 */
public WorkbookRowDTO getNextRow() {
  if (sheet == null) {
    throw new RuntimeException("Not opened workbook yet.");
  }

  HSSFRow row = sheet.getRow(rowNumber++);
  HSSFCell propertiesFileNameCell = row.getCell(COLUMN_PROPERTIES_FILE_NAME);
  String propertiesFileName = propertiesFileNameCell.getStringCellValue();

  HSSFCell propKeyCell = row.getCell(COLUMN_PROPERTY_KEY);
  HSSFCell defaultLangCell = row.getCell(COLUMN_DEFAULT_LANG);
  HashMap<String, String> langValues = new HashMap<String, String>();

  langColumnNumber.forEach((key, value) -> {
    HSSFCell langCell = row.getCell(value);
    String langValue = langCell == null ? "" : langCell.getStringCellValue();
    langValues.put(key, langValue);
  });

  return new WorkbookRowDTO()
      .propertiesFile(propertiesFileName)
      .propKey(propKeyCell.getStringCellValue())
      .defaultLangValue(defaultLangCell.getStringCellValue())
      .langValues(langValues);
}
 

import org.apache.poi.hssf.usermodel.HSSFCell; //導入方法依賴的package包/類
private String fromHSSFRowtoCSV(HSSFRow row){
    StringBuffer csvRow = new StringBuffer();
    int l = row.getLastCellNum();
    for (int i=0;i<l;i++){
        HSSFCell cell = row.getCell((short)i);
        String cellValue = "";
        if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
            cellValue = "";
        } else if (cell.getCellType()== HSSFCell.CELL_TYPE_STRING){
            cellValue = "\"" + cell.getStringCellValue() + "\"";
        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
            double value = cell.getNumericCellValue();
            cellValue = getNumberFormat().format(value);
            cellValue = "\"" + cellValue + "\"";
        }

        csvRow.append(cellValue);

        if (i<l){
            csvRow.append(getCsvDelimiter().toCharArray()[0]);
        }
    }
    return csvRow.toString();

}
 

import org.apache.poi.hssf.usermodel.HSSFCell; //導入方法依賴的package包/類
private String getStringValueOfCell(HSSFCell cell, int cellType) {
	
	switch (cellType) {

	case Cell.CELL_TYPE_BOOLEAN:
		return cell.getBooleanCellValue() ? "Ja" : "Nein";

	case Cell.CELL_TYPE_FORMULA:
		return getStringValueOfCell(cell, cell.getCachedFormulaResultType());

	case Cell.CELL_TYPE_NUMERIC:
		double number = cell.getNumericCellValue();
		if(Double.isInfinite(number) || Double.isNaN(number) || Math.floor(number) != number)
			return Double.toString(number);
		else
			return String.valueOf(new Double(number).intValue());

	case Cell.CELL_TYPE_STRING:
		return cell.getStringCellValue();
		
	}
	AtomTools.log(Level.SEVERE, "unknown celltype: " + cellType + "; content of cell = " + cell.toString(), this);
	return null;
	
}
 

import org.apache.poi.hssf.usermodel.HSSFCell; //導入方法依賴的package包/類
private Integer manageInteger(PreparedStatement ps, PreparedStatement psUpdate, int lfdCol, HSSFCell cell) throws SQLException {
	Integer result = null;
	if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
	} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
		if (cell.getStringCellValue().trim().length() > 0) {
			result = new Integer(cell.getStringCellValue());
			if (ps != null) ps.setInt(lfdCol, result);
			if (psUpdate != null) psUpdate.setInt(lfdCol, result);
			return result;
		}
	} else {
		result = new Integer((int) cell.getNumericCellValue());
		if (ps != null) ps.setInt(lfdCol, result);
		if (psUpdate != null) psUpdate.setInt(lfdCol, result);
		return result;
	}
	if (ps != null) ps.setNull(lfdCol, java.sql.Types.INTEGER);
	if (psUpdate != null) psUpdate.setNull(lfdCol, java.sql.Types.INTEGER);
	return result;
}
 

import org.apache.poi.hssf.usermodel.HSSFCell; //導入方法依賴的package包/類
private Long manageBigInteger(PreparedStatement ps, PreparedStatement psUpdate, int lfdCol, HSSFCell cell) throws SQLException {
	Long result = null;
	if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
	} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
		if (cell.getStringCellValue().trim().length() > 0) {
			result = new Long(cell.getStringCellValue());
			if (ps != null) ps.setLong(lfdCol, result);
			if (psUpdate != null) psUpdate.setLong(lfdCol, result);
			return result;
		}
	} else {
		result = new Long((long) cell.getNumericCellValue());
		if (ps != null) ps.setLong(lfdCol, result);
		if (psUpdate != null) psUpdate.setLong(lfdCol, result);
		return result;
	}
	if (ps != null) ps.setNull(lfdCol, java.sql.Types.BIGINT);
	if (psUpdate != null) psUpdate.setNull(lfdCol, java.sql.Types.BIGINT);
	return result;
}
 

import org.apache.poi.hssf.usermodel.HSSFCell; //導入方法依賴的package包/類
private String parseStringCell(HSSFCell cell) {
if (cell != null) {
    cell.setCellType(Cell.CELL_TYPE_STRING);
    if (cell.getStringCellValue() != null) {
	return cell.getStringCellValue().trim();
    }
}
return null;
   }
 

import org.apache.poi.hssf.usermodel.HSSFCell; //導入方法依賴的package包/類
public static String getCellStringValue(HSSFCell cell) {
	String cellValue = "";
	switch (cell.getCellType()) {
	case HSSFCell.CELL_TYPE_STRING:
		cellValue = cell.getStringCellValue();
		if (cellValue.trim().equals("") || cellValue.trim().length() <= 0) {
			cellValue = " ";
		}
		break;
	case HSSFCell.CELL_TYPE_NUMERIC:
		// cellValue = String.valueOf(cell.getNumericCellValue());
		DecimalFormat formatter = new DecimalFormat("######");
		cellValue = formatter.format(cell.getNumericCellValue());
		break;
	case HSSFCell.CELL_TYPE_FORMULA:
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cellValue = String.valueOf(cell.getNumericCellValue());
		break;
	case HSSFCell.CELL_TYPE_BLANK:
		cellValue = " ";
		break;
	case HSSFCell.CELL_TYPE_BOOLEAN:
		break;
	case HSSFCell.CELL_TYPE_ERROR:
		break;
	default:
		break;
	}
	return cellValue;
}
 

import org.apache.poi.hssf.usermodel.HSSFCell; //導入方法依賴的package包/類
private String getStringVal(HSSFCell cell){
	String value="";
	if(cell != null) {
		switch (cell.getCellTypeEnum()) {

			case FORMULA:
				value =  cell.getCellFormula();
				break;

			case NUMERIC:
				value = String.valueOf(new DecimalFormat("#").format(cell.getNumericCellValue()));
				break;

			case STRING:
				value = cell.getStringCellValue();
				break;

			case BLANK:
				value ="";
				break;

			case BOOLEAN:
				value =	String.valueOf(cell.getBooleanCellValue());
				break;

			case ERROR:
				value =	String.valueOf(cell.getErrorCellValue());
				break;

			default:
				value =	String.valueOf(cell.getCellTypeEnum());
		}
		
	}
	return value;
}
 

import org.apache.poi.hssf.usermodel.HSSFCell; //導入方法依賴的package包/類
public String get(int sheetIndex, int x_index, int y_index) {
    String str = "";
    try {
        if (wb == null) throw new Exception("未打開文件");
        HSSFCell cell = wb.getSheetAt(sheetIndex).getRow(x_index).getCell(y_index);
        cell.setCellType(Cell.CELL_TYPE_STRING);//處理讀取xls時 單元格使用各類函數的數據讀取問題
        str = cell.getStringCellValue();
    } catch (Exception e) {
        logger.error(e.getMessage());
    }
    return str;
}
 

import org.apache.poi.hssf.usermodel.HSSFCell; //導入方法依賴的package包/類
public void openFile(File f) throws ReadException {
	trace.trace("Reading "+ f.getAbsolutePath());
	//If the file is existing, open and read it
	try {
		FileInputStream fin = new FileInputStream(f);
		POIFSFileSystem poifs = new POIFSFileSystem(fin);
		wb = new HSSFWorkbook(poifs);
	} catch (IOException e) {
		throw new ReadException("Error while trying to read file "+f.getAbsolutePath(), e);
	}
	currentSheet = wb.getSheet(sheetName);
	//If we do not have a sheet with the given name, throw exception.
	if (currentSheet == null)
		throw new ReadException("No sheet with name "+sheetName+" found in file "+f.getAbsolutePath(), null);
	nextRowNumber = currentSheet.getFirstRowNum();
	
	//If we have a aheader row, read it to get the actual schema
	if (hasHeader){
		if (useHeaderNames){
			HSSFRow row = currentSheet.getRow(nextRowNumber);
			//Last cell num is zero based => +1
			String[] fields = new String[row.getLastCellNum()];
			trace.trace("Number of fields:"+fields.length);
			Iterator<Cell> iter = row.cellIterator();
			while (iter.hasNext()) {
				HSSFCell element = (HSSFCell) iter.next();
				String value = element.getStringCellValue();
				fields[element.getCellNum()]=value;
			}
			//We might have some nulls in the array. Default them.
			for (int i = 0; i < fields.length; i++) {
				String string = fields[i];
				if (string==null)
					fields[i]="_Unknown_"+i+"_";
			}
			schema = fields;
			nextRowNumber++;
		}
	}
}