有 Java 编程相关的问题?

你可以在下面搜索框中键入要查询的问题!

java Apache poi条件格式无法正常工作

我正在使用ApachePOI在我的java应用程序中创建excels

我的用例是A1中的值为Change it

细胞中的花柱将发生变化

为此,我将遵循poi提供的条件格式特性

但这种风格只适用于cellA10&;直到A14才开始

我错过了什么

代码:

private void addValidations(Sheet sheet) {
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("=A1=\"Change it\"");
    FontFormatting fontFmt = rule1.createFontFormatting();
    fontFmt.setFontStyle(true, false);
    fontFmt.setFontColorIndex(IndexedColors.YELLOW.index);
    BorderFormatting bordFmt = rule1.createBorderFormatting();
    bordFmt.setBorderBottom(BorderStyle.THIN);
    bordFmt.setBorderTop(BorderStyle.THICK);
    bordFmt.setBorderLeft(BorderStyle.DASHED);
    bordFmt.setBorderRight(BorderStyle.DOTTED);
    ConditionalFormattingRule [] cfRules =
            {
                    rule1
            };
    CellRangeAddress[] regions = {
            CellRangeAddress.valueOf("A10:A14")
    };
    sheetCF.addConditionalFormatting(regions, cfRules);
}

共 (2) 个答案

  1. # 1 楼答案

    如果有用的话,试试这个

    CellRangeAddress[] regions = new CellRangeAddress[]{ CellRangeAddress.valueOf("A10:A14") };
    
  2. # 2 楼答案

    代码sheetCF.createConditionalFormattingRule("=A1=\"Change it\"");根本无法工作。在HSSF中,它抛出org.apache.poi.ss.formula.FormulaParseException: The specified formula '=A1="Change it"' starts with an equals sign which is not allowed.。在XSSF中,它创建了一个损坏的*.xlsx文件。前导等号不存储在Excel公式单元格中。t仅显示在ExcelGUI中。所以它一定是sheetCF.createConditionalFormattingRule("A1=\"Change it\"");

    公式=A1="Change it"在列字母和行号上都是相对的。所以应用于细胞A10,它的意思是=A1="Change it"。但适用于细胞A11,它的意思是=A2="Change it"。适用于A12细胞,意思是=A3="Change it"等等。因此,如果需要更改A10:A14中的所有字体颜色,如果A1的内容更改,那么公式中的引用必须使用$进行修复。所以一定是sheetCF.createConditionalFormattingRule("A$1=\"Change it\"");

    完整示例:

    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    
    import org.apache.poi.ss.util.CellRangeAddress;
    
    import java.io.FileOutputStream;
    
    public class ConditionalFormatting {
    
     public static void main(String[] args) throws Exception {
      Workbook workbook = new XSSFWorkbook(); String filePath ="./ConditionalFormatting.xlsx";
      //Workbook workbook = new HSSFWorkbook(); String filePath ="./ConditionalFormatting.xls";
    
      Sheet sheet = workbook.createSheet();
    
      for (int r = 9; r < 14; r++) {
       sheet.createRow(r).createCell(0).setCellValue("Text in Cell A" + (r+1));
      }
    
      SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    
      ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule("A$1=\"Change it\"");
      FontFormatting fontFormatting = rule.createFontFormatting();
      fontFormatting.setFontStyle(false, true);
      fontFormatting.setFontColorIndex(IndexedColors.YELLOW.index);
    
      ConditionalFormattingRule[] cfRules = new ConditionalFormattingRule[]{rule};
    
      CellRangeAddress[] regions = new CellRangeAddress[]{CellRangeAddress.valueOf("A10:A14")};
    
      sheetCF.addConditionalFormatting(regions, cfRules);
    
      FileOutputStream out = new FileOutputStream(filePath);
      workbook.write(out);
      out.close();
      workbook.close();
    
     }
    }
    

    如果A1中的单元格内容为Change it,则会将A10:A14中的字体颜色更改为黄色