excel import cell colors

[#1]

I have used import cell colors in the excel import because I have clients who color cells that should be included or excluded.  The colors import fine but I havent found an easy way to access the cell's color state in order to exclude those cells.  The only workaround I've found is to get the columns script and parse out the row numbers.  I'm afraid that this might not be very reliable.  Are there any functiosn to read the cell's color directly?  Note that the import cell colors does not change the row states or set any color properties for the column.

dt = Open(
	"Data.xlsx",
	Worksheets( "Sheet1" ),
	Use for all sheets( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 2 ),
		Data Starts on Row( 3 ),
		Data Starts on Column( 1 ),
		Data Ends on Column( 1 ),
		Import Cell Colors( 1 )
	)
);
Rlist = {};
t = arg(arg((:SubjectID << get script),4),1);
for(i=1,i<=narg(t),i++,insert into(Rlist, arg(arg(t,i),2))); //the first arg is the colour the second the list of rows
dt1 = dt << subset(rows(Rlist));

[#2 · Accepted solution]

The only way that I know to retrieve the Cell Colors is to revrieve the Script for the column, as you stated.  I have not found any change for this in JMP 14. 

[#3]

This is just an FYI for readers of the blog, not as adept at peeling the arguments of an expression.

If you need to get the row numbers (and color) of colored cells in a column of any JMP data table, Color Cells is not the 4th argument if there are other properties associated with the column.

Attached is the sample data table Big Class.jmp with column age with 2 sets of colored cells (red and blue). And below is a function that can be applied to any column with an example applied to the column :age.

CellColors = Function({colnme}, {ccol, cjsl, i, targ, clr={}, found=0},
 ccol = column(current data table(), colnme);
 cjsl = ccol << get script;	
 //show(cjsl);
 for(i=1, i<=N Arg(cjsl) & found==0, i++,
 	targ = Arg Expr(cjsl, i);
 	//show(i, HeadName(targ));
 	if(Head Name(targ) == "Color Cells", found=1; )
  );
 for(i=1, i<=N Arg(targ), i++,
  insert into(clr, Arg Expr(targ,i))
 );
 clr
);

clrList = CellColors("age");
show(clrList);