Conditional Formatting — cell value rules, color scales, data bars, top N

# conditional-format.py — same verification as the Node.js sample (pyosbxl)
import pyosbxl

e = pyosbxl.enums


def color(hex):  # ColorObject takes a 6-digit hex without "#"
    c = pyosbxl.dto.ColorObject()
    c.setHexColor(hex)
    return c


def num(v):  # print numbers like JS does (60.0 -> "60")
    return str(int(v)) if float(v) == int(v) else str(v)


def bol(v):  # print booleans like JS does (True -> "true")
    return str(bool(v)).lower()


app = pyosbxl.App()
wb = app.createWorkBook("condfmt-py.xlsx")  # output text says condfmt.xlsx to match the Node.js sample
ws = wb.openWorkSheet("Sheet1")

# ---------- data table ----------
# col B: cell value rule / col C: color scale / col D: data bar / col E: top 3 + above average
for i, h in enumerate(["Month", "Cell value rule", "Color scale", "Data bar", "Top/Average"]):
    col = "ABCDE"[i]
    ws.getRange(f"{col}1:{col}1").setValue(h)
sales = [45, 78, 23, 91, 56, 34, 87, 12, 65, 49]
for i, v in enumerate(sales):
    ws.getRange(f"A{i + 2}:A{i + 2}").setNumberValue(i + 1)
    for col in ["B", "C", "D", "E"]:
        ws.getRange(f"{col}{i + 2}:{col}{i + 2}").setNumberValue(v)

fc = ws.getFormatConditions()

# ---------- (1) cell value rule: greater than 60 -> dark red bold + light red fill ----------
cellVal = pyosbxl.dto.CellValueObject()
cellVal.setValueCondition(60, e.XlFormatConditionOperator.FormatConditionGreater)
cellVal.setA1C1("B2:B11")
redFont = pyosbxl.dto.FontObject()
redFont.setBold(True)
redFont.setColorObject(color("9C0006"))
cellVal.setFont(redFont)
pinkFill = pyosbxl.dto.PatternFillObject()
pinkFill.setPattern(e.XlPatternType.PatternSolid)
pinkFill.setCellColor(color("FFC7CE"))
cellVal.setPatternFill(pinkFill)
fc.setCellValueCondition(1, cellVal)

# ---------- (2) color scale: 3-color gradient on column C ----------
colorScale = pyosbxl.dto.ColorScaleObject()
colorScale.setA1C1("C2:C11")
colorScale.setMinimumCondition(color("F8696B"), e.XlConditionValueType.ConditionValueTypeMinimum)
colorScale.setMediumCondition(color("FFEB84"), e.XlConditionValueType.ConditionValueTypePercentile, 50)
colorScale.setMaximumCondition(color("63BE7B"), e.XlConditionValueType.ConditionValueTypeMaximum)
fc.setColorScaleFormatCondition(2, colorScale)

# ---------- (3) data bar: column D ----------
dataBar = pyosbxl.dto.DataBarObject()
dataBar.setA1C1("D2:D11")
dataBar.setDataBarColor(color("638EC6"))
dataBar.setMinimumCondition(e.XlConditionValueType.ConditionValueTypeMinimum)
dataBar.setMaximumCondition(e.XlConditionValueType.ConditionValueTypeMaximum)
fc.setDataBarFormatCondition(3, dataBar)

# ---------- (4) top 3 + above average: column E ----------
top3 = pyosbxl.dto.Top10Object()
top3.setRank(3, True)                        # True = top (False = bottom)
top3.setA1C1("E2:E11")
blueFont = pyosbxl.dto.FontObject()
blueFont.setBold(True)
blueFont.setColorObject(color("0070C0"))
top3.setFont(blueFont)
fc.setTop10FormatCondition(4, top3)

aboveAvg = pyosbxl.dto.AboveAverageObject()
aboveAvg.setAboveBelow(e.XlAboveBelow.XlAboveAverage)
aboveAvg.setA1C1("E2:E11")
greenFont = pyosbxl.dto.FontObject()
greenFont.setBold(True)
greenFont.setColorObject(color("006100"))
aboveAvg.setFont(greenFont)
fc.setAboveAverageCondition(5, aboveAvg)

fc.apply()                                   # apply() is required after setting all conditions
print("[1] Registered 5 rules and called apply()")

# ---------- read back the rule list ----------
types = fc.getConditionTypes()
print(f"[2] rule count = {len(types)}")
for prio in sorted(types):
    for rng, t in types[prio].items():
        print(f"    priority {prio}: {rng} → {t.name} (type={int(t)})")

