Excel电子表格使用XML和XSLT的动态单元格着色 [英] Excel Spreadsheet Dynamic cell colouring using XML and XSLT
问题描述
我需要的是动态更改基于使用XSL的节点的XML属性的电子表格中单元格的背景颜色
示例：
Source.XML内容
<工作簿>
< Wrkbook
< table Id =我的表1>
< Colnames>
< ColumnName>学生ID< / ColumnName>
< ColumnName>学生名称< / ColumnName>
< ColumnName> Subject 1< / ColumnName>
< ColumnName> Subject 2< / ColumnName>
< ColumnName> Subject 3< / ColumnName>
< ColumnName> Subject 4< / ColumnName>
< ColumnName> Subject 5< / ColumnName>
< ColumnName> Subject 6< / ColumnName>
< / Colnames>
< Rows>
< CI> 534< / CI>
< CI> Ramu< / CI>
< CI> 67< / CI>
< CI Color =Green> 67< / CI>
< CI Color =＃e8e9e8> 48< / CI>
< CI> 66< / CI>
< CI Color =＃B3C389> 39< / CI>
< CI> 67< / CI>
< / Rows>
< Rows>
< CI> 534< / CI>
< CI> Raul< / CI>
< CI Color =Green> 63< / CI>
< CI> 89< / CI>
< CI Color =＃007788> 67< / CI>
< CI> 57< / CI>
< CI> 75< / CI>
< CI Color =＃AABBCC> 92< / CI>
< / Rows>
< / table>
< / Wrkbook>
< / workbooks>
以前我以前做过的是我以前有一个特定的绿色样式，对于各自的XML标签中的属性，因此我可以管理它。但现在源文件包含颜色代码，我需要基于颜色代码突出显示。
XSL文件：
< xsl：template match ='/'&
<工作簿>
<样式>
<样式ss：ID =绿色>
<边框>
< Border ss：Position =Bottomss：LineStyle =Continuousss：Weight =1/>
< Border ss：Position =Leftss：LineStyle =Continuousss：Weight =1/>
< Border ss：Position =Rightss：LineStyle =Continuousss：Weight =1/>
< Border ss：Position =Topss：LineStyle =Continuousss：Weight =1/>
< / Borders>
<字体ss：FontName =Calibrix：Family =Swissss：Size =11/>
<内部ss：Color =＃CCFFCCss：Pattern =Solid/>
< / Style>
< / Styles>
< xsl：for-each select ='workbooks'>
< xsl：for-each select ='Wrkbook'>
<工作表ss：Name ='Scores'>
<表x：FullColumns ='1'x：FullRows ='1'>
< xsl：for-each select ='table'>
< Row>
< xsl：for-each select ='Colnames / ColumnName'>
<单元格ss：样式ID ='s41'>
<数据ss：Type ='String'>
< xsl：value-of select ='。'disable-output-escaping ='yes'/>
< / Data>
< / Cell>
< / xsl：for-each>
< / Row>
< xsl：for-each select ='Rows'>
< Row>
< xsl：for-each select ='CI'>
< xsl：choose>
< xsl：when test =@ Color ='Green'>
<单元格ss：样式ID ='绿色'>
<数据ss：Type ='String'>
< xsl：value-of select ='。'disable-output-escaping ='yes'/>
< / Data>
< / Cell>
< / xsl：when>
< xsl：否则>
<单元格ss：样式ID ='s38'>
<数据ss：Type ='String'>
< xsl：value-of select ='。'disable-output-escaping ='yes'/>
< / Data>
< / Cell>
< / xsl：否则>
< / xsl：choose>
< / xsl：for-each>
< / Row>
< / xsl：for-each>
< Row>
< Cell>< / Cell>
< / Row>
< / xsl：for-each>
< /表>
< / Worksheet>
< / xsl：for-each>
< / xsl：for-each>
< / Workbook>
< / xsl：template>
请忽略XSL中是否存在语法错误，只是我正在寻找突出显示的电子表格颜色XML属性动态
谢谢!!
使用个人模板，而不是使用许多嵌套的 for-each 元素。所以我建议重组整个样式表，这样每个模板都可以处理源代码树的一部分。另外，由于您必须生成不遵循源代码树结构的附加数据，因此可以调用命名模板。我把这个答案分为两部分。第二部分将尝试回答您的问题。
第1部分：重构样式表
在模板中匹配根（ / ），您将只放置文件的不变的基本结构。在其中可以使用< apply-templates select =* / * / table/> ，以便选择与表格中的数据匹配的模板（跳过工作簿和 Wrkbook ）。由于标题涉及基于颜色的样式的创建，我们将在后面处理。还生成所需的<？mso-application progid =Excel.Sheet？> 处理指令：
< xsl：template match ='/'>
< xsl：processing-instruction name =mso-application> progid =Excel.Sheet< / xsl：processing-instruction>
<工作簿>
<样式>
<！ - 在这里为每种颜色制作样式元素 - >
< / Styles>
<工作表ss：Name ='Scores'>
<表x：FullColumns ='1'x：FullRows ='1'>
< xsl：apply-templates select =* / * / table/> <！ - 将处理树的其余部分 - >
< /表>
< / Worksheet>
< / Workbook>
< / xsl：template>
表的模板也很简单。根据提供的样式表 ，它包含一个< Row> ，其中包含 Colnames / ColumnName 元素在您的源XML中，几个< Row> 元素从每个< Rows> 元素，而一个空 < Row> 包含一个空的< Cell> 。这可以这样重写：
< xsl：template match =table>
< Row>< xsl：apply-templates select ='Colnames / ColumnName'/>< / Row>
< xsl：apply-templates select ='Rows'/>
< Row>< Cell />< / Row>
< / xsl：template>
有两个 < xsl：apply -templates /> 。第一个将调用一个模板来构建包含几个 ColumnName 的第一个< Row> 。第二个将构建单个数据行。这是将为每个 ColumnName 调用的模板：
; xsl：template match =ColumnName>
<单元格ss：样式ID ='s41'>
<数据ss：Type ='String'>
< xsl：value-of select ='。'disable-output-escaping ='yes'/>
< / Data>
< / Cell>
< / xsl：template>
这个将为每个行调用：
< xsl：template match =Rows>
< Row>< xsl：apply-templates select ='CI'/>< / Row>
< / xsl：template>
再次另一个< xsl：aply-templates /> 。这将处理将包含单元格数据的每个 CI 元素。它将必须从每个元素读取 Color 属性，并决定如何渲染它。目前，您只需要区分 Green 而不需要。我们可以将其重写为：
< xsl：template match =CI>
< xsl：choose>
< xsl：when test =@ Color ='Green'>
<单元格ss：样式ID ='绿色'>
< xsl：call-template name =print-cell/>
< / Cell>
< / xsl：when>
< xsl：否则>
<单元格ss：样式ID ='s38'>
< xsl：call-template name =print-cell/>
< / Cell>
< / xsl：否则>
< / xsl：choose>
< / xsl：template>
删除重复并将单元格打印放在命名模板中，我们用< xsl：call-template> ：
< xsl：template name =print -cell>
<数据ss：Type ='String'>
< xsl：value-of select ='。'disable-output-escaping ='yes'/>
< / Data>
< / xsl：template>
第2部分：添加颜色信息
现在，样式表在较小的模板中进行了重组，我们可以继续修改它，以便正确地读取颜色信息。
首先我们应该创建一个键包含所有可用的颜色：
< xsl：key name =colors match =CI / @ Coloruse =。/>
这将匹配所有颜色属性元素 CI 我们可以使用 Color 属性的内容来选择它。
在根模板，我们可以通过为每种颜色构建一个样式元素来构建< Styles> 块。我们将在单独的模板中执行此操作，但首先需要在文档中的 颜色之间进行迭代。这可以通过在XSLT 1.0中使用 Muenchian分组技术来完成（如果您是使用XSLT 2.0，您可以使用 for-each-group ）以更简单的方式处理此问题：
<样式>
< xsl：for-each select =// CI / @ Color [count（。| key（'colors'，。）[1]）= 1]>
< xsl：call-template name =make-style>
< xsl：with-param name =colorselect =。/>
< / xsl：call-template>
< / xsl：for-each>
< / Styles>
这将调用 make-style 模板为您的源文档中找到的每个唯一颜色。正在使用包含当前颜色的参数 color 调用模板。
我们需要每种颜色的ID。可以使用颜色名称，但是ID不能以散列开始，因此解决问题的一种方法是使用删除＃的颜色代码。您可以使用XPath translate（）函数： translate（$ color，'＃'，''）
由于您的源数据仍然使用 Green 颜色，并且不符合该模式，因此我们有分别处理。要设置样式 ss：ID 属性，我们需要使用原始属性的内容，如果 $ color 是绿色并剥离＃否则
< xsl：choose>
< xsl：when test =$ color ='Green'>
< xsl：value-of select =$ color/>
< / xsl：when>
< xsl：否则>
< xsl：value-of select =translate（$ color，'＃'，''）/>
< / xsl：否则>
< / xsl：choose>
我们还必须这样做来构建 Interior 元素：
< xsl：choose>
< xsl：when test =$ color ='Green'>
<内部ss：Color =＃CCFFCCss：Pattern =Solid/>
< / xsl：when>
< xsl：否则>
< Interior ss：Color ={$ color}ss：Pattern =Solid/>
< / xsl：否则>
< / xsl：choose>
这是最终的 make-style 模板：
< xsl：template name =make-style>
< xsl：param name =color/>
<样式ss：ID =绿色>
< xsl：attribute name =ss：ID>
< xsl：choose>
< xsl：when test =$ color ='Green'>
< xsl：value-of select =$ color>< / xsl：value-of>
< / xsl：when>
< xsl：否则>
< xsl：value-of select =translate（$ color，'＃'，''）/>
< / xsl：否则>
< / xsl：choose>
< / xsl：attribute>
<边框>
< Border ss：Position =Bottomss：LineStyle =Continuousss：Weight =1/>
< Border ss：Position =Leftss：LineStyle =Continuousss：Weight =1/>
< Border ss：Position =Rightss：LineStyle =Continuousss：Weight =1/>
< Border ss：Position =Topss：LineStyle =Continuousss：Weight =1/>
< / Borders>
<字体ss：FontName =Calibrix：Family =Swissss：Size =11/>
< xsl：choose>
< xsl：when test =$ color ='Green'>
<内部ss：Color =＃CCFFCCss：Pattern =Solid/>
< / xsl：when>
< xsl：否则>
< Interior ss：Color ={$ color}ss：Pattern =Solid/>
< / xsl：否则>
< / xsl：choose>
< / Style>
< / xsl：template>
现在我们可以重构 CI 模板添加一个 xsl：当块生成具有 StyleID 的单元格，以获得适当的颜色：
< xsl：template match =CI>
< xsl：choose>
< xsl：when test =@ Color ='Green'>
<单元格ss：样式ID ='绿色'>
< xsl：call-template name =print-cell/>
< / Cell>
< / xsl：when>
< xsl：when test =starts with with（@Colour，'＃'）>
<单元格ss：StyleID ={translate（@Colour，'＃'，''）}>
< xsl：call-template name =print-cell/>
< / Cell>
< / xsl：when>
< xsl：否则>
<单元格ss：样式ID ='s38'>
< xsl：call-template name =print-cell/>
< / Cell>
< / xsl：否则>
< / xsl：choose>
< / xsl：template>
这是最终的样式表。
<？xml version =1.0encoding =UTF-8？>
< xsl：stylesheet xmlns：xsl =http://www.w3.org/1999/XSL/Transform
xmlns：xs =http://www.w3.org/2001 / XMLSchema
exclude-result-prefixes =xs
xmlns：x =urn：schemas-microsoft-com：office：excel
xmlns：ss =urn： microsoft-com：office：spreadsheet
version =1.0>
< xsl：output indent =yes/>
< xsl：key name =colorsmatch =CI / @ Coloruse =。/>
< xsl：template match ='/'>
< xsl：processing-instruction name =mso-application> progid =Excel.Sheet< / xsl：processing-instruction>
<工作簿>
<样式>
< xsl：for-each select =// CI / @ Color [count（。| key（'colors'，。）[1]）= 1]>
< xsl：call-template name =make-style>
< xsl：with-param name =colorselect =。/>
< / xsl：call-template>
< / xsl：for-each>
< / Styles>
<工作表ss：Name ='Scores'>
<表x：FullColumns ='1'x：FullRows ='1'>
< xsl：apply-templates select =* / * / table/>
< /表>
< / Worksheet>
< / Workbook>
< / xsl：template>
< xsl：template match =table>
< Row>< xsl：apply-templates select ='Colnames / ColumnName'/>< / Row>
< xsl：apply-templates select ='Rows'/>
< Row>< Cell />< / Row>
< / xsl：template>
< xsl：template match =ColumnName>
<单元格ss：样式ID ='s41'>
<数据ss：Type ='String'>
< xsl：value-of select ='。'disable-output-escaping ='yes'/>
< / Data>
< / Cell>
< / xsl：template>
< xsl：template match =Rows>
< Row>< xsl：apply-templates select ='CI'/>< / Row>
< / xsl：template>
< xsl：template name =make-style>
< xsl：param name =color/>
<样式ss：ID =绿色>
< xsl：attribute name =ss：ID>
< xsl：choose>
< xsl：when test =$ color ='Green'>
< xsl：value-of select =$ color>< / xsl：value-of>
< / xsl：when>
< xsl：否则>
< xsl：value-of select =translate（$ color，'＃'，''）/>
< / xsl：否则>
< / xsl：choose>
< / xsl：attribute>
<边框>
< Border ss：Position =Bottomss：LineStyle =Continuousss：Weight =1/>
< Border ss：Position =Leftss：LineStyle =Continuousss：Weight =1/>
< Border ss：Position =Rightss：LineStyle =Continuousss：Weight =1/>
< Border ss：Position =Topss：LineStyle =Continuousss：Weight =1/>
< / Borders>
<字体ss：FontName =Calibrix：Family =Swissss：Size =11/>
< xsl：choose>
< xsl：when test =$ color ='Green'>
<内部ss：Color =＃CCFFCCss：Pattern =Solid/>
< / xsl：when>
< xsl：否则>
< Interior ss：Color ={$ color}ss：Pattern =Solid/>
< / xsl：否则>
< / xsl：choose>
< / Style>
< / xsl：template>
< xsl：template match =CI>
< xsl：choose>
< xsl：when test =@ Color ='Green'>
<单元格ss：样式ID ='绿色'>
< xsl：call-template name =print-cell/>
< / Cell>
< / xsl：when>
< xsl：when test =starts-with（@Colour，'＃'）>
<单元格ss：StyleID ={translate（@Colour，'＃'，''）}>
< xsl：call-template name =print-cell/>
< / Cell>
< / xsl：when>
< xsl：否则>
<单元格ss：样式ID ='s38'>
< xsl：call-template name =print-cell/>
< / Cell>
< / xsl：否则>
< / xsl：choose>
< / xsl：template>
< xsl：template name =print-cell>
<数据ss：Type ='String'>
< xsl：value-of select ='。'disable-output-escaping ='yes'/>
< / Data>
< / xsl：template>
< / xsl：stylesheet>
您可以在这个 XSLT小提琴 。
I got an XML source file (from other source) and its respective XSL file to get the XML transformed into Excel Spreadsheet.
What i need is to dynamically change background color of cell in spreadsheet based on XML attribute for a node using XSL
Example: Source.XML content
<workbooks>
<Wrkbook
<table Id="My table 1">
<Colnames>
<ColumnName>Student ID</ColumnName>
<ColumnName>Student Name</ColumnName>
<ColumnName>Subject 1</ColumnName>
<ColumnName>Subject 2</ColumnName>
<ColumnName>Subject 3</ColumnName>
<ColumnName>Subject 4</ColumnName>
<ColumnName>Subject 5</ColumnName>
<ColumnName>Subject 6</ColumnName>
</Colnames>
<Rows>
<CI>534</CI>
<CI>Ramu</CI>
<CI>67</CI>
<CI Colour="Green">67</CI>
<CI Colour="#e8e9e8">48</CI>
<CI>66</CI>
<CI Colour="#B3C389">39</CI>
<CI>67</CI>
</Rows>
<Rows>
<CI>534</CI>
<CI>Raul</CI>
<CI Colour="Green">63</CI>
<CI>89</CI>
<CI Colour="#007788">67</CI>
<CI>57</CI>
<CI>75</CI>
<CI Colour="#AABBCC">92</CI>
</Rows>
</table>
</Wrkbook>
</workbooks>
What i used to do earlier is i used to have a specific style for green and Yelow in XSL sheet for the attributes in their respective XML tags, hence i could manage it. But now the source file contains color codes and i need to highlight based on color code. XSL File :
<xsl:template match='/'>
<Workbook>
<Styles>
<Style ss:ID="Green">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11"/>
<Interior ss:Color="#CCFFCC" ss:Pattern="Solid"/>
</Style>
</Styles>
<xsl:for-each select='workbooks'>
<xsl:for-each select='Wrkbook'>
<Worksheet ss:Name='Scores'>
<Table x:FullColumns='1' x:FullRows='1'>
<xsl:for-each select='table'>
<Row>
<xsl:for-each select='Colnames/ColumnName'>
<Cell ss:StyleID='s41'>
<Data ss:Type='String'>
<xsl:value-of select='.' disable-output-escaping='yes'/>
</Data>
</Cell>
</xsl:for-each>
</Row>
<xsl:for-each select='Rows'>
<Row>
<xsl:for-each select='CI'>
<xsl:choose>
<xsl:when test="@Colour = 'Green'">
<Cell ss:StyleID='Green'>
<Data ss:Type='String'>
<xsl:value-of select='.' disable-output-escaping='yes'/>
</Data>
</Cell>
</xsl:when>
<xsl:otherwise>
<Cell ss:StyleID='s38'>
<Data ss:Type='String'>
<xsl:value-of select='.' disable-output-escaping='yes'/>
</Data>
</Cell>
</xsl:otherwise>
</xsl:choose>
</xsl:for-each>
</Row>
</xsl:for-each>
<Row>
<Cell></Cell>
</Row>
</xsl:for-each>
</Table>
</Worksheet>
</xsl:for-each>
</xsl:for-each>
</Workbook>
</xsl:template>
Please ignore if there are syntax errors in XSL, just i am looking for highlighting the spreadsheet color based on XML attribute dynamically.
Thank you!!
It's much easier to work with individual templates instead of many nested for-each elements. So I would suggest reorganizing the entire stylesheet so each template can deal with a part of the source tree. Also, since you have to generate additional data which doesn't follow the structure of the source tree, it would be appropriate to have named templates that you could call. I organized this answer in two parts. The second part will attempt to answer your question.
Part 1: refactoring the stylesheet
In the template that matches root (/) you would place only the invariant basic structure of the file. Inside it you can use <apply-templates select="*/*/table"/> so select the templates that will match the data in your table (skipping workbooks and Wrkbook). Since the header involves the creation of styles based on colours, we will deal with it later. The required <?mso-application progid="Excel.Sheet" ?> processing instruction is also generated:
<xsl:template match='/'>
<xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>
<Workbook>
<Styles>
<!-- make Style elements for each color here -->
</Styles>
<Worksheet ss:Name='Scores'>
<Table x:FullColumns='1' x:FullRows='1'>
<xsl:apply-templates select="*/*/table"/> <!-- will process the rest of the tree -->
</Table>
</Worksheet>
</Workbook>
</xsl:template>
The template for table is also very simple. Based on the stylesheet you provided, it consists on one <Row> with data from the Colnames/ColumnName elements in your source XML, several <Row> elements from each <Rows> element, and one empty <Row> containing an empty <Cell>. This can be rewritten like this:
<xsl:template match="table">
<Row><xsl:apply-templates select='Colnames/ColumnName'/></Row>
<xsl:apply-templates select='Rows'/>
<Row><Cell/></Row>
</xsl:template>
There are two <xsl:apply-templates/>. The first one will call a template to build the first <Row> containing several ColumnNames. The second will build the individual data rows. This is the template which will be called for each ColumnName:
<xsl:template match="ColumnName">
<Cell ss:StyleID='s41'>
<Data ss:Type='String'>
<xsl:value-of select='.' disable-output-escaping='yes'/>
</Data>
</Cell>
</xsl:template>
And this one will be called for each Rows:
<xsl:template match="Rows">
<Row><xsl:apply-templates select='CI'/></Row>
</xsl:template>
Again another <xsl:aply-templates/>. This one will process each CI element which will contain the Cell data. It will have to read the Colour attribute from each element and decide how to render it. Currently you only need to distinguish Green and nothing. We could rewrite it as:
<xsl:template match="CI">
<xsl:choose>
<xsl:when test="@Colour = 'Green'">
<Cell ss:StyleID='Green'>
<xsl:call-template name="print-cell"/>
</Cell>
</xsl:when>
<xsl:otherwise>
<Cell ss:StyleID='s38'>
<xsl:call-template name="print-cell"/>
</Cell>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
removing duplication and placing the cell printing in a named template, which we call with <xsl:call-template>:
<xsl:template name="print-cell">
<Data ss:Type='String'>
<xsl:value-of select='.' disable-output-escaping='yes'/>
</Data>
</xsl:template>
Part 2: Adding colour information
Now that the stylesheet is reorganized in smaller templates, we can proceed to adapt it so it will read colour information correctly.
First we should make a key containing all the colours that are avaliable:
<xsl:key name="colours" match="CI/@Colour" use="."/>
This will match all Colour attributes in all CI elements. We can use the contents of the Colour attribute to select it.
In the root template, we can build the <Styles> block by building a Style element for each colour. We will do that in a separate template, but first we need to iterate among the unique colours in your document. This can be done by using the Muenchian grouping technique in XSLT 1.0 (if you are using XSLT 2.0 you can handle this in a simpler way using for-each-group):
<Styles>
<xsl:for-each select="//CI/@Colour[count(. | key('colours', .)[1]) = 1]">
<xsl:call-template name="make-style">
<xsl:with-param name="colour" select="."/>
</xsl:call-template>
</xsl:for-each>
</Styles>
This will call the make-style template for each unique colour found in your source document. The template is being called with a parameter colour which contains the current colour.
We need IDs for each colour. The colour name could be used but the ID can't start with a hash, so one way to solve the problem is use the colour code removing the #. You can use the XPath translate() function for that: translate($colour,'#','').
Since your source data still uses the Green colour, and it doesn't follow that pattern, we have to deal with it separately. To set the Style ss:ID attribute we need to do use the contents of the original attribute if the $colour is Green and strip the # otherwise:
<xsl:choose>
<xsl:when test="$colour = 'Green'">
<xsl:value-of select="$colour"/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="translate($colour,'#','')"/>
</xsl:otherwise>
</xsl:choose>
We also have to do that to build the Interior element:
<xsl:choose>
<xsl:when test="$colour = 'Green'">
<Interior ss:Color="#CCFFCC" ss:Pattern="Solid"/>
</xsl:when>
<xsl:otherwise>
<Interior ss:Color="{$colour}" ss:Pattern="Solid"/>
</xsl:otherwise>
</xsl:choose>
This is the final make-style template:
<xsl:template name="make-style">
<xsl:param name="colour"/>
<Style ss:ID="Green">
<xsl:attribute name="ss:ID">
<xsl:choose>
<xsl:when test="$colour = 'Green'">
<xsl:value-of select="$colour"></xsl:value-of>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="translate($colour,'#','')"/>
</xsl:otherwise>
</xsl:choose>
</xsl:attribute>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11"/>
<xsl:choose>
<xsl:when test="$colour = 'Green'">
<Interior ss:Color="#CCFFCC" ss:Pattern="Solid"/>
</xsl:when>
<xsl:otherwise>
<Interior ss:Color="{$colour}" ss:Pattern="Solid"/>
</xsl:otherwise>
</xsl:choose>
</Style>
</xsl:template>
Now we can refactor the CI template adding a xsl:when block to generate a cell with a StyleID for the appropriate colour:
<xsl:template match="CI">
<xsl:choose>
<xsl:when test="@Colour = 'Green'">
<Cell ss:StyleID='Green'>
<xsl:call-template name="print-cell"/>
</Cell>
</xsl:when>
<xsl:when test="starts-with(@Colour, '#')">
<Cell ss:StyleID="{translate(@Colour,'#','')}">
<xsl:call-template name="print-cell"/>
</Cell>
</xsl:when>
<xsl:otherwise>
<Cell ss:StyleID='s38'>
<xsl:call-template name="print-cell"/>
</Cell>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
This is the final stylesheet.
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
exclude-result-prefixes="xs"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
version="1.0">
<xsl:output indent="yes"/>
<xsl:key name="colours" match="CI/@Colour" use="."/>
<xsl:template match='/'>
<xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>
<Workbook>
<Styles>
<xsl:for-each select="//CI/@Colour[count(. | key('colours', .)[1]) = 1]">
<xsl:call-template name="make-style">
<xsl:with-param name="colour" select="."/>
</xsl:call-template>
</xsl:for-each>
</Styles>
<Worksheet ss:Name='Scores'>
<Table x:FullColumns='1' x:FullRows='1'>
<xsl:apply-templates select="*/*/table"/>
</Table>
</Worksheet>
</Workbook>
</xsl:template>
<xsl:template match="table">
<Row><xsl:apply-templates select='Colnames/ColumnName'/></Row>
<xsl:apply-templates select='Rows'/>
<Row><Cell/></Row>
</xsl:template>
<xsl:template match="ColumnName">
<Cell ss:StyleID='s41'>
<Data ss:Type='String'>
<xsl:value-of select='.' disable-output-escaping='yes'/>
</Data>
</Cell>
</xsl:template>
<xsl:template match="Rows">
<Row><xsl:apply-templates select='CI'/></Row>
</xsl:template>
<xsl:template name="make-style">
<xsl:param name="colour"/>
<Style ss:ID="Green">
<xsl:attribute name="ss:ID">
<xsl:choose>
<xsl:when test="$colour = 'Green'">
<xsl:value-of select="$colour"></xsl:value-of>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="translate($colour,'#','')"/>
</xsl:otherwise>
</xsl:choose>
</xsl:attribute>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11"/>
<xsl:choose>
<xsl:when test="$colour = 'Green'">
<Interior ss:Color="#CCFFCC" ss:Pattern="Solid"/>
</xsl:when>
<xsl:otherwise>
<Interior ss:Color="{$colour}" ss:Pattern="Solid"/>
</xsl:otherwise>
</xsl:choose>
</Style>
</xsl:template>
<xsl:template match="CI">
<xsl:choose>
<xsl:when test="@Colour = 'Green'">
<Cell ss:StyleID='Green'>
<xsl:call-template name="print-cell"/>
</Cell>
</xsl:when>
<xsl:when test="starts-with(@Colour, '#')">
<Cell ss:StyleID="{translate(@Colour,'#','')}">
<xsl:call-template name="print-cell"/>
</Cell>
</xsl:when>
<xsl:otherwise>
<Cell ss:StyleID='s38'>
<xsl:call-template name="print-cell"/>
</Cell>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<xsl:template name="print-cell">
<Data ss:Type='String'>
<xsl:value-of select='.' disable-output-escaping='yes'/>
</Data>
</xsl:template>
</xsl:stylesheet>
You can see the results and make adjustments in this XSLT fiddle.
这篇关于Excel电子表格使用XML和XSLT的动态单元格着色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
