在导入到Excel之前，在XSLT上应用XSLT [英] Apply XSLT on XML before importing into Excel

问题描述

我有一个XSLT模板可以应用于数据的XML文件。该模板生成Excel电子表格XML文件。

除了一个小问题，一切都很好用。

当我在Excel中打开电子表格时，它将其视为XML导入，并询问是否应用样式表。我想拥有它，所以它自动应用任何与该文件相关联的XSLT模板。

以下是一些示例代码....提前感谢... 。

XML文档...

 <？xml version =1.0？> 
<？xml-stylesheet type =text / xslhref =spreadstyle.xsl？> 
 
< ResponseBody> 
< ActivityDetails> 
< ActivityDetail> 
< TranCodeDesc> LateChargeAssessment< / TranCodeDesc> 
< / ActivityDetail> 
< / ActivityDetails> 
< / ResponseBody> 
  

XSLT模板...

 <？xml version =1.0？> 
<？mso-application progid =Excel.Sheet？> 
 
< xsl：stylesheet version =1.0xmlns：xsl =http://www.w3.org/1999/XSL/Transform> 
 
< xsl：template match =/> 
 
 
<工作簿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 > 
 
< DocumentProperties xmlns =urn：schemas-microsoft-com：office：office> 
<作者> tbarbedo< / Author> 
<创建> 2009-05-29T18：21：48Z< / Created> 
< Version> 12.00< / Version> 
< / DocumentProperties> 
< ExcelWorkbook xmlns =urn：schemas-microsoft-com：office：excel> 
< WindowHeight> 8895< / WindowHeight> 
< WindowWidth> 18015< / WindowWidth> 
< WindowTopX> 0< / WindowTopX> 
< WindowTopY> 105< / WindowTopY> 
< ProtectStructure> False< / ProtectStructure> 
< ProtectWindows> False< / ProtectWindows> 
< / ExcelWorkbook> 
<样式> 
< Style ss：ID =Defaultss：Name =Normal> 
<对齐ss：垂直=底部/> 
<边框/> 
< Font ss：FontName =Calibrix：Family =Swissss：Size =11ss：Color =＃000000/> 
< Interior /> 
< NumberFormat /> 
<保护/> 
< / Style> 
< / Styles> 
<工作表ss：Name =Sheet1> 
< Table ss：ExpandedColumnCount =1ss：ExpandedRowCount =5000x：FullColumns =1
 x：FullRows =1ss：DefaultRowHeight =15> 
< Row>< Cell>< Data ss：Type =String>事务代码描述（MAGIC）< / Data>< / Cell>< / Row> 
< xsl：for-each select =ResponseBody / ActivityDetails / ActivityDetail> 
< Row> 
<单元格> 
<数据ss：Type =String> 
< xsl：value-of select =TranCodeDesc/> 
< / Data> 
< / Cell> 
 
< / Row> 
< / xsl：for-each> 
 
< / Table> 
< WorksheetOptions xmlns =urn：schemas-microsoft-com：office：excel> 
< PageSetup> 
<标题x：Margin =0.3/> 
< Footer x：Margin =0.3/> 
< PageMargins x：Bottom =0.75x：Left =0.7x：Right =0.7x：Top =0.75/> 
< / PageSetup> 
< Selected /> 
< Panes> 
< Pane> 
< Number> 3< / Number> 
< ActiveRow> 1< / ActiveRow> 
< / Pane> 
< / Panes> 
< ProtectObjects> False< / ProtectObjects> 
< ProtectScenarios> False< / ProtectScenarios> 
< / WorksheetOptions> 
< / Worksheet> 
< / Workbook> 
 
< / xsl：template> 
< / xsl：stylesheet> 
  

解决方案

如果在xsl表中添加此指令： / p>

 < xsl：template match =/> 
< xsl：processing-instruction name =mso-application> 
< xsl：text> progid =Excel.Sheet< / xsl：text> 
< / xsl：processing-instruction> 
  

....
excel将运行良好

I have an XSLT template that gets applied to an XML file of data. The template generates an Excel Spreadsheet XML file.

Everything works wonderful except for one minor issue....

When I open the spreadsheet in Excel, it treats it as an XML import and asks me whether the stylesheet should be applied. I would like to have it so it automatically applies any XSLT template that is associated with the file.

Below is some sample code....thanks in advance....

XML Document...

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="spreadstyle.xsl"?>

<ResponseBody>
    <ActivityDetails>
        <ActivityDetail>
            <TranCodeDesc>LateChargeAssessment</TranCodeDesc>
        </ActivityDetail>
    </ActivityDetails>
</ResponseBody>

XSLT Template...

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">


    <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">

     <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
      <Author>tbarbedo</Author>
      <Created>2009-05-29T18:21:48Z</Created>
      <Version>12.00</Version>
     </DocumentProperties>
     <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
      <WindowHeight>8895</WindowHeight>
      <WindowWidth>18015</WindowWidth>
      <WindowTopX>0</WindowTopX>
      <WindowTopY>105</WindowTopY>
      <ProtectStructure>False</ProtectStructure>
      <ProtectWindows>False</ProtectWindows>
     </ExcelWorkbook>
     <Styles>
      <Style ss:ID="Default" ss:Name="Normal">
       <Alignment ss:Vertical="Bottom"/>
       <Borders/>
       <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
       <Interior/>
       <NumberFormat/>
       <Protection/>
      </Style>
     </Styles>
     <Worksheet ss:Name="Sheet1">
      <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="5000" x:FullColumns="1"
       x:FullRows="1" ss:DefaultRowHeight="15">
            <Row><Cell><Data ss:Type="String">Transaction Code Descriptions (MAGIC) </Data></Cell></Row>
            <xsl:for-each select="ResponseBody/ActivityDetails/ActivityDetail">     
                <Row> 
                    <Cell>
                        <Data ss:Type="String">
                            <xsl:value-of select="TranCodeDesc" />
                        </Data>
                    </Cell>

                </Row>
            </xsl:for-each>

      </Table>
      <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
       <PageSetup>
        <Header x:Margin="0.3"/>
        <Footer x:Margin="0.3"/>
        <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
       </PageSetup>
       <Selected/>
       <Panes>
        <Pane>
         <Number>3</Number>
         <ActiveRow>1</ActiveRow>
        </Pane>
       </Panes>
       <ProtectObjects>False</ProtectObjects>
       <ProtectScenarios>False</ProtectScenarios>
      </WorksheetOptions>
     </Worksheet>
    </Workbook>

</xsl:template>
</xsl:stylesheet>

解决方案

if you add this instruction in the xsl sheet :

<xsl:template match="/">
  <xsl:processing-instruction name="mso-application">
    <xsl:text>progid="Excel.Sheet"</xsl:text>
  </xsl:processing-instruction>

.... excel will run fine

这篇关于在导入到Excel之前，在XSLT上应用XSLT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！