The Origin Forum
File Exchange
Try Origin for Free
The Origin Forum
Home | Profile | Register | Active Topics | Members | Search | FAQ | Send File to Tech support
Username:
Password:
Save Password
Forgot your Password? | Admin Options

 All Forums
 Origin Forum for Programming
 Forum for Origin C
 Row to Column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic Lock Topic Edit Topic Delete Topic New Topic Reply to Topic

Chris2

20 Posts

Posted - 08/27/2010 :  05:37:52 AM  Show Profile  Edit Topic  Reply with Quote  View user's IP address  Delete Topic
Windows Vista
Origin 8.1

Hi,

I have two workbooks created. One is filled with ImportASCII, and now I want to copy the complete row of the longnames to a new workbook, not as row but as column.
The creation and the importing works fine, I just don't know how to copy and transpose it.

Thanks for any suggestions!

rlewis

Canada
253 Posts

Posted - 08/27/2010 :  5:32:29 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
The following function will extract a row from the source Worksheet (WksSource) and append it as column to the destination Worksheet (WksDest)


bool RowToWksColumn(Worksheet WksSource, Worksheet &WksDest,uint nRow)
{
    if(WksSource.IsValid()==true && WksDest.IsValid()==true && nRow<WksSource.GetNumRows())
       {
	   matrix mat;
	   if(mat.CopyFromWks(WksSource,0,-1,nRow,nRow )==true)
	   {
		vector<double> vecRow;
		if(mat.GetAsVector(vecRow)==true)
		{
			int iCol=WksDest.AddCol();
			Dataset dS(WksDest, iCol);
			if(dS.Attach(WksDest, iCol)==true)
			{
				dS=vecRow;
				return (true);
			}
		}
	}
   }
   return (false);
}
Go to Top of Page

Chris2

20 Posts

Posted - 08/28/2010 :  05:51:21 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Hi,

thanks for your quick answer!
I'm still very inexperienced in working with origin and it would be very helpful to see the code on one specific example. Let's say I want to copy the row of the longnames of "Book1" to the first column of "Book2". Both books exist already.

Thanking you in anticipation!
Go to Top of Page

rlewis

Canada
253 Posts

Posted - 08/29/2010 :  02:38:19 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
The original function posted will work with numeric data.
To perform the same operation with strings one needs to do a bit more
Try following code will extract the column longnames of the active worksheet, add a new sheet to the current workbook and store the data in the first column of the newly added sheet.
It seems to work quite well on my system 8.1Pro-SR3

