RE: [xsl] Excel XML tranformation

["bryan" <bry@xxxxxxxxxx>]

>Hmmm, is there an option to do this?
>I just use the "Save As" within Excel 2002

I always save as xml as well, using Excel 2002. Here is an example
fragment(not all tags are closed as I don't want to clip the whole table
out) this is from the Solvsamp sample that ships with Excel

  <Table ss:ExpandedColumnCount="11" ss:ExpandedRowCount="190"
x:FullColumns="1"
   x:FullRows="1" ss:StyleID="s21" ss:DefaultRowHeight="10.5">
   <Column ss:StyleID="s21" ss:AutoFitWidth="0" ss:Width="64.5"/>
   <Column ss:StyleID="s21" ss:AutoFitWidth="0" ss:Width="45.75"
ss:Span="4"/>
   <Column ss:Index="7" ss:StyleID="s21" ss:AutoFitWidth="0"
ss:Width="22.5"/>
   <Column ss:StyleID="s21" ss:AutoFitWidth="0" ss:Width="18.75"/>
   <Column ss:StyleID="s21" ss:AutoFitWidth="0" ss:Width="36"/>
   <Column ss:StyleID="s21" ss:AutoFitWidth="0" ss:Width="41.25"/>
   <Column ss:StyleID="s21" ss:AutoFitWidth="0" ss:Width="24.75"/>
   <Row ss:AutoFitHeight="0" ss:Height="14.25">
    <Cell ss:StyleID="s30"><Data ss:Type="String">Quick Tour of
Microsoft Excel Solver</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0" ss:Height="13.5">
    <Cell ss:StyleID="s31"><Data ss:Type="String">Month</Data></Cell>
    <Cell ss:StyleID="s32"><Data ss:Type="String">Q1</Data></Cell>
    <Cell ss:StyleID="s32"><Data ss:Type="String">Q2</Data></Cell>
    <Cell ss:StyleID="s32"><Data ss:Type="String">Q3</Data></Cell>
    <Cell ss:StyleID="s32"><Data ss:Type="String">Q4</Data></Cell>
    <Cell ss:StyleID="s33"><Data ss:Type="String">Total</Data></Cell>
    <Cell ss:Index="8" ss:StyleID="Default"/>
    <Cell ss:StyleID="Default"/>
    <Cell ss:StyleID="Default"/>
    <Cell ss:StyleID="Default"/>
   </Row>

here's an example where an empty cell comes in between full cells

  <Row ss:AutoFitHeight="0">
    <Cell ss:StyleID="s35"><Data
ss:Type="String">Salesforce</Data></Cell>
    <Cell ss:StyleID="s41"><Data ss:Type="Number">8000</Data></Cell>
    <Cell ss:StyleID="s41"><Data ss:Type="Number">8000</Data></Cell>
    <Cell ss:StyleID="s41"><Data ss:Type="Number">9000</Data></Cell>
    <Cell ss:StyleID="s41"><Data ss:Type="Number">9000</Data></Cell>
    <Cell ss:StyleID="s42" ss:Formula="=SUM(RC[-4]:RC[-1])"><Data
ss:Type="Number">34000</Data></Cell>
    <Cell ss:Index="8" ss:StyleID="s48"/>
    <Cell ss:StyleID="s55"/>
    <Cell ss:StyleID="s53"><Data ss:Type="String">   Changing
cells</Data></Cell>
    <Cell ss:StyleID="s50"/>
   </Row>

><Row>
>	<Cell>
>		<Data ss:Type="Number">37530</Data>
>	</Cell>
>	<Cell>
>		<Data ss:Type="Number">50006</Data>
>	</Cell>
>	<Cell>
>		<Data ss:Type="String">IRWIN I-100, 6 mm</Data>
>	</Cell>
>	<Cell>
>		<Data ss:Type="Number">24721013699</Data>
>	</Cell>
>	<Cell ss:Index="6">
>		<Data ss:Type="String">IR-i100.jpg</Data>
>	</Cell>
>	<Cell ss:Index="8">
>		<Data ss:Type="String">IRcover.jpg</Data>
>	</Cell>
>	<Cell>
>		<Data ss:Type="String">Irwin-b.eps</Data>
>	</Cell>
>	<Cell>
>		<Data ss:Type="String">iri-100a.eps</Data>
>	</Cell>
></Row>


>What I am missing is the empty cells in spreadsheet. I can have them
>exported, by filling out the "blanks" with an "'", but the are not
exported
>by default. Not all the cells are filled out, but in the transformation
I
>refer to the cells individually (that's all I could figure out so far).

In my experience with MS office styling stuff there are a lot of little
problems like this. Check if you add an empty cell with nothing in it
that cell gets removed but if you increase the size of a cell then in
the output an extra cell perhaps will be added.

>So when I do a match in the row for the image, like
><xsl:value-of select="ss:Cell[6]\Data"/>
>This would actually give me "IRcover.jpg" instead of "IR-i100.jpg"
which I
>was expecting.

Ss:Index is used to keep track of table structure. Try ss:Cell[@ss:Index
= '6']/Data

>From the documentation: "Specifies the column index of this cell within
the containing row. If this tag is not specified, the first instance of
a Cell element within a row has an assumed Index="1". Each additional
Cell element has an assumed Index that is one higher. "

This use of ss:Index, while irritating, also means that one could make a
generic table handler which either placed cells in, or left them out,
dependant on parameters, etc. passed to the application.


 XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list