use of org.apache.poi.ss.usermodel.ConditionalFormatting in project poi by apache.
the class ConditionalFormattingEvaluator method getRules.
/**
* lazy load by sheet since reading can be expensive
*
* @param sheet
* @return unmodifiable list of rules
*/
protected List<EvaluationConditionalFormatRule> getRules(Sheet sheet) {
final String sheetName = sheet.getSheetName();
List<EvaluationConditionalFormatRule> rules = formats.get(sheetName);
if (rules == null) {
if (formats.containsKey(sheetName)) {
return Collections.emptyList();
}
final SheetConditionalFormatting scf = sheet.getSheetConditionalFormatting();
final int count = scf.getNumConditionalFormattings();
rules = new ArrayList<EvaluationConditionalFormatRule>(count);
formats.put(sheetName, rules);
for (int i = 0; i < count; i++) {
ConditionalFormatting f = scf.getConditionalFormattingAt(i);
//optimization, as this may be expensive for lots of ranges
final CellRangeAddress[] regions = f.getFormattingRanges();
for (int r = 0; r < f.getNumberOfRules(); r++) {
ConditionalFormattingRule rule = f.getRule(r);
rules.add(new EvaluationConditionalFormatRule(workbookEvaluator, sheet, f, i, rule, r, regions));
}
}
// need them in formatting and priority order so logic works right
Collections.sort(rules);
}
return Collections.unmodifiableList(rules);
}
use of org.apache.poi.ss.usermodel.ConditionalFormatting in project poi by apache.
the class TestHSSFWorkbook method setSheetOrderHSSF.
@Test
public void setSheetOrderHSSF() throws IOException {
Workbook wb = new HSSFWorkbook();
Sheet s1 = wb.createSheet("first sheet");
Sheet s2 = wb.createSheet("other sheet");
Name name1 = wb.createName();
name1.setNameName("name1");
name1.setRefersToFormula("'first sheet'!D1");
Name name2 = wb.createName();
name2.setNameName("name2");
name2.setRefersToFormula("'other sheet'!C1");
Row s1r1 = s1.createRow(2);
Cell c1 = s1r1.createCell(3);
c1.setCellValue(30);
Cell c2 = s1r1.createCell(2);
c2.setCellFormula("SUM('other sheet'!C1,'first sheet'!C1)");
Row s2r1 = s2.createRow(0);
Cell c3 = s2r1.createCell(1);
c3.setCellFormula("'first sheet'!D3");
Cell c4 = s2r1.createCell(2);
c4.setCellFormula("'other sheet'!D3");
// conditional formatting
SheetConditionalFormatting sheetCF = s1.getSheetConditionalFormatting();
ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(CFRuleRecord.ComparisonOperator.BETWEEN, "'first sheet'!D1", "'other sheet'!D1");
ConditionalFormattingRule[] cfRules = { rule1 };
CellRangeAddress[] regions = { // A3:A5
new CellRangeAddress(2, 4, 0, 0) };
sheetCF.addConditionalFormatting(regions, cfRules);
wb.setSheetOrder("other sheet", 0);
// names
assertEquals("'first sheet'!D1", wb.getName("name1").getRefersToFormula());
assertEquals("'other sheet'!C1", wb.getName("name2").getRefersToFormula());
// cells
assertEquals("SUM('other sheet'!C1,'first sheet'!C1)", c2.getCellFormula());
assertEquals("'first sheet'!D3", c3.getCellFormula());
assertEquals("'other sheet'!D3", c4.getCellFormula());
// conditional formatting
ConditionalFormatting cf = sheetCF.getConditionalFormattingAt(0);
assertEquals("'first sheet'!D1", cf.getRule(0).getFormula1());
assertEquals("'other sheet'!D1", cf.getRule(0).getFormula2());
wb.close();
}
Aggregations