# ---------- read back each rule's details ----------
print("[3] Reading back rule details")
g1 = fc.getCellValueCondition(1)
print(f"    cell value : {g1.getA1C1()} value={num(g1.getConditionValue())}"
      f" operator={int(g1.getConditionOperetor())}"
      f" (Greater={int(e.XlFormatConditionOperator.FormatConditionGreater)})"
      f" fontColor={g1.getFont().getColorObject().getHexColor()} bold={bol(g1.getFont().isBold())}"
      f" fill={g1.getPatternFill().getCellColor().getHexColor()}")
g2 = fc.getColorScaleFormatCondition(2)
print(f"    color scale: min={g2.getMinimumColor().getHexColor()} (type={int(g2.getMinimumValueType())})"
      f" mid={g2.getMediumColor().getHexColor()} (type={int(g2.getMediumValueType())}, threshold={num(g2.getMediumValue())})"
      f" max={g2.getMaximumColor().getHexColor()} (type={int(g2.getMaximumValueType())})")
g3 = fc.getDataBarFormatCondition(3)
print(f"    data bar: color={g3.getDataBarColor().getHexColor()}"
      f" minType={int(g3.getMinimumCondition())} maxType={int(g3.getMaximumCondition())}")
g4 = fc.getTop10FormatCondition(4)
print(f"    top N: rank={g4.getRank()} isTop={bol(g4.getTopBottom())}")
g5 = fc.getAboveAverageCondition(5)
print(f"    above average: aboveBelow={int(g5.getAboveBelow())}"
      f" (XlAboveAverage={int(e.XlAboveBelow.XlAboveAverage)})")

# ---------- cells that met the conditions and got formatted (available after apply()) ----------
byRow = lambda a: int(a[1:])
print("[4] Cells that received formatting")
appliedB = sorted(fc.getAppliedFontObject("B2:B11").keys(), key=byRow)
print(f"    col B (>60)     : {' '.join(appliedB)}")
appliedE = sorted(fc.getAppliedFontObject("E2:E11").keys(), key=byRow)
print(f"    col E (top/avg) : {' '.join(appliedE)}")
appliedBar = fc.getAppliedDataBar("D2:D11")
print(f"    col D data bars : {sum(1 for v in appliedBar.values() if v)} cells")

# ---------- removal ----------
fc.removeCondition(4)                        # remove only the top-3 rule
print(f"[5] rule count after removeCondition(4) = {len(fc.getConditionTypes())}")
fc.removeAllCondition()                      # remove all rules
print(f"[6] rule count after removeAllCondition() = {len(fc.getConditionTypes())}")

# re-register the cell value rule and save (so the effect is visible when the file is opened)
fc.setCellValueCondition(1, cellVal)
fc.apply()

wb.save()
wb.close()
print("[done] saved condfmt.xlsx (cell-value rule re-registered)")

// conditional-format.java — checks how much of the Node.js sample javaosbxl supports
//
// [API gap] In javaosbxl 1.5.0, the condition-object wrappers (CellValueObjectWrapper etc.)
// do not expose the base-class methods setA1C1() / setFont() / setPatternFill().
// With no way to set the apply range (A1C1), setCellValueCondition() throws
// "apply range(A1C1) must be set", so conditional format rules cannot be registered.
// This block demonstrates that registering the same rule as the Node.js version throws,
// while apply() / getConditionTypes() / removeCondition() / removeAllCondition() can all
// be called without exceptions, making the gap explicit.
import com.osboffice.osbxl.*;
import com.osboffice.osbxl.dto.*;
import com.osboffice.osbxl.enums.*;
import java.util.Map;

