RE: [xsl] XML to EXCEL problem...

Subject: RE: [xsl] XML to EXCEL problem...
From: cknell@xxxxxxxxxx
Date: Sun, 16 Dec 2007 08:45:23 -0500
Unfortunately, your problem is not an XSLT problem, but an Excel problem. As you have said, the transformation results in a file that Excel can open, the problem appears when you use Excel to save the file.

You have made a statement which, while no doubt perfectly accurate, is ambiguous:
.. "when i modify it (text bold for example) and i save-close it ..."

I have done a great deal of generating Excel spreadsheets with XSLT. I was targeting an older version of Excel that did not have an XML format for saving the file, instead I had to use HTML as the target format. When I chose the "Save as ..." option from Excel, I was presented with a large number of format options. 

What file format option did you choose when you did the save-close? You may not have explicitly chosen an option, and therefor got the default. What that specifically means will vary from one Excel release to another. I might help to know exactly what the format was.

You might experiment by saving the file in various format (HTML for example), then repeat the re-formatting with Excel experiment and try reopening the file in Excel and viewing the file in your text editor to find some clues.


-- 
Charles Knell
cknell@xxxxxxxxxx - email



-----Original Message-----
From:     batis batuus <batis.04@xxxxxxxxx>
Sent:     Sun, 16 Dec 2007 10:26:18 +0100
To:       xsl-list@xxxxxxxxxxxxxxxxxxxxxx
Subject:  [xsl] XML to EXCEL problem...

I'm using xslt to transform an xml file to an excel spreadsheet...
*****xml*****
<?xml version="1.0"?>
<library>
      <book>
              <writers>Henry VANDEN, Christina MONAY, Jarod SITH</writers>
              <title>The National Council of Teachers of Mathematics:
Teaching of
Arithmetic (10th yearbook)</title>
      </book>
      <book>
              <writers>Ronald Staszkow</writers>
              <title>Developmental Mathematics: Basic Arithmetic with
Introductions to Algebra and Geometry</title>
      </book>
<library>
*****xsl******
<xsl:stylesheet version="1.0"
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform";
xmlns:msxsl="urn:schemas-microsoft-com:xslt"
xmlns:user="urn:my-scripts"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" >
<xsl:output method="xml" indent="yes" encoding="UTF-8" />

<xsl:template match="library">
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40";>

      <Worksheet>
              <xsl:attribute name="ss:Name">Publications title</xsl:attribute>
              <Table>
                      <xsl:apply-templates select="library"/>
                      <Row>
                              <Cell>
                                      <Data ss:Type="String">Title</Data>
                              </Cell>
                      </Row>

                      <xsl:for-each select="book">
                              <Row>
                                      <Cell>
                                              <Data ss:Type="String">
                                              <xsl:value-of select="title"/>
                                              </Data>
                                      </Cell>
                              </Row>
                      </xsl:for-each>

              </Table>
      </Worksheet>
</Workbook>
</xsl:template>
</xsl:stylesheet>
************
That gives me an xml file wich i can open with ms-excel.....
The problem is when i modify it (text bold for example) and i
save-close it... I can't re-open it anymore!!!???
when i edit the file i found that excel added some binary data (in
PageSetup tag)....
*****xml-xls file*****
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40";
 xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:user="urn:my-scripts">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
 <Version>11.5606</Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
 <WindowHeight>10005</WindowHeight>
 <WindowWidth>10005</WindowWidth>
 <WindowTopX>120</WindowTopX>
 <WindowTopY>135</WindowTopY>
 <ProtectStructure>False</ProtectStructure>
 <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
 <Style ss:ID="Default" ss:Name="Normal">
 <Alignment ss:Vertical="Bottom"/>
 <Borders/>
 <Font/>
 <Interior/>
 <NumberFormat/>
 <Protection/>
 </Style>
 <Style ss:ID="s22">
 <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
 <Font x:Family="Swiss" ss:Bold="1"/>
 </Style>
 </Styles>
 <Worksheet ss:Name="Publications title">
 <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="3" x:FullColumns="1"
 x:FullRows="1" ss:DefaultColumnWidth="60">
 <Column ss:AutoFitWidth="0" ss:Width="408.75"/>
 <Row ss:StyleID="s22">
  <Cell><Data ss:Type="String">Title</Data></Cell>
 </Row>
 <Row>
  <Cell><Data ss:Type="String">The National Council of Teachers of
Mathematics: Teaching of Arithmetic (10th yearbook)</Data></Cell>
 </Row>
 <Row>
  <Cell><Data ss:Type="String">Developmental Mathematics: Basic
Arithmetic with Introductions to Algebra and Geometry</Data></Cell>
 </Row>
 </Table>
 <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
 <PageSetup>
  <Header x:Margin="BINARY DATA"/>
  <Footer x:Margin="BINARY DATA"/>
  <PageMargins x:Bottom="BINARY DATA"
   x:Right="BINARY DATA"/>
 </PageSetup>
 <Print>
  <ValidPrinterInfo/>
  <VerticalResolution>0</VerticalResolution>
 </Print>
 <Selected/>
 <Panes>
  <Pane>
   <Number>3</Number>
   <RangeSelection>R1</RangeSelection>
  </Pane>
 </Panes>
 <ProtectObjects>False</ProtectObjects>
 <ProtectScenarios>False</ProtectScenarios>
 </WorksheetOptions>
 </Worksheet>
</Workbook>
**********
Please what's wrong whith this??

Current Thread