Removing Gridlines in EXCEL

[SanjayM]

I am using ODS tagsets.excelxp to output data into excel.

There are two tables that are generated using PROC REPORT.

I am using style for both the table to supress the borderlines.

The first table / PROC REPORT has only two columns and the
second one has 5 columns.

PROBLEM:
Borders are invisible only in the first two columns shown by table 1 and I want to remove borders from the 3rd 4th and 5th column as well from the first table.
This is because it should be consistent with the second table.

[Cynthia_sas]

Hi:
You told us your destination of interest (TAGSETS.EXCELXP), but did not share WHAT style you were using for your output -- a SAS provided style template? a style template that you wrote yourself? Also relevant are which version of SAS you are running and, if you look in the SAS log, what version of TAGSETS.EXCELXP you are using. Since different versions of the TAGSETS.EXCELXP template can possibly produce different outputs, unless someone has the same template, they could get different results from what you see.

Also,I don't understand your two PROC REPORTS -- you say that the first on generates 2 columns and the other generates 5 columns??? If borders are gone in the first 2 columns, it almost sounds like you could be using the new SPANROWS option -- if so, the behavior that you observe is the way that SPANROWS works -- it causes row spanning for ORDER or GROUP items, but the "data" values still have border lines???

After you said the first table only has 2 columns, you then said "I want to remove borders from the 3rd 4th and 5th column as well from the first table" -- but you said the first table only had 2 columns??? This is an instance where seeing the PROC REPORT code would be useful.

If you do not want to share your data and your code, can you replicate your issue using SASHELP data such as SASHELP.CLASS or SASHELP.SHOES or SASHELP.CARS??? It is far easier to attempt a constructive suggestion if there is some context for the question. If you do not want to share your code or your data, then your best resource will be to open a track with Tech Support.

cynthia

[SanjayM]

Hello Cynthia,

The Excel XP tagset version is (SAS 9.1.3, v1.28, 08/29/05).

What I intend to do sounds very simple but can't get it to work:
1. Remove the grid lines.
2. Print the 2 tables with 1pt border width and white cell background.

Here's the sample code:


/************ Code starts **********/
proc template;
define style Styles.Custom1 / store=work.styles;
parent = Styles.Default;
style fonts from fonts /
'TitleFont' = ("Arial, Helvetica, Helv",14pt,Bold Italic)
'TitleFont2' = ("Arial, Helvetica, Helv",12pt,Bold Italic)
'StrongFont' = ("Arial, Helvetica, Helv",12pt,Bold)
'EmphasisFont' = ("Arial, Helvetica, Helv",10pt,Italic)
'headingFont' = ("Arial, Helvetica, Helv",12pt,Bold)
'docFont' = ("Arial, Helvetica, Helv",10pt)
'footFont' = ("Arial, Helvetica, Helv",8pt);
replace color_list /
'fgB2' = blue /* links */
'fgB1' = darkmagenta /* visited links */
'fgA1' = black /* table cell foreground */
'bgA3' = lightgrey /* table cell background */
'bgA1' = lightgrey /* table background - shows through if cellspacing>0 */
'fgR' = darkblue /* row header foreground */
'bgR' = darkgray /* row header background */
'fgA2' = darkblue /* column header foreground */
'bgA2' = darkgray /* column header background */
'fgA' = navy /* foreground for everything else: notes, proc titles, ... */
'bgA' = white /* background for everything else: notes, proc titles, ... */
'bgP' = white /* page background */
;
replace Output from Container /
frame = box /* outside borders: void, box, above/below, vsides/hsides, lhs/rhs */
rules = all /* internal borders: none, all, cols, rows, group */
borderwidth = 1pt /* the width of the borders and rules */
bordercolor = color_list('fgA1') /* the color of the borders and rules */
cellpadding = 1pt /* the space between table cell contents and the cell border */
cellspacing = 0pt /* the space between table cells, allows background to show */;
* Leave code below this line alone ;
style Body from Body /
background = color_list('bgP');
style Contents from Contents /
background = color_list('bgP');
style Data from Data /
font = fonts("docFont");
style SystemTitle from SystemTitle /
font = fonts("TitleFont");
style SystemFooter from SystemFooter /
font = fonts("footFont");
style RowHeader from Header /
font = fonts("headingFont");
style Header from Header /
font = fonts("headingFont");
end;
run;

ODS LISTING CLOSE;

OPTIONS LeftMargin = .5in
RightMargin = .5in
TopMargin = .5in
BottomMargin = .5in;

ods tagsets.excelxp file="&rep_filename."
style=styles.custom1
options(embedded_titles='yes' sheet_interval='none'
width_fudge='0.75' sheet_name="&customer_number"
default_column_width="10,0,8,8,8,8,8,8,8"
FitToPage = 'yes' PAGES_FITWIDTH = '1' Pages_FitHeight = '100'
gridlines = 'yes'
)
;
ods tagsets.excelxp
options(embedded_titles='yes'
sheet_interval='none' sheet_name="&customer_number"
FitToPage = 'yes' PAGES_FITWIDTH = '1' Pages_FitHeight = '100'
gridlines = 'no'
);

proc report data=sashelp.shoes NOHEADER NOWD NOWINDOWS
;
column region product;
run;

proc report data=sashelp.shoes ;
run;
title;
ods tagsets.excelxp close;

/************ Code ends **********/

Also the margins don't seem to apply.

Please help.

Regards
Sanjay

[SanjayM]

Also can you please help me wrapping the text. I cannot get the text to autofit_height.

[Cynthia_sas]

Hi:
Are you also running SAS 9.1.3????

