Java -条件格式-将单元格设置为数字

Question

是否有任何方法将字符串转换为数字?我一直在尝试使用apache poi的条件格式规则，但我不能这样做，因为excel文件有数字，但作为字符串和规则不起作用。下面是代码:

公共类ConditionalFormatting {

public static void main(String[] args) throws IOException {
String excelFilePath = "Excel.xlsx";
FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

Workbook workbook = new XSSFWorkbook(inputStream);
Sheet firstSheet = workbook.getSheetAt(0);
Iterator<Row> iterator = firstSheet.iterator();

while (iterator.hasNext()) {
Row nextRow = iterator.next();
Iterator<Cell> cellIterator = nextRow.cellIterator();
firstSheet.setColumnHidden(4, true);

while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();

switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue());
break;
}

System.out.print(" - ");
}
System.out.println();
}

/* Access conditional formatting facet layer */
SheetConditionalFormatting my_cond_format_layer = firstSheet.getSheetConditionalFormatting();

/* Rule */
ConditionalFormattingRule my_rule = my_cond_format_layer.createConditionalFormattingRule(ComparisonOperator.EQUAL, "5.00");

/* RED color */
FontFormatting my_rule_pattern = my_rule.createFontFormatting();
my_rule_pattern.setFontColorIndex(IndexedColors.RED.getIndex());

/* background Yellow */
PatternFormatting fill_pattern = my_rule.createPatternFormatting();
fill_pattern.setFillBackgroundColor(IndexedColors.YELLOW.index);

/* Cell Range Address */
CellRangeAddress[] my_data_range = {CellRangeAddress.valueOf("F1:F15")};

/* Attach rule to cell range */
my_cond_format_layer.addConditionalFormatting(my_data_range,my_rule);

inputStream.close();
FileOutputStream fileOut = new FileOutputStream("file.xlsx");
workbook.write(fileOut);
fileOut.close();
System.out.println("Successfully Created workbook");
}

}

Answer 1

" excel文件有数字，但作为字符串":这应该避免而不是解决这个问题。在Excel中以字符串形式存储数字将导致使用电子表格计算时出现多种问题，而不仅仅是使用条件格式。

解决方法是

ConditionalFormattingRule my_rule = my_cond_format_layer.createConditionalFormattingRule("VALUE(F1)=5.00");

使用公式条件格式规则，而不是直接比较单元格是否相等。该公式使用Excel的VALUE函数始终从单元格中获取数值。