代码之家 › 专栏 › 技术社区 › The KNVB

Apache POI条件格式搜索字符串以一些文本开头

conditional-formatting apache-poi java

The KNVB · 技术社区 · 8 年前

我可以创建一个规则,搜索指定范围内等于某个文本的所有内容。

 XSSFSheet sheet1 = workbook.getSheet("sheet1");
 XSSFSheetConditionalFormatting sheet1cf = sheet1.getSheetConditionalFormatting();
 XSSFConditionalFormattingRule aRule = sheet1cf.createConditionalFormattingRule(ComparisonOperator.EQUAL,"\"a\"");
//that search value="a"

我正在使用Excel 2007。

单击“条件格式”下拉列表
选择“新规则”
在“仅格式化单元格”中,在第一个下拉框中选择“特定文本”
在我的示例中,在第三个下拉框中输入“a”

最后,为包含“a”的单元格设置背景色。

1 回复 | 直到 8 年前

Axel Richter 8 年前

所以这个问题有两个不同的答案。

XSSF Excel 根据你的描述。

然后我们需要使用 apache poi 自从不支持 ComparisonOperator BEGINS_WITH 尽管有 STConditionalFormattingOperator.BEGINS_WITH .

所以我们首先需要创建一个条件格式规则 ComparisonOperator 还有一个合适的公式。

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import org.apache.poi.ss.util.CellRangeAddress;

import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;

import java.lang.reflect.Field;

import java.io.FileOutputStream;

public class XSSFConditionalFormattingBeginsWith {

 static XSSFConditionalFormattingRule createConditionalFormattingRuleBeginsWith(
         XSSFSheetConditionalFormatting sheetCF, 
         String text) throws Exception {

  XSSFConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule(
   ComparisonOperator.EQUAL /*only dummy*/, 
   "" /*only dummy*/);

  Field _cfRule = XSSFConditionalFormattingRule.class.getDeclaredField("_cfRule");
  _cfRule.setAccessible(true); 
  CTCfRule ctCfRule  = (CTCfRule)_cfRule.get(rule); 
  ctCfRule.setType(STCfType.BEGINS_WITH);
  ctCfRule.setOperator(STConditionalFormattingOperator.BEGINS_WITH);
  ctCfRule.setText(text);
  ctCfRule.addFormula("(LEFT(INDEX($1:$1048576, ROW(), COLUMN())," + text.length() + ")=\""+ text + "\")");
  _cfRule.set(rule, ctCfRule);

  return rule;
 }

 public static void main(String[] args) throws Exception {
  XSSFWorkbook workbook = new XSSFWorkbook();

  XSSFSheet sheet = workbook.createSheet("new sheet");

  XSSFSheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
  XSSFConditionalFormattingRule rule = createConditionalFormattingRuleBeginsWith(sheetCF, "bla");

  PatternFormatting fill = rule.createPatternFormatting();
  fill.setFillBackgroundColor(IndexedColors.YELLOW.index);
  fill.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

  XSSFConditionalFormattingRule[] cfRules = new XSSFConditionalFormattingRule[]{rule};

  CellRangeAddress[] regions = new CellRangeAddress[]{CellRangeAddress.valueOf("A1:B1000")};

  sheetCF.addConditionalFormatting(regions, cfRules);

  workbook.write(new FileOutputStream("XSSFConditionalFormattingBeginsWith.xlsx"));
  workbook.close();

 }
}

第二:你想使用 阿帕奇poi HSSF 和支持。

那么我们只能使用基于公式的条件格式规则。Excel本身也通过选择 Use a formula to determine which cells to format 在您描述的过程的步骤3中。

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 ConditionalFormattingBeginsWith {

 public static void main(String[] args) throws Exception {
  Workbook workbook = new XSSFWorkbook();
  //Workbook workbook = new HSSFWorkbook();

  Sheet sheet = workbook.createSheet("new sheet");
  SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

  String text = "bla";
  int lastRow = 1000;

  ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule(
   "(LEFT(INDEX($1:$" + lastRow + ",ROW(),COLUMN())," + text.length() + ")=\"" + text + "\")");

  PatternFormatting fill = rule.createPatternFormatting();
  fill.setFillBackgroundColor(IndexedColors.YELLOW.index);
  fill.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

  ConditionalFormattingRule[] cfRules = new ConditionalFormattingRule[]{rule};

  CellRangeAddress[] regions = new CellRangeAddress[]{CellRangeAddress.valueOf("A1:B" + lastRow)};

  sheetCF.addConditionalFormatting(regions, cfRules);

  if (workbook instanceof XSSFWorkbook) {
   workbook.write(new FileOutputStream("ConditionalFormattingBeginsWith.xlsx"));
  } else if (workbook instanceof HSSFWorkbook) {
   workbook.write(new FileOutputStream("ConditionalFormattingBeginsWith.xls"));
  }
  workbook.close();

 }
}