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