3.2.7.2 Extract Data from Worksheet

Version Info

Minimum Origin Version Required: Origin 8 SR0

Description

The following example shows how to extract data with LT condition expression from one worksheet to another worksheet, or highlight result rows, or add a new column with 0/1 as mark. Please refer to Worksheet::Extract for more details.

Example

Please take a look on strLTRunBeforeloop variable in example code below, it is used to set LT script command running before test condition, here used to define two range variables a and b to column 1 and column 2. strCondition variable is used to specified LT condition expression.

There are two functions below, before example function Worksheet_Extract_Example running please run Before_Running to prepare worksheet with random data to do extract.

• Worksheet_Extract_Example 1, extract data to new worksheet base on condition
• Worksheet_Extract_Example 2, highlight rows in source worksheet base on condition
• Worksheet_Extract_Example 3, add a new column in source worksheet with 0/1 to mark result

enum
{
	EXTRACT_TO_NEW_WKS = 1,
	EXTRACT_WITH_HIGH_LIGHT,
	EXTRACT_WITH_NEW_TAG_COL
};

void Worksheet_Extract_Example(int nOption = EXTRACT_TO_NEW_WKS)
{	
	Worksheet wks = Project.ActiveLayer();
	if( !wks )
		return;

	// prepare test condition string
    string strCondition = "abs(a) >= 1 && abs(b) >= 1";  //Note: here used "&&" not "AND". And || for "OR".
    string strLTRunBeforeloop = "range a=1; range b=2"; // define range objects, a is column 1 and b is column 2.
    
    // check worksheet with strCondition and result the number of rows base on the condition expression. 
    // vnRowIndices included the row index as result.
    vector<uint> vnRowIndices;
    int nn = wks.SelectRows(strCondition, vnRowIndices, 0, -1, -1, strLTRunBeforeloop);
    
    if(nn < 0)
    {
    	out_str("User cancel");
    	return;
    }    
    
    if(nn == 0)
    {
        out_str("No matching row");
        return;
    }    
    
	printf("%d rows selected with condition -- %s\n", nn, strCondition);
	
	switch(nOption)
	{
	case EXTRACT_TO_NEW_WKS:	    
	    Worksheet wksResult;
	    wksResult.Create();	    
	    
		// Extract data to new worksheet with column indices "vsCols" and row indices "vnRowIndices"
		vector<uint> vnCols = {0, 1}; // only extract the first two columns to new worksheet
	    BOOL bRet = wks.Extract(wksResult, vnRowIndices, vnCols);
	    if(bRet)
	        printf("Extract selected rows to [%s]!%s.\n", wksResult.GetPage().GetName(), wksResult.GetName());  	    
	    break;
	
	case EXTRACT_WITH_HIGH_LIGHT:
        // hight light rows by vnRowIndices
        vector<int> vnRows;
        vnRows = vnRowIndices; // convert vector<uint> to vector<int>
        wks.SetSelectedRange(vnRows);
        break;
        
    case EXTRACT_WITH_NEW_TAG_COL:
        // add a new column with 0/1 to mark if not fit or fit condiftion
        Dataset ds(wks, wks.AddCol());
        ds.SetSize(wks.GetNumRows());
        ds = 0;
        ds.Replace(vnRowIndices, 1);
        break;

	default:
		break;
	}    
}

void Before_Running()
{
	// prepare worksheet with data to do extract
	Worksheet wks;
	wks.Create();
	wks.SetSize(30, 3); // set worksheet with 3o rows and 3 columns	
	
	// set random data for the first TWO columns
	for(int nCol = 0; nCol < wks.GetNumCols()-1; nCol++)
	{
		Dataset ds(wks, nCol);
		ds.Normal(wks.GetNumRows());
	}
	
	// fill row numbers to Column C
	Dataset dsC(wks, 2); 
	dsC.Data(1, wks.GetNumRows(), 1);
}