1.6.2.2 Worksheet Column Data Manipulation

Basic Arithmetic Operation

To perform the base arithmetic operation on the column data, you can first get the column data into vector, and then operate on the corresponding vectors. 

// Get data from the 1st and 2nd columns
// Then add two columns together, 
// and put results to 3rd column
Worksheet wks = Project.ActiveLayer();
if(!wks) 
{
	return;
}
Column col1 = wks.Columns(0);  // 1st column
Column col2 = wks.Columns(1);  // 2nd column
Column col3 = wks.Columns(2);  // 3rd column

vectorbase &v1 = col1.GetDataObject();  // Get data object
vectorbase &v2 = col2.GetDataObject(); 
vectorbase &v3 = col3.GetDataObject();
v3 = v1 + v2;  // Add together

Set Value by Formula

The DataObject::SetFormula and DataObject::ExecuteFormula methods are used to set column/matrix values, which is the same as setting values in the Set Values dialog. The following example is of creating a worksheet with three columns, and then setting values by a formula to each column. 

Worksheet wks;
wks.Create("origin", CREATE_VISIBLE);
wks.AddCol();

// set value to the first column
Column colA;
colA.Attach(wks, 0);
colA.SetFormula("5*(i-1)");
colA.ExecuteFormula();

// for the next two columns we will set Recalculate = Auto
Column colB;
colB.Attach(wks, 1);
colB.SetFormula("sin(4*col(A)*pi/180)", AU_AUTO);
colB.ExecuteFormula();

// using declared variables in Before Formula Script
Column colC;
colC.Attach(wks, 2);
string strExpression = "cos(Amp*x*pi/180)";
string strBeforeScript = "double Amp=4.5;" + "\r\n" + "range x=col(A);";
string strFormula = strExpression + STR_COL_FORMULAR_SEPARATOR + strBeforeScript;
colC.SetFormula(strFormula, AU_AUTO);
colC.ExecuteFormula();

Sort Column

To sort a specified column, first get the column's data into a vector, and then put the data back after sorting the vector. By using a vector reference for getting data object from column, the vector will attach to the column automatically, and the data update on vector will map back to column. 

Worksheet wks = Project.ActiveLayer();
if(!wks) 
{
	return;
}
Column col1 = wks.Columns(0);  // 1st column
vectorbase &v1 = col1.GetDataObject();  // Get data object using reference
v1.Sort(SORT_DESCENDING);  // Sort descendingly

Reverse Column

To reverse column's data, first you can get the column data into a vector, and then reverse the data in vector and put them back. 

// Reverse the 1st column's data
Worksheet wks = Project.ActiveLayer();
if(!wks) 
{
	return;
}
Column col1 = wks.Columns(0);  // 1st column
vectorbase &v1 = col1.GetDataObject();  // Get data object
vector<uint> vnIndices;  // vector for reverse indices
vnIndices.Data(v1.GetSize() - 1, 0, -1);  // Reverse indices
v1.Reorder(vnIndices);  // Reverse the data

Get & Set Data from Column

Get & Set Numeric Data Values from Column

// Attach to the first column, make sure the format of the column is 
// Text & Numeric(default) or Numeric.
Column col(wks, 0);

// Here assume the data type of the column is double. 
// Other numeric data type supported, for example, int, short, complex.
vector<double>& vec = col.GetDataObject();

// Append 100 at the end of this column
vec.Add(100);

Or we can use a Dataset object to get and set numeric data for a column. For example: 

Worksheet wks = Project.ActiveLayer();

Dataset ds(wks, 1);

for(int ii=0; ii<ds.GetSize(); ii++)
    out_double("", ds[ii]);

Get & Set String Values from Column

Column col(wks, 0); // Attach to the first column

// Get string array from column 
vector<string> vs;
col.GetStringArray(vs);

// Put string array back to column
vs.Add("test");
col.PutStringArray(vs);

Get & Set Date and Time Data from Column

If the column's format is Date or Time, the data you get from this column will be Julian date/time data, but not the display-date-time-format string. 

// Get active worksheet
Worksheet wks = Project.ActiveLayer();
Column col1(wks, 0);  // The first column
Column col2(wks, 1);  // The second column
// Check if the first column's format is Date or Time, or not
if(col1.GetFormat() == OKCOLTYPE_DATE || col1.GetFormat() == OKCOLTYPE_TIME)
{
	// Get data from 1st column, v1 holds Julian data
	vector &v1 = col1.GetDataObject();  
	vector &v2 = col2.GetDataObject();  // Get data from 2nd column
	v2 = v1;  // Set 1st column's Julian data to 2nd column
	col2.SetFormat(OKCOLTYPE_DATE);  // Set 2nd column to be Date column
	// Set display format to be MM/dd/yyyy HH:mm:ss
	col2.SetSubFormat(LDF_SHORT_AND_HHMMSS_SEPARCOLON);  
}


Get the columns of different worksheets\workbooks

To calculate the sum of a specific column of all worksheets in all workbooks, you can loop all the worksheets in the current folder, and operate on the wanted column(s). 

// Retrieve the second column of each worksheet in each workbook of current folder, calculate its sum and output to a new worksheet.
void Calculate_Column_Sum()
{
    StringArray ColNames;
    vector<double> ColMeans;
    int K = 1;
    Dataset ds;
    double colSum;
    Folder fld = Project.ActiveFolder();  // Get the active/current folder	
    foreach(PageBase pb in fld.Pages) 
    {  
            // Loop all Pages in folder
            WorksheetPage wksPgSource = pb;  // Convert the Page to WorksheetPage
	    // If convert failed, that is to say the Page is not WorksheetPage
	    if(!wksPgSource) 
	    {  
		continue;  // Next Page
       	    }

	    // Loop all worksheet in workbook
	    foreach(Layer lay in wksPgSource.Layers) 
	    {
		Worksheet wks = lay; 
                // Get column info
		ds.Attach(wks, K);
		DataRange dr;
		dr.Add("X", wks,0,K,-1,K);
		ColNames.Add(dr.GetDescription());			
		// Calculate column sum
		ds.Sum(colSum);	
		ColMeans.Add(colSum);
	    }

    }	
    // Prepare the resulting worksheet
    Worksheet wksResult;
    wksResult.Create("Origin");
    DataRange dr;
    dr.Add(wksResult,0,"X");
    dr.Add(wksResult,1,"Y");
    dr.SetData(&ColMeans,&ColNames);	
}
English | Deutsch | 日本語