public class Main {
    public static void main(String[] args) {
        AppWrapper app = new AppWrapper();
        WorkBookWrapper wb = app.createWorkBook("condfmt-java.xlsx");
        WorkSheetWrapper ws = wb.openWorkSheet("Sheet1");

        // ---------- data table (same as the Node.js version) ----------
        String[] headers = {"Month", "Cell value rule", "Color scale", "Data bar", "Top/Average"};
        for (int i = 0; i < headers.length; i++) {
            String col = "ABCDE".substring(i, i + 1);
            ws.getRange(col + "1:" + col + "1").setValue(headers[i]);
        }
        int[] sales = {45, 78, 23, 91, 56, 34, 87, 12, 65, 49};
        for (int i = 0; i < sales.length; i++) {
            ws.getRange("A" + (i + 2) + ":A" + (i + 2)).setNumberValue(i + 1);
            for (String col : new String[]{"B", "C", "D", "E"}) {
                ws.getRange(col + (i + 2) + ":" + col + (i + 2)).setNumberValue(sales[i]);
            }
        }

        FormatConditionsWrapper fc = ws.getFormatConditions();

        // ---------- (1) cell value rule: greater than 60 ----------
        // The Node.js version calls cellVal.setA1C1("B2:B11") / setFont(...) / setPatternFill(...),
        // but javaosbxl's CellValueObjectWrapper does not have these methods (API gap)
        CellValueObjectWrapper cellVal = new CellValueObjectWrapper();
        cellVal.setValueCondition(60, XlFormatConditionOperator.FormatConditionGreater);
        try {
            fc.setCellValueCondition(1, cellVal);
            System.out.println("[1] Registered the cell-value rule");
        } catch (RuntimeException e) {
            // registration always throws because A1C1 cannot be set (known API gap in javaosbxl 1.5.0)
            System.out.println("[1] Registering the cell-value rule threw: " + e.getMessage());
        }
        fc.apply();  // apply() can be called without exceptions even with 0 rules

        // ---------- read back the rule list (nothing was registered, so 0) ----------
        Map<Integer, Map<String, XlFormatConditionType>> types = fc.getConditionTypes();
        System.out.println("[2] rule count = " + types.size());

        // ---------- removal methods also work without exceptions at 0 rules ----------
        fc.removeCondition(4);
        System.out.println("[5] rule count after removeCondition(4) = " + fc.getConditionTypes().size());
        fc.removeAllCondition();
        System.out.println("[6] rule count after removeAllCondition() = " + fc.getConditionTypes().size());

        wb.save();
        wb.close();
        System.out.println("[done] saved condfmt-java.xlsx (0 rules because javaosbxl cannot register conditional format rules)");
    }
}

// conditional-format.wasm.js — same verification as the Node.js sample (wasmosbxl / embind)
// Note (WebAssembly build): load the module first with createOsbxl(createModule).
// osbxl / dto / chart / enums used below are the four namespaces it returns
// (the equivalent of require("nodeosbxl") in the Node.js build).
const app = new osbxl.App();
const wb = app.createWorkBook("condfmt-wasm.xlsx"); // output text says condfmt.xlsx, same as the Node.js version
const ws = wb.openWorkSheet("Sheet1");

// color helper: ColorObject takes a 6-digit hex without "#"
const color = (hex) => {
    const c = new dto.ColorObject();
    c.setHexColor(hex);
    return c;
};

// ---------- data table ----------
// col B: cell value rule / col C: color scale / col D: data bar / col E: top 3 + above average
["Month", "Cell value rule", "Color scale", "Data bar", "Top/Average"].forEach((h, i) => {
    const col = "ABCDE"[i];
    ws.getRange(`${col}1:${col}1`).setValue(h);
});
const sales = [45, 78, 23, 91, 56, 34, 87, 12, 65, 49];
sales.forEach((v, i) => {
    ws.getRange(`A${i + 2}:A${i + 2}`).setNumberValue(i + 1);
    for (const col of ["B", "C", "D", "E"]) {
        ws.getRange(`${col}${i + 2}:${col}${i + 2}`).setNumberValue(v);
    }
});

const fc = ws.getFormatConditions();

// ---------- (1) cell value rule: greater than 60 -> dark red bold + light red fill ----------
const cellVal = new dto.CellValueObject();
cellVal.setValueCondition(60, enums.XlFormatConditionOperator.FormatConditionGreater);
cellVal.setA1C1("B2:B11");
const redFont = new dto.FontObject();
redFont.setBold(true);
redFont.setColorObject(color("9C0006"));
cellVal.setFont(redFont);
const pinkFill = new dto.PatternFillObject();
pinkFill.setPattern(enums.XlPatternType.PatternSolid);
pinkFill.setCellColor(color("FFC7CE"));
cellVal.setPatternFill(pinkFill);
fc.setCellValueCondition(1, cellVal);

// ---------- (2) color scale: 3-color gradient on column C ----------
const colorScale = new dto.ColorScaleObject();
colorScale.setA1C1("C2:C11");
colorScale.setMinimumCondition(color("F8696B"), enums.XlConditionValueType.ConditionValueTypeMinimum);
colorScale.setMediumCondition(color("FFEB84"), enums.XlConditionValueType.ConditionValueTypePercentile, 50);
colorScale.setMaximumCondition(color("63BE7B"), enums.XlConditionValueType.ConditionValueTypeMaximum);
fc.setColorScaleFormatCondition(2, colorScale);

