06-22-2016 08:01 PM
Is there any way to update the named range within excel using xlsx in EG?
I have a client whose EG is running on linux server with no PCfile server installed. I want to export the data into a pre-defined Excel template without using proc export (you can't specify cell range) or ODS as I need to load the data to the proper cell ranges (defined by named range). I need to maintain the excel format while update the data in the named range. Is it possible to achieve that? (Client does not have AMO)
06-23-2016 04:26 PM
Excel: Replacing data in a named range while maintaing formatting without SAS Access to PC Files This post calls R to replace data in the named range. You may need full SAS for this, especially if you are locked out of a an operating system. HAVE * create excel file with named range "class"; * I am doing this to provide a excel file with a named range; * you already have the a excel sheet with named range; * create named range "class"; %utlfkil(d:\xls\class.xlsx); * delete if exists; libname xls "d:\xls\class.xlsx"; data xls.class; set sashelp.class; ;run;quit; proc contents data=xls._all_; ;run;quit; libname xls clear; /* Libref XLS Engine EXCEL Physical Name d:\xls\class.xlsx User Admin DBMS Member Obs, Entries Member # Name Type or Indexes Vars Label Type 1 class DATA . 5 TABLE 2 class$ DATA . 5 TABLE to 40 obs from d:\xls\class.xlsx total obs=19 Rows 1-6 are highlighted in yellow Obs NAME SEX AGE HEIGHT WEIGHT 1 Alfred M 14 69.0 112.5 2 Alice F 13 56.5 84.0 3 Barbara F 13 65.3 98.0 4 Carol F 14 62.8 102.5 5 Henry M 14 63.5 102.5 .... */ WANT (with rows 1-6 highlighted in yellow - retained) (sorted by descending name) NAME SEX AGE HEIGHT WEIGHT 1 William M 15 66.5 112.0 2 Thomas M 11 57.5 85.0 3 Ronald M 15 67.0 133.0 4 Robert M 12 64.8 128.0 5 Philip M 16 72.0 150.0 SOLUTION * Create some replacement data; * dataset to overwrite named region data keeping your customizations; proc sort data=sashelp.class out="d:/sd1/newclass.sas7bdat"; by descending name; run;quit; * overwite data in named range class; %utl_submit_r64(%nrbquote( library(XLConnect); library(haven); newclass<-read_sas("d:/sd1/newclass.sas7bdat"); wb <- loadWorkbook("d:/xls/class.xlsx"); /* set Style says to retain formatting */ setStyleAction(wb,XLC$"STYLE_ACTION.NONE"); writeNamedRegion(wb,newclass,name="class"); saveWorkbook(wb); )); %macro utl_submit_R64(pgmx)/des="Semi colon separated set of R commands"; * write the program to a temporary file; filename r_pgm temp lrecl=32766 recfm=v; data _null_; file r_pgm; pgm=compbl("&pgmx"); put pgm; putlog pgm; run; %let __loc=%sysfunc(pathname(r_pgm)); * pipe file through R; filename rut pipe "c:\Progra~1\R\R-3.2.4\bin\x64\R --quiet --no-save < &__loc"; data _null_; file print; infile rut; input; put _infile_; putlog _infile_; run; filename rut clear; filename r_pgm clear; %mend utl_submit_r64;