If you want to print the cells with a white cell background, then why does your style define so many table elements with a light gray color??? Also, the default color_list in STYLES.DEFAULT uses these style attributes:
[pre]
class color_list
"Colors used in the default style" /
'fgB2' = cx0066AA
'fgB1' = cx004488
'fgA4' = cxAAFFAA
'bgA4' = cx880000
'bgA3' = cxD3D3D3
'fgA2' = cx0033AA
'bgA2' = cxB0B0B0
'fgA1' = cx000000
'bgA1' = cxF0F0F0
'fgA' = cx002288
'bgA' = cxE0E0E0;
[/pre]


However, you define "new" or "different" style attributes that are not in the above list (such as fgR, bgR, bgP, bgA):
[pre]
replace color_list /
'fgB2' = blue /* links */
'fgB1' = darkmagenta /* visited links */
'fgA1' = black /* table cell foreground */
'bgA3' = lightgrey /* table cell background */
'bgA1' = lightgrey /* table background - shows through if cellspacing>0 */
'fgR' = darkblue /* row header foreground */
'bgR' = darkgray /* row header background */
'fgA2' = darkblue /* column header foreground */
'bgA2' = darkgray /* column header background */
'fgA' = navy /* foreground for everything else: notes, proc titles, ... */
'bgA' = white /* background for everything else: notes, proc titles, ... */
'bgP' = white /* page background */
;
[/pre]

Style attributes in the color_list like fgA and bgA and bgA4 are "expected" in the rest of the template. Usually, folks redefine what is on the right side of the = sign, not what's on the left side. You define several style attributes that are not in STYLES.DEFAULT, which would be OK, if you USED them in your STYLE template. But the only "new" attribute that I see you using is bgP -- where do you instruct the template how to use fgR and bgR, for example??? Here's the code you have for using bgP:
style Body from Body /
[pre]
background = color_list('bgP');
style Contents from Contents /
background = color_list('bgP');
[/pre]

What about bgR and fgR and the other attributes, where do you use them? Your use of the REPLACE statement (if you are using SAS 9.1.3 is incorrect). If you are replacing style elements and attributes, then you need to respecify ALL the style attributes for an element or else inheritance will not resolve correctly. If you are using SAS 9.2, then you should be using the CLASS statement and the new style template syntax instead of the older syntax.

One easy thing to do is to stop using a custom style template and use a style that already has white backgrounds -- such as PRINTER, RTF or JOURNAL -- even MINIMAL style is a better place to start.

What version of SAS are you using and can you explain a bit more why you are not using the regular attributes from the COLOR_LIST????

cynthia

PS -- the system options change the margins for me. Note that you will not SEE the margins being used in the worksheet until you either go into the Margin settings and verify or go into PRINT PREVIEW or actually PRINT the sheet. SAS System Margins used for TAGSETS.EXCELXP output only impact the print view of the sheet. I am using Office2010 and ExcelXP tagset version: v1.94, 09/09/12 with SAS 9.2 M3.

[SanjayM]

Hi Cynthia,

Sorry if that has confused you but I was just trying some options to get it working.
Yes I am using SAS 9.1.3.
I will explain it in a different and more simplier way.

I am using the below code to in a stored process which is called in WRS. So that means the WRS report is based on a stored process.
The intention is to display the data on the screen as well as export it to excel. When I use the below code I get the correct output in excel as expected i.e. with grid borders for the table. However in the web page the borders are a bit thick.
I want thin borders in the excel as good as gridlines.

/****/
ODS LISTING CLOSE;

OPTIONS LeftMargin = 0.5in
RightMargin = 0.5in
TopMargin = 0.5in
BottomMargin = 0.5in ;

ods tagsets.excelxp file="&report_filename."
/* style=styles.printer*/
options(embedded_titles='yes' sheet_interval='none'
width_fudge='0.75' sheet_name="&customer_number"
default_column_width="10,0,8,8,8,8,8,8,8"
FROZEN_HEADERS = 'yes'
FROZEN_ROWHEADERS = 'yes'
gridlines = 'no'
AUTOFIT_HEIGHT = 'yes'
print_footer = 'Page: &P of &N'
)
;
ods tagsets.excelxp
options(embedded_titles='yes'
sheet_interval='none' sheet_name="&customer_number"
FitToPage = 'yes' PAGES_FITWIDTH = '1' Pages_FitHeight = '100'
row_repeat = '1 - 4'
frozen_headers = '1 - 4'
);
title1 height=10pt Account Number: customer_number;
title2 height=10pt Customer Name(s): cust_name;


proc report data=sashelp.shoes split='*'
;
columns region product;

define region / display "Region" center format=$10. style(column)={outputwidth=0.6in just=center borderstyle=solid background=white borderwidth=1pt};
define product / display "Product" format=$100. style(column)={outputwidth=2.7in background=white borderstyle=solid borderwidth=1pt};
where region='Africa';
run;

title;
ods tagsets.excelxp close;
/****/

Margins are all sorted. Thanks.
Please help.

Regards
Sanjay

[Cynthia_sas]

Hi:
Web Report Studio will NOT use your custom style -- unless you are deploying the style as a CSS definition in the XML that runs on the web tier. Web Report Studio only has 4 styles that it uses by default: FESTIVAL, MEADOW, PLATEAU and SEASIDE.

When you run a stored process in Web Report Studio, your output is generated as SASReport XML -- borders and gridlines on the WRS screen are controlled by the interaction between the SASReport XML and the CSS version of the styles that are defined specifically for WRS.

When you do an "Export to Excel" from within WRS, the export happens using WRS and Java technology. There is a huge difference between running TAGSETS.EXCELXP code in local SAS or in EG and running a stored process in Web Report Studio. My recommendation is that you work with Tech Support on this question.

cynthia