getting cell reference AFTER records are populated

[wwwhack]

I'm desperately looking for a mechanism suitable to style an inserted record based on a value in that record.
In my example, any record that has a "#DoNotPopulate" == "X" all of the cells for that record should be colored red.

It seems the recommended way is to implement n AreaListener - but the methods in that class are unsuitable.
The method only seems to be called once for the source/template - not once per record.
In afterTransformCell the context doesn't include a value for context.getRunVar("r") (how could I know if "#DoNotPopulate" == "X")
The workbook collected from the transformer includes only the template - not the output - so there is no cell to style.

The other solution suggested is using the jx:if command. That requires setting up two template areas per record - in a pool of more than 50 templates - and I have more conditions that need to be styled - writing a separate area template for each possible condition is not reasonable.

Using the CellDataUpdater command is closer to what I need - it is called for each record and the data is accessible - but, again, transformer->getWorkbook() doesn't include the cells that need to be styled. How do I a get a reference to the target cell?

Am I missing something?
I'm invested in making this work with jxls now so I'm considering a hack to note the Excel ranges that need to be formatted and adding the formatting after the output is produced. But for that I don't see how I can get a reference to the cells that were added for a record. Is there no event or hook that is called AFTER a record is written?

I've included code demonstrating that my code using AreaListener and styling the workbook doesn't work.

jx:area(lastCell="F11")
jx:each(items="rows" var="r" lastCell="F11" direction="DOWN")

package com.xlsmbuilder;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.jxls.area.Area;
import org.jxls.area.XlsArea;
import org.jxls.builder.AreaBuilder;
import org.jxls.builder.xls.XlsCommentAreaBuilder;
import org.jxls.common.AreaListener;
import org.jxls.common.CellRef;
import org.jxls.common.Context;
import org.jxls.transform.poi.PoiTransformer;
import org.jxls.util.JxlsHelper;

public class TestXlsm {

	public static void main(String[] args) {
		SheetBean testSheet = TestXlsm.makeTestSheet();
		try {
			TestXlsm.addSheet(testSheet);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public static void addSheet(SheetBean sheetBean) throws IOException {
		
		JxlsHelper jxls = JxlsHelper.getInstance();
		File templateFile = new File("D:\\src\\tools\\xlsmBuilder\\resources\\templates\\testTemplate.xlsx");
		File outFile = new File("C:/Users/Chris Newell/Desktop/tmp/XLS/OUTPUT/testOut.xlsx");
		InputStream tmpltIs = new FileInputStream(templateFile);
		OutputStream os = new FileOutputStream(outFile);
	
		Context context = new Context();
		ArrayList<RowBean> rows = sheetBean.getRows();
		context.putVar("rows", rows);
			
		PoiTransformer transformer = PoiTransformer.createTransformer(tmpltIs, os);
		AreaBuilder areaBuilder = new XlsCommentAreaBuilder(transformer, false);
		List<Area> areaList = areaBuilder.build();
		
		XlsArea myArea = new XlsArea(areaList.get(0).getAreaRef(), transformer);
		myArea.addAreaListener(new CellUpdateAreaListener(myArea));
		myArea.applyAt(new CellRef(myArea.getStartCellRef().getCellName()), context);
		jxls.processTemplate(context, transformer);
		System.out.println("wrote " + outFile.getPath());

		os.close();
		tmpltIs.close();
	}

	private static class CellUpdateAreaListener implements AreaListener {
		
		PoiTransformer transformer = null;
		public CellUpdateAreaListener(XlsArea area) {
			super();
			this.transformer = (PoiTransformer) area.getTransformer();
		}
		
		@Override
		public void afterTransformCell(CellRef srcCellRef, CellRef targetCellRef, Context context) {
			System.out.println("CALLED afterTransformCell " + srcCellRef + " " + targetCellRef);
			/////////////////////
			// this method is called once per column for just the template row
			// no access to current record data - how to know to apply style?
			/////////////////////
			XSSFSheet sheet = transformer.getXSSFWorkbook().getSheet(targetCellRef.getSheetName());
			XSSFCell cell = sheet.getRow(targetCellRef.getRow()).getCell(targetCellRef.getCol());

			/////////////////////
			// this workbook doesn't include the output - so nothing to style!		
			/////////////////////
			XSSFCellStyle style = transformer.getXSSFWorkbook().createCellStyle();
			style.cloneStyleFrom(transformer.getCellStyle(srcCellRef));
			style.setFillForegroundColor(IndexedColors.RED.getIndex());
			style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			cell.setCellStyle(style);
			System.out.println("added format to " + cell.getSheet().getSheetName() + "!" + cell.getAddress());
		}

		@Override
		public void beforeApplyAtCell(CellRef cellRef, Context context) {}
		@Override
		public void afterApplyAtCell(CellRef srcCellRef, Context context) { }
		@Override
		public void beforeTransformCell(CellRef srcCellRef, CellRef targetCellRef, Context context) { }
	}
	
	public static class SheetBean {
		private ArrayList<RowBean> rows = new ArrayList<RowBean>();
		public void addRow(RowBean row) { rows.add(row); }
		public ArrayList<RowBean> getRows() { return rows; }
	}

	public static class RowBean {
		public HashMap<String, String> map = new HashMap<String, String>();
		public int rowNum = 0;
		public void addCell(CellBean cell) { map.put(cell.getTitle(), cell.getData()); }
		public String get(String title) { return map.get(title); }
		public int getRowNum() { return rowNum; }
		public void setRowNum(int rowNum) { this.rowNum = rowNum; }
	}

	public static class CellBean {
		private String title = "";
		private String data = ""; 
		public CellBean(String title, String data) {
			super();
			this.title = title;
			this.data = data;
		}
		public String getTitle() { return title; }
		public void setTitle(String title) { this.title = title; }
		public String getData() { return data; }
		public void setData(String data) { this.data = data; }

	}
	
	private static SheetBean makeTestSheet() {
		SheetBean sheet = new SheetBean();
		RowBean row = new RowBean();
		row.setRowNum(1);
		row.addCell(new CellBean("#Owner", "S"));
		row.addCell(new CellBean("#DoNotPopulate", "X"));
		row.addCell(new CellBean("!PayerName", "Payer"));
		row.addCell(new CellBean("PayerFedID", "YYYYYYY"));
		sheet.addRow(row);
		
		row = new RowBean();
		row.setRowNum(2);
		row.addCell(new CellBean("#Owner", "T"));
		row.addCell(new CellBean("#DoNotPopulate", ""));
		row.addCell(new CellBean("!PayerName", "Payer2"));
		row.addCell(new CellBean("PayerFedID", "XXXXXX"));
		sheet.addRow(row);	
		return sheet;
	}
	
}

testTemplate.xlsx

[leonate]

Can't you use the Excel conditional formatting for that? E.g. define a rule for a particular column that applies a style you need based on the value?