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
 Extracting a range of data to a new worksheet
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic Lock Topic Edit Topic Delete Topic New Topic Reply to Topic

JulesOZ

18 Posts

Posted - 11/26/2012 :  4:56:28 PM  Show Profile  Edit Topic  Reply with Quote  View user's IP address  Delete Topic
Origin Ver. and Service Release (Select Help-->About Origin): OriginPro8 SR1
Operating System:Win7

Hello!
I have a question about extracting data to a new worksheet. I have a worksheet with a bunch of datasets that are all in the same columns underneath each other separated by a few free rows. I wrote a program to determine the start and the end row of each dataset and now I would like to extract the whole range between the start and end point to a new worksheet.
I found the Worksheet Extract function (BOOL Extract( Worksheet & wksDest, const vector<uint> & vnRowIndices, const vector<uint> & vnColIndices )) but it seems I can only extract specific rows and not a whole range? Or I have to loop through the whole range to get all the data?
Is there any function, where I have the start and the end row of the desired range as input and it transfers the whole data range in between to a new worksheet?

Thanks,
Jules

JessieWoo

China
46 Posts

Posted - 11/27/2012 :  04:04:28 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Hi Jules,

You can try using BOOL Data( int nStartVal, int nEndVal, int nIncVal = 1 ) to generate the range indices for vnRowIndices and vnColInices. And then use Extract to extract the whole range.

For example, if the start row index is 3 and the end row index is 6, use "vnRowIndices.Data(2, 5, 1);" to generate the row indices. The index starts from 0 for Origin C function.

If you want to copy the rows for all column, use "vnColIndices.Data(0, nNumCols - 1, 1);" to generate the column indices.

For more details of the DATA function, please refer to this link.

Best Regard,
Jessie
Originlab Corp.

Edited by - JessieWoo on 11/27/2012 05:03:16 AM
Go to Top of Page

JulesOZ

18 Posts

Posted - 11/27/2012 :  6:50:25 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Dear Jessie,
I've programmed at snip that determines the start and the end row of the data range I want to extract and it writes it into vnRowIndices.
But when I run the Extract function it creates a new worksheet but copies only the very first row of the whole range into the new worksheet.
Here is the snip of code related to that:
vnRowIndices[0]=j; //sets j as starting row for extraction
vnRowIndices[1]=k; //sets k as final row for extraction
vnCols[0] = 0;
vnCols[1] = 1; // only the first two columns will be extracted
wks.Extract(wksdest, vnRowIndices, vnCols); // function that extracts defined rows to a new worksheet wksdest


I checked that the values j and k are set correctly.
What's the difference between setting the values like that and setting the range how you described it? Does the vnRowIndices.Data create a vector of the size of my data range - which in my case would have something like 400 entries and in my version I just entered two specific ones?

Cheers,
Julia
Go to Top of Page

rlewis

Canada
253 Posts

Posted - 11/27/2012 :  11:34:53 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
You may need to do something like ...

void ExtractDataToWks(Worksheet WkSource, vector<uint> StartRow, vector<uint> EndRow, vector<uint> StartCol, vector<uint> EndCol)
{
	if(StartRow.GetSize()!= EndRow.GetSize() || StartRow.GetSize()!= StartCol.GetSize() || StartRow.GetSize()!= EndCol.GetSize())
	{
		out_str("Input vectors StartRow, EndRow, StartCol and EndCol must all be the same size");
		return;
	}
	if(WkSource.IsValid()==false)
	{
		out_str("Ivvalid Source Worksheet");
		return;
	}
	for(int i=0;i<StartRow.GetSize();i++)
	{
		vector<uint>RowsToExtract;
		uint RowBgn=StartRow[i];
		uint RowEnd=EndRow[i];
		if(RowBgn>RowEnd)
		{
			RowBgn=EndRow[i];
			RowEnd=StartRow[i];
		}
		if(RowBgn>=WkSource.GetNumRows() || RowEnd>=WkSource.GetNumRows())
		{
			out_str("Required Row Extract Range is incompatible with Source Worksheet");
			return;
		}
		RowsToExtract.Data(RowBgn,RowEnd,1);
		vector<uint>ColumnsToExtract;
		uint ColStart=StartCol[i];
		uint ColEnd=EndCol[i];
		if(ColStart>ColEnd)
		{
			ColStart=EndCol[i];
			ColEnd=StartCol[i];	
		}
		if(ColStart>=WkSource.GetNumCols() || ColEnd>=WkSource.GetNumCols())
		{
			out_str("Required Column Extract Range is incompatible with Source Worksheet");
			return;
		}		
		ColumnsToExtract.Data(ColStart,ColEnd,1);
		WorksheetPage wP;
		if(wP.Create("Origin.Otw",CREATE_HIDDEN)==false)
		{
			out_str("Workbook Creation Error");
			return;
		}
		Worksheet WkDest(wP.Layers(0));
		if(WkDest.IsValid()==false)
		{
			out_str("Output Worksheet Validation Error ...");
			return;
		}
		if(WkSource.Extract(WkDest,RowsToExtract,ColumnsToExtract)==false)
		{
			out_str("Worksheet::Extract Function Fail ...");
			return;
		}
	}
}

Go to Top of Page

JulesOZ

18 Posts

Posted - 11/28/2012 :  01:58:38 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Thanks rlewis! I guess I'm not experienced enough yet to understand what your code does .
I just figured out how to get my script working! Mostly Jessies reply fixed the main problem.
But I'm sill curious if my guess, why it wasn't working my way is correct...
Go to Top of Page

Penn

China
644 Posts

Posted - 11/28/2012 :  03:16:40 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Hi,

You can upgrade to Origin 8 SR6 from this page, and then try the CopyTo method.

Penn
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