The SAS Output Delivery System and reporting techniques

excelXP data cell wrapping

Reply
N/A
Posts: 0

excelXP data cell wrapping

Hi

I'm using the excelXP tagset. I would like to have a space after the value in tabulate data cells. They are a bit too close when printed. With data style containing tagattr='format:#,##0" ";[Red]-#,##0" "' I can easily add a trailing blank. However, all datacells are "wrapping" and this causes that trailing blank to be ignored.
Does anyone know how to switch off the wrapping for data cells, or some alternative way to get a space between the value and the cell boundary?

PeterC
Super Contributor
Posts: 358

Re: excelXP data cell wrapping

Peter:

Had exactly the same problem.

Firstly, make sure the cell with is wide enough for the value being written - we use the absolute_column_width parameter of the Excelxp tagset to set each column.

If that doesn't fix the problem, then you need to turn off the wrap 'option' on the text being written to the cell. We made a change to the ExcelXP tagset to turn off the text wrap on only the data cells and this worked. We also received a piece of code from SAS that did the same thing by making a modification to the tagset from the program being run (using PROC TEMPLATE).

As far as the print being too close to the right cell border - SAS support says that this is an Excel problem. We tried putting the space in the format but this caused us to have problems with the alignment of the values in the cell. (This only appears on the print, the screen view and print preview have the text further from the cell boundary).

Hope this helps.
N/A
Posts: 0

Re: excelXP data cell wrapping

thank you for the encouragement
~ guess I'm on the right lines with absolute column width ~~~

Is there any chance you would provide a link to the solutions you found ? I couldn't find them searching the forum.

Peter
Super Contributor
Posts: 358

Re: excelXP data cell wrapping

Peter:

Here is the code that was sent to us by SAS:

proc template;
define tagset tagsets.test;
parent=tagsets.excelxp;
define event align_tag;
start:
break /if $align_tag;
put ' do /if cmp($cell_class, 'systemtitle');
put ' ss:Wraptext="0"';
else /if contains($cell_class, 'data');
put ' ss:WrapText="0"';
else;
put ' ss:WrapText="1"';
done;
set $align_tag "True";
finish:
break /if ^$align_tag;
putl '/>';
unset $align_tag;
end;
end;
run;


This code makes a change to the WrapText so that the data cells in the Excel spreadsheet do not wrap text.
Super Contributor
Posts: 358

Re: excelXP data cell wrapping

Well....

That didn't work, did it.

Someone know how I can post the code without the editor f***ing it up?
N/A
Posts: 0

Re: excelXP data cell wrapping

Dear Rules

thank you for sharing that piece of code. Let me return the favour, and indicate how you can format for code :

try [pre]
data ;
infile ;
input ;
run;
[/pre]

you place [ pre ] as preceding tag and [ /pre ] as closing tag
but without the spaces embedded inside those [ ] square brackets

PeterC
Super Contributor
Posts: 358

Re: excelXP data cell wrapping

Thanks Peter.

Problem is that my code contains forward slashs and the when the editor hits the first one the rest of the code goes missing. Even with the tags before and after.

Now what ....
SAS Super FREQ
Posts: 8,739

Re: excelXP data cell wrapping

here is some code with slashes inside a [ pre ] and [ /pre ] (remember that the 5 characters [ pre ] do not contain ANY spaces):

[pre]
here's a forward slash twice / in this code snippet
define something / group
style(column) = {foreground=purple};

now, here's a backslash \ just in case
[/pre]

You MUST use (pre) and (/pre) before and after the code, but where I have parentheses, you MUST use square brackets. [ ] IF your code has any greater than or less than symbols, you MUST refer to those with their named entity versions < and >
cynthia

cynthia
Super Contributor
Posts: 358

Re: excelXP data cell wrapping

Cynthia:

Thanks for the instructions, but ...

I still have the formatting problem. I followed your instructions and added the code between the tags (with the square brackets). When I "Preview" the code, it ends at the same place as happened above, which is somewhere near the middle of the code.

I'm no expert, but methinks that there is something in the code sample that if mucking up the formatting.
SAS Super FREQ
Posts: 8,739

Re: excelXP data cell wrapping

Hi,
The ONLY other thing that could mess up inside the [ pre ] section would be greater than and less than symbols. Since you're talking about a tagset that produces XML, those symbols will be all over the place and the work to protect them will be labor intensive (you'd have to turn every > into &gt; symbol and every < into &lt; symbol).

I don't know whether there is a way to attach a file to the post messages. Otherwise, the only way to send/receive code would be for Patrick to contact Tech Support and reference your tracking number and ask for the same code or for Patrick to give you his email address and for you to email the code directly.

cynthia
N/A
Posts: 0

Re: excelXP data cell wrapping

I have asked our UK SAS Customer Support to obtain that code. They're searching. The search would be easier with a tracking number for their original solution.

PeterC
Super Contributor
Posts: 358

Re: excelXP data cell wrapping

Sorry for the delay:

Here is the track: Trknum: us6592415

Hope that helps.
Super Contributor
Posts: 358

Re: excelXP data cell wrapping

I'm going to try the code again with Cynthia's changes:

[pre]
proc template;
define tagset tagsets.test;
parent=tagsets.excelxp;
define event align_tag;
start:
break /if $align_tag;
put '<Alignment';
do /if cmp($cell_class, 'systemtitle');
put ' ss:Wraptext="0"';
else /if contains($cell_class, 'data');
put ' ss:WrapText="0"';
else;
put ' ss:WrapText="1"';
done;
set $align_tag "True";
finish:
break /if ^$align_tag;
putl '/>';
unset $align_tag;
end;
end;
run;

ods tagsets.test file="c:\temp.txt";

proc print data=sashelp.class;
run;
[/pre]

Hope this works... the "preview" liked it this time.
N/A
Posts: 0

Re: excelXP data cell wrapping

Many thanks to all who have provided the help and support : Cynthia, OS2_Rules, and some support from SAS Customer Support in UK

The only proc template excelXP override which I found to work and produce valid xml, was the final code OS2_rules was able to generate.

The unexpected feature I found I had to add, appears in my tabulate style code on the proc statement[pre]
proc tabulate style= [ tagattr='format:#,##0"" "";[Red]-#,##0"" ""']
[/pre]
(That style element affects the data cells of analysis variable statistics.)
Normally we might add a trailing character in an excel custom format like
[pre] 'format:#,##0" ";[Red]-#,##0" "' [/pre]
However this style element gets inserted within "double quotes" and I think the interpretation of a pair of ", within a pair of " gets confused and so ignored.

This was the (less effective) xml [pre] <NumberFormat ss:Format="#,##0" ";[Red]-#,##0" "" /> [/pre]
When the extra quotes are added, the result looks better, and it works for me ! [pre] <NumberFormat ss:Format="#,##0"" "";[Red]-#,##0"" """ />[/pre]

again, many thanks

PeterC
Ask a Question
Discussion stats
  • 13 replies
  • 425 views
  • 0 likes
  • 3 in conversation