How to clear the fill of the whole Excel workbook?

[angeliquec]

Hi!

I need to output a table using ODS TAGSETS.EXCELXP. I'm using the parent style minimal, however the "no fill" option is only applied in the table, but not outside the table. The background of the workbook is just all white outside the table. May I know how can the "no fill" option be applied to the whole Excel workbook when the SAS table is output?

Using the proc export dbms = excel is not an option since this prevents the export of SAS formats.

Thank you!

[Reeza]

This currently isn't a feature available in Tagsets.ExcelXP

I believe ODS HTML can generate a file that will export SAS formats and maintain the no fill option. Still not a native Excel file but an option.

[angeliquec]

I'm just thinking --- can this be done thru a VB script?

[RW9]

Hi,

Well, you can just post-processes the output XML.  If you right click on the output file and open in Notepad you will see it is just a text file in XML format which Excel interprets.  You can manipulate this file after export as per any text file.  To get rid of the solid white background look for:

<Style ss:ID="_body">

<Interior ss:Pattern="Solid" />

<Protection ss:Protected="1" />

Its the solid part which causes the issue.  So do:

ods tagsets.excelxp file="s:\temp\rob\tt.xls" style=minimal;

proc report data=sashelp.class nowd;

  columns name sex age height weight;

run;

ods tagsets.excelxp close;

data _null_;

  infile "s:\temp\rob\tt.xls" lrecl=32767 dsd missover;

  file "s:\temp\rob\tx.xls";

  length buffer $200.;

  input buffer $;

  if index(buffer,'<Interior ss:Pattern="Solid" />') > 0 then buffer=tranwrd(buffer,"Solid","None");

  put buffer;

run;

[data_null__ · Accepted solution]

Your program seems to work OK for this test data but your choice of options is a bit disconcerting; DSD and MISSOVER should not be used and the LRECL should be the same for FILE and INFILE.  You can modify the BUFFER directly it is called _INFILE_ by default (there is an option to change the name).

ods tagsets.excelxp file="~/tt.xls" style=minimal;
proc report data=sashelp.class(obs=2) nowd;
   columns _all_;
   run;
ods tagsets.excelxp close;
data _null_;
   infile "~/tt.xls" lrecl=1024;
   file   "~/tx.xls" lrecl=1024;
   input;
   if find(_infile_,'<Interior ss:Pattern="Solid" />','i')
      then _infile_=tranwrd(_infile_,"Solid","None");
   put _infile_;
   run;

[RW9]

Cut and paste from another program which uses different data.  Didn't even think about the reading, main point I was trying to make was to just text replace that XML option.

[data_null__]

Yes I understand your point but I don't expect the OP is an expert at INFILE/FILE and the rest.