dingsir的个人博客分享 http://blog.sciencenet.cn/u/dingsir

博文

学习笔记: Excel文件格式之条件格式研究及XSL实现

已有 1537 次阅读 2020-8-6 01:11 |个人分类:软件杂谈|系统分类:教学心得| Excel, 文件格式, 条件格式, XML, XLST, Excel, XML, Excel, XML

以前我对Word/Excel这类文件的结构很感兴趣,一直想了解它是如何存贮数据的,但苦于找不到相关的资料,而且听说它的文件结构也相当复杂,我等无法理解,因此这种想法也一直是个悬念而已. 随着时代发展, 这些二进制格式的文档由于生成和提取信息都很困难, 影响到了Office在网络时代的数据再利用, Office在2007版之后引进了docx和xlsx等新一代基于XML格式的文档之后,慢慢的这些格式也被用户接受了.原来的版本也日趋老去,微软也在网上开始公开这些过去的二进制的文件结构了. 感兴趣的朋友可以访问 Office文件格式规范   Office文件格式规范 链接2   了解Excel二进制文件格式    了解Word二进制文件格式       因为二进制文件格式使用了大量的结构记录,数据的读取与指针\记录大小\位置偏移等关系密切,一旦某处代码有错,二进制数据很容易越界,影响前后部分的读写, 生成的文件很容易出错.因此对这类文档的读写要求很高的精确性,实现起来不容易.

在新一代的文档格式中,本质上大部分都是文本形式的XML文档,而且各个部分是按一定规则分离存储的,一个部件损坏不会导致全部数据失效,文件的强壮程度好了很多.比如在Excel的XLSX格式中, 整个文档是一个压缩包,里面包括了多个部分.有的存贮数值,有的存贮样式,有的存贮查询表,有的存贮共享字串.各自有一定的独立性.比如本文中要实现的条件格式,它在文档中和表格的数据就是分开存贮的. 单元格数据在<sheetData>元素内部, 条件格式在<conditionalFormatting>元素内部.

本文涉及的文件在此,由于本博客系统的代码排版很丑, 有兴趣的可以看以下位置的PDF或PNG图片:

链接:https://pan.baidu.com/s/1Zhs42hD_RJhLza3Rky9EZg 

提取码:hiu8

我们先来看看Excel的XLSX文件的结构,这里举例的文件使用上一博客中生成的sampledata.xlsx, 为了便于查看结构,将后缀xlsx改为zip再查看:

2020-08-05_22-30-44.png

在xl目录下的workbook.xml存放的是指向表单的引用,大概内容如下:

<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里放的是样式的规定,这个后面再讲.

我们转到worksheets目录下看:


2020-08-05_22-37-26.png


有三个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&gt;20</formula> <!--大于号被转义为&gt; -->
          </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&gt;50</formula>
          </cfRule>
         </conditionalFormatting>
       ...
    
      </worksheet>
    </xsl:result-document>
  </xsl:template>
</xsl:stylesheet>

其实理解了条件格式的公式设置, 如何生成条件格式并不复杂.

最后还剩下一个dxfId是什么东西没有说. 是这样的: Excel中为了减少存贮各种格式设置的空间占用, 建立了一套套样式的全集, 每个样式在对应的集合中都有编号. 用到了哪一项,就用一个属性存贮一下这个索引值. 需要用的时候就按索引找来使用即可. 这些样式都存在压缩包的目录xl下style.xml文件中. 与这个相关的部分我掏出来加上注释如下:

<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




http://blog.sciencenet.cn/blog-1213210-1245157.html

上一篇:锂离子电池电解液基础知识培训PPT(修正链接不能下载文件的问题)
下一篇:详解Excel的Open XML中单元格样式的cellStyleXfs,cellStyle,cellXfs之间关系

0

该博文允许注册用户评论 请点击登录 评论 (0 个评论)

数据加载中...

Archiver|手机版|科学网 ( 京ICP备07017567号-12 )

GMT+8, 2020-9-28 23:30

Powered by ScienceNet.cn

Copyright © 2007- 中国科学报社

返回顶部