The SAS Output Delivery System and reporting techniques

How to match Excel default appearance with ExcelXP tagset

Reply
Occasional Contributor
Posts: 18

How to match Excel default appearance with ExcelXP tagset

I'm trying to convert some reports that use Proc Export to create multi-sheet Excel workbooks. Most users then use a macro or macros to apply formatting to the Excel file.

The Proc Export output is completely unformatted except for numeric formats (dates, currency, etc.). No background color, no borders, and the area outside the exported data is formatted (or non-formatted) the same way. Print Preview shows no cell borders in the data area or outside the data area of a spreadsheet.

I've tried this code that I found on this forum to create a template that somewhat mimics what Excel does:

proc template;
define style styles.mymin;
parent = styles.Statistical;
replace Body from Body/
background=_undef_;
replace table from Output /
background=_undef_
foreground=_undef_
borderwidth=_undef_
font_size=10pt;
replace Header from Header /
background=_undef_
foreground=_undef_
borderwidth=_undef_
font_size=10pt;
replace RowHeader from RowHeader /
background=_undef_
foreground=_undef_
borderwidth=_undef_
font_size=10pt;
replace Data from Data /
background=_undef_
foreground=_undef_
borderwidth=_undef_
font_size=10pt;
end;
run;

When I look at output generated by the ExcelXP tagset and this style, the data portion looks like I would expect - the familiar Excel grid is there, but the area outside the data block is solid white, without the cell grid; it has the appearance of having a solid white background. Additionally, if I select a range of empty cells and apply "no fill" to it, it reverts to the "default" Excel look. So the entire spreadsheet is filled with some color, or there is some Fill setting there that gives the appearance of solid white fill. And Print Preview reveals that the Data area actually has light grey borders around and inside the range. This is confirmed in the XML file:

(right, top, and bottom borders are the same)

If I save the XML spreadsheet as an XLS and then re-open, the appearance does not change.

Is it possible to use ODS to create a spreadsheet that has no borders (except for ones I explicitly want around the header cells) and no fill. I understand, this isn't producing an actual Excel file, but an XML file that Excel can "play nice" with - just trying to get our output as close as possible to what is produced by our current Export + Excel Macro method.
SAS Super FREQ
Posts: 8,743

Re: How to match Excel default appearance with ExcelXP tagset

Hi:
When I go into Excel -without- SAS and type some numbers into cells and then save the file as Excel 2003 XML, the ONLY style information in the file is this:
[pre]
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Calibri" x:Family="Swiss" ssSmiley Frustratedize="11" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
[/pre]

To me, this says that Excel uses this style section for everything. I do not know what <Interior/> or <Borders/> empty tags are doing. Frequently, I find that empty tags in Excel Spreadsheet Markup Language XML are sometimes used as on/off switches.

When I use your template, I see the behavior I expect from SAS -- the cells in the tabular output have interior table lines and the rest of the worksheet does NOT have interior table lines. ODS TAGSETS.EXCELXP works by a different mechanism than PROC EXPORT. You can't compare the two, because it really is apples (PROC EXPORT writing to BINARY mode XLS file format ) and oranges (ODS writing to TEXT mode XML format files that Excel knows how to render).

The underlying assumptions of these two methods is different. The PROC EXPORT method has the underlying assumption that you want the data treated as -data- ...so, SAS has no way to store color and font information in a dataset, therefore, PROC EXPORT does not pass any color or font information to Excel.

On the other hand, one of the underlying assumptions of ODS TAGSETS.EXCELXP is that you WANT the ODS style template used to format the sheets. So, for example, if you took advantage of ODS TAGSETS.EXCELXP and the ability to use style templates for coloring the header and doing traffic lighting, etc, you would find that ODS can do a lot and you might not need your Excel macros anymore.

If you like the look and feel of PROC EXPORT, you -can- create multi-sheet workbooks with PROC EXPORT using code similar to what is shown below. Note, that for ease of understanding, I use PROC SORT to create 3 datasets and then write each dataset to a separate worksheet in the EXP_METHOD.XLS workbook. This is a true, binary Excel workbook with 3 sheets....and each sheet is named with the sheet= option.

Otherwise, if you want to use TAGSETS.EXCELXP, I'm not sure there's a style template way to do what you want to do. This is where I would recommend that you work with Tech Support. What you want to do may involve changing the XML that's written by TAGSETS.EXCELXP -- either to include an explicit Default style section in the XML or to reference a style section like this. Changing the tagset template is harder than changing a style template, which is another reason to work with Tech Support.

cynthia

[pre]
** Proc Export method to create multiple sheets;

proc sort data=sashelp.shoes out=Pshoes;
where region = 'Pacific';
by region subsidiary;
run;

proc sort data=sashelp.shoes out=WEshoes;
where region = 'Western Europe';
by region subsidiary;
run;

proc sort data=sashelp.shoes out=Drshoes;
where product contains 'Dress';
by region subsidiary;
run;

proc export data=Pshoes
outfile= "exp_method.xls"
dbms=excel2002 replace;
sheet="Pacific";
run;

proc export data=WEshoes
outfile= "exp_method.xls"
dbms=excel2002 replace;
sheet="West_Eur";
run;

proc export data=DRshoes
outfile= "exp_method.xls"
dbms=excel2002 replace;
sheet="Dressy";
run;

[/pre]
New Contributor
Posts: 2

Re: How to match Excel default appearance with ExcelXP tagset

The original poster has come across the same problem I was having - the Excel spreadsheet is filled and the output table itself has borders which make it look like the grid is showing through.

You can turn the borders off in the output style

replace Output /
rules = NONE;

And you can turn off the fill across the whole spreadsheet by setting the background to transparent

replace Body from Body/
background=transparent;

The second option is detailed in Sample 38282: Add Microsoft Excel grid lines when using the ExcelXP tagset.
http://support.sas.com/kb/38/282.html
Ask a Question
Discussion stats
  • 2 replies
  • 420 views
  • 0 likes
  • 3 in conversation