void GetWksColLongNames()
{
	// Assuming that the Source worksheet is the Active Sheet of the current Workbook ...and attach it to the Worksheet Object wkSource...
    Worksheet wkSource = Project.ActiveLayer();
    if(wkSource.IsValid()==false)
    {
    	out_str("Active Layer is not a Worksheet");
    	return;
    }
    
    // Get the Workbook object containing the Source Worksheet
    WorksheetPage wP=wkSource.GetPage();
    if(wP.IsValid()==false)
    {
    	out_str("Worksheet page validation fail");
    	return;
    }
    
    // Add a new sheet to the current Workbook ... and attach it to the Worksheet Object wkDest...
    int iWks=wP.AddLayer();
    Worksheet wkDest(wP.Layers(iWks));
    if(wkDest.IsValid()==false)
    {
    	out_str("Workbook Layer validation fail");
    	return;
    }

    // Store the current size of the Source worksheet ...
 	int nRows=wkSource.GetNumRows();
 	int nCols=wkSource.GetNumCols();
 	
 	// Store Format of source Worksheet .....
 	Tree trFormat;
 	trFormat=wkSource.GetFormat(FPB_ALL, FPB_ALL, true, true);
 	
 	// Set all columns in the source worksheet to Text and Numeric
 	// Worksheet transpose does not work if the columns are not all the same format
 	for(int i=0;i<nCols;i++)
 	{
 		Column wCol(wkSource,i);
 		if(wCol.IsValid()==false)
 		{
 			out_str("Column Validation Fails ...");
 			return;
 		}
 		wCol.SetFormat(OKCOLTYPE_TEXT_NUMERIC);
 	}
 	
 	// Transpose source Worksheet ...with the following parameters ...
 	// If successful the Column Longnames wil all be in Column 0 ...
 	int nLabelType = RCLT_LONG_NAME;
	bool bInsertCol = false;
 	bool bUndo = false;
    if(wkSource.Transpose(false, nLabelType, bInsertCol, bUndo)!=0)
    {
    	out_str("Datasheet Transpose fail ...");
 		return;				
    }
    

 	// Get the Contents of Column 0 into a string Vector ...
 	vector<string> vColLongNames;
 	Column wColSource(wkSource,0);
    if(wColSource.IsValid()==false)
 	{
 		out_str("Column Validation Fails ...");
 		return;
 	}
    if(wColSource.GetStringArray(vColLongNames)==false)
    {
   		out_str("Column to String Array Transfer fail");
 		return;  	
    }
    
    // Put the contents of the string Vector into the First column of the Worksheet wkDest.
    Column wColDest(wkDest,0);
    if(wColDest.IsValid()==false)
 	{
 		out_str("Column Validation Fails ...");
 		return;
 	}
 	if(wColDest.PutStringArray(vColLongNames)==false)
 	{
    	out_str("String Array to Column Transfer fail");
 		return;  			
 	}
 	
 	// Reset the Format of the Souurce Worksheet to the original state
 	if(wkSource.Transpose(false, nLabelType, bInsertCol, bUndo)!=0)
    {
    	out_str("Datasheet Transpose fail ...");
 		return;				
    }
    wkSource.SetSize(nRows,nCols);
    wkSource.ApplyFormat(trFormat);
 	out_str("Column Long Names Stored in "+wkDest.GetName());
}

Go to Top of Page

Chris2

20 Posts

Posted - 08/31/2010 :  04:23:06 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Hi,

thanks again! The code is very helpful but I have still two problems.

First problem: The first longname (x-axis) gets not transposed and is nowhere to find in the destination column.

Second problem: The ASCII-Files I'm going to load into the active workbook are very big and have about 20000 rows. At this size origin gets trouble with the code and crashes.

Hope you can help me!
Go to Top of Page

rlewis

Canada
253 Posts

Posted - 09/01/2010 :  01:23:08 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Currently Worksheets are limited to something like 10000 columns. Thus transpose operation on worksheets with more thatn 10000 rows will probably cause to system to grumble ...
Thus one has to extract a subset of the worksheet and perform the transpose on that before extracting the data..

I've thus posted a couple of general purpose functions for excising the a worksheet subset and for doing the transpose along with a couple of examples ... Hope this helps ...


