Stored process writing to the browser and excelxp tagset

[mrom34]

Hi,

I am trying to have a stored process write to the browser some javascript function before generating an Excel file using excelXP tagset. It seems to work on chrome but I get an issue  with IE. The stored process basically write my first data step to the beginning of the excel file. I  don't understand why because I redirect the code to Excel only after my first data step.

/* First data step to write to the browser*/

  data _null_;

  file _webout;

   put "<script type='text/javascript' src='http://ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js'></script>";

  put"<script type='text/javascript'>";

  put"function detectIE() {";

      put"var ua = window.navigator.userAgent;";

      put"var msie = ua.indexOf('MSIE ');";

      put"var trident = ua.indexOf('Trident/');";

      put"if (msie > 0) {";

          put" // IE 10 or older => return version number";

          put"return parseInt(ua.substring(msie + 5, ua.indexOf('.', msie)), 10);";

      put"}";

      put"else if (trident > 0) {";

          put"// IE 11 (or newer) => return version number";

          put"var rv = ua.indexOf('rv:');";

          put"return parseInt(ua.substring(rv + 3, ua.indexOf('.', rv)), 10);";

      put"}";

  put"else {";

  put"window.stop();";

  put"document.execCommand('Stop');";

  put"alert('Please use Internet Explorer');";

  put"newWindow = window.open('', '_self', '');";

    put"window.close(newWindow);";

  put" // other browser";

      put"return false;";

  put"}";

  put"}";

  put"window.onpaint = detectIE();";

  put '</script>';

run;

/* Then I redirect the Excel for the Excelxp tagset*/

%let RV=%sysfunc(stpsrv_header(Content-type,application/vnd.ms-excel));

%let RV=%sysfunc(stpsrv_header(Content-disposition,attachment; filename= Histostaffperformance.xml)); 

ODS listing close;

ODS noresults; 

ODS tagsets.ExcelXP

  file=_webout

  STYLE=SRReportStyle_custom2  

      options     (Embedded_titles = 'yes'      

                   Embedded_Footnotes   = 'yes'

                   sheet_name= 'Histo_performance'

                   autofilter= 'yes'

                   frozen_headers= '3'

                   autofit_height= 'yes'

    absolute_column_width= '20,20,15,30,13'

                   );

TITLE1 j=l color=cxF8971D h=14pt bold "Histology - Staff Performance";

PROC REPORT DATA=all NOWD style(Header)=[just=center ];

COLUMN  taskdate histotech testtype steps count;

DEFINE Taskdate  /CENTER 'Date'  ;

DEFINE histotech /CENTER "Histotech"   ;  

DEFINE testtype /CENTER "Test Type"   ;  

DEFINE steps /CENTER "Step"   ;  

DEFINE count /CENTER "# cases"   ;  

run;

RUN;

ods tagsets.excelxp close;

  ods listing;

[jakarman]

You are sending data to _webout that is a basic html approach, the standard with SAS/Intrnet.

What is happening that html-headers and trailers are automatic added in this process.
You have to think/design of a html when you are splitting it up as you have done now.

SAS(R) 9.3 Stored Processes: Developer's Guide (SAS(R) 9.3 Stored Processes: Developer's Guide  http headers)

[mrom34]

Thanks Jaap. I am not sure if what I am trying to do is actually possible. It looks like MIME header are sent to the browser before the data. I don't think I can use multiple MIME header in my program

[boemskats]

Hi mrom34,

What you're doing is possible. I've done something very similar a number of times and know exactly what you're talking about with IE.

I'm assuming what you are after is a 'Would you to open or save this spreadsheet file' type response from your browser when you run your stored process. If this is the case, from my understanding IE especially insists that any MIME content is displayed standalone in an iframe or its own window. Browsers generally won't allow you to reset the header halfway through the transmission of the page, as you're doing above. You're correct - you can't use multiple MIME headers. This is set at the beginning of your _webout stream.

I have a couple of suggestions for you, and these assume that the program is to be used by multiple users concurrently:

- firstly, make this a stored process that recursively calls itself, and works in a way that outputs the HTML if certain (or no) parameters are passed,  and outputs your mime headers and the ODS markup if another paremeter is present. Let me explain with some pseudocode (only the second half is real code):

* if no filenameParameter parameter is passed to this stored process;

%if (%length(&filenameParameter < 3) (or symexist, or whatever works for you)) do;

  myTempFilename = fn || ranuni() || '.xls'; * generate a random filename string with whatever method you choose ;

  ods tagsets file=myTempFilename;

  * write the temporary generated excel filename to disk somewhere in a temp location;

  * output the put statements above to _webout;

  * then output something like this html, no need for a popup;

  put '<iframe src="myserver/SASStoredProcess/do?_program=/myDownloader?filenameParameter=' || myTempFilename || ' "></iframe>';

end;

* if the filename paremeter is present, which it will be when called the second time via iframe reference above;

else if length(filenameParameter > 3) then do;

  filename myFileInput="/myTempFileLocation/&filenameParameter..xml";

  %let RV=%sysfunc(stpsrv_header(Content-type,application/vnd.ms-excel));

  %let RV=%sysfunc(stpsrv_header(Content-disposition,attachment; filename= Histostaffperformance.xml));

  data outmenow;

    length data $1;

    infile myFileInput recfm=n;

    file _webout recfm=n mod;

    input data $char1. @@;

    put data $char1. @@;

  run;

* here you can delete your file myfileinput so that the program cleans up after itself;

end;

This way, your program can take user parameters during the first run, use these to create your output file, and then send the browser an instruction to load that same temporary file as the only output of a stored process stream by calling itself again using that iframe reference and the unique temp file string which tells it this is a download request for a previously constructed file, and because the new request uses fresh mime headers and a simple stream of the file contents, it'll come up as a download request.

Also, rather than wrapping each line of your html/javascript in put statements, it's much easier to write any static code to a static file and read it out using data step. Something like:

  data _null_;

    infile myStaticCodeStoredSomewhere.js;

    file _webout;

    put _infile_;

  run;

... will work well for the first half of your program, in place of the put statements. This way you also don't have to worry about quoting etc., and it's a lot easier to edit.

Hope this helps you and anyone else trying to achieve something similar.

Nik

[jakarman]

Of course you can achieve what you want the question is how.

The stpsrv_header is one option to influence the header.

ODS is full wiht this SAS(R) 9.4 Output Delivery System: User's Guide, Second Edition

no_top_matter no_bottom_matter are the keywords to get a full correct HTML-stream.