1.6.3.2 Worksheet Data Manipulation

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

Get Worksheet SelectionWorksheet, Get SelectionWorksheet, Get Data from Specified Data Range

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 WorksheetWorksheet, Set Display Range

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 WorksheetWorksheet, Put Large Dataset toWorksheet, Delete All ColumnsColumn, Access Data Buffer

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.

• Prepare the columns and rows before putting data into the worksheet.
• Use Worksheet::SetSize, don't use Worksheet::AddCol to set the size.
• Set the size on an empty worksheet, meaning no columns and rows, since otherwise Origin will need to check the short names of the existing columns to avoid duplicate names when adding new columns, and this could cost you lots of time. You can use while( wks.DeleteCol(0) ); to remove all columns to make an empty Worksheet.
• Put data into worksheet columns by buffer, DataObject::GetInternalDataBuffer.
• Improve OC Execution SpeedKeep Code Builder closed when running functions to improve the speed of execution.

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 WorksheetWorksheet, Embedded Graph

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 DataWorksheet, Sorting 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 DataWorksheet, Masking DataWorksheet, Find 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

Reduce Worksheet Dataworksheet, reduce 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 ConditionWorksheet, Extract Data

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

Worksheet, Highlight SelectionThere 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);
}

Worksheet, Set Color for the Selected RangeThe 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 WorksheetsWorksheet, Compare Data

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

Data ComparisonTo 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");
}