How to - Make Fit Reports Widths and Heights for Tagset.ExcelXP Reports

[turcay]

Hello everyone,

I have a sample code as below. I try to get my reports being a ODS Tagset.ExcelXP. However, if I get some reports  by default, their widths's seems too long. So I found a weight option to prevent this situation. This time, the reports height's seems too large. How can I prevent this situation. My desired output as below.

Can somebody help me, please?

Data AppendixRoc;
Length Range_ $ 32 Explanation $ 32;
Infile Datalines Missover Delimiter="|"; 
Input Range_ Explanation;
Datalines;
ROC|Ayirim Yok
ROC|Zayif Ayirim
ROC|Kabul Edilebilir Ayirim
ROC|Mukemmel Ayirim
ROC|Olaganustu Ayirim
;
Run;

Proc Format;
  Value $color "Ayirim Yok"="Red"
                "Zayif Ayirim"="Orange"
                "Kabul Edilebilir Ayirim"="Yellow"
				"Mukemmel Ayirim"="LightGreen"
				"Olaganustu Ayirim"="CX00B050";
Run;

Ods Tagsets.ExcelXP Path="C:\Temp" file="CAP.xml"
style=htmlblue options(zoom="80" embedded_titles='yes' sheet_interval='none' sheet_name="CAP");
PROC Report Data=AppendixRoc;
Title "ROC icin Genel Kural";
Columns Range_ Explanation;

Define Range_ /"" Display Style(Column)=Header {width=1.041666667in};
Define Explanation /"" Display style=[BackGround=$color. width=1.510416667in] ;
Run;
Title;
 
Ods Tagsets.ExcelXP Close;

Width

 

Height

Desired

[Reeza]

Have you tried the height options in tagsets?

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html

This is an excellent tagset reference, find the issue, and then it links to papers with examples on that topic. 

http://www.sas.com/events/cm/867226/ExcelXPPaperIndex.pdf

[Cynthia_sas]

Hi:

  While you might have reports that cause you to need sub-options for height and/or width controls in TAGSETS.EXCELXP suboptions, I don't think this is one of those times.  I suspect your attempt to "overcontrol" the width with measurements like:

Define Range_ /"" Display Style(Column)=Header {width=1.041666667in};
Define Explanation /"" Display style=[BackGround=$color. width=1.510416667in] 

might be causing your issues. When I tried your code with slightly more reasonable widths, I got your desired output without the need for other sub-options. So I suspect something in the over-specified width caused Excel to render the rows in an undesired basis.

Here's my output:

And here were the ONLY changes I made to the program:

Define Range_ /"" Display Style(Column)=Header {width=1.05in};
Define Explanation /"" Display style=[BackGround=$color. width=1.8in] ;

cynthia

[turcay]

Hello @Reeza and @Cynthia_sas,

Thank you very much for trying to help me 🙂

@Cynthia_sas, I think you are right, it can be related to "width"'s option fractions. Hovewer, the title still seems too large. what can be the reason for?

I checked @Reeza's web-site links and I used the Auto_Height="Yes" option but it doesn't work, the height(title) is still too large.

Ods Tagsets.ExcelXP Path="C:\Temp" file="CAP.xml"
style=htmlblue options(zoom="80" embedded_titles='yes' sheet_interval='none' sheet_name="CAP" AUTOFIT_HEIGHT="yes");
PROC Report Data=AppendixRoc;
Title "ROC icin Genel Kural";
Columns Range_ Explanation;

Define Range_ /"" Display Style(Column)=Header {width=1.05in};
Define Explanation /"" Display style=[BackGround=$color. width=1.8in] ;

Run;
Title;
 
Ods Tagsets.ExcelXP Close;

Thank you,

[Tim_SAS]

The Excelxp tagset (and the new ODS destination for Excel) creates titles by merging multiple cells in the row. Excel's Autoheight feature doesn't work on rows that contain merged cells. This is an Excel limitation.

You should be able to use the ROW_HEIGHTS keyword in the OPTIONS() option to specify a height for the title row.

[turcay]

Hello @Tim_SAS,

I don't exactly understand how can I use the Row_Heights option. I tried to use the following statements but it seems it didn't work. Maybe, I used the option incorrect. Can you help me to fix the title of report.

Ods Tagsets.ExcelXP Path="C:\Temp" file="CAP.xml"
style=htmlblue options(zoom="80" Row_Heights="0,0,0,0" Embedded_Titles='yes' Autofit_Height="yes" sheet_interval='none' sheet_name="CAP");

Thank you,

[Cynthia_sas · Accepted solution]

Hi:

  I don't think you want to specify 0. Here's the output using 10 and 20:

cynthia

[turcay]

Thank you very much @Cynthia_sas, it worked.

But I don't really understand the logic behind of the values. Yes, it works when we write 0,0,0,10 but what do this values stand for? Do they stand for mm or inch or etc.?

I didn't understand from the following link -> https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html 

Do you have an another links which explain more detailed?

Thank you

[Cynthia_sas]

Hi:

  if you run your code with doc='Help" in the suboption list, you will see everything about row_heights in the SAS log. All the suboptions are documented inside the template definition, so you need to use that suboption to see the full explanation.

  I believe that the number you specify is in points. however, I believe the number is then further manipulated, based on other option values on the font and other items, like whether you are using the merge across option for the title, or width=. So if you need a further explanation, you will need to work with Tech Support, after you use doc='Help' in the code. (in the code snippet I posted, I show the use of doc='Help' in the suboption list -- it is the first item in the list)

cynthia

[turcay]

@Cynthia_sas,

Yes, you are totally right. You told me before, sorry. I always forget the add doc="Help" suboption.

Thank you very much again 🙂