1.6.3.3 Converting Worksheet to Matrix

You may need to re-organize your data by converting from worksheet to matrix, or vice versa, for certain analysis or graphing needs. This page provides information and examples of converting worksheet to matrix, and please refer to Converting Matrix to Worksheet for the "vice versa" case.

Worksheet Gridding

  1. Run the following command in the Command Window to compile the nag_utils.c file and add it into the current workspace 
    Run.LoadOC(Originlab\nag_utils.c, 16);
  2. Include header files in the Origin C file. 
    #include <wks2mat.h>
#include <Nag_utils.h>
  3. Get XYZ data from the active worksheet XYZ columns. 
    // Construct XYZ data range from XYZ columns
XYZRange rng;
rng.Add(wks, 0, "X");
rng.Add(wks, 1, "Y");
rng.Add(wks, 2, "Z");

// Get XYZ data from data range objects to vectors
vector vX, vY, vZ;
rng.GetData(vZ, vY, vX);
  4. Examine source data type, for example: regular, sparse. 
    UINT nVar;
double xmin, xstep, xmax, ymin, ystep, ymax;
int nSize = vX.GetSize();
int nMethod = ocmath_xyz_examine_data(nSize, vX, vY, vZ, 1.0e-8, 1.0e-8, 
&nVar, &xmin, &xstep, &xmax, &ymin, &ystep, &ymax);
  5. Calculate the number of rows and columns for the result matrix window. 
    int nRows = 10, nCols = 10;
if( 0 == nMethod || 1 == nMethod ) // Regular or sparse
{
	double dGap = 1.5;
	if( !is_equal(ystep, 0) )
		nRows = abs(ymax - ymin)/ystep + dGap; 

	if( !is_equal(xstep, 0) )
		nCols = abs(xmax - xmin)/xstep + dGap;
}
  6. Prepare the result matrix window. 
    // Prepare matrix window to put gridding result
MatrixPage mp;
mp.Create("origin"); // Create matrix window
MatrixLayer ml = mp.Layers(0); // Get the first matrixsheet
MatrixObject mo(ml, 0); // Get the first matrix object

mo.SetXY(xmin, ymin, xmax, ymax); // Set the from/to for X and Y	
mo.SetSize(nRows, nCols); // Set the number of rows and columns
  7. Do XYZ gridding with the different method types. 
    matrix& mat = mo.GetDataObject(); // Get data object from matrix object

int iRet;
switch(nMethod) 
{
case 0: // Regular
	iRet = ocmath_convert_regular_xyz_to_matrix(nSize, vX, vY, vZ, 
		mat, xmin, xstep, nCols, ymin, ystep, nRows);
	printf("--- %d: regular conversion ---\n", iRet);	
	break;

case 1: // Sparse
	iRet = ocmath_convert_sparse_xyz_to_matrix(nSize, vX, vY, vZ, 
		mat, xmin, xstep, nCols, ymin, ystep, nRows);
	printf("--- %d: sparse conversion ---\n", iRet);		
	break;

case 2: // Random(Renka Cline) 
	vector vxGrid(nRows*nCols), vyGrid(nRows*nCols);
	iRet = ocmath_mat_to_regular_xyz(NULL, nRows, nCols, xmin, 
		xmax, ymin, ymax, vxGrid, vyGrid);
	if( iRet >= 0 )
	{
		iRet = xyz_gridding_nag(vX, vY, vZ, vxGrid, vyGrid, mat);
	}
	printf("--- %d: random conversion ---\n", iRet);
	break;

default: // Error.
	printf("--- Error: Other method type ---\n");
}

Worksheet to Matrix

Data contained in a worksheet can be converted to a matrix using a set of functions.

To converts matrix-like worksheet data directly into a matrix, data in source worksheet can contain the X or Y coordinate values in the first column, first row. However, because the coordinates in a matrix should be uniform spaced, you should have uniformly spaced X/Y values in the source worksheet. The CopyFramWks method can be used directly, or just attach XYZ data range to matrix.

The following example show how to perform direct worksheet to matrix conversion: 

// Method 1: using CopyFromWks
Worksheet wks = Project.ActiveLayer();
if(!wks) 
{
	return;
}
MatrixPage matPg;
matPg.Create("Origin");
MatrixLayer matLy = matPg.Layers(0);
Matrix mat(matLy);

matrix<double> mat1;
if(!mat1.CopyFromWks(wks, 1, -1, 1, -1)) 
{
	out_str("Error: CopyFromWks failed!");
	return;
}
mat = mat1;


// Method 2: attach to MatrixObject
Worksheet wks = Project.ActiveLayer();
if(!wks) 
{
	return;
}
int nCols = wks.GetNumCols();
int nRows = wks.GetNumRows();
DataRange dr;
dr.Add("X", wks, 0, 1, 0, nCols - 1);  // First row excep the first cell
dr.Add("Y", wks, 1, 0, nRows - 1, 0);  // First column except the first cell
dr.Add("Z", wks, 1, 1, nRows - 1, nCols - 1);
MatrixPage matPg;
matPg.Create("Origin");
MatrixLayer matLy = matPg.Layers(0);
MatrixObject mo = matLy.MatrixObjects(0);
MatrixObject moTmp;
moTmp.Attach(dr);
matrixbase &matTmp = moTmp.GetDataObject();
matrixbase &mat = mo.GetDataObject();
mat = matTmp;
moTmp.Detach();

When your worksheet data is organized in XYZ column form, you should use Gridding to convert such data into a matrix. Many gridding methods are available, which will interpolate your source data and generate a uniformly spaced array of values with the X and Y dimensions specified by you.

The following example converts XYZ worksheet data by Renka-Cline gridding method. 

// Convert worksheet data into a 20 x 20 matrix by Renka-Cline gridding method
Worksheet wks = Project.ActiveLayer();
if(!wks) 
{
	return;
}
Dataset dsX(wks, 0);
Dataset dsY(wks, 1);
Dataset dsZ(wks, 2);
int nPoints = dsX.GetSize();
vector vX = dsX;
vector vY = dsY;
vector vZ = dsZ;

ocmath_RenkaCline_Struct comm;
ocmath_renka_cline_interpolation(nPoints, vX, vY, vZ, &comm);


//set X and Y of the gridding
double dXMin, dXMax, dYMin, dYMax;
vX.GetMinMax(dXMin, dXMax);
vY.GetMinMax(dYMin, dYMax);

//perform random matrix conversion using Kriging algorithm
int nRows = 20;
int nCols = 20;
matrix mZ(nRows, nCols);
vector vEvalX(nRows * nCols);
vector vEvalY(nRows * nCols);
ocmath_mat_to_regular_xyz(NULL, nRows, nCols, dXMin, dXMax, dYMin, dYMax, vEvalX, vEvalY, NULL, true);
ocmath_renka_cline_eval(&comm, nRows * nCols, vEvalX, vEvalY, mZ);    
ocmath_renka_cline_struct_free(&comm);

//create Matrix storing the result
MatrixLayer    mResultLayer;
mResultLayer.Create();
Matrix    matResult(mResultLayer);
matResult = mZ;
MatrixObject mo = mResultLayer.MatrixObjects(0);
mo.SetXY(dXMin, dYMin, dXMax, dYMax);//set X and Y range of Matrix
English | Deutsch | 日本語