如何用SAS读取加密的excel?
 

如何用SAS读取加密的excel?  


0

请教,如何用SAS读取加密的excel文件呢?密码可以写在程序里,谢谢。

 

1 Answer
0
%macro readpass(xlsfile1,xlsfile2,passwd,outfile,sheetname,getnames);

options macrogen symbolgen mprint nocaps; options noxwait noxsync;

%* we start excel here using this routine here *;

filename cmds dde 'excel|system';

data _null_;
length fid rc start stop time 8;
fid=fopen('cmds','s');
if (fid le 0) then do;
rc=system('start excel');
start=datetime();
stop=start+20;
do while (fid le 0);
fid=fopen('sas2xl','s');
time=datetime();
if (time ge stop) then fid=1;
end;
end;
rc=fclose(fid);
run; quit;

%* then we open the excel sheet here with its password *;

filename cmds dde 'excel|system';

data _null_;
file cmds;
put '[open("'"&xlsfile1"'",,,,"'"&passwd"'")]';
run;

%* then we save it without the password *;

data _null_;
file cmds;
put '[error("false")]';
put '[save.as("'"&xlsfile2"'",51,"")]';
put '[quit]';
run;

%* Then we import the file here *;

proc import datafile="&xlsfile2" out=&outfile dbms=xlsx replace;
%* sheet="%superq(datafilm&i)";
sheet="&sheetname";
getnames=&getnames;
run; quit;

%* then we destroy the non password excel file here *;

systask command "del ""&xlsfile2"" ";

proc contents data=&outfile varnum;
run;

%mend readpass;

%readpass(j:\access\accpcff\excelfiles\passpro.xlsx, /* name of the xlsx 2007 file */
c:\sastest\nopass.xlsx, /* temporary xls file for translation for import */
mypass, /* password of the excel spreadsheet */
work.temp1, /* name of the sas dataset you want to write */
sheet1, /* name of the sheet */
yes) ; /* getnames */

参考论文见附件

This post was modified 2月 前 4 times by xsmile
Share:

 

登录注册