| Author |
 Topic  |
|
|
Chris2
20 Posts |
 Posted - 08/27/2010 : 05:37:52 AM
|
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
|
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);
}
|
 |
|
|
Chris2
20 Posts |
Posted - 08/28/2010 : 05:51:21 AM
|
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! |
|
|
|
rlewis
Canada
253 Posts |
Posted - 08/29/2010 : 02:38:19 AM
|
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());
}
|
|
|
|
Chris2
20 Posts |
Posted - 08/31/2010 : 04:23:06 AM
|
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! |
|
|
|
rlewis
Canada
253 Posts |
Posted - 09/01/2010 : 01:23:08 AM
|
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;
}
|
|
|
|
Chris2
20 Posts |
Posted - 09/01/2010 : 05:53:59 AM
|
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 |
|
|
|
rlewis
Canada
253 Posts |
Posted - 09/02/2010 : 03:50:40 AM
|
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 ...")
}
|
|
|
|
Chris2
20 Posts |
Posted - 09/02/2010 : 07:19:38 AM
|
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? |
|
|
|
Chris2
20 Posts |
Posted - 09/02/2010 : 08:54:10 AM
|
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. |
|
|
|
Chris2
20 Posts |
Posted - 09/03/2010 : 10:19:36 AM
|
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 |
|
|
|
Chris2
20 Posts |
Posted - 09/07/2010 : 03:23:22 AM
|
| Has nobody any ideas? :-( |
|
|
| |
Topic |
|
|
|
Row to Column