// ---------- (3) data bar: column D ----------
const dataBar = new dto.DataBarObject();
dataBar.setA1C1("D2:D11");
dataBar.setDataBarColor(color("638EC6"));
dataBar.setMinimumCondition(enums.XlConditionValueType.ConditionValueTypeMinimum);
dataBar.setMaximumCondition(enums.XlConditionValueType.ConditionValueTypeMaximum);
fc.setDataBarFormatCondition(3, dataBar);

// ---------- (4) top 3 + above average: column E ----------
const top3 = new dto.Top10Object();
top3.setRank(3, true);                       // true = top (false = bottom)
top3.setA1C1("E2:E11");
const blueFont = new dto.FontObject();
blueFont.setBold(true);
blueFont.setColorObject(color("0070C0"));
top3.setFont(blueFont);
fc.setTop10FormatCondition(4, top3);

const aboveAvg = new dto.AboveAverageObject();
aboveAvg.setAboveBelow(enums.XlAboveBelow.XlAboveAverage);
aboveAvg.setA1C1("E2:E11");
const greenFont = new dto.FontObject();
greenFont.setBold(true);
greenFont.setColorObject(color("006100"));
aboveAvg.setFont(greenFont);
fc.setAboveAverageCondition(5, aboveAvg);

fc.apply();                                  // apply() is required after setting all conditions
console.log("[1] Registered 5 rules and called apply()");

// ---------- read back the rule list ----------
const typeName = Object.fromEntries(
    Object.entries(enums.XlFormatConditionType).map(([name, val]) => [val, name]));
console.log(`[2] rule count = ${Object.keys(fc.getConditionTypes()).length}`);
for (const [prio, ranges] of Object.entries(fc.getConditionTypes())) {
    for (const [range, type] of Object.entries(ranges)) {
        console.log(`    priority ${prio}: ${range} → ${typeName[type]} (type=${type})`);
    }
}

// ---------- read back each rule's details ----------
console.log("[3] Reading back rule details");
const g1 = fc.getCellValueCondition(1);
console.log(`    cell value : ${g1.getA1C1()} value=${g1.getConditionValue()}` +
    ` operator=${g1.getConditionOperetor()} (Greater=${enums.XlFormatConditionOperator.FormatConditionGreater})` +
    ` fontColor=${g1.getFont().getColorObject().getHexColor()} bold=${g1.getFont().isBold()}` +
    ` fill=${g1.getPatternFill().getCellColor().getHexColor()}`);
const g2 = fc.getColorScaleFormatCondition(2);
console.log(`    color scale: min=${g2.getMinimumColor().getHexColor()} (type=${g2.getMinimumValueType()})` +
    ` mid=${g2.getMediumColor().getHexColor()} (type=${g2.getMediumValueType()}, threshold=${g2.getMediumValue()})` +
    ` max=${g2.getMaximumColor().getHexColor()} (type=${g2.getMaximumValueType()})`);
const g3 = fc.getDataBarFormatCondition(3);
console.log(`    data bar: color=${g3.getDataBarColor().getHexColor()}` +
    ` minType=${g3.getMinimumCondition()} maxType=${g3.getMaximumCondition()}`);
const g4 = fc.getTop10FormatCondition(4);
console.log(`    top N: rank=${g4.getRank()} isTop=${g4.getTopBottom()}`);
const g5 = fc.getAboveAverageCondition(5);
console.log(`    above average: aboveBelow=${g5.getAboveBelow()}` +
    ` (XlAboveAverage=${enums.XlAboveBelow.XlAboveAverage})`);

// ---------- cells that met the conditions and got formatted (available after apply()) ----------
const byRow = (a, b) => Number(a.slice(1)) - Number(b.slice(1));
console.log("[4] Cells that received formatting");
console.log(`    col B (>60)     : ${Object.keys(fc.getAppliedFontObject("B2:B11")).sort(byRow).join(" ")}`);
console.log(`    col E (top/avg) : ${Object.keys(fc.getAppliedFontObject("E2:E11")).sort(byRow).join(" ")}`);
const appliedBar = fc.getAppliedDataBar("D2:D11");
console.log(`    col D data bars : ${Object.values(appliedBar).filter(Boolean).length} cells`);

// ---------- removal ----------
fc.removeCondition(4);                       // remove only the top-3 rule
console.log(`[5] rule count after removeCondition(4) = ${Object.keys(fc.getConditionTypes()).length}`);
fc.removeAllCondition();                     // remove all rules
console.log(`[6] rule count after removeAllCondition() = ${Object.keys(fc.getConditionTypes()).length}`);

// re-register the cell value rule and save (so the effect is visible when the file is opened)
fc.setCellValueCondition(1, cellVal);
fc.apply();

wb.save();
wb.close();
console.log("[done] saved condfmt.xlsx (cell-value rule re-registered)");