| Author |
 Topic  |
|
|
alex_eagle
Austria
36 Posts |
 Posted - 11/13/2002 : 04:48:50 AM
|
Hi everybody,
we use the following script to import Excel data into origin worksheets. The script makes the first row of the data the labels of the worksheet with the doc -e command.
However, when the script is executed more than once, the doc -e command also affects the already imported data.
What commands can be chosen to only affect the data which is about to be imported with the script ?
thanx
Alex
run.section(,Init);
FDLOG.UseGroup(Excel);
if(FDLOG.Open(A))
return 1;
%B=FDLOG.path$;
ExOpen=system.Excel.OpenAsExcel;
ExProm=system.Excel.OpenAsPrompt;
system.Excel.OpenAsExcel=0;
system.Excel.OpenAsPrompt=0;
%B=FDlog.path$;
doc -append %B%A; ;
doc -e W {thisrow=1;
loop(jj,1,wks.ncols) {wks.col$(jj).label$=%(%H,jj,thisrow);}
wks.labels();
mark -d %(%H,1) -b thisrow -e thisrow;}
system.Excel.OpenAsExcel=ExOpen;
system.Excel.OpenAsPrompt=ExProm;
delete -v ExOpen;
delete -v ExProm;
|
|
|
greg
USA
1380 Posts |
Posted - 11/19/2002 : 2:10:49 PM
|
You need to manage this on your own. You could keep track of what has been processed and exclude those worksheets from further processing.
Here is a modification of your script that uses the cell in row 9999 of column 1 as a marker (keeps track that this sheet is done):
run.section(file,Init);
FDLOG.UseGroup(Excel);
if(FDLOG.Open(A)) return 1;
%B=FDLOG.path$;
ExOpen=system.Excel.OpenAsExcel;
ExProm=system.Excel.OpenAsPrompt;
system.Excel.OpenAsExcel=0;
system.Excel.OpenAsPrompt=0;
%B=FDlog.path$;
doc -append %B%A;
doc -e W
{
thisrow=1;
if(wcol(1)[9999]==0/0) // Is the cell empty?
{
wcol(1)[9999]=1; // Now it isn't empty, it's marked
loop(jj,1,wks.ncols)
{
wks.col$(jj).label$=%(%H,jj,thisrow);
}
wks.labels();
mark -d %(%H,1) -b thisrow -e thisrow;
}
}
system.Excel.OpenAsExcel=ExOpen;
system.Excel.OpenAsPrompt=ExProm;
delete -v ExOpen;
delete -v ExProm;
If you have Origin 7 SR2, you can try this:
run.section(file,Init);
FDLOG.UseGroup(Excel);
if(FDLOG.Open(A)) return 1;
%B=FDLOG.path$;
ExOpen=system.Excel.OpenAsExcel;
ExProm=system.Excel.OpenAsPrompt;
system.Excel.OpenAsExcel=0;
system.Excel.OpenAsPrompt=0;
%B=FDlog.path$;
doc -append %B%A;
doc -e W
{
thisrow=1;
if(page.info.scripts.ImportAndMove != 1) // Is this set to 1?
{
page.info.add(Scripts); // Create storage
page.info.scripts.ImportAndMove = 1; // Create and Set
loop(jj,1,wks.ncols)
{
wks.col$(jj).label$=%(%H,jj,thisrow);
}
wks.labels();
mark -d %(%H,1) -b thisrow -e thisrow;
}
}
system.Excel.OpenAsExcel=ExOpen;
system.Excel.OpenAsPrompt=ExProm;
delete -v ExOpen;
delete -v ExProm;
Note that "Info Storage" is a feature that is still in beta, so you might not want to use it for a production script and I won't document the properties and methods here - since they are subject to change.
|
 |
|
|
Mike Buess
USA
3037 Posts |
Posted - 11/19/2002 : 5:10:15 PM
|
Hi Alex,
You could also use the column labels themselves as markers...
doc -e W {
%L=wks.col1.label$;
// is the label empty?
if(%L=="") {
thisrow=1;
loop(jj,1,wks.ncols) {wks.col$(jj).label$=%(%H,jj,thisrow);}
wks.labels();
mark -d %(%H,1) -b thisrow -e thisrow;
};
};
Or you could minimize all worksheets prior to the import and then just operate on non-minimized windows...
doc -e W {win -i};
doc -append %B%A;
doc -e O {
// is this a new worksheet window?
if(exist(%H)==2) {
thisrow=1;
loop(jj,1,wks.ncols) {wks.col$(jj).label$=%(%H,jj,thisrow);}
wks.labels();
mark -d %(%H,1) -b thisrow -e thisrow;
};
};
Mike Buess
Origin WebRing Member |
|
|
|
alex_eagle
Austria
36 Posts |
Posted - 11/20/2002 : 04:02:00 AM
|
Thank you Mike for your suggestions.
I am absolutely sure they work fine.
However, I managed my problem in another way.
I count the worksheets in the project prior to importing and after importing. Then I do the modifications only on the newly imported worksheets using the %Z=doc.wksname$(number)$ command.
But I am not sure if this is a very safe method.
Is it guaranteed that the newly imported worksheets always get the highest numbers? What happens if a worksheet is deleted ? What if some graph windows are also present ?
If there are some possible bugs with this method, I have to use some of your programs.
Alex
|
|
|
|
Mike Buess
USA
3037 Posts |
Posted - 11/20/2002 : 09:07:25 AM
|
Alex,
I think your method will work fine. I haven't used doc.wksname$(number)$, but if it returns wks names in creation order as advertised there should be no problem. Now you don't have to use doc -e W and I assume that your script looks something like this.
...
doc -cw; cc1=count+1;
doc -append %B%A;
doc -cw; cc2=count;
loop (ii,cc1,cc2) {
%W=doc.wksname$(ii)$;
win -o %W {
thisrow=1;
loop(jj,1,wks.ncols) {wks.col$(jj).label$=%(%H,jj,thisrow);}
wks.labels();
mark -d %(%H,1) -b thisrow -e thisrow;
};
};
...
Mike Buess
Origin WebRing Member |
|
|
| |
Topic |
|
|
|
Excel Import