1.6.3.2 Worksheet Data Manipulation

In this section we present examples of how to manipulate worksheet data by Origin C.

Contents

Get Worksheet Selection

Worksheet::GetSelectedRange can be used to get one or multiple selected data ranges from a worksheet. The following code shows how to get data from one column by worksheet selection. This function returns range type, like one column, one row, whole worksheet, etc. 

Worksheet wks = Project.ActiveLayer();

int r1, c1, r2, c2;
int nRet = wks.GetSelectedRange(r1, c1, r2, c2);

if( WKS_SEL_ONE_COL & nRet ) // exactly one column selected
{
	// construct a data range object by selection
	DataRange dr;
	dr.Add("X", wks, r1, c1, r2, c2);
	
	// get data from the selected column
	vector vData;
	dr.GetData(&vData, 0);
}

Set Display Range in Worksheet

If you want to set a display range in a Worksheet, you can use Worksheet::SetBounds, and it is the same as using the Set As Begin/End menu.

The following code shows how to set a beginning and end for all columns in the current worksheet window. 

Worksheet wks = Project.ActiveLayer();

// the beginning and end of rows	
int begin = 9, end = 19;

// set beginning and end for all columns
int c1 = 0, c2 = -1; // -1 means end

wks.SetBounds(begin, c1, end, c2);

Put Large Dataset to Worksheet

In order to keep an Origin C function running efficiently when working with a large data set (e.g. 1000 columns) in a worksheet, use the steps below.

See the following example codes: 

// prepare worksheet size
Worksheet wks;
wks.Create("Origin");
while( wks.DeleteCol(0) ); 
int rows = 100, cols = 1000;
wks.SetSize(rows, cols);

// put data set into worksheet columns one by one
foreach(Column col in wks.Columns)
{
    col.SetFormat(OKCOLTYPE_NUMERIC);
    col.SetInternalData(FSI_SHORT);
    col.SetUpperBound(rows-1);//index of last row, 0 offset
    
    int     nElementSize;
    uint    nNum;
    LPVOID  pData = col.GetInternalDataBuffer(&nElementSize, &nNum);        
    short* psBuff = (short*)pData;
    
    // OC loop is still slow, but you might pass this pointer to your DLL
    // for much faster manipulation, here we just show that the pointer works
    for(int ii = 0; ii < rows; ii++, psBuff++)
    {
        *psBuff = (ii+1) * (col.GetIndex()+1);
    }
    col.ReleaseBuffer(); // do NOT forget to call this
}

Access Embedded Graph in a Worksheet

Create a new graph and a new worksheet, and then embed the graph within one of the worksheet's cells: 

GraphPage gp;
gp.Create("Origin");

Worksheet wks;
wks.Create();

int nOptions = EMBEDGRAPH_KEEP_ASPECT_RATIO | EMBEDGRAPH_HIDE_LEGENDS;

// Put the graph in worksheet cell (0, 0)
wks.EmbedGraph(0, 0, gp, nOptions);

Access a graph that is embedded within a worksheet; by name or by index: 

// Get embedded graph from active worksheet
Worksheet wks = Project.ActiveLayer();

GraphPage gp;
gp = wks.EmbeddedPages(0); // Get embedded graph page by index

gp = wks.EmbeddedPages("Graph1"); // Get embedded graph page by name

Sort Worksheet Data

Perform a row-wise sort of column data with the Sort method. For sorting a single column, use the vectorbase::Sort method: 

// Sort column
// Before running, please keep active worksheet with two columns fill with data.
// For example, import \Samples\Mathematics\Sine Curve.dat to worksheet.
Worksheet wks = Project.ActiveLayer();
Column colY(wks, 1); // Y column

// After sort, the original relation for (x, y) will be broken.
vectorbase& vec = colY.GetDataObject();
vec.Sort();

To sort all columns in a worksheet, use the Worksheet::Sort method: 

// Sort worksheet
// Before running, please keep active worksheet with two columns fill with data.
// For example, import \Samples\Mathematics\Sine Curve.dat to worksheet.
Worksheet wks = Project.ActiveLayer();
 
int nCol = 1; // Ascending sort all worksheet data on the second column
BOOL bIsAscending = true;
BOOL bMissingValuesSmall = TRUE; // Treat missing value as smallest
int r1 = 0, c1 = 0, r2 = -1, c2 = -1; // -1 means end for r2 and c2
 
// After sort, each (x, y) still keep the original relation
wks.Sort(nCol, bIsAscending, bMissingValuesSmall, r1, c1, r2, c2);

Mask Worksheet Data

The following code shows how to set a mask on the rows of data that are less than or equal to 0 for the specified column. 

int nCol = 1;
Worksheet wks = Project.ActiveLayer();
Column col(wks, nCol);
vector vData = col.GetDataObject();

// to find all less than and equal 0 and return row index
vector<uint> vnRowIndex;
vData.Find(MATREPL_TEST_LESSTHAN | MATREPL_TEST_EQUAL, 0, vnRowIndex);

// construct a range including multiple subranges added by row and column index
DataRange dr;
for(int nn = 0; nn < vnRowIndex.GetSize(); nn++)
{
	int r1, c1, r2, c2;
	r1 = r2 = vnRowIndex[nn];
	c1 = c2 = nCol;
	dr.Add("X", wks, r1, c1, r2, c2); 		
}

// set mask on data range
dr.SetMask();

