| Author |
 Topic  |
|
|
Thomas83
24 Posts |
 Posted - 07/10/2013 : 06:50:48 AM
|
Origin Ver. 8
Operating System: Win 7
Hi.
I would like to export data from a worksheet. The worksheet has let´s say 6 columns, 1st is X the following are Y. I want to export each Y column together with the X column each to a separate *.dat file. i.e.
Filename1 X Y1
Filename2 X Y2
... .
The code as follows is what I did so fare. I´m not sure if I´m quite close to a solution or if it is nonsense. Could anyone help me please?.
int Worksheet_ExportASCII_Ex1()
{
Worksheet wks = Project.ActiveLayer();
StringArray saFilePaths;
char cSeparator = '\t';
int nR1 = 0, nR2 = -1, nC1 = 0 , nC2 = ii;
int iNumCols = wks.GetNumCols();
for (int ii=0; ii<=iNumCols;ii++)
{
if(wks)
{
string strFile = "C:\System\Test " + ii + ".dat" ;
return wks.ExportASCII(strFile,WKS_EXPORT_SELECTED);
}
else
return -99;
}
} |
|
|
greg
USA
1378 Posts |
Posted - 07/10/2013 : 10:19:03 AM
|
Use of WKS_EXPORT_SELECTED means you need to set the worksheet selection before each export and the selections are (mostly) not contiguous - e.g. the final export occurs with the first column and the last columns (only) selected.
We have a method (SetSelectedRange) that can set such non-contiguous ranges which uses vectors that hold beginning row, beginning column, ending row and ending column.
int WksExp()
{
Worksheet wks = Project.ActiveLayer();
if(wks)
{
// Vectors hold the worksheet selection indeces
vector<int> vnRowStart;
vector<int> vnRowEnd;
vector<int> vnColStart;
vector<int> vnColEnd;
// We only need two selection sets
vnRowStart.SetSize(2);
vnColStart.SetSize(2);
vnRowEnd.SetSize(2);
vnColEnd.SetSize(2);
// Now loop over Y columns
int iNumCols = wks.GetNumCols();
for ( int ii = 1 ; ii < iNumCols ; ii++ )
{
// Always select first column
vnRowStart[0] = 0;
vnRowEnd[0] = -1;
vnColStart[0] = 0;
vnColEnd[0] = 0;
// Select next Y
vnRowStart[1] = 0;
vnRowEnd[1] = -1;
vnColStart[1] = ii;
vnColEnd[1] = ii;
wks.SetSelectedRange(vnRowStart, vnColStart, vnRowEnd, vnColEnd);
string strFile = "D:\Test " + ii + ".dat" ;
// Export defaults to TAB so all we need is this
wks.ExportASCII(strFile,WKS_EXPORT_SELECTED);
}
return 0;
}
else
return -99;
} |
 |
