topic tagset ExcelXP with autofit_width in ODS and Base Reporting

tagset ExcelXP with autofit_width

deleted_user — Wed, 18 Jun 2008 08:35:31 GMT

Hi,

I would like to format the column-width in the Excel output from a stored proces using te ExcelXP tagset in the same way as the AUTOFIT_HEIGHT can be used.

The AUTOFIT_HEIGHT will adjust the height of each row to present the complete content of the row, where the height is set to fit the largest cell.

Is there a way to set the WIDTH for each column to fit the content of the largest cell. The width cannot be set to a fixed value because the result of the query is not predictable in terms of column content.

Please help
Thanks

Henk

Re: tagset ExcelXP with autofit_width

Eric_SAS — Wed, 18 Jun 2008 13:50:47 GMT

Hello Henk,

Unfortunately Autofit height does not work in Excel. It is in fact, set, but has no effect.
The heights are calculated by the tagset based on the content of the cells and their widths.

If there is a width set on a cell the tagset will honor that width and adjust the heights accordingly.

The column widths are calculated based on the width given by the procedure, the font size and the length of the header for that column.

I would expect the column widths to match the widths of the actual data, If you want them to be narrower there are a few ways to do that. The cellwidth style attribute takes several different units of measure, points is most common. Setting that on a style over ride will allow you to control the column width.

Are the columns too wide for you or are they too narrow?

Which version of the tagset are you using? That can make a big difference.
The current version is 1.86 and is available at http://support.sas.com/rnd/base/ods/odsmarkup/

Re: tagset ExcelXP with autofit_width

deleted_user — Wed, 18 Jun 2008 14:04:48 GMT

Eric,

The columns are too narrow, in fact they are not adjusted according the data.
It seems that the columns have the default width set by Excel.
The height is regulated bij the AUTOFIT_HEIGHT options (which in my case is set to 'yes'). The result of this is the height of each row to fit the largest data in a cell in that row, where this will be a multible line cell depending on the amount of text to fit in that cell, as the width is not regulated in any way.

I was looking for something simular for the width (AUTOFIT_WIDTH ??), but seems not to be available.

The version of the tagset is the one 1.86 as you refered to.

Henk Message was edited by: HenkCZ

Re: tagset ExcelXP with autofit_width

Eric_SAS — Thu, 19 Jun 2008 20:03:39 GMT

Are you using proc Report with SAS 9.1.3?

Are the row heights adjusting for you? I would not expect them to. That option turns
off the tagset's row height calculations so Excel can do it. But I've never seen excel do the right thing.

with proc report at SAS 9.1.3 there are no column widths. Proc print will give them. The default_column_widths tagset option offers a way around it, but you will have to choose some reasonable width. The tagset will calculate row heights based on that width and the width could grow if a single word is too long to fit.

Eric