Set Size

The Worksheet::SetSize method is used to set the number of rows and columns in a worksheet. 

// Set the number of rows and columns, and data will be kept.
// If want to add a lots of columns and rows at once time, better use SetSize
int nNumRows = 100;
int nNumCols = 20;
wks.SetSize(nNumRows, nNumCols); 
// If want to change the number of rows but keep the number of columns,
// can use -1 replace. For example:
wks.SetSize(nNumRows, -1); 
// The same usage also used to change column number and keep row number.

Reduce Worksheet Data

Origin C provides some functions for reducing XY data in worksheet, such as ocmath_reducexy_fixing_increbin for reducing XY data by X increment, ocmath_reducexy_n_groups for reducing XY data by number of groups, ocmath_reducexy_n_points for reducing XY data by every N points, etc. The following is an example to show how to reduce XY data by every N points. 

Worksheet wks = Project.ActiveLayer();  // Get active worksheet
if(!wks) 
{
	return;
}
Column colX(wks, 0);  // First column in worksheet
Column colY(wks, 1);  // Second column in worksheet
if(colX && colY) 
{ 
	vectorbase &vbInterY = colY.GetDataObject();  // Get Y column data
	vector vY = vbInterY;
	vector vReduced(vY.GetSize());
	int nPoints = 3;
	// Reduce every 3 points, and result is the mean of every 3 points
	int nNewSize = ocmath_reducexy_n_points(vY, vReduced, vY.GetSize(), 
		nPoints, REDUCE_XY_STATS_MEAN);
	int iReduced = wks.AddCol("Reduced");  // Add a new column for result
	Column colReduced(wks, iReduced);
	vectorbase &vbReduced = colReduced.GetDataObject();
	vbReduced = vReduced;
}

Extract Data from Worksheet with LT Condition

Select worksheet data using the Worksheet::SelectRows method. Rows can be selected across many columns. 

// Select data from a worksheet based on a condition;
// put the indices of the selected rows into a vector of type 'uint'.
Worksheet wks = Project.ActiveLayer();

// Check the worksheet data based on the condition expression and 
// output the row index into 'vnRowIndices'.
// Define Labtalk range objects, 'a' = column 1, 'b' = column 2.    
string strLTRunBeforeloop = "range a=1; range b=2"; 
string strCondition = "abs(a) >= 1 && abs(b) >= 1";	    
vector<uint> vnRowIndices; // This is output
int r1 = 0, r2 = -1; // The row range, -1 means the last row for r2
 
// Optional maximum number of rows to select, -1 indicates no limit
int nMax = -1;

int num = wks.SelectRows(strCondition, vnRowIndices, r1, r2, nMax, 
		strLTRunBeforeloop);

There are two ways to highlight the selection. The first is to highlight the selected indices. 

// Method 1 of show selection: highlight rows by vnRowIndices
Grid gg;
if( gg.Attach(wks) )
{
	// convert uint type vector to int type vector	    	
    vector<int> vnRows;
    vnRows = vnRowIndices;	        
    
    gg.SetSelection(vnRows);
}

The second method of highlighting a data selection is to prescribe a fill color for the selected rows. 

// Method 2 of show selection: fill color on the selected rows by vnRowIndices
DataRange dr;

// Construct data ranges by the row indices in vnRowIndices.
for(int index=0; index<vnRowIndices.GetSize(); index++)
{
	// The following 0(1st col) and -1(last col) for all columns
	// "" for range name variable, not specified, default name will be used
	dr.Add("", wks, vnRowIndices[index], 0, vnRowIndices[index], -1);
}

Tree tr;
tr.Root.CommonStyle.Fill.FillColor.nVal = SYSCOLOR_BLUE; // fill color = blue
tr.Root.CommonStyle.Color.nVal = SYSCOLOR_WHITE; // font color = white

if( 0 == dr.UpdateThemeIDs(tr.Root) ) // Return 0 for no error
{
	bool bRet = dr.ApplyFormat(tr, true, true);
}

Compare Data in Two Worksheets

It may be useful to compare the number of rows or columns between two worksheets, or compare the data themselves. Get a row or column count from a worksheet with the Datasheet::GetNumRows and Datasheet::GetNumCols methods. 

if( wks1.GetNumRows() != wks2.GetNumRows() 
	|| wks1.GetNumCols() != wks2.GetNumCols() )
{
	out_str("The two worksheets are not the same size");
	return;
}

Another way to perform a similar operation is to copy the data from each worksheet into a vector, and compare the size of the vectors. 

// get all data from worksheet 1 columns one by one
vector vec1;
foreach(Column col in wks1.Columns)
{
	vector& vecCol = col.GetDataObject();
	vec1.Append(vecCol);
}

// get all data from worksheet 2 columns one by one
vector vec2;
foreach(col in wks2.Columns)
{
	vector& vecCol = col.GetDataObject();
	vec2.Append(vecCol);
}	

if( vec1.GetSize() != vec2.GetSize() ) 
{
	out_str("The size of the two data sets is not equal");
	return;
}

To compare data elements themselves, use the ocmath_compare_data function on the vectors in the example above. 

bool bIsSame = false;
double dTolerance = 1e-10;
ocmath_compare_data(vec1.GetSize(), vec1, vec2, &bIsSame, dTolerance);
if( bIsSame )
{
	out_str("Data in the two worksheets are the same");
}