XMLSS - SpreadsheetML Styles

[MarcG]

I am using an xslt to transform a dataset into a formatted spreadsheet.

I got the base xml by opening Excel, formatting the rows and columns the way
I wanted them; setting up a header row; specifying the print area; and
setting one of the columns to text/wrap. I then entered one data row and
saved the whole thing as an XML spreadsheet (Excel xml, i.e., XMLSS).

I then set up the xslt. For the root, I included all of the xmlss that
preceeded the data row, then put in an apply-template that repeated for each
data row in the dataset, and followed this with all of the trailing xmlss.

In the template that handles the data row, I took the xmlss for the
<row>...</row> node and inserted the record field values into the <data>
nodes.

The template basically looks like:
<Row>
<Cell ss:StyleID="s92">
<Data ss:Type="String">
<xsl:value-of select="BayNo"/>
</Data>
</Cell>
....
....
<Cell ss:StyleID="s97">
<Data ss:Type="String">
<xsl:value-of select="NoteText"/>
</Data>
</Cell>
</Row>

The whole process actually works like a dream ... my web app sends the xml
to the browser with approptiate headers and up pops Excel with the
spreadsheet in it.

Except for one problem. The last column in each row contains wrapped text
however, the row itself does not autosize. The effect looks as though the
data in the cell is truncated. But if you manually autosize the row, it
adjusts to the proper height and shows all of the text.

Note that the last cell above uses ss:StyleID="s97" which is defined as
follows:
<Style ss:ID="s97">
<Alignment ss:Vertical="Top" ss:WrapText="1"/>
<Borders>
<Border ssosition="Bottom" ss:LineStyle="Continuous"
ss:Weight="1"/>
<Border ssosition="Left" ss:LineStyle="Continuous"
ss:Weight="1"/>
<Border ssosition="Right" ss:LineStyle="Continuous"
ss:Weight="2"/>
<Border ssosition="Top" ss:LineStyle="Continuous"
ss:Weight="2"/>
</Borders>
<Font ss:Size="11"/>
</Style>

Note ss:WrapText="1"

Now, if you open a new spreadsheet, set a column to text/wrap and start
typing, it will automatically wrap and the row will assume the proper height
when you exit the cell. Save this as an xml spreadsheet. then select the
entire sheet and Format|Row|Autofit and save it again under a different file
name.

Compare the two xml files and they are identical - the Format|Row|Autofit
leaves no markup in the output. Not only that, but the row you entered the
wrapped text in has an explicit height setting, e.g., <Row ss:Height="210.25">

So, my question is: What can I put in the xml that will force Excel to
autosize the row based on the content in wrapped text cells?

Thx
Marc

 

[Jialiang Ge [MSFT]]

Hello Marc,

Now, if you open a new spreadsheet, set a column to text/wrap and
start typing, it will automatically wrap and the row will assume the
proper height when you exit the cell. Save this as an xml spreadsheet.
then select the entire sheet and Format|Row|Autofit and save it
again under a different file name.

I have reproduced it on my side. Format|Row|AutoFit does not set any style
in the spreadsheet. It simply recalculate each row's height, and set it to
fit its content. The resulting set of ss:Height (<Row ss:Height="210.25">)
on the wrapped cell will not prevent the cell from auto-sizing. If the
height of the rows in the worksheet is already fit for their content before
Format|Row|AutoFit, as you see, the operation does not take any effect to
its XML behind the scene, because no row height needs to be changed.

So, my question is: What can I put in the xml that will force
Excel to autosize the row based on the content in wrapped
text cells?

I am not sure if I understand this question correctly. Based on my test,
Excel will *automatically* auto-size the row height based on the content in
the word-wrap cells.
For example, I type "aaaaaaaaaaaaaaaaaaaaaaaaa" in cell A1, type
"bbbbbbbbbbbbbbbbbbbbbbbb" in cell B1, and set B1 as WordWrap. Then row1
automatically adjusts its height to fit the need of B1 (the only wordwrap
cell). Am I misunderstanding anything here?

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[MarcG]

Jialiang

I don't think I'm going to like the answer.

1) It looks like Excel's default behaviour for wrapped text columns is to
calculate the required row height after cell data entry has been completed.
It then sets the row height attribute to the calculated value (As though I
had explicitly set the height to 212.25).

2) Explicitly telling Excel to Format|Row|Autoheight simply tells Excel to
go back over the current spreadsheet content and adjust row height as needed,
again setting the height attribute explicitly and individually for each row.

