| Author |
 Topic  |
|
|
spstills23
1 Posts |
 Posted - 05/11/2017 : 3:45:53 PM
|
Origin Ver. 2017 and Service Release (Select Help-->About Origin):
Operating System:
Hello I have what seems to be a simple problem but after looking through others examples I find it may be more difficult for me at least.
I am just trying to create a Table that would be filled with the values within the same row but 2 different columns (U and V) in one active "tab", a simple 1 row x 2 column table.
Now ultimately I would love to be able to pull the data from the same columns but from all 8 tabs that I batch processed into one same table...so columns U and V for tabs 1-8....resulting in a 8 row 2 column table to export into a saved report template I have created for other graphs created previously.
sheet1 col(U) [1] sheet 1 col(V) [1]
sheet2 col(U) [1] sheet 2 col(V) [1]
......and so on.....
Is this possible or am I asking to much? Thanks for the help with this Novice!
|
|
|
JacquelineHe
287 Posts |
Posted - 05/12/2017 : 03:35:15 AM
|
Hi,
If I do not misunderstand your mean, you want to "merge" the dataset from 8 Worksheets (sheet1 to 8) to a single sheet, right?
If so, you can try to use the Append Worksheet tool.
1. Active a sheet, and select Worksheet:Append Worksheet to open the wAppend dialog
2. Add "Sheet1 to Sheet8" for the Worksheets option, and choose "Row" for the Append by drop-down list. Click OK.
Thanks
Jacqueline
OriginLab |
 |
