The Origin Forum
File Exchange
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
 Origin Forum
 loop over workbooks freezes software
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic Lock Topic Edit Topic Delete Topic New Topic Reply to Topic

GaussianFit

38 Posts

Posted - 08/26/2012 :  03:11:50 AM  Show Profile  Edit Topic  Reply with Quote  View user's IP address  Delete Topic
Origin Ver. and Service Release (Select Help-->About Origin): 8.0 SR6
Operating System: Windows XP

I'm trying to run a LabTalk script that loop over all the workbooks in the project folder:

doc -ef W {
WorkBookProcess;
}

The loop script calls an Origin C function "WorkBookProcess", which extracts data from the active workbook by selecting rows that have column A values not equal 0 or 10 or 20 ..., then the selected rows are extracted to a new worksheet in the same workbook.

The script works well for a few workbooks, somehow the software freezes when the project has several hundred workbooks to process. One thing I did noticed is that each time a workbook is processed, Origin automatically recovers it to original size from minimized status. This could slow down the process. Is there a way to keep the workbooks minimized during the loop process? Another thing is that SelectRows uses AND operation of multiple conditions like the following code:

string strCondition = "a != 0 && a != 10 && a != 20 && ...";
string strLTRunBeforeloop = "range a=1"; // define range objects, a is column 1 and b is column 2.
vector<uint> vnRowIndices;
int nn = wks.SelectRows(strCondition, vnRowIndices, 0, -1, -1, strLTRunBeforeloop);

It could be faster by declare an array of row indices, then directly delete rows having these indices in the original worksheet instead of adding a new worksheet.

I'm grateful to any suggestion on how to speed things up.

rlewis

Canada
253 Posts

Posted - 08/26/2012 :  5:31:05 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Since most of your operations are coded in OriginC, you might as well code the entire process in OriginC which would also make it easier for you to manage status of the pages in the document.
Try something like ....