void GetWksColRow(Worksheet &wkSource, vector<string> &vColData, uint RowNumber, bool IsColLabelType=false, bool IncludeFirst=false)
{
	if(wkSource.IsValid()==false)
    {
    	out_str("Source Worksheet validation fail");
    	return;
    }

    // Store the current size of the Source worksheet ...
 	int nRows=wkSource.GetNumRows();
 	int nCols=wkSource.GetNumCols();
 	
 	// Store Format of source Worksheet .....
 	Tree trFormat;
 	trFormat=wkSource.GetFormat(FPB_ALL, FPB_ALL, true, true);
 	
 	// Set all columns in the source and destination Worksheets to worksheet to Text and Numeric
 	// Worksheet transpose does not work if the columns are not all the same format
 	string strColFormat="9";
 	wkSource.SetColFormats(strColFormat,true);
 	
 	bool IgnoreLabelFlag=true;
 	if(IsColLabelType==true)
 	{
 		IgnoreLabelFlag=false;
 	}
 	string strTemp;
 	if(IsColLabelType==true)
 	{
 		// Store the Label Name of the First Column ...
 		Column zCol(wkSource,0);
  		if(zCol.IsValid()==false)
 		{
 			out_str("Column Validation Fails ...");
 			return;
 		}
 		strTemp=zCol.GetLongName();
 	}
 	// Transpose source Worksheet ...with the following parameters ...
 	// if successful the Column Longnames will all be in Column 0 ...
 	int nLabelType =RowNumber;
	bool bInsertCol = false;
 	bool bUndo = false;
    if(wkSource.Transpose(IgnoreLabelFlag, nLabelType, bInsertCol, bUndo)!=0)
    {
    	out_str("Datasheet Transpose fail ...");
 		return;				
    }

    int iSourceCol;
    if(IsColLabelType==true)
    {
    	iSourceCol=0;
    }
 	else
 	{
 		iSourceCol=RowNumber;
 		if(iSourceCol>=wkSource.GetNumRows())
 		{
     		out_str("Row Number Beyond Worksheet end ...");
 			return;							
 		}
 	}

 	// Get the Contents of the Appropriate Column into the String Array vColData
 	Column wColSource(wkSource,iSourceCol);
    if(wColSource.IsValid()==false)
 	{
 		out_str("Column Validation Fails ...");
 		return;
 	}
    if(wColSource.GetStringArray(vColData)==false)
    {
   		out_str("Column to String Array Transfer fail");
 		return;  	
    }
    if(IsColLabelType==true)
    {
    	if(IncludeFirst==true)
    	{
    		// Insert the Label of the First column at the beginning of the string array
    		vColData.InsertAt(0,strTemp);
    	}
    }
 	 //// Reset the Format of the Source Worksheet to the original state
 	if(wkSource.Transpose(IgnoreLabelFlag, nLabelType, bInsertCol, bUndo)!=0)
    {
    	out_str("Datasheet Transpose fail ...");
 		return;				
    }
 	wkSource.SetSize(nRows,nCols);
    wkSource.ApplyFormat(trFormat);
}

bool GetWorksheetSubset(Worksheet wkSource, Worksheet &wkDest, uint iRowBgn, uint iRowEnd, uint iColBgn, uint iColEnd)
{
	if(wkSource.IsValid()==false)
	{
		out_str("Source Worksheet Validation fail");
		return (false);		
	}
	if(iRowBgn>iRowEnd || iRowBgn>=wkSource.GetNumRows() || iColBgn>iColEnd || iColBgn>=wkSource.GetNumCols())
	{
		out_str("Invalid input paraneters");
		return (false);		
	}
	
	int iLastRow=iRowEnd;
	if(iRowEnd>=wkSource.GetNumRows())
	{
		iLastRow=wkSource.GetNumRows()-1;
	}
	int iLastCol=iColEnd;
	if(iColEnd>=wkSource.GetNumCols())
	{
		iLastCol=wkSource.GetNumCols()-1;
	}

	// Create a Worksheet Page
	WorksheetPage wP;
	if(wP.Create("Origin.Otw",CREATE_HIDDEN )==false)
	{
		out_str("Workbook creation fail");
		return (false);
	}
	
	// Attach the Worksheet Object wkDest to Layer 0 of the newly created workbook
	int iLayr=wP.AddLayer(wkSource);
	wkDest=wP.Layers(iLayr);
	if(wkDest.IsValid()==false)
	{
		out_str("Destination Worksheet Validation fail");
		wP.Destroy();
		return (false);			
	}
	
	// Exract a subset of the Source worksheet wkSource into  wkDest
	vector<uint>vnRowIndices;
	vnRowIndices.Data(iRowBgn,iLastRow,1);
	vector<uint>vnColIndices;
	vnColIndices.Data(iColBgn,iLastCol,1);
	if(wkSource.Extract(wkDest,vnRowIndices,vnColIndices)==false)
	{
		out_str("Worksheet data extractcreation fail");
		wP.Destroy();
		return (false);						
	}
	if(wkDest.SetSize(vnRowIndices.GetSize(),vnColIndices.GetSize())==false)
	{
		out_str("Worksheet resize fail");
		wP.Destroy();
		return (false);									
	}
	Layer Ly=wP.Layers(0);
	Ly.Destroy();
	return (true);
}