|
|
spstills23
1 Posts |
Posted - 05/12/2017 : 10:30:10 AM
|
Thanks for the help!
I am asking to not create a brand new sheet but rather that information from sheets 1-8 to be generated into a table. So I am just trying to create a table that I could export into a report sheet template.
So I want the values in the first row of columns U and V from each of the 8 sheets to be generated into this one 8x2 table.
I'm just hoping its possible to do!
Thanks! |
|
|
|
spstills23
1 Posts |
Posted - 05/12/2017 : 10:32:24 AM
|
Thanks for the help!
I am asking to not create a brand new sheet but rather that information from sheets 1-8 to be generated into a table. So I am just trying to create a table that I could export into a report sheet template.
So I want the values in the first row of columns U and V from each of the 8 sheets to be generated into this one 8x2 table.
I'm just hoping its possible to do!
Thanks! |
|
|
|
Hideo Fujii
USA
1582 Posts |
Posted - 05/12/2017 : 12:02:46 PM
|
Hi spstills23,
Maybe you can try the following script?
To do so, first select a cell block (in any sheet) in your source worksheet, then run this script.
It collects the block in every sheet at the highlighted position. //////////////////////////////////////////////////////
bc1=wks.c1; //highlighted columns from
bc2=wks.c2; //highlighted columns to
br1=wks.r1; //highlighted rows from
br2=wks.r2; //highlighted rows to
nbc=bc2-bc1+1; //number of columns in block
nbr=br2-br1+1; //number of rows in block
bn1$=%H; //input book name
newbook; //make a new book
wks.ncols=nbc; //new book gets columns
bn2$=%H; //output book name
win -a %(bn1$); //activate input book
rowpos=0; //output row position
Doc -e LW { //Loop every sheet in book
range -v rb1=[bn1$]$(page.active)!$(bc1)[br1]:$(bc2)[br2]; //input block
range -v rb2=[bn2$]1!1[rowpos+1]:$(nbc)[rowpos+nbr]; //output block
rb2=rb1; //copy the block
rowpos=rowpos+nbr; //update row position
}
//////////////////////////////////////////////////////
Hope this does what you wanted.
--Hideo Fujii
OriginLab |
Edited by - Hideo Fujii on 05/12/2017 12:07:02 PM |
|
|
|
jdf726
78 Posts |
Posted - 05/12/2017 : 6:48:19 PM
|
I spent about five minutes last night wondering if I could answer this question (I was also thinking about the 'document' loop over sheets) but realised its was better if an expert advised!
This post is a reminder of concepts that are needed for data collation, and of the things that I always get wrong - the '$' symbols...
I am not sure I understand this line.
range -v rb1=[bn1$]$(page.active)!$(bc1)[br1]:$(bc2)[br2]; //input block
What is the -v switch for?
The square brackets around br1/br2 are to denote a 'row' index, but why does bc2 have $() around it? (bc1 and br1 are both integers)
To run this my understanding was that you could either
a) run from the script panel in one of the worksheets
b) paste and run in the command window
c) paste into an .ogs file and call it with the 'run' command.
Is it actually recommended to organise your stuff into .ogs file to make it more re-usable, rather than just pasting?
(You can also pass 'arguments' in the run command as an alternative to the 'column selection' way of controlling which columns are copied).
jdf726
quote:
Originally posted by Hideo Fujii
Hi spstills23,
Maybe you can try the following script?
To do so, first select a cell block (in any sheet) in your source worksheet, then run this script.
It collects the block in every sheet at the highlighted position. //////////////////////////////////////////////////////
bc1=wks.c1; //highlighted columns from
bc2=wks.c2; //highlighted columns to
br1=wks.r1; //highlighted rows from
br2=wks.r2; //highlighted rows to
nbc=bc2-bc1+1; //number of columns in block
nbr=br2-br1+1; //number of rows in block
bn1$=%H; //input book name
newbook; //make a new book
wks.ncols=nbc; //new book gets columns
bn2$=%H; //output book name
win -a %(bn1$); //activate input book
rowpos=0; //output row position
Doc -e LW { //Loop every sheet in book
range -v rb1=[bn1$]$(page.active)!$(bc1)[br1]:$(bc2)[br2]; //input block
range -v rb2=[bn2$]1!1[rowpos+1]:$(nbc)[rowpos+nbr]; //output block
rb2=rb1; //copy the block
rowpos=rowpos+nbr; //update row position
}
//////////////////////////////////////////////////////
Hope this does what you wanted.
--Hideo Fujii
OriginLab
|
|
|
|
Hideo Fujii
USA
1582 Posts |
Posted - 05/15/2017 : 12:26:47 PM
|
Hi jdf726,
> range -v rb1=[bn1$]$(page.active)!$(bc1)[br1]:$(bc2)[br2]; //input block
> What is the -v switch for?
Range notation's -v option allows to "absorb" the shape difference between the source and
destination of the range assignment.
See: http://www.originlab.com/doc/LabTalk/guide/Range-Notation#Option_Switch_-vOption_Switches
So, in general it can make the code more robust. In my specific case, if all the sheets have
the same number of rows, and same number of columns, it shouldn't matter whether -v option is issued,
or not. But, if some cells in some sheet are out of the highlighted block, you can avoid, I thought, the error.
> The square brackets around br1/br2 are to denote a 'row' index, but why does bc2 have $() around it?
> (bc1 and br1 are both integers)
Since that is the place for the "sheet", and Origin always expects a string (like "Sheet1") there.
(Remember that LabTalk is an interpreter.)
> To run this my understanding was that you could either
> a) run from the script panel in one of the worksheets
> b) paste and run in the command window
> c) paste into an .ogs file and call it with the 'run' command.
> Is it actually recommended to organise your stuff into .ogs file to make it more re-usable, rather than just pasting?
There are variety of places for script to place and run such as: 1) From Script window or Command window:
http://www.originlab.com/doc/LabTalk/guide/From-Script-and-Command-Window
2) From a custom menu:
http://www.originlab.com/doc/Origin-Help/CustomMenuOrganizer-Dialog#The_Add_Custom_Menu_Panel
3) From "Custom Routine" button:
http://www.originlab.com/doc/LabTalk/guide/Getting-Started-with-LT#Custom_RoutineCustom_Routine_Button
4) From OGS script file: (by run.section(...) command, or further customization for app or tool button)
http://www.originlab.com/doc/LabTalk/guide/From-Files
5) From an app icon:
http://www.originlab.com/doc/APP/App-Development#The_Process_of_Creating_an_App
6) From custom tool button:
http://www.originlab.com/doc/LabTalk/guide/From-a-Toolbar-Button
7) From a text label/graphic object on a graph or a worksheet (which can be saved as a template):
http://www.originlab.com/doc/LabTalk/guide/From-Graphical-objs
8) Others:
http://www.originlab.com/doc/LabTalk/guide/Running-Scripts
Creating .OGS script file is certainly the most general and versatile way to store your collection of scripts
as you can "call" anytime from Script/Command window, from other script, from app/tool button, etc.
So, having .OGS files is a good choice, though not necessarily been "recommended".
If you want to run a script across Origin sessions from user interface, but don't want to make a button graphics,
you can go to the custom menu, or the Custom Routine button (if you have only one script).
My personal preference to prototype or run an ad hoc simple script is to use the Script window.
Hope this information helps.
--Hideo Fujii
OriginLab |
|
|
| |
Topic |
|
|
|
Creating Tables