The SAS Output Delivery System and reporting techniques

ODS & Excel column widths

Reply
Occasional Contributor
Posts: 11

ODS & Excel column widths

Hello,

When I use ods html to create xls file, below, the var logfile is a long char string and it wraps in the cell and the column is too narrow, abt 35 when viewed in Excel.

proc print data=all u;
sum realtime / style={htmlstyle="vnd.ms-excel.numberformat:[hh]:mm:ss.00"};
var logfile ;
var stepname ;
var realtime / style={htmlstyle="vnd.ms-excel.numberformat:[hh]:mm:ss.00"};
var obsin obsout varsout stepcnt;
run;

When I use excelxp tagset, as in

ods tagsets.excelxp file=all.xls" style=whatever
options(
sheet_label=' ');

ods tagsets.excelxp options(
sheetname=' ');

proc print label data=all;
sum realtime / style={htmlstyle="vnd.ms-excel.numberformat:[hh]:mm:ss.00"};
var logfile ;
var stepname;
var realtime / style={htmlstyle="vnd.ms-excel.numberformat:[hh]:mm:ss.00"};
var obsin obsout varsout stepcnt;
run;
ods tagsets.excelxp close;

The long char string no longer wraps, but now the column is much too wide, abt 227 in the column width viewed in Excel.

The other column widths are acceptable.

Any ideas?
SAS Super FREQ
Posts: 8,743

Re: ODS & Excel column widths

Hi:
When you use ODS HTML, you are not creating a "true, binary" .XLS file. Using the file extension .XLS with an ODS HTML program is just a convenient way to get the Windows registry to launch Excel when you double click on the file name. Otherwise, if you used the correct extension of .HTML, your browser would launch when you double clicked on the file name.

You can prove that you have an HTML file by finding the file that you created with ODS HTML and open the file with a text editor, such as Windows Notepad. You should see HTML tags in the file.

This means that your behavior (column widths and formats) are determined by Excel's rules for how to render an HTML file. In fact, when you use the HTMLSTYLE override in your code, you are trying to "influence" Excel's rendering by sending Excel some "mso-number-formats" to use.

If there is an "mso-" property for column widths, you would have to look in the Microsoft documentation to find it. But, even so, my experience with Microsoft Excel and "mso-" properties is that you might get a width you want in the browser, but possibly not in Excel.

If you would switch to Spreadsheet Markup Language XML -- as the result type -- this is a type of XML introduced in Office 2002/2003, then you would be able to use a sub-option for absolute_column_width in order to send Excel instructions about how to render the XML, as far as column widths go. There is a lot of documentation on ODS TAGSETS.EXCELXP (how ODS creates Spreadsheet Markup Language XML) in previous forum postings and you should be able to find lots of information by doing a search.

cynthia
Ask a Question
Discussion stats
  • 1 reply
  • 266 views
  • 0 likes
  • 2 in conversation