topic Re: excel import cell colors in Discussions

excel import cell colors

ghartel — Mon, 28 May 2018 23:04:32 GMT

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));

Re: excel import cell colors

txnelson — Tue, 29 May 2018 02:57:54 GMT

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.

Re: excel import cell colors

gzmorgan0 — Tue, 29 May 2018 07:27:26 GMT

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);