Apache POI Java：为Excel模板设置跨单元格范围的条件格式

Got it, let's fix this conditional formatting scenario for your Excel template. The core issue here is shifting the focus: instead of formatting the 3rd column when it's filled, we need to apply the rule to the 7th-12th columns and use a formula to check if the corresponding 3rd column cell has content.

Here's a complete working code snippet that does exactly what you need:

// Get the conditional formatting object for your sheet
XSSFSheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

// 1. Create a formula-based rule: check if column C (3rd column) of the same row is not blank
// The $C locks the column, while the row number (1) stays relative for each row
ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule("NOT(ISBLANK($C1))");

// 2. Define the formatting style (highlight with yellow background as example)
PatternFormatting fill = rule.createPatternFormatting();
fill.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
fill.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

// 3. Specify the range to apply the rule: columns 7-12 (G to L), adjust rows as needed
// Example: applies to rows 1 to 100 in G-L columns
CellRangeAddress[] targetRegions = {
    CellRangeAddress.valueOf("G1:L100")
};

// 4. Add the rule to the target regions
sheetCF.addConditionalFormatting(targetRegions, rule);

Key Explanations: #

• Formula Logic: NOT(ISBLANK($C1)) checks if the cell in column C of the current row isn't empty. The $ before C ensures we always reference column C, even when the rule is applied to other columns.
• Target Range: We apply the rule directly to columns 7-12 (G to L) instead of column C. This means whenever column C in a row has content, all cells in G-L of that row will highlight.
• Style Customization: You can tweak the formatting (like font color, border) by modifying the PatternFormatting or adding other format types (e.g., FontFormatting).

Why Your Original Code Didn't Work: #

Chances are your initial code was applying the rule to column C itself, which only formats the cell that meets the condition. By flipping the target range and using a relative-row formula, we can trigger formatting on the required columns based on column C's value.

内容的提问来源于stack exchange，提问作者nshweta