cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
ghartel
Level III

excel import cell colors

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));
1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: excel import cell colors

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. 

Jim

View solution in original post

2 REPLIES 2
txnelson
Super User

Re: excel import cell colors

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. 

Jim
gzmorgan0
Super User (Alumni)

Re: excel import cell colors

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