The SAS Output Delivery System and reporting techniques

Why does ODS EXCEL not rely on EXCEL Spreassheet XML schemas to handle line wrapping?

Reply
Regular Contributor
Posts: 156

Why does ODS EXCEL not rely on EXCEL Spreassheet XML schemas to handle line wrapping?

Why does ODS EXCEL insert line feeds into the data to force lines to wrap when the user doesn't request it?  Can we make ODS EXCEL stop this behavior?

SAS Super FREQ
Posts: 8,739

Re: Why does ODS EXCEL not rely on EXCEL Spreassheet XML schemas to handle line wrapping?

Hi:
  I don't know the answer to your questions, specifically, but Excel Spreadsheet Markup Language uses an XML file and was originated with Office 2003. https://en.wikipedia.org/wiki/Microsoft_Office_XML_formats

  On the other hand, ODS Excel conforms to the Office Open XML standard and creates an archive file with a .XLSX extension that should conform to the Office Open XML standard that was introduced in Office 2007 https://en.wikipedia.org/wiki/Office_Open_XML

  ODS TAGSETS.EXCELXP can ONLY produce the Office 2003 Spreadsheet ML type of XML; while ODS EXCEL can ONLY produce the Office Open XML or .XLSX file.

  What I find is that if my long line wraps, it is because my column width is too small. I can usually control the wrapping with a change in width, as shown below.

wrap_depends_on_width.png

  I find in most ODS destinations that wrapping depends on width, whether we are talking Excel, RTF or PDF.

cynthia

Regular Contributor
Posts: 156

Re: Why does ODS EXCEL not rely on EXCEL Spreassheet XML schemas to handle line wrapping?

[ Edited ]

Cynthia, thanks for your time,

The core of my question deals with lines feeds that are being generated by ODS EXCEL.  I have created this file, d.xlsx.  In it, an XML file named \\xl\sharedStrings.xml resides.  It reads:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="8" uniqueCount="8">
<si></si>
<si><t>Min</t></si>
<si><t>Median</t></si>
<si><t>Max</t></si>
<si><t>edu</t></si>
<si><t>4 YEARS OF COLLEG 4 YEARS OF
COLLEGE</t></si>
<si><t>1 YEAR OF GRAD SC 1 YEAR OF
GRAD SCHOOL</t></si>
<si><t>4 OR MORE YEARS O 4 OR MORE YEARS OF
GRAD SCHOOL</t></si>
</sst>

Note the line feeds here, on certain long lines.  This file was not opened by EXCEL after SAS and ODS Excel created the file.  Now consider the code that produced it:

 

proc format;
  value EDUCLAM
    16 = '4 YEARS OF COLLEG 4 YEARS OF COLLEGE'
    17 = '1 YEAR OF GRAD SC 1 YEAR OF GRAD SCHOOL'
    18 = '2 YEARS OF GRAD S 2 YEARS OF GRAD SCHOOL'
    19 = '3 YEARS OF GRAD S 3 YEARS OF GRAD SCSCHOOL'
    20 = '4 OR MORE YEARS O 4 OR MORE YEARS OF GRAD SCHOOL'
  ;
run;

data nowrap;
  infile datalines;
  input edu;
  format edu EDUCLAM.;
datalines;
16
17
20
;
run;

ods excel file="d.xlsx"
style=Sasweb;
  PROC TABULATE DATA=WORK.NOWRAP ;
    VAR edu/ style=[tagattr='wraptext:no' ];
    TABLE edu,(min Median max)*F=EDUCLAM.;
  run;
ods excel close;

No line feeds...   It is not one of the principles of SAS output that the data that is provided for output is presented for output without modification.  Yet this is happening, added linefeeds appear.  I don't think it should be doing this, or specifically, it should do this only if the user specifically request this behavior.  Is this grounds for a more formal error report --- in your opinion?  thanks again

 

SAS Super FREQ
Posts: 8,739

Re: Why does ODS EXCEL not rely on EXCEL Spreassheet XML schemas to handle line wrapping?

Hi:

  When I submit code using your format and slightly different style overrides, using WIDTH= on the TABLE statement for the data cells, this is what I see in the XML file:

my_sharedstrings.png

 

  I do not observe the line feed issue you illustrated.

 

  The style override on the VAR statement,  impacts the header cell -- so the header cell with Min, Median and Max was never in any danger of needing WRAPTEXT, but those were the cells you were conrolling with your override on the VAR statement. When you put a style override on a TABLE statement, it impacts the data cells.  Here's what I did that produced the above example in the shared strings file:

width_controls_text_wrap.png

Although I don't understand your need to put long text strings in data cells, when I used WIDTH in the right place, I did not observe wrapping in the XML or in the sheet when opened with Excel. The bottom line is that what is showing above in blue background with white text is a column header or row header cell -- those are changed on the class or var statement with style overrides. Or, as shown in my example below, statistic header cells are changed on the KEYWORD statement. What is in black font with white background above is a data cell inside the table and to impact those cells, you usually put your style override on the TABLE statement.

 

In my code, I did not use TAGATTR with WRAPTEXT -- I just made the width wide enough to accomodate the long value. Yes, it took some fiddling because even when I specify inches, Excel seems to convert my inches to some internal calculation that is opaque to me.

 

I find that WIDTH= works for me in most cases. I don't think that TAGATTR with WRAPTEXT by itself will be sufficient. If you need to understand the internal workings or this example is not sufficient to illustrate a workaround, then you might need to open a track with Tech Support.

 

Here's an example of the difference with style overrides in the var and class statements vs the TABLE statement. I just used colors because those are easy to spot and link the statement to the piece of the table that is impacted.

style_override_tab.png

 

cynthia

Regular Contributor
Posts: 156

Re: Why does ODS EXCEL not rely on EXCEL Spreassheet XML schemas to handle line wrapping?

[ Edited ]

I see two ways wrap text is being implemented

1. using Excel's cell attribute "WrapText", defined in the XML Schema, which is turned on or off by STYLE and TAGATTR.

2. ODS EXCEL replaces space characters with lines feeds, if columns are not requested to be wide enough.

 

This makes ODS Excel a pain-- My opinion.  I would like SAS to use the latter and not the former, and I don't want to play "column width cowboy".  (sorry, _too_ sardonic? I hope you can laugh at that. ) This is the second time I've encountered this and I guess I can use tagsets.excelxp.  again thanks for your time.

 

Ask a Question
Discussion stats
  • 4 replies
  • 253 views
  • 0 likes
  • 2 in conversation