void WkbProcess()
{
	foreach (PageBase pB in Project.Pages)
	{
		if(pB.GetType()==EXIST_WKS)
		{
			WorksheetPage Wpg(pB);
			if(Wpg.IsValid()==false)
			{
				our_str("Error Accessing WorksheetPage : "+pB.GetName();
				return;
			}
			// Edit Code in your WorkBookProcess function so that it operates on the WorksheetPage Object "Wpg"
			WorkBookProcess();
			pB.SetShow(PAGE_HIDDEN); // Set the Worksheet page to Hidden ....
		}	
	}
}


Also I recommend that you recode you WorkBookProcess function to include tests as to whether the desired operations were successful or not.
These tests would assist in the debugging of your code and may help you to nail down what the problem really is ..
Go to Top of Page

GaussianFit

38 Posts

Posted - 08/26/2012 :  6:31:37 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
quote:
Originally posted by rlewis

Since most of your operations are coded in OriginC, you might as well code the entire process in OriginC which would also make it easier for you to manage status of the pages in the document.
Try something like ....

void WkbProcess()
{
	foreach (PageBase pB in Project.Pages)
	{
		if(pB.GetType()==EXIST_WKS)
		{
			WorksheetPage Wpg(pB);
			if(Wpg.IsValid()==false)
			{
				our_str("Error Accessing WorksheetPage : "+pB.GetName();
				return;
			}
			// Edit Code in your WorkBookProcess function so that it operates on the WorksheetPage Object "Wpg"
			WorkBookProcess();
			pB.SetShow(PAGE_HIDDEN); // Set the Worksheet page to Hidden ....
		}	
	}
}


Also I recommend that you recode you WorkBookProcess function to include tests as to whether the desired operations were successful or not.
These tests would assist in the debugging of your code and may help you to nail down what the problem really is ..



Hi rlewis,

Thank you very much for the tips, when I tried to compile the code, somehow it shows some strange error message:

Error, Variable "out_str("Error Accessing WorksheetPage : "+pB.GetName(); return;" not declared
Go to Top of Page

rlewis

Canada
253 Posts

Posted - 08/26/2012 :  10:24:38 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Oops acouple of typos on my part .. The line should read ..

out_str("Error Accessing WorksheetPage : "+pB.GetName());
Go to Top of Page

GaussianFit

38 Posts

Posted - 08/27/2012 :  03:34:21 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
quote:
Originally posted by rlewis

Oops acouple of typos on my part .. The line should read ..

out_str("Error Accessing WorksheetPage : "+pB.GetName());




After adding the missing ")", I got another error message "Function or variable WorkBookProcess not found" although I have saved function WorkBookProcess as a separate .C file into the same workspace. This error message disappeared after I placed function WorkBookProcess into the same .C file, but nothing happened after running it - no new worksheet got added. Not sure what went wrong. Here is my WorkBookProcess() function:

void WorkBookProcess()
{
Worksheet wks = Project.ActiveLayer();
if( !wks )
return;


// prepare test condition string
string strCondition = "a != 0 && a != 10 && a != 20 && a != 30 && a != 40 && a != 50"; //Note: here used "&&" not "AND". And || for "OR".
string strLTRunBeforeloop = "range a=1"; // define range objects, a is column 1 and b is column 2.

// check worksheet with strCondition and result the number of rows base on the condition expression.
// vnRowIndices included the row index as result.
vector<uint> vnRowIndices;
int nn = wks.SelectRows(strCondition, vnRowIndices, 0, -1, -1, strLTRunBeforeloop);

if(nn < 0)
{
out_str("User cancel");
return;
}

if(nn == 0)
{
out_str("No matching row");
return;
}

printf("%d rows selected with condition -- %s\n", nn, strCondition);


// Get the Workbook object containing the Source Worksheet
WorksheetPage wksPage=wks.GetPage();
if(wksPage.IsValid()==false)
{
out_str("Worksheet page validation fail");
return;
}

// Add a new sheet to the current workbook
int iWks=wksPage.AddLayer(wks.GetName()+" New Sheet");
// Access the new worksheet
Worksheet wksResult = wksPage.Layers(iWks);
if(wksResult.IsValid()==false)
{
out_str("Workbook Layer validation fail");
return;
}
// Set the new worksheet to be active
set_active_layer(wksResult);

// Extract data to new worksheet with column indices "vsCols" and row indices "vnRowIndices"
vector<uint> vnCols = {0,1,2,3,4}; // set column indices to be extracted
BOOL bRet = wks.Extract(wksResult, vnRowIndices, vnCols);

if(bRet)
printf("Extract selected rows to [%s]!%s.\n", wksResult.GetPage().GetName(), wksResult.GetName());

}
Go to Top of Page

rlewis

Canada
253 Posts

Posted - 08/27/2012 :  04:31:45 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Try Something like ....

void WkbProcess()
{
	foreach (PageBase pB in Project.Pages)
	{
		if(pB.GetType()==EXIST_WKS)
		{
			WorksheetPage Wpg(pB);
			if(Wpg.IsValid()==false)
			{
				out_str("Error Accessing WorksheetPage : "+pB.GetName());
				return;
			}
			// Edit Code in your WorkBookProcess function so that it operates on the WorksheetPage Object "Wpg"
			if(WorkBookProcess(Wpg)==false)
			{
				out_str("WorkbookProcess Fail ....")
			}
			return;
			pB.SetShow(PAGE_HIDDEN); // Set the Worksheet page to Hidden ....
		}	
	}
}


bool WorkBookProcess(WorksheetPage & wPage)
{ 
	if(wPage.IsValid()==false)
	{
		out_str("WorksheetPage validation Fail");
		return (false);
	}

	// Assuming that the target worksheet is workbook Layer 0
	Worksheet Wks=wPage.Layers(0);
	if(!Wks)
	{
		out_str("Worksheet Validation Fail");
		return (false);
	}

// prepare test condition string
	string strCondition = "a != 0 && a != 10 && a != 20 && a != 30 && a != 40 && a != 50"; //Note: here used "&&" not "AND". And || for "OR".
	string strLTRunBeforeloop = "range a=1"; // define range objects, a is column 1 and b is column 2.

	// check worksheet with strCondition and result the number of rows base on the condition expression.
	// vnRowIndices included the row index as result.
	vector<uint> vnRowIndices;
	int nn = Wks.SelectRows(strCondition, vnRowIndices, 0, -1, -1, strLTRunBeforeloop);

	if(nn < 0)
	{
		out_str("User cancel");
		return (false);
	} 

	if(nn == 0)
	{
		out_str("No matching row");
		return (false);
	} 
	printf("%d rows selected with condition -- %s\n", nn, strCondition);

// Add a new sheet to the current workbook
	int iWks=wPage.AddLayer();
// Access the new worksheet
	Worksheet wksResult = wPage.Layers(iWks);
	if(wksResult.IsValid()==false)
	{
		out_str("Workbook Layer validation fail");
		return (false);
	} 

// Extract data to new worksheet with column indices "vsCols" and row indices "vnRowIndices"
	vector<uint> vnCols = {0,1,2,3,4}; // set column indices to be extracted
	if(Wks.Extract(wksResult, vnRowIndices, vnCols)==false)
	{
		out_str("");
		return (false);
	}
	printf("Extract selected rows to [%s]!%s.\n", wPage.GetName(), wksResult.GetName());
	return (true);
}


Also ..Please check your email
Go to Top of Page

GaussianFit

38 Posts

Posted - 08/28/2012 :  01:34:31 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
quote:
Originally posted by rlewis

Try Something like ....

Also ..Please check your email



The above code also hang up during execution. It looks that the problem lies in the workbook names. I have been using data file names like "R=1234ohmOtherstringI=56uA.dat" as the long names for workbooks, while Origin automatically generate a short name that is truncated from the file name. If I use very short names for workbooks the code works fine but for long names it will freeze up after processing a few workbooks. Long names are needed for data bookkeeping so not sure how to fix this problem.
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 © 2008 Originlab Corporation Go To Top Of Page
Snitz Forums 2000