topic Re: excelXP data cell wrapping in ODS and Base Reporting

excelXP data cell wrapping

deleted_user — Fri, 20 Jul 2007 10:05:39 GMT

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

Re: excelXP data cell wrapping

OS2Rules — Fri, 20 Jul 2007 13:00:15 GMT

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.

Re: excelXP data cell wrapping

deleted_user — Fri, 20 Jul 2007 13:22:25 GMT

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

Re: excelXP data cell wrapping

OS2Rules — Mon, 23 Jul 2007 12:10:23 GMT

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.

Re: excelXP data cell wrapping

OS2Rules — Mon, 23 Jul 2007 12:13:10 GMT

Well....

That didn't work, did it.

Someone know how I can post the code without the editor f***ing it up?

Re: excelXP data cell wrapping

deleted_user — Mon, 23 Jul 2007 16:27:48 GMT

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

Re: excelXP data cell wrapping

OS2Rules — Mon, 23 Jul 2007 19:17:45 GMT

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 ....

Re: excelXP data cell wrapping

Cynthia_sas — Sat, 28 Jul 2007 13:12:05 GMT

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

Re: excelXP data cell wrapping

OS2Rules — Mon, 30 Jul 2007 12:17:48 GMT

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.

Re: excelXP data cell wrapping

Cynthia_sas — Mon, 30 Jul 2007 14:43:00 GMT

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 > symbol and every < into < 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

Re: excelXP data cell wrapping

deleted_user — Mon, 30 Jul 2007 15:02:37 GMT

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

Re: excelXP data cell wrapping

OS2Rules — Mon, 30 Jul 2007 16:25:38 GMT

Sorry for the delay:

Here is the track: Trknum: us6592415

Hope that helps.

Re: excelXP data cell wrapping

OS2Rules — Mon, 30 Jul 2007 16:29:40 GMT

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.

Re: excelXP data cell wrapping

deleted_user — Mon, 06 Aug 2007 15:36:28 GMT

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