Java Code Examples for org.apache.poi.hssf.usermodel.HSSFCell#getStringCellValue()

private boolean parseBooleanCell(HSSFCell cell) {
if (cell != null) {
    String value;
    try {
	cell.setCellType(CellType.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(CellType.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;
   }
 

private String getCellValue(HSSFCell cell) {
    String result = null;
    if (cell != null) {
        switch (cell.getCellType()) {

        case HSSFCell.CELL_TYPE_FORMULA:
            result = cell.getCellFormula().toString();
            break;

        case HSSFCell.CELL_TYPE_NUMERIC:
            result = "" + cell.getNumericCellValue();
            break;

        case HSSFCell.CELL_TYPE_STRING:
            result = cell.getStringCellValue();
            break;

        default:
        }
    }
    return result;
}
 

@Override
public void apply() {
    for (DataObject dataObject : data) {
        HSSFCell resultCell = dataObject.resultCell;
        HSSFCell templateCell = dataObject.templateCell;

        String templateCellValue = templateCell.getStringCellValue();

        Matcher matcher = pattern.matcher(templateCellValue);
        if (matcher.find()) {
            String paramName = matcher.group(1);
            Integer width = (Integer) dataObject.bandData.getParameterValue(paramName);
            if (width != null) {
                resultCell.getSheet().setColumnWidth(resultCell.getColumnIndex(), width);
            }
        }
    }
}
 

private String parseStringCell(HSSFCell cell) {
if (cell != null) {
    cell.setCellType(CellType.STRING);
    if (cell.getStringCellValue() != null) {
	return cell.getStringCellValue().trim();
    }
}
return null;
   }
 

private String parseStringCell(HSSFCell cell) {
if (cell != null) {
    try {
	cell.setCellType(CellType.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(CellType.NUMERIC);
	double d = cell.getNumericCellValue();
	emptyRow = false;
	// log.debug("numeric cell value: '"+d+"'");
	return (new Long(new Double(d).longValue()).toString());
    }
}
return null;
   }
 

private String parseCell(HSSFCell cell) {
	String valueField = null;
	
	if(cell == null) return "";
	
	switch (cell.getCellType()) {
	case HSSFCell.CELL_TYPE_FORMULA:
		valueField = cell.getCellFormula().toString();
		break;
	
	case HSSFCell.CELL_TYPE_NUMERIC:
		Double numericValue = cell.getNumericCellValue();
		//testing if the double is an integer value
		if ((numericValue == Math.floor(numericValue)) && !Double.isInfinite(numericValue)) {
		    //the number is an integer, this will remove the .0 trailing zeros
			int numericInt = numericValue.intValue();
			valueField = String.valueOf(numericInt);
		} else {
			valueField = String.valueOf(cell.getNumericCellValue());

		}
		break;
	
	case HSSFCell.CELL_TYPE_STRING:
		valueField = cell.getStringCellValue();
		break;
	
	default:
}
	
	return valueField;
}
 

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;
}
 

/**
 * 替换Excel模板文件内容
 *
 * @param datas
 *            文档数据
 * @param sourceFilePath
 *            Excel模板文件路径
 * @param targetFilePath
 *            Excel生成文件路径
 */
public static boolean replaceModel(List<ExcelReplaceDataVO> datas,
		String sourceFilePath, String targetFilePath) {
	boolean bool = true;
	try {
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
				sourceFilePath));
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFSheet sheet = wb.getSheetAt(0);
		for (ExcelReplaceDataVO data : datas) {
			// 获取单元格内容
			HSSFRow row = sheet.getRow(data.getRow());
			HSSFCell cell = row.getCell((short) data.getColumn());
			String str = cell.getStringCellValue();
			// 替换单元格内容
			str = str.replace(data.getKey(), data.getValue());
			// 写入单元格内容
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(str);
		}
		// 输出文件
		FileOutputStream fileOut = new FileOutputStream(targetFilePath);
		wb.write(fileOut);
		fileOut.close();
	} catch (Exception e) {
		bool = false;
		e.printStackTrace();
	}
	return bool;
}
 

/**
 * Dummerweise ist im DATEV-Export die Spalte SH zweimal vertreten. Da wir SH aber für Haben/Soll auswerten müssen, müssen die Spalten
 * unterschiedlich heißen. Die zweite Spalte wird hier in SH2 umbenannt, sofern vorhanden.
 * @param sheet
 */
private void rename2ndSH(final HSSFSheet sheet)
{
  try {
    final HSSFRow row = sheet.getRow(ROW_COLUMNNAMES);
    if (row == null) {
      return;
    }
    short numberOfSH = 0;
    for (int col = 0; col < MAX_COLUMNS; col++) {
      final HSSFCell cell = row.getCell(col);
      if (cell == null) {
        break;
      }
      final String name = cell.getStringCellValue();
      log.debug("Processing column '" + name + "'");
      if ("SH".equals(cell.getStringCellValue()) == true) {
        numberOfSH++;
        if (numberOfSH == 2) {
          log.debug("Renaming 2nd column 'SH' to 'SH2' (column no. " + col + ").");
          cell.setCellValue("SH2");
        }
      }
    }
  } catch (final Exception ex) {
    log.error(ex.getMessage(), ex);
    throw new UserException(PFUserContext.getLocalizedString("finance.datev.import.error.titleRowMissed"));
  }
}
 

private static String cellToString(HSSFCell 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();
}
 

@Override
   @SuppressWarnings("unchecked")
   public int importScales(MultipartFile fileItem) throws IOException {
int counter = 0;
POIFSFileSystem fs = new POIFSFileSystem(fileItem.getInputStream());
try (HSSFWorkbook wb = new HSSFWorkbook(fs)) {
    HSSFSheet sheet = wb.getSheetAt(0);
    int startRow = sheet.getFirstRowNum();
    int endRow = sheet.getLastRowNum();
    User user = null;

    // make import work with files with header ("exported on") or without (pure data)
    HSSFRow row = sheet.getRow(startRow);
    HSSFCell cell = row.getCell(0);
    String header = cell.getStringCellValue();
    startRow += "name".equalsIgnoreCase(header) ? 1 : 5;

    for (int i = startRow; i < (endRow + 1); i++) {
	row = sheet.getRow(i);
	cell = row.getCell(1);
	String code = cell.getStringCellValue();
	List<OutcomeScale> foundScales = outcomeDAO.findByProperty(OutcomeScale.class, "code", code);
	if (!foundScales.isEmpty()) {
	    if (log.isDebugEnabled()) {
		log.debug("Skipping an outcome scale with existing code: " + code);
	    }
	    continue;
	}
	cell = row.getCell(0);
	String name = cell.getStringCellValue();
	cell = row.getCell(2);
	String description = cell == null ? null : cell.getStringCellValue();
	cell = row.getCell(3);
	String itemsString = cell.getStringCellValue();

	OutcomeScale scale = new OutcomeScale();
	scale.setName(name);
	scale.setCode(code);
	scale.setDescription(description);
	if (user == null) {
	    UserDTO userDTO = OutcomeService.getUserDTO();
	    user = (User) outcomeDAO.find(User.class, userDTO.getUserID());
	}
	scale.setCreateBy(user);
	scale.setCreateDateTime(new Date());
	outcomeDAO.insert(scale);

	List<String> items = OutcomeScale.parseItems(itemsString);
	int value = 0;
	for (String itemString : items) {
	    OutcomeScaleItem item = new OutcomeScaleItem();
	    item.setName(itemString);
	    item.setValue(value++);
	    item.setScale(scale);
	    outcomeDAO.insert(item);
	}

	counter++;
    }
}
return counter;
   }
 

@Override
   @SuppressWarnings("unchecked")
   public int importOutcomes(MultipartFile fileItem) throws IOException {
int counter = 0;
POIFSFileSystem fs = new POIFSFileSystem(fileItem.getInputStream());
try (HSSFWorkbook wb = new HSSFWorkbook(fs)) {
    HSSFSheet sheet = wb.getSheetAt(0);
    int startRow = sheet.getFirstRowNum();
    int endRow = sheet.getLastRowNum();
    User user = null;

    // make import work with files with header ("exported on") or without (pure data)
    HSSFRow row = sheet.getRow(startRow);
    HSSFCell cell = row.getCell(0);
    String header = cell.getStringCellValue();
    startRow += "name".equalsIgnoreCase(header) ? 1 : 5;

    for (int i = startRow; i < (endRow + 1); i++) {
	row = sheet.getRow(i);
	cell = row.getCell(1);
	String code = cell.getStringCellValue();
	List<Outcome> foundOutcomes = outcomeDAO.findByProperty(Outcome.class, "code", code);
	if (!foundOutcomes.isEmpty()) {
	    if (log.isDebugEnabled()) {
		log.debug("Skipping an outcome with existing code: " + code);
	    }
	    continue;
	}
	cell = row.getCell(3);
	String scaleCode = cell.getStringCellValue();
	List<OutcomeScale> foundScales = outcomeDAO.findByProperty(OutcomeScale.class, "code", scaleCode);
	OutcomeScale scale = foundScales.isEmpty() ? null : foundScales.get(0);
	if (scale == null) {
	    if (log.isDebugEnabled()) {
		log.debug("Skipping an outcome with missing scale with code: " + scaleCode);
	    }
	    continue;
	}
	cell = row.getCell(0);
	String name = cell.getStringCellValue();
	cell = row.getCell(2);
	String description = cell == null ? null : cell.getStringCellValue();

	Outcome outcome = new Outcome();
	outcome.setName(name);
	outcome.setCode(code);
	outcome.setDescription(description);
	outcome.setScale(scale);
	if (user == null) {
	    UserDTO userDTO = OutcomeService.getUserDTO();
	    user = (User) outcomeDAO.find(User.class, userDTO.getUserID());
	}
	outcome.setCreateBy(user);
	outcome.setCreateDateTime(new Date());
	outcomeDAO.insert(outcome);

	counter++;
    }
}
return counter;
   }