Can Apache POI apply Top 10 conditional formatting?

Question

I have already created the excel files already has such as createConditionalFormattingRule With defrent kind like if the cell value EQUAL to another one :

XSSFConditionalFormattingRule my_rule1 = ContedFormat.createConditionalFormattingRule(ComparisonOperator.EQUAL, "$M$" + (CountRower + 1));

But my question is that there is a conditional format Used to select like top 10 value on the renege.

can Apache POI Create this kind ?

Edit : I found something like :

CTConditionalFormatting TopScall = 
sheet.getCTWorksheet().addNewConditionalFormatting();
TopScall.setSqref(my_range);

CTCfRule myCFRule = TopScall.addNewCfRule();
myCFRule.setType(STCfType.TOP_10);
myCFRule.setPriority(1);

And i tryed it dose add the Formily roll but with no formatting and value is 0

This is Example about Conditional Formatting Color Scale What I want to do is something similar but instead of Color Scale i need Top 10

Answer 1

In current Apache poi 5.0.0 SheetConditionalFormatting does not have a method to create a ConditionalFormattingRule for top 10. But it has SheetConditionalFormatting.createConditionalFormattingColorScaleRule(). So your linked excample which uses underlying org.openxmlformats.schemas.spreadsheetml.x2006.main.* classes for creating color scale rule is outdated.

But top 10 rule settings are more complex than color scale rule settings. For color scale rule all settings are in sheet's CTConditionalFormatting. For top 10 rule a fill pattern formatting needs to be used. That pattern formatting links to the style part of the workbook.

So best way would be creating a XSSFConditionalFormattingRule for top 10 which sets type STCfType.TOP_10 and rank. This ConditionalFormattingRule already provides a method to create pattern formatting.

Unfortunately constructor of XSSFConditionalFormattingRule is not public as well as the method to get CTCfRule. So reflection needs to be used.

Following complete example provides XSSFConditionalFormattingRule createConditionalFormattingRuleTop10(XSSFSheetConditionalFormatting sheetCF, int rank) to create a XSSFConditionalFormattingRule for top 10 given a special rank. All other stuff is like the default stuff for creating conditional formatting as described in Busy Developers' Guide to HSSF and XSSF Features - Conditional Formatting.

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.lang.reflect.Constructor;

import java.io.FileOutputStream;

public class CreateXSSFConditionalFormattingTop10 {

 static XSSFConditionalFormattingRule createConditionalFormattingRuleTop10(XSSFSheetConditionalFormatting sheetCF, int rank) throws Exception {
  Field _sheet = XSSFSheetConditionalFormatting.class.getDeclaredField("_sheet");
  _sheet.setAccessible(true);
  XSSFSheet sheet = (XSSFSheet)_sheet.get(sheetCF);
  Constructor constructor = XSSFConditionalFormattingRule.class.getDeclaredConstructor(XSSFSheet.class);
  constructor.setAccessible(true);
  XSSFConditionalFormattingRule rule = (XSSFConditionalFormattingRule)constructor.newInstance(sheet);
  Field _cfRule = XSSFConditionalFormattingRule.class.getDeclaredField("_cfRule");
  _cfRule.setAccessible(true);
  CTCfRule cfRule = (CTCfRule)_cfRule.get(rule);
  cfRule.setType(STCfType.TOP_10);
  cfRule.setRank(rank);
  return rule;
 }

 public static void main(String[] args) throws Exception {
  Workbook workbook = new XSSFWorkbook(); String filePath = "./CreateXSSFConditionalFormattingTop10.xlsx";

  Sheet sheet = workbook.createSheet();

  java.util.Random random = new java.util.Random();
  for (int r = 0; r < 100; r++) {
   sheet.createRow(r).createCell(0).setCellValue(random.nextInt(100)+r);
  }

  SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
  if (sheetCF instanceof XSSFSheetConditionalFormatting) {
   XSSFConditionalFormattingRule rule = createConditionalFormattingRuleTop10((XSSFSheetConditionalFormatting)sheetCF, 10);
   XSSFPatternFormatting fill = rule.createPatternFormatting();
   fill.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index);
   fill.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

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

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

   sheetCF.addConditionalFormatting(regions, cfRules);
  }

  FileOutputStream out = new FileOutputStream(filePath);
  workbook.write(out);
  out.close();
  workbook.close();

 }
}