3.1.1 Data Access from Worksheet

Version Info

Minimum Origin Version Required: Origin 8 SR0

Remark

This section introduced access numeric data, text, Julian date/time data from Origin worksheet window.

More related details please reference to the following Origin C Reference sections:

WorksheetPage class

Worksheet class

Column class

DataRange class

vectorbase class

vector class

Numeric

Get Numeric Dataset

void GetNumericDataset()
{
	Worksheet wks = Project.ActiveLayer();
	if( wks ) // if active layer is Worksheet
	{
		foreach(Column col in wks.Columns)
		{
			vectorbase& vec = col.GetDataObject(); // use vectorbase here since data in column can be any numeric data type, for example, double, int, complex. 
			
			double dSum;
			vec.Sum(dSum);
			printf("The sum of the %dth column is %f\n", col.GetIndex()+1, dSum); // GetIndex return index with 0 offset
		}
	}
}

Modify Numeric Dataset

void ModifyNumericDataset()
{
	Worksheet wks = Project.ActiveLayer();
	if( wks && wks.GetNumCols() >= 2 ) //to make sure active window is Worksheet and at least exist two columns
	{
		DataRange dr;
		dr.Add(wks, 0, "X");
		dr.Add(wks, 1, "X");
		
		vector vA, vB;
		dr.GetData(&vA, 0);
		dr.GetData(&vB, 1);
		
		// sort the whole worksheet by column A with ascending
		vector<uint> vnIndices;
		vA.Sort(SORT_ASCENDING, TRUE, vnIndices); // sort column A and returned order indices
		vB.Reorder(vnIndices); // reorder column B by column A indices
		
		dr.SetData(vA, false, 0);
		dr.SetData(vB, false, 1);
	}
}

Access One Numeric Cell

void AccessNumericData(int nRow = 0, int nCol = 0)
{
	Worksheet wks = Project.ActiveLayer();
	if( !wks )
		return;
	
	double dd = wks.Cell(nRow, nCol);
	out_double("The original data is ", dd);
	
	dd = 100;
	wks.SetCell(nRow, nCol, dd);
	out_double("The modified data is ", dd);
}

Put Large Dataset to Worksheet

In order to keep Origin C function more efficient on putting the large dataset to worksheet, for example 5000 columns, need to do like the steps below.

• Prepare the columns and rows before put into data to worksheet.
• Use Worksheet::SetSize, not use Worksheet::AddCol to set size.
• Set size on an empty worksheet, means no column, since need to check the short name of the existed columns to avoid duplicate name when add new column. This check may cost many times. Can use while( wks.DeleteCol(0) ); to remove all columns.
• Put data to worksheet column by buffer, DataObject::GetInternalDataBuffer.
• Keep Code Builder close when running function to set size and put back data to worksheet.

See following example codes:

void PutLargeDataset(int rows = 100, int cols = 500)
{
	// prepare worksheet size
	Worksheet wks;
	wks.Create("Origin");
	while( wks.DeleteCol(0) );	
	wks.SetSize(rows, cols);
	
	// put dataset to worksheet column one by one
	foreach(Column col in wks.Columns)
	{
		col.SetFormat(OKCOLTYPE_NUMERIC);
		col.SetInternalData(FSI_SHORT);
		col.SetUpperBound(rows-1);//index of last row, 0 offset
		
		int 	nElementSize;
		uint 	nNum;
		LPVOID 	pData = col.GetInternalDataBuffer(&nElementSize, &nNum);		
		short* psBuff = (short*)pData;
		
		// OC loop is still slow, but you might pass this pointer to your DLL
		// for much faster manipulation, here we just show that the pointer works
		for(int ii = 0; ii < rows; ii++, psBuff++)
		{
			*psBuff = (ii+1) * (col.GetIndex()+1);
		}
		col.ReleaseBuffer(); // do NOT remember to call this
	}	
}

String

Get/Put String Array

void AccessStringArray()
{
	Worksheet wks = Project.ActiveLayer();
	if( wks )
	{
		foreach(Column col in wks.Columns)
		{
			int nFormat = col.GetFormat();
			if( OKCOLTYPE_TEXT == nFormat || OKCOLTYPE_TEXT_NUMERIC == nFormat )
			{
				vector<string> vstr;
				col.GetStringArray(vstr);
				
				DWORD dwOptions = SORT_ASCENDING | SORT_CASE_SENSITIVE;
				vstr.Sort(dwOptions); // ascending sort with case sensitive
				
				col.PutStringArray(vstr); // put back sorted string array to column
			}
		}		
	}
}

Access One String Cell

void AccessString(int nRow = 0, int nCol = 0)
{
	Worksheet wks;
	wks.Create("Origin");
	
	string strVal;
	wks.GetCell(nRow, nCol, strVal);
	out_str(strVal);
	
	strVal = "This is an example!";
	wks.SetCell(nRow, nCol, strVal);
	out_str(strVal);
	
	Column col = wks.Columns(nCol);
	col.SetWidth(-1); // -1 to set the width to the largest cell in the column.	
}

Date and Time

void AccessDateTimeData(int nColIndex)
{
	Worksheet wks = Project.ActiveLayer();
	if( !wks )
		return;	
	
	Column col(wks, nColIndex);
	if( !col )
		return;	
		
	// find out the smallest date
	if( OKCOLTYPE_DATE == col.GetFormat() || OKCOLTYPE_TIME == col.GetFormat() )
	{
		vector& vec = col.GetDataObject(); // return the Julian date value
		if( vec.GetSize() != 0 )
		{
			vec.Sort();
			printf("The smallest date is %s\n", get_date_str(vec[0], LDF_LONG));
		}
	}
}

Missing Value

void fill_with_missing()
{
	Worksheet wks = Project.ActiveLayer();  // get active worksheet
	if(!wks)
	{
		out_str("The active worksheet not exist!");
		return;
	}
	
	DataRange dr;  
	dr.Add("X", wks, 0, 0, -1, -1);  // the whole worksheet
	dr.Replace(0, NANUM, WKSREPL_TEST_EQUAL);  // replace by missing, missing=NANUM

	Dataset ds(wks, 0);  // dataset attached to column 1
	ds = NANUM;  // clear column 1
}