Get or set worksheet cell properties using a tree
1. wcellFmt tr:=cfmt; cfmt.=;
2. wcellFmt tr:=cfmt; cfmt.FillColor=2; cfmt.Size=18; wcellFmt execute:=1 tr:=cfmt;
Please refer to the page for additional option switches when accessing the x-function from script
| Display Name |
Variable Name |
I/O and Type |
Default Value |
Description |
|---|---|---|---|---|
| Input Range | irng |
Input Range |
|
Specify the input range. |
| Execute Mode | execute |
Input int |
|
Get or set cell properties.
|
| GUI Tree | tr |
Input/Output TreeNode |
|
The cell property tree. |
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.
//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;