2.2.3.35 wcellfmt

Brief Information

Get or set worksheet cell properties using a tree

Additional Information

Command Line Usage

1. wcellFmt tr:=cfmt; cfmt.=;

2. wcellFmt tr:=cfmt; cfmt.FillColor=2; cfmt.Size=18; wcellFmt execute:=1 tr:=cfmt;

X-Function Execution Options

Please refer to the page for additional option switches when accessing the x-function from script

Variables

Description

This X-Function can be used to get the worksheet cell properties tree tr (execute:=0), or set the worksheet properties tree tr (execute:=1).

See X-Function wproperties, the section on TextControl.

Examples

  //set selected cells' Filled Color to green
  Tree cfmt;
  wcellFmt tr:=cfmt;//default is to get
  cfmt.FillColor=2;
  wcellFmt exec:=set tr:=cfmt;

  //set cell's Fill Color to green without pre-selecting  
  Tree cfmt;
  wcellFmt 2[3:5] get cfmt;
  cfmt.FillColor=2;
  wcellFmt 2[3:5] set cfmt;

  
  //the following will dump the tree from selected cells
  Tree cfmt;
  wcellFmt tr:=cfmt;
  cfmt.=;

// Imports three spectra and creates a 10-level heatmap of sorts for Y data in worksheet.

// Import three data files from Origins Samples Folder.
newbook;
impASC fname:="C:\Program Files\OriginLab\Origin2018\Samples\Batch Processing\T275K.csv
C:\Program Files\OriginLab\Origin2018\Samples\Batch Processing\T305K.csv
C:\Program Files\OriginLab\Origin2018\Samples\Batch Processing\T335K.csv" options.ImpMode:=1;

// Delete extra X columns.
for (int ii = wks.ncols -1; ii > 2; ii -= 2)
	delete wcol(ii);

// Create a StringArray of 10 hex colors.
string strColors$ = "#A000C8|#6E00DC|#1E3CFF|#00A0FF|#00C8C8|#00DC00|#E6DC32|#E6AF2D|#FA3C3C|#F00082";
StringArray sa;
sa.Append(strColors$, |);

// Get the cell format for col 1, row 1 into a Tree variable.
Tree trFmt;
wcellFmt irng:=col(1)[1] exec:=get tr:=trFmt;

// Preserve original font size from format Tree.
double dFontSize = trFmt.Size;

// Get min and max values for all values Y columns.
stats ix:=(2:end);
double dMin = stats.min;
double dMax = stats.max;

// Loop through every Y column.
int nCols = wks.ncols;
for (int ii = 2; ii <= nCols; ii++)
{
	// Get a range to current column and skip if column is empty.
	range rng = wcol(ii);
	if (!rng.GetSize())
		continue;

	// Create a temporary dataset of values in current column
	// normalized (1-10) based on min and max values of all cells in sheet.
	dataset ds = round(1 + (rng - dMin) * (10 - 1) / (dMax - dMin));

	// Loop through every row in current column.
	int nSize = ds.GetSize();
	for(jj = 1; jj <= nSize; jj++)
	{
		// Apply modified font size to format Tree based
		// on normalized cell value.
		trFmt.Size = dFontSize + ds[jj];

		// Apply Hex background color to format Tree based
		// on normalized cell value.
		string str$ = sa.GetAt(ds[jj])$;
		trFmt.FillColor = color("%(str$)");

		// Apply format Tree to the cell.
		wcellFmt irng:=rng[jj] exec:=set tr:=trFmt;
	}
}
//Resize worksheet based on col and row size.
wautosize hw:=b;

Related X-Functions