How to make the whole titles be displayed in Excel

[William]

I am pretty new to ExcelXP, using ExcelXP to create a Excel file. Somehow, when the titles are very long, only partial be displayed.

Below is a example
ods listing close;
ods tagsets.ExcelXP file="test.xls";
ods tagsets.ExcelXP options(sheet_name="test" EMBEDDED_TITLES='yes');
title1 "title1 title1 title1 title1 title1 title1 title1 title1 title1 title1";
title2 "title2 title2 title2 title2 title2 title2 title2";
proc print data=sashelp.class noobs;
var age;
var name;
run;
ods tagsets.ExcelXP close;
ods listing;

After open the file in Excel, the title rows look below
title1 title1 title1
title2 title2 title2
(first 2 cells be merged, and wrapped)

My expected result would be like
title1 title1 title1
title1 title1 title1
title1 title1 title1
title1
title2 title2 title2
title2 title2 title2
title2
(besides merging and wrapping, I hope the heights should be big enough to display whole titles)

Thanks in advance for any help or suggestion.
William

[Cynthia_sas]

Hi:
It's been my experience that wrapped text for titles when using TAGSETS.EXCELXP does -not- happen automatically. When you use the EMBEDDED_TITLES suboption, I believe the behavior is to merge the cells across the top of the table and put the embedded title text into that merged cell. With only 2 variables in your table, there just wasn't enough real estate for the title text to show. If you had more variables, or explicitly made the columns wider, then you'd see the whole text. And, of course, if you make the table wide enough so you can see the whole title string, then there's no need to wrap text, because it all fits.

Consider the difference in output from the programs below, with step 1, where the output has only 2 variables and absolute_column_width is explicitly set; and step 2, where the output has 10 variables and no column width set. As you can see, the more variables you have, the wider the table and the more the title statement is revealed.

There are some row height adjustment options in the ExcelXP tagset, which you can reveal by using the doc='Help' sub-option. However, I'm not sure of the interaction between column width and row height sub-options or how you'd make them work together -and- if you did get them to work together, whether you'd get wrapping the way you want.

You might wish to open a track with Tech Support to explore the best way to accomplish what you are trying to do.

cynthia

[pre]
ods listing close;
** step1;
ods tagsets.ExcelXP file="c:\temp\test1.xml"
options(doc='Help' sheet_name="test" EMBEDDED_TITLES='yes'
absolute_column_width='35') style=sasweb;
title1 "title1 word1 word2 word3 word4 word5 word6 word7 word8 word9";
title2 "title2 word1 word2 word3 word4 word5 word6 word7";
proc print data=sashelp.class noobs;
var age name;
run;
ods tagsets.excelxp close;

** step 2;
ods tagsets.ExcelXP file="c:\temp\test2.xml"
options(doc='Help' sheet_name="test" EMBEDDED_TITLES='yes')
style=sasweb;

title1 "title1 word1 word2 word3 word4 word5 word6 word7 word8 word9";
title2 "title2 word1 word2 word3 word4 word5 word6 word7";
proc print data=sashelp.class noobs;
var age name height weight sex age name height weight sex;
run;

ods tagsets.ExcelXP close;
ods listing;
[/pre]

[William]

thanks Cynthia.

Actually I tried a couple options, like Row_Heights and Autofit_height, not successful.

1. Autofit_height:yes
never works

As mentioned in the help doc
Autofit_height: Default Value 'no'
Values: yes, no
If yes no row heights will be specified. This allows the auto fit height
of Excel to do it's job, sometimes not so well.


2. Row_Heights
it works. BUT for all the titles (here title1 and title2) share one setting, so in my case, I need different settings for title1 and title2.

Anyway, I will open a track tomorrow
William

[EllieBelle]

Hi William,

I am having the same problem that you encountered in 2009.  Were you able to resolve it?

Thanks!