博文
学习笔记: Excel文件格式之条件格式研究及XSL实现
|||
因为二进制文件格式使用了大量的结构记录,数据的读取与指针\记录大小\位置偏移等关系密切,一旦某处代码有错,二进制数据很容易越界,影响前后部分的读写, 生成的文件很容易出错.因此对这类文档的读写要求很高的精确性,实现起来不容易.
在新一代的文档格式中,本质上大部分都是文本形式的XML文档,而且各个部分是按一定规则分离存储的,一个部件损坏不会导致全部数据失效,文件的强壮程度好了很多.比如在Excel的XLSX格式中, 整个文档是一个压缩包,里面包括了多个部分.有的存贮数值,有的存贮样式,有的存贮查询表,有的存贮共享字串.各自有一定的独立性.比如本文中要实现的条件格式,它在文档中和表格的数据就是分开存贮的. 单元格数据在<sheetData>元素内部, 条件格式在<conditionalFormatting>元素内部.
本文涉及的文件在此,由于本博客系统的代码排版很丑, 有兴趣的可以看以下位置的PDF或PNG图片:
链接：https://pan.baidu.com/s/1Zhs42hD_RJhLza3Rky9EZg
提取码：hiu8
我们先来看看Excel的XLSX文件的结构,这里举例的文件使用上一博客中生成的sampledata.xlsx, 为了便于查看结构,将后缀xlsx改为zip再查看:
<workbook xmlns:....>
...
<workbookPr defaultThemeVersion="124226"/>
...
<sheets>
<sheet r:id="rId1" sheetId="1" name="Sheet1"/>
<sheet r:id="rId2" sheetId="2" name="Sheet2"/>
<sheet r:id="rId3" sheetId="3" name="Sheet3"/>
</sheets>
...
</workbook>
用三个不同的r:id属性代表了三个不同名字的表单. 同一目录下, 还有一个styles.xml里放的是样式的规定,这个后面再讲.
有三个XML文件,sheet1.xml到sheet3.xml对应三个工作表.其中sheet1.xml存放了我们导入的数据. 为了例于理解条件格式,我把这个文件中相关性不大的部分省略了,留下了部分导入的数据以及生成好的条件格式的部分.,并增加了一些注释. 数据导入的部分,其格式前一博客说过这里不再重复.重点讲条件格式的描述.
<?xml version="1.0" encoding="UTF-8"?> <worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <sheetViews> <sheetView workbookViewId="0" tabSelected="1"> <selection sqref="C2" activeCell="C2"/> <!--活跃的单元格 --> </sheetView> </sheetViews> <sheetFormatPr defaultRowHeight="15"/> <cols> <!--列的宽度等信息--> <col customWidth="1" bestFit="1" width="9" max="2" min="2"/> <col customWidth="1" width="12" max="3" min="3"/> <col customWidth="1" width="13" max="7" min="4"/> <col customWidth="1" width="16.5" max="9" min="8"/> </cols> <!--最重要的部分,表单数据 --> <sheetData> ... <row r="6"> <!--每行一个元素row,下面列出该行内每一个有数据的单元格,第6行为标题行 --> ... <c r="E6" t="inlineStr"> <!--这里导入的文本我特意用了inlineStr类型,没有用共享字串这种类型,是为了简化一点便于理解 --> <is><t>水分</t></is> </c> <c r="F6" t="inlineStr"> <is><t>水分单位</t></is> </c> <c r="G6" t="inlineStr"> <is><t>酸度</t></is> </c> <c r="H6" t="inlineStr"> <is><t>酸度单位</t></is> </c> </row> <row r="7"> <!--第7行开始放数据 --> ... <c r="E7" t="n"> <v>31.3</v> </c> <c r="F7" t="inlineStr"> <is><t>ppm</t></is> </c> <c r="G7" t="n"> <v>25.9</v> </c> <c r="H7" t="inlineStr"> <is><t>ppm</t> </is></c> ... <row r="8"> ... <c r="E8" t="n"> <v>9.2</v> </c> <c r="F8" t="inlineStr"> <is><t>ppm</t></is> </c> <c r="G8" t="n"> <v>59.5</v> </c> <c r="H8" t="inlineStr"> <is><t>ppm</t></is> </c> ... </row> </sheetData> <!--以下为条件格式部分,与sheetData是并列的,允许有多个条件格式的设定 --> <conditionalFormatting sqref="E7:E11"> <!--第1个条件格式设定,其作用范围为sqref指定的范围 --> <cfRule priority="2" dxfId="2" type="expression"> <!--条件格式类型为公式,当条件成立时,格式由dxfID指定. --> <formula>(F7:F11="ppm")*E7>20</formula> <!--这个公式意思是,如果F列数据为字串ppm(成立时括号内公式为True即1),并且E7的值大于20,则按上一行dxfID指定的格式来设定这个单元格. 条件格式中的公式不太好理解,(F7:F11="ppm")*E7 应用于第7行的单元格时,是(F7="ppm")*E7的意思; 相应地,公式(F7:F11="ppm")*E7 作用于第8行的单元格时, 公式是 (F8="ppm")*E8的意思,这样就能理解了.--> </cfRule> </conditionalFormatting> <!--第1条条件格式结束 --> <conditionalFormatting sqref="G7:G11"> <!--第2个条件格式,作用于G7:G11 --> <cfRule priority="2" dxfId="2" type="expression"> <formula>(H7:H11="ppm")*G7>50</formula> </cfRule> </conditionalFormatting> <pageMargins footer="0.3" header="0.3" bottom="0.75" top="0.75" right="0.7" left="0.7"/> <pageSetup r:id="rId1" verticalDpi="0" horizontalDpi="4294967293" orientation="portrait"/> </worksheet>
前面的博客中将xml数据转化为excel文件时,我用了"硬编码"的方式处理异常数据的显示.比如,先判断一下酸度数值是不是大于50,如果大于50则将数值的格式用rPr元素来指定(这种也称为直接样式化),指定了某个单元格的数值显示为红色.
这种转化判断的逻辑要写在XSL的语法中, 实现起来不太灵活,. 既然Excel已经提供了条件格式这样便利的工具,我们何不直接用它来处理呢? 因此,我将转换数据的XSL进行精简, 去除了导入时对数值的逻辑判断(减少了一大堆冗长的xsl:choose/ xsl:when xsl:otherwise),再生成一个条件格式部分,让Excel载入后自己根据条件格式设定异常值的显示效果.
以下为了突出条件格式的生成, 精简了之前展示过的数据导入的内容.
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">
...
<sheetData>
...
<!--建立表格首行 -->
<row r="6">
...
<c r="E6" t="inlineStr">
<is><t>水分</t></is>
</c>
<c r="F6" t="inlineStr">
<is><t>水分单位</t></is>
</c>
<c r="G6" t="inlineStr">
<is><t>酸度</t></is>
</c>
<c r="H6" t="inlineStr">
<is><t>酸度单位</t></is>
</c>
...
</row>
<!--读取数据,逐个导入 这里保留E/F/G/H这几个数据导入的样子,比之前的版本简化多了-->
<xsl:for-each select="样品COA/样品记录">
<xsl:variable name="pos" select="position() + 6"/>
<row r="{$pos}">
...
<c r="E{$pos}" t="n">
<v>
<xsl:value-of select="水分"/>
</v>
</c>
<c r="F{$pos}" t="inlineStr">
<is>
<t>
<xsl:value-of select="水分/@单位"/>
</t>
</is>
</c>
<c r="G{$pos}" t="n">
<v>
<xsl:value-of select="酸度"/>
</v>
</c>
<c r="H{$pos}" t="inlineStr">
<is>
<t>
<xsl:value-of select="酸度/@单位"/>
</t>
</is>
</c>
<!--这里保留了备注导入的内容,可以看出使用XSL语法来判断内容并格式化是比较罗嗦的-->
<c r="Q{$pos}" t="inlineStr">
<xsl:choose>
<xsl:when test="备注 != ''"> <!--如果备注内容不是空的 -->
<is><t>
<xsl:value-of select="备注"/> <!--导入备注的内容 -->
</t></is>
</xsl:when>
<xsl:otherwise> <!--否则 -->
<is>
<r>
<rPr> <!--以rPr元素设置格式 -->
<i/> <!--斜体 -->
<sz val="11"/>
<color rgb="FFD0D0D0"/>
<rFont val="Tahoma"/>
<family val="2"/>
<scheme val="minor"/>
</rPr>
<t>[No MEMO]</t> <!--空备注导入时,设置为[No MEMO] -->
</r>
</is>
</xsl:otherwise>
</xsl:choose> <!-- 结束判断 -->
</c>
</row>
</xsl:for-each>
</sheetData>
<!--重点注意以下内容: 为数据区域建立条件格式-->
<xsl:variable name="LastRow" select ="count(样品COA/样品记录) + 6" /> <!--对样品记录进行计数, 用来设置最后一行的行号 -->
<!--对水分应用条件格式, 当单位为ppm且值大于20ppm时显示为红色,监控E列 -->
<!--突出显示的样式定义在styles.xml中, dxfId=0表示引用<dxhfs>集合中的第一项设置 -->
<!--小技巧: 这里用<xsl:value-of> 求出变量的值,如果只是简单的将{$LastRow}放在公式中,XSL转换时不会自动替换为数值再写入,那样会出错! -->
<conditionalFormatting sqref="E7:E{$LastRow}">
<cfRule type="expression" dxfId="2" priority="2"> <!--当类型为expression时,不需要操作符属性了,下同. -->
<formula>(F7:F<xsl:value-of select="$LastRow"/>="ppm")*E7>20</formula> <!--大于号被转义为> -->
</cfRule>
</conditionalFormatting>
<!--对酸度应用条件格式,当单位为ppm且值大于50ppm时显示为红色 -->
<conditionalFormatting sqref="G7:G{$LastRow}">
<cfRule type="expression" dxfId="2" priority="2" >
<formula>(H7:H<xsl:value-of select="$LastRow"/>="ppm")*G7>50</formula>
</cfRule>
</conditionalFormatting>
...
</worksheet>
</xsl:result-document>
</xsl:template>
</xsl:stylesheet><dxfs count="4"> <!--有四项dxf形成集合dxfs --> <dxf> <!--第1项,索引dxfId=0 --> <font> <color rgb="FFFF0000"/> <!--只设置了字体颜色,Alpha为FF, R值FF,G B分别为00 00 --> </font> </dxf> <dxf> <!--第2项, dxfId=1 --> <font> <b/> <!--粗体--> <i/> <!--斜体 --> <strike/> <!--删除线--> <color rgb="FFFF0000"/> <!--字色为红色 --> </font> <fill> <patternFill> <!--背景有填充色, 一种浅灰色E0E0E0,Alpha值FF --> <bgColor rgb="FFE0E0E0"/> </patternFill> </fill> </dxf> <dxf> <!-- dxfId=2 --> <font> <b/> <i/> <strike/> <color rgb="FFFF0000"/> </font> <fill> <patternFill> <bgColor rgb="FFE0E0E0"/> </patternFill> </fill> </dxf> <dxf> <!--dxfID=3 --> <font> <color rgb="FFFF0000"/> </font> </dxf> </dxfs>
看到这些,你就可以知道(Excel也是),条件格式指定为dxfId=2,就该用上面第3项的字体样式和填充样色来突出显示了.
如果需要研究这些规范,可以查阅SpreadsheetML的schema(以下压缩包中的sml.xsd)
链接：https://pan.baidu.com/s/1nHAfAFx8RBxcN-dY1pfpfw
提取码：sche
https://blog.sciencenet.cn/blog-1213210-1245157.html
上一篇：锂离子电池电解液基础知识培训PPT（修正链接不能下载文件的问题）
下一篇：详解Excel的Open XML中单元格样式的cellStyleXfs，cellStyle，cellXfs之间关系