|
|
Thomas83
24 Posts |
Posted - 07/11/2013 : 05:39:26 AM
|
Thanks a lot. This is what I need.
I have 2 more questions.
1)
How can I include the Header of each column?
2)
Is it easy to extend this code to apply for the following?
I have 2 workbooks (WBK1, WBK2), each with 4 worksheets (WKS1-4). Each worksheet with 1 X and 6 Y columns. I want to export the following:
Filename_A1: X Y1 of WKB1 WKS1 and Y1 of WBK2 WKS1 in one single file (X of each WBK or WKS is the same).
Filename_A2: X Y2 of WKB1 WKS1 and Y2 of WBK2 WKS1 in one single file
...
Filename_B1: X Y1 of WKB1 WKS2 and Y1 of WBK2 WKS2
Filename_B2: X Y2 of WKB1 WKS2 and Y2 of WBK2 WKS2
...
(last file)
Filename_D6: X Y6 of WKB1 WKS4 and Y6 of WBK2 WKS4
I suppose with the code above only selected columns of the active workbook are exported.
Thanks again for your help.
Regards |
Edited by - Thomas83 on 07/11/2013 05:53:14 AM |
|
|
|
Thomas83
24 Posts |
Posted - 07/11/2013 : 08:23:57 AM
|
Hi.
My idea was to copy the columns of interest in a temporary workbook and export all columns of this temp. workbook. But when I run the script Origin crashes. Could anyone show me the fault?
void Worksheet_Copy()
{
Worksheet wksSrc1("[WBK1]WKS1"), wksSrc2("[WBK2]WKS1"), wksDest("[Temp]Temp");
int iNumCols = wksSrc1.GetNumCols();
for (int kk=1; kk <= iNumCols ; kk++);
{
//Copy "X" Column of workbook WBK1 and worksheet WKS1
{
int nC1 = 0;
int nC2 = 0;
int nR1 = 0;
int nR2 = wksSrc1.Columns(0).GetNumRows();
int nDestC1 = 0;
wksSrc1.CopyTo(wksDest, nC1, nC2, nR1, nR2, nDestC1, -2);
//Copy "Y" Columns of workbook WBK1 and worksheet WKS1
int nC1 = kk;
int nC2 = kk;
int nR1 = 0;
int nR2 = wksSrc1.Columns(kk).GetNumRows();
int nDestC1 = 1;
wksSrc1.CopyTo(wksDest, nC1, nC2, nR1, nR2, nDestC1, -2);
//Copy "Y" Columns of workbook WBK2 and worksheet WKS1
int nC1 = kk;
int nC2 = kk;
int nR1 = 0;
int nR2 = wksSrc2.Columns(kk).GetNumRows();
int nDestC1 = 2;
wksSrc2.CopyTo(wksDest, nC1, nC2, nR1, nR2, nDestC1, -2);
string strFile = "C:\Test" + (kk) + ".dat" ;
wksDest.ExportASCII(strFile, WKS_EXPORT_ALL)
}
}
PS: I know this code can only be applied for a specified worksheet. I do not loop over all sheets (so far).
Regards |
|
|
|
rlewis
Canada
253 Posts |
Posted - 07/11/2013 : 3:26:35 PM
|
Try the following ...
bool Export_Worksheet_Columns(Worksheet WkSource1, Worksheet WkSource2, string strFileNameStem)
{
/*
Export_Worksheet_Columns Collates and Exports Worksheet Columns as per the following specs
Assumes that the input worksheet contains the same number of columns (at least 2)
Worksheet WkSource1 ... x-Column (Column 0) and y-Columns Column 1 to ...) will be exported
Worksheet WkSource2 ... y-Columns (Column 1 to ...) will be exported
On Success ... returns true
Each OutPut File contains 1 x-Column form WkSource1 (col 0) , 1 y-Column form WkSource1 (Col 1) and 1 y-Column form WkSource1 (Col 2)
On Failure ... return false
*/
if(WkSource1.IsValid()==false || WkSource2.IsValid()==false)
{
out_str("Input Worksheet Validation Fail ...");
return (false);
}
// Create a temporary Workbook to hold the exported data ...
WorksheetPage wpDest;
if(wpDest.Create("Origin",CREATE_TEMP)==false)
{
out_str("Origin Workbook Creation Fail ...");
return (false);
}
Worksheet WksDest(wpDest.Layers(0));
if(WksDest.IsValid()==false)
{
out_str("Destination Worksheet Validation Fail ...");
return (false);
}
int iNumCols = WkSource1.GetNumCols();
for (int i=1; i <= iNumCols ; i++);
{
int nR2 = WkSource1.Columns(0).GetNumRows();
if(WksDest.SetSize(nR2,3)==false)
{
out_str("Temporary Worksheet Resize Fail ...");
return (false);
}
//Copy "X" Column of Worksheet WkSource1 to Column 0 of Temporary Worksheet
if(WkSource1.CopyTo(WksDest, 0, 0, 0, nR2, 0, -1)!=0)
{
out_str("Worksheet (Source1 X-Column) CopyTo Function Fail ...");
return (false);
}
//Copy "Y" Columns of Worksheet WkSource1 to Column 1 of Temporary Worksheet
nR2 = WkSource1.Columns(i).GetNumRows();
if(WkSource1.CopyTo(WksDest, i, i, 0, nR2, 1, -1)!=0)
{
out_str("Worksheet (Source1 Y-Column) CopyTo Function Fail ...");
return (false);
}
//Copy "Y" Columns of Worksheet WkSource2 to Column 2 of Temporary Worksheet
nR2 = WkSource2.Columns(i).GetNumRows();
if(WkSource2.CopyTo(WksDest, i, i, 0, nR2, 2, -1)!=0)
{
out_str("Worksheet (Source2 Y-Column) CopyTo Function Fail ...");
return (false);
}
string strFileName = strFileNameStem+"_col"+(i)+".dat";
if(WksDest.ExportASCII(strFileName, WKS_EXPORT_ALL)==-1)
{
out_str("Worksheet Export ASCII function Fail ...");
return (false);
}
}
return (true);
}
void Collate_Export_Wbk_Data(string strWbk1, string strWbk2)
{
/*
Collate_Export_Wbk_Data Collates and Exports Workbook data as per the following specs
strWbk1 ... Short Name of Workbook 1
strWbk1 ... Short Name of Workbook 2
Requirenents
Each Workbook has the same number of Worksheets
Each Worksheet has the same number of Columns (at least 2)
Filename_A1: X Y1 of WKB1 WKS1 and Y1 of WBK2 WKS1 in one single file (X of each WBK or WKS is the same).
Filename_A2: X Y2 of WKB1 WKS1 and Y2 of WBK2 WKS1 in one single file
...
Filename_B1: X Y1 of WKB1 WKS2 and Y1 of WBK2 WKS2
Filename_B2: X Y2 of WKB1 WKS2 and Y2 of WBK2 WKS2
...
(last file)
Filename_D6: X Y6 of WKB1 WKS4 and Y6 of WBK2 WKS4
*/
WorksheetPage wP1(strWbk1);
if(wP1.IsValid()==false)
{
out_str("Source Workbook Validation Fail ... "+strWbk1);
return;
}
WorksheetPage wP2(strWbk2);
if(wP2.IsValid()==false)
{
out_str("Source Workbook Validation Fail ... "+strWbk2);
return;
}
// Check that the Input Workbooks have the same number of worksheets ...
Collection <Layer> LayCo1, LayCo2;
LayCo1=wP1.Layers;
LayCo2=wP2.Layers;
if(LayCo1.Count() != LayCo2.Count())
{
out_str("Input Error ... Incompatible Source Workbooks ...");
return;
}
for(int i=0; i<LayCo1.Count(); i++)
{
Worksheet WkSource1(wP1.Layers(i));
if(WkSource1.IsValid()==false)
{
out_str("Worksheet Validation Error ... "+strWbk1+"Sheet "+(i));
return;
}
Worksheet WkSource2(wP2.Layers(i));
if(WkSource2.IsValid()==false)
{
out_str("Worksheet Validation Error ... "+strWbk2+"Sheet "+(i));
return;
}
// Check that the Worksheets all have the same number of Columns ....
if(WkSource1.GetNumCols() != WkSource2.GetNumCols())
{
out_str("Input Error ... Incompatible Source Worksheets "+strWbk1+"Sheet "+(i)+" "+strWbk2+"Sheet "+(i));
return;
}
// Check that the Worksheets all have at least two Columns ...
if(WkSource1.GetNumCols()<2)
{
out_str("Input Error ... Insufficient Worksheet Columns "+strWbk1+"Sheet "+(i)+" "+strWbk2+"Sheet "+(i));
return;
}
string strFileNameStem=strWbk1+"_wks"+(i)+"_"+strWbk2+"_wks"+(i);
if(Export_Worksheet_Columns(WkSource1, WkSource2, strFileNameStem)==false)
{
return;
}
}
}
|
Edited by - rlewis on 07/11/2013 4:31:17 PM |
|
|
|
Thomas83
24 Posts |
Posted - 07/12/2013 : 04:30:02 AM
|
Hi rlewis.
Thanks for the reply. I have some difficulties to follow this code (I wrote some codes before, but I´m no expert).
1)
Don´t I have to define what WkSource1 and WkSource2 is, at first?
e.g. take line #20 in your code
"int iNumCols = WkSource1.GetNumCols();"
Does Origin know what WkSource1 is? If not, where do I have to define it?
If I run this code Origin gives: "command error".
2)
What´s wrong with my code (it seems much easier)? If I give "kk" a fixed value (here 37) copying and exporting work porperly. But if I try to loop over all columns (here: int iNumCols = wksSource1.GetNumCols();
for (int kk=1; kk <= iNumCols ; kk++);
)
Origin crashes and I don´t understand why.
void Worksheet_Copy_Exp()
{
Worksheet wksSource1("[workbook1]Sheet1"), wksSource2("[workbook2]Sheet1"), wksDest("[Temp]Tempo");
//int iNumCols = wksSource1.GetNumCols();
//for (int kk=1; kk <= iNumCol ; kk++);
int kk=37;
{
//Copy "X" Column of "[workbook1]Sheet1"
//wksSrcR.CopyTo(wksDest, nC1, nC2, nR1, nR2, nDestC1, -2);
wksSrcR.CopyTo(wksDest, 0, 0, 0, -1, 0, -2);
//Copy "Y" Columns of "[workbook1]Sheet1"
wksSrcR.CopyTo(wksDest, kk, kk, 0, -1, 1, -2);
//Copy "Y" Columns of "[workbook2]Sheet1"
wksSrcT.CopyTo(wksDest, kk, kk, 0, -1, 2, -2);
string strFile = "C:\System\Test" + (kk) + ".dat" ;
wksDest.ExportASCII(strFile, WKS_EXPORT_ALL);
}
} |
|
|
|
rlewis
Canada
253 Posts |
Posted - 07/12/2013 : 05:28:39 AM
|
Hi ...
I tracked down the problem to a couple of typos in the function "Export_Worksheet_Columns". I've corrected these errors in the version of the function listed below. This function is intended to be called by function "Collate_Export_Wbk_Data". Thus you should compile both functions and call the function Collate_Export_Wbk_Data with the names of the two workboons as arguments.
For example if the workbooks concerned are named Book1 and Book2, you can do the following at the command window ..
%P=Book1;%Q=Book2;Collate_Export_Wbk_Data(%P,%Q);
bool Export_Worksheet_Columns(Worksheet WkSource1, Worksheet WkSource2, string strFileNameStem)
{
/*
Export_Worksheet_Columns Collates and Exports Worksheet Columns as per the following specs
Assumes that the input worksheet contains the same number of columns (at least 2)
Worksheet WkSource1 ... x-Column (Column 0) and y-Columns Column 1 to ...) will be exported
Worksheet WkSource2 ... y-Columns (Column 1 to ...) will be exported
On Success ... returns true
Each OutPut File contains 1 x-Column form WkSource1 (col 0) , 1 y-Column form WkSource1 (Col 1) and 1 y-Column form WkSource1 (Col 2)
On Failure ... return false
*/
if(WkSource1.IsValid()==false || WkSource2.IsValid()==false)
{
out_str("Input Worksheet Validation Fail ...");
return (false);
}
// Create a temporary Workbook to hold the exported data ...
WorksheetPage wpDest;
if(wpDest.Create("Origin",CREATE_TEMP)==false)
{
out_str("Origin Workbook Creation Fail ...");
return (false);
}
Worksheet WksDest(wpDest.Layers(0));
if(WksDest.IsValid()==false)
{
out_str("Destination Worksheet Validation Fail ...");
return (false);
}
int iNumCols = WkSource1.GetNumCols();
for (int i=1; i<iNumCols; i++)
{
int nR2 = WkSource1.Columns(0).GetNumRows();
if(WksDest.SetSize(nR2,3)==false)
{
out_str("Temporary Worksheet Resize Fail ...");
return (false);
}
//Copy "X" Column of Worksheet WkSource1 to Column 0 of Temporary Worksheet
if(WkSource1.CopyTo(WksDest, 0, 0, 0, nR2, 0, -1)!=0)
{
out_str("Worksheet (Source1 X-Column) CopyTo Function Fail ...");
return (false);
}
//Copy "Y" Columns of Worksheet WkSource1 to Column 1 of Temporary Worksheet
nR2 = WkSource1.Columns(i).GetNumRows();
if(WkSource1.CopyTo(WksDest, i, i, 0, nR2, 1, -1)!=0)
{
out_str("Worksheet (Source1 Y-Column) CopyTo Function Fail ...");
return (false);
}
//Copy "Y" Columns of Worksheet WkSource2 to Column 2 of Temporary Worksheet
nR2 = WkSource2.Columns(i).GetNumRows();
if(WkSource2.CopyTo(WksDest, i, i, 0, nR2, 2, -1)!=0)
{
out_str("Worksheet (Source2 Y-Column) CopyTo Function Fail ...");
return (false);
}
string strFileName = strFileNameStem+"_col"+(i)+".dat";
if(WksDest.ExportASCII(strFileName, WKS_EXPORT_ALL)==-1)
{
out_str("Worksheet Export ASCII function Fail ...");
return (false);
}
}
return (true);
}
|
|
|
|
Thomas83
24 Posts |
Posted - 07/15/2013 : 03:32:20 AM
|
Hi rlewis.
Great. This is what I wanted to have.
Just for understanding. Do you have any idea why my script doesn´t work?
Best Regards and thank you very much, again. |
|
|
|
rlewis
Canada
253 Posts |
Posted - 07/15/2013 : 5:12:58 PM
|
There are two possibilities ..
First ...
The 7th parameter of the Worksheet CopyTo function should be a positive integer of -1.
Thus in your case the line ...
"wksSrcR.CopyTo(wksDest, 0, 0, 0, -1, 0, -2); "
should be
"wksSrcR.CopyTo(wksDest, 0, 0, 0, -1, 0, -1); "
I'm not sure what this would do but for your requirements the manual requires a value of -1
Second ...
Temporary destination Worksheet should be resized to accommodate the Y column of Workbook2 before executing the the function Worksheet CopyTo function ...
In the code that I posted it was performed by the following ..
int nR2 = WkSource1.Columns(0).GetNumRows();
if(WksDest.SetSize(nR2,3)==false)
{
out_str("Temporary Worksheet Resize Fail ...");
return (false);
}
|
|
|
|
Thomas83
24 Posts |
Posted - 07/16/2013 : 03:10:19 AM
|
Hi.
As far as I can see, I did a simple mistake in my code. I left a ";" after the "for (int i=1; i < iNumCols ; i++)" loop. Without the ";" the code works.
Only one further question. How can I add the header (longname, comments, ...)? Preferably only for the "X" Column of workbook1,worksheet1.
Thank you very much. |
|
|
|
rlewis
Canada
253 Posts |
Posted - 07/16/2013 : 1:15:35 PM
|
The simplest way to do this is to set the dwCntrl parameter of the CopyTo funcrion to CPYT_COPY_COLUMN_LABELS ... (see example shown below)
if(WkSource1.CopyTo(WksDest, 0, 0, 0, nR2, 0, -1,CPYT_COPY_COLUMN_LABELS)!=0)
{
out_str("Worksheet (Source1 X-Column) CopyTo Function Fail ...");
return (false);
}
|
|
|
| |
Topic |
|
|
|
ASCII Export