Java - Conditional Formatting - Set cell as numeric-CodePudding

Is there any way of converting a string to numeric? I've been trying to use a rule of Conditional Formatting with apache poi but i can't do it because the excel file has numbers but as strings and the rule doesn't work. Here's the code:

public class 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");
}

}

CodePudding user response：

"the excel file has numbers but as strings": This should be avoided rather than working around this problem. Storing numbers in Excel as string will lead to multiple problems using the spreadsheet calculation, not only using conditional formatting.

The workaround would be

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

This uses a formula conditional formatting rule instead of the comparison the cell to equal directly. The formula uses Excel's VALUE function to always get numeric values from cells.