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

// 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
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;
// Calculate column sum
ds.Sum(colSum);
}