void GetWksColLongNames()
{
	// Isolate  Worksheet column Long Names and place into a worksheet column;
	Worksheet Wks=Project.ActiveLayer();
	Worksheet wk1;
	vector<string> vColData;
	vColData.RemoveAll();
	GetWorksheetSubset(Wks, wk1, 0, 0, 0, Wks.GetNumCols()-1);
	GetWksColRow(wk1,vColData,RCLT_LONG_NAME, true,true);
	WorksheetPage wP=Wks.GetPage();
	int iLayr=wP.AddLayer();
	Worksheet Wk2(wP.Layers(iLayr));
	if(Wk2.IsValid()==true)
	{
 		string strColFormat="9";
 		Wk2.SetColFormats(strColFormat,true);
 		Column wCol(Wk2,0);
 		wCol.PutStringArray(vColData);
 		out_str("Column Long Names placed in "+Wk2.GetName());
	}
	if(wk1.IsValid()==true)
	{
        // Destroy the Worksheet on which the transpose was performed
		wk1.GetPage().Destroy();
	}
	return;

}


void GetWksRow(int RowIndex)
{
	// Isolate row # RowIndex (zero offset) and place into a worksheet column;
	Worksheet Wks=Project.ActiveLayer();
	Worksheet wk1;
	vector<string> vColData;
	vColData.RemoveAll();
	GetWorksheetSubset(Wks, wk1, RowIndex, RowIndex, 0, Wks.GetNumCols()-1);
	GetWksColRow(wk1,vColData,0, false);
	WorksheetPage wP=Wks.GetPage();
	int iLayr=wP.AddLayer();
	Worksheet Wk2(wP.Layers(iLayr));
	if(Wk2.IsValid()==true)
	{
 		string strColFormat="9";
 		Wk2.SetColFormats(strColFormat,true);
 		Column wCol(Wk2,0);
 		wCol.PutStringArray(vColData);
 		out_str("Row Data  placed in "+Wk2.GetName());
	}
	if(wk1.IsValid()==true)
	{
         // Destroy the worksheet on which the transpose was performed
		wk1.GetPage().Destroy();
	}
	return;
}
Go to Top of Page

Chris2

20 Posts

Posted - 09/01/2010 :  05:53:59 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Hi,

thank you very much for your last post. The code works great!!!!
Nevertheless I have some more questions on how to go on.
In the following lines I’ll try to explain my problem. This has nothing to do with a code I just write this way to make my problem more understandable. I have two workbooks, one with the data I imported to Origin called wksSrc and the other one with all longnames in the first column called wksDest.

For (I = 0; I<number of rows in my column with longnames; I++)
{
if ([wksDest]Sheet!Col(x)[I] = “num”)
{
change [WksSrc]Sheet!Col ( [WksDest]Sheet!Col(longnames)[I] ) into numeric data
}
}

Legend:

X = a column called for example “datatype”. In this column you shall write the datatype you wish in relation to the according longname.

Num = just a example for a possible datatype. There will be a legend available which shows the possible datatypes.



Unfortunately I don’t really know how I can do this that it works properly.

Hope you can help me

Edited by - Chris2 on 09/01/2010 06:15:40 AM
Go to Top of Page

rlewis

Canada
253 Posts

Posted - 09/02/2010 :  03:50:40 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
The codes for setting the column formats are actually listed in the enumeration OKCOLTYPE located in OC_const.h