My problem (unfortunately) is not handled by either of the above. I can
create perfect XMLSS for the entire sheet, except that I can't calculate the
row heights.

When I developed my xslt, when I got to generating the Row element I looked
at what Excel had and I removed the ss:Height attribute. My (foolish) hope
was that if it wasn't there, and the cell was wrapped text, Excel would
recalculate the height as though the data had just been entered into the cell.

The result I'm getting is that the text is being displayed in a default
height row and the visual appearance is as though I had taked a properly
sized row and shortened it (i.e., the text appears truncated).

So, since there is no autoheight style, and since Excel won't automatically
recalculate the heights when the xmlss is loaded, I seem to be up the
proverbial creek.

I really don't want to do this, but...
Could I specify a macro in the xmlss that would run automatically when the
sheet was loaded that would
- select All
- Format|Row|Autoheight
- unselect All

How would I do that? Does anyone have a better idea?

Thx
Marc

 

[Jialiang Ge [MSFT]]

Hello Marc,

Thank you for the clarification. I now have a clearer picture of the issue.
In order to apply height-autosize when the XMLSS is opened, a more
reasonable resolution is to use the ss:AutoFitHeight property of ss:Row.

According to the MSDN article
http://msdn2.microsoft.com/en-us/library/aa140066(office.10).aspx
If ss:AutoFitHeight is True(1), it means that this row should be autosized.
This property will take effect to autosize the row height when XMLSS is
opened when
1. We do not specify ssefaultRowHeight property,
- and -
2. We do not specify ss:Height property.

Here is an example:
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ssefaultColumnWidth="54">
<Column ss:Index="2" ss:StyleID="s21"/>
<Row ss:AutoFitHeight="1">
<Cell><Data
ss:Type="String">aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa</Data></Cell>
<Cell><Data ss:Type="String">bbbbbbbbbbbbbbbbbbb</Data></Cell>
</Row>
</Table>

<Style ss:ID="s21">
<Alignment ss:Vertical="Center" ss:WrapText="1"/>
</Style>

I have tested the above xml snippet. When the test XMLSS is opened, the row
height of the second column will be automatically autosized even if I do
not explicitly specify the exact row height in XML. Please have a try on
your side and let me know if it works for you. If you have any other
concerns or questions, feel free to let me know.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

 

[MarcG]

Jailiang,

Thanks for the reference. I inserted the ss:AutoFitHeight="1" in the row
tag, but unfortunately it made no difference. My rows now look like (for
example)

<Row ss:AutoFitHeight="1" >
<Cell ss:StyleID="s92"><Data ss:Type="String">X16PL3</Data></Cell>
<Cell ss:StyleID="s93"><Data ss:Type="Number">44444</Data></Cell>
<Cell ss:StyleID="s94"><Data
ss:Type="DateTime">2007-12-07T00:00:00.000</Data></Cell>
<Cell ss:StyleID="s94"><Data
ss:Type="DateTime">2007-12-05T00:00:00.000</Data></Cell>
<Cell ss:StyleID="s95"><Data ss:Type="String">CENTURY CO LTD</Data></Cell>
<Cell ss:StyleID="s93"><Data ss:Type="Number">100</Data></Cell>
<Cell ss:StyleID="s96"><Data ss:Type="String">jsmith</Data></Cell>
<Cell ss:StyleID="s97"><Data ss:Type="String">There's a or==problem that
Mark is looking into ......</Data></Cell>
</Row>

The last column does not fit in the allocated cell width, but the row still
does not autofit.

The article noted that if both AutoFit and Size were specified that autofit
would be applied if size made the row too narrow. as an experiment, I changed
the row tag to:
<Row ss:AutoFit="1" ss:Height="1">

The effect was to collapse all rows to "1", i.e., the height specification
took precedence.

I note that the url you gave me was for Office 10. I'm using Office 2003,
and my excel is 11.821.8202 SP3. Do you suppose that the feature was wiped
out in 11?

Thx
Marc

 

[MarcG]

Jialiang,

Perfect thank you.

Please make sure the property *ssefaultRowHeight* has been removed from
the ssTable element

Doing that along with specifying ss:AutoFitHeight="1" in the <Row> tag fixes
the problem.

Thanks,
Marc

 

[Jialiang Ge [MSFT]]

You are welcome. Have a nice day!

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

 

[Ashish]

I tried using the autorowheight attribute and also removing the height and
the defaultrowheight property. But it does not work for me. Any help.