enum OKCOLTYPE
{
OKCOLTYPE_NUMERIC = 0,
OKCOLTYPE_TEXT,
OKCOLTYPE_TIME,
OKCOLTYPE_DATE,
OKCOLTYPE_MONTH,
OKCOLTYPE_WEEKDAY,
OKCOLTYPE_COLUMN,
OKCOLTYPE_DATASET,
OKCOLTYPE_DATASET_X,
OKCOLTYPE_TEXT_NUMERIC,
OKCOLTYPE_CATEGORICAL,
OKCOLTYPE_PERCENTAGE,
OKCOLTYPE_CURRENCY,
OKCOLTYPE_STROBJ,
};

Thus if you place the appropriate codes for the column datatype in the appropriate rows
of column 2 in the Worksheet wkdest ...
The following function should work ...

void ChangeColFormats(Worksheet &wkSource, Worksheet wkDest)
{
	if(wkDest.IsValid()==true && wkDest.IsValid()==true)
	{
		Dataset dS;
		if(dS.Attach(wkDest,1)==true)
		{
			for(int i=0;i<dS.GetSize();i++)
			{
				Column wCol(wkSource,i);
				if(wCol.IsValid()==false)
				{
					out_str("Column Validate Fail ...");
				}
				wCol.SetFormat(dS[i]);
			}
			out_str("Column Format Change Complete");
			return;
			
		}
		out_str("Dataset column Attach Fail ..");
		return;
	}
	out_str("Worksheet validation Fail ...")
}
Go to Top of Page

Chris2

20 Posts

Posted - 09/02/2010 :  07:19:38 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Sadly the code doesn't work to me.
In column 2 of WksDest I wrote in the appropiate row for example "OKCOLTYPE_WEEKDAY" but Origin doesn't change the column in WksSrc into this datatype. Is there something wrong with the way I filled the cells in WksDest?
Go to Top of Page

Chris2

20 Posts

Posted - 09/02/2010 :  08:54:10 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
I think I get the problem. In the helpfile I found the following:

[input] the column format currently only supports OKCOLTYPE_NUMERIC

Because of this I wanted to try the code with an other example.
I tried to multiply the column in WksSrc with the number I filled in the appropiate row in WksDest.

Here is my code:

void Multiply()
{
Worksheet WksSrc = Project.ActiveLayer();
WorksheetPage wksPage1("Book1");
Worksheet wksDest = wksPage1.Layers("Sheet1");
if(WksSrc.IsValid()==true && wksDest.IsValid()==true)
{
Dataset dS;
if(dS.Attach(WksDest,1)==true)
{
for(int i=0;i<dS.GetSize();i++)
{
Column wCol(WksSrc,i);
if(wCol.IsValid()==true)
{
string strExpression = "x*amp";
string strBeforeScript = "double amp = dS[i]" + "\r\n" + "range x = wCol";
string strFormula = strExpression + STR_COL_FORMULAR_SEPARATOR + strBeforeScript;
wCol.SetFormula(strFormula, AU_AUTO);
wCol.ExecuteFormula();
}
}

}
}
}

However the code doesn't work and I don't know why.
Go to Top of Page

Chris2

20 Posts

Posted - 09/03/2010 :  10:19:36 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Instead of

"string strBeforeScript = "double amp = dS[i]" + "\r\n" + "range x = wCol";"

I also tried this one:

string strBeforeScript = "range x = wCol;" + "\r\n" + "double amp = wksDest.Cell(i, 1);";.

But with no luck either!

Edited by - Chris2 on 09/03/2010 10:26:03 AM
Go to Top of Page

Chris2

20 Posts

Posted - 09/07/2010 :  03:23:22 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Has nobody any ideas? :-(
Go to Top of Page
  Previous Topic Topic Next Topic Lock Topic Edit Topic Delete Topic New Topic Reply to Topic
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
The Origin Forum © 2020 Originlab Corporation Go To Top Of Page
Snitz Forums 2000