科学网—标签

相关帖子	版块	作者	回复/查看	最后发表

详解Excel的Open XML中单元格样式的cellStyleXfs，cellStyle，cellXfs之间关系

dingsir 2020-8-18 01:03

详解Excel的Open XML中单元格样式的cellStyleXfs，cellStyle，cellXfs之间关系 Excel的新文档格式xlsx使用了SpreadsheetML标记语言(以下简称sml), 这个规范中比较容易混淆的定义就有单元格的样式设置,有三个名字很相近的集合: cellXfs, cellStyle, cellStyleXfs, 一下子不容易搞懂. 这三个元素存在于/xl/Styles.xml 背景知识: 设计目的: 为了减少重复项的特征反复存贮,sml使用了集合的方法来存贮各种不同的格式,全部放在一起. 需要用到它的地方只需要某一个索引,指向这个集合中的某一项就可以了.这样就大大减少了描述样式的字符或数据,做到一次存贮,多处使用. 当讨论某个单元格具体的设置时,用到的是集合的某一种样式,我把这个叫做集合的一个子项. 比如集合有5种样式,我这次只用了第3种.这个第3种就是集合的一个子项.sml通过索引来指向这个子项,索引从0开始计数, 第1项索引为0, 第N项索引为N-1.这个第3项的索引就是2. 集合的属性中设置了count属性,代表每个集合下面有多少个子项, count属性由Excel自动维护. 这三种都是复杂类型,有嵌套定义. 区别所在为了防止大家记名字时混淆, 先来个不太严谨的介绍: cellXfs 存贮了已经用了的单元格格式 . xsd:complexType name = CT_CellXfs xsd:sequence xsd:element name = xf type = CT_Xf minOccurs = 1 maxOccurs = unbounded / / xsd:sequence xsd:attribute name = count type = xsd:unsignedInt use = optional / / xsd:complexType 这个集合就是CT_Xf类型的记录的汇总.这个CT_Xf类型后面展开讲. cellStyle 存贮了可能用到也可能还没有使用的单元格样式的粗略信息. 有点像Word的样式, Word文档可能有许多种样式,但不是每一种都会在文档中应用. 可以理解为这是一个可能用得到的样式库. 它至少要存贮默认样式Normal或是常规. xsd:complexType name = CT_CellStyles xsd:sequence xsd:element name = cellStyle type = CT_CellStyle minOccurs = 1 maxOccurs = unbounded / !--至少有1项-- / xsd:sequence xsd:attribute name = count type = xsd:unsignedInt use = optional / !--属性count是可选的,但通常都有-- / xsd:complexType !--看看上面的CT_CellStyle类型展开后是什么样的: -- xsd:complexType name = CT_CellStyle !--可以看出,这里没有具体的格式设置信息-- xsd:sequence xsd:element name = extLst type = CT_ExtensionList minOccurs = 0 maxOccurs = 1 / / xsd:sequence xsd:attribute name = name type = s:ST_Xstring use = optional / !--但存贮了样式的名称-- xsd:attribute name = xfId type = ST_CellStyleXfId use = required / !--具体的样式由这个参数指定, 内容存贮在cellStylesXfs集合-- xsd:attribute name = builtinId type = xsd:unsignedInt use = optional / !--指定格式时,实际上看builtinId而不是name.多语言情景-- xsd:attribute name = iLevel type = xsd:unsignedInt use = optional / xsd:attribute name = hidden type = xsd:boolean use = optional / xsd:attribute name = customBuiltin type = xsd:boolean use = optional / !--是自定义内建样式吗? -- / xsd:complexType cellStyles从名字就看得出来,是样式的合集, 由多个子项(CT_cellStyle类型,注意这个类型没有s后缀了) 汇总而成. 子项也只存贮了样式名称、内部Id等信息,不含有具体的样式设置,但有一个属性xfId告诉Excel，从哪里找到样式的具体规定. 到哪里找这个样式，自然就是从下面这个集合里找： cellStyleXfs 这个集合存放的是cellStyles中各种样式对应的格式设置信息. 用到了没有用到的都可以放在这里. 也有可能用到了的不放在这里. 但是cellStyles中指定的xfId,在这里应该都可以找得到相应的子项. 上一个集合cellStyles定义了样式的名称和内部Id, 这个cellStyleXfs集合定义了样式的具体设置. 连接这两个集合的就是上面cellStyles集合的子项的属性 xfId, 它实际就是这个cellStyleXfs集合的子项的索引,当然也是从0开始数的. xsd:complexType name = CT_CellStyleXfs xsd:sequence xsd:element name = xf type = CT_Xf minOccurs = 1 maxOccurs = unbounded / / xsd:sequence xsd:attribute name = count type = xsd:unsignedInt use = optional / / xsd:complexType 下面我来举实例加深一下印象,以下是同一文件中的内容: cellStyles count = 1 cellStyle name = 常规 xfId = 0 builtinId = 0 / / cellStyles cellStyleXfs count = 1 xf numFmtId = 0 fontId = 0 fillId = 0 borderId = 0 alignment vertical = center / / xf / cellStyleXfs cellStyles集合只有一项名字为常规的设置,它的xfID=0, 指向cellStyleXfs集合中第1项(也是唯一的一项), 具体设置是: 数字格式,字体,填充,边框都没有规定,排列居中. cellXfs count = 8 !--内容太长删除一些-- xf numFmtId = 0 fontId = 0 fillId = 0 borderId = 0 xfId = 0 alignment vertical = center / / xf xf numFmtId = 0 fontId = 2 fillId = 0 borderId = 0 xfId = 0 applyFont = 1 alignment vertical = center / / xf xf numFmtId = 0 fontId = 2 fillId = 2 borderId = 0 xfId = 0 applyFont = 1 applyFill = 1 alignment vertical = center / / xf xf numFmtId = 176 fontId = 0 fillId = 0 borderId = 0 xfId = 0 applyNumberFormat = 1 alignment vertical = center / / xf / cellXfs 这个cellXfs集合中, 汇集了8种样式,但多数都没有出现在cellStyles中. 可以这么理解: 这三个集合中, cellStyles和cellStyleXfs是配合使用的,相当于一个公版的有名字的样式库,但里面的样式不一定都在表格中应用了; cellXfs集合是已经使用了的各种样式的全集,但它有点像个没有名字的样式库,它的各个子项就是各种样式规定,只是没有指定名字. CT_Xf类型心细的读者可能发现, cellStyleXfs集合和cellXfs集合, 其定义是相同的: 有count属性,有至少一个CT_xf类型的子项. 那我们来看看这两者共同的子项是怎么定义的: xsd:complexType name = CT_Xf !--这个定义很重要,CellStyleXfs和CellXfs的子项都是这个类型-- xsd:sequence xsd:element name = alignment type = CT_CellAlignment minOccurs = 0 maxOccurs = 1 / xsd:element name = protection type = CT_CellProtection minOccurs = 0 maxOccurs = 1 / xsd:element name = extLst type = CT_ExtensionList minOccurs = 0 maxOccurs = 1 / / xsd:sequence xsd:attribute name = numFmtId type = ST_NumFmtId use = optional / xsd:attribute name = fontId type = ST_FontId use = optional / xsd:attribute name = fillId type = ST_FillId use = optional / xsd:attribute name = borderId type = ST_BorderId use = optional / xsd:attribute name = xfId type = ST_CellStyleXfId use = optional / xsd:attribute name = quotePrefix type = xsd:boolean use = optional default = false / xsd:attribute name = pivotButton type = xsd:boolean use = optional default = false / xsd:attribute name = applyNumberFormat type = xsd:boolean use = optional / xsd:attribute name = applyFont type = xsd:boolean use = optional / xsd:attribute name = applyFill type = xsd:boolean use = optional / xsd:attribute name = applyBorder type = xsd:boolean use = optional / xsd:attribute name = applyAlignment type = xsd:boolean use = optional / xsd:attribute name = applyProtection type = xsd:boolean use = optional / / xsd:complexType 实际上,这个CT_Xf类型就是sml中对单元格样式(不包括文本串字符级别的定义)的全部定义.值得重点研究研究. CT_Xf类型有个参数xfId, 也比较容易混淆. 它用于cellXfs集合时,代表着指向cellStyleXfs集合的零基索引,其类型的名称就表明了是cellStyleXfs的ID,实际就是无符号整数类型. 在cellStyleXfs集合中使用CT_Xf元素时,不用这个属性. 为此xml在规范18.8.45条款中特别提到: For xf records contained in cellXfs this is the zero-based index of an xf record contained in cellStyleXfs corresponding to the cell style applied to the cell. Not present for xf records contained in cellStyleXfs. 看到这里如果你头脑还没有发昏的话,可能会问:既然cellXfs中规定了样式设置的索引, 它又指向了cellStyleXfs中某一项样式,那里也规定了样式设置的索引,那到底单元格会用哪个来设置呢? sml中没有仔细说明,只说了都要读取并进行设置. (可能别的地方说了我不知道,毕竟标准太长了没看完).经过实验我发现是这样的逻辑: 优先看cellXfs记录中的设置(numFmtId, fontId等等),如果这个记录中没有规定applyXXX=0,那就用这个设置,不用看cellStyleXfs中的设置; 也就是如果你在这里修改, 会立即生效. 如果单元格的格式中没有c ...s=X这样的指令,表明是使用默认样式,那就找cellStyles集合中的Normal样式或常规样式. 根据这个记录中的xfId找出cellStyleXfs中索引xfId的子项,那里有详细的设定的索引.. cellXfs记录中的xfId似乎只是表明它的基准样式是哪一款,没有实际用途. 既然当前记录中已经指定了各项设置, 再提供哪些被覆盖掉了的设置又有什么意义呢? 存疑! 从上述定义可以看到, 里面定义了很多格式的属性,还有三种子元素 alignment , protection , extLst . 我们这里只讨论属性的情况,子元素的另文介绍. 这里我们可以看到许多设置的内容了，如数字格式的numFmtId, 字体格式的fontId, 填充样式的ﬁllId, 边框设定的borderId, 等等. 如果你查看这些记录,这里面存储的还是一个个数字,没有实际的格式设置, 你还是不知道设置是什么. 聪明的你可能会想到,这些数字还是某个集合的Id,要想知道详细的设置,要继续找到它的集合. 进一步阅读: 有关单元格样式设置的几个集合. !-- 以下内容与三种集合的区别无关-- 事实正是这样子的, Styles.xml中(与上述三个集合都在这同一个文件)还有几个集合没有介绍,分别是: 数字样式集合. 用于设定单元格里面数字的显示风格,如千分号,小数点后几位等. !--数字的样式集合-- xsd:complexType name = CT_NumFmts xsd:sequence xsd:element name = numFmt type = CT_NumFmt minOccurs = 0 maxOccurs = unbounded / / xsd:sequence xsd:attribute name = count type = xsd:unsignedInt use = optional / / xsd:complexType !--上面是集合,下面是子项的定义:一个ID值,一个字符串来表示格式-- xsd:complexType name = CT_NumFmt xsd:attribute name = numFmtId type = ST_NumFmtId use = required / xsd:attribute name = formatCode type = s:ST_Xstring use = required / / xsd:complexType 定义很简单, 技术都在这个字符串怎么设置上了. ,我们来看一下简单的例子,只有一种样式, 格式就是显示三位小数: numFmts count = 1 numFmt numFmtId = 176 formatCode = 0.000 / / numFmts 实际上有些格式设置的字串可以是很复杂的. 根据定义, Styles.xml中没有这个集合也是允许的. 字体样式集合 !--字体集合的定义-- xsd:complexType name = CT_Fonts xsd:sequence xsd:element name = font type = CT_Font minOccurs = 0 maxOccurs = unbounded / / xsd:sequence xsd:attribute name = count type = xsd:unsignedInt use = optional / / xsd:complexType !--字体子项的定义比较长,有名称/加粗/斜体/阴影/颜色等-- xsd:complexType name = CT_Font xsd:choice maxOccurs = unbounded xsd:element name = name type = CT_FontName minOccurs = 0 maxOccurs = 1 / xsd:element name = charset type = CT_IntProperty minOccurs = 0 maxOccurs = 1 / xsd:element name = family type = CT_FontFamily minOccurs = 0 maxOccurs = 1 / xsd:element name = b type = CT_BooleanProperty minOccurs = 0 maxOccurs = 1 / xsd:element name = i type = CT_BooleanProperty minOccurs = 0 maxOccurs = 1 / xsd:element name = strike type = CT_BooleanProperty minOccurs = 0 maxOccurs = 1 / xsd:element name = outline type = CT_BooleanProperty minOccurs = 0 maxOccurs = 1 / xsd:element name = shadow type = CT_BooleanProperty minOccurs = 0 maxOccurs = 1 / xsd:element name = condense type = CT_BooleanProperty minOccurs = 0 maxOccurs = 1 / xsd:element name = extend type = CT_BooleanProperty minOccurs = 0 maxOccurs = 1 / xsd:element name = color type = CT_Color minOccurs = 0 maxOccurs = 1 / xsd:element name = sz type = CT_FontSize minOccurs = 0 maxOccurs = 1 / xsd:element name = u type = CT_UnderlineProperty minOccurs = 0 maxOccurs = 1 / xsd:element name = vertAlign type = CT_VerticalAlignFontProperty minOccurs = 0 maxOccurs = 1 / xsd:element name = scheme type = CT_FontScheme minOccurs = 0 maxOccurs = 1 / / xsd:choice / xsd:complexType 看个实际的例子, 其实也不复杂,这个集合有3种字体设置: fonts count = 3 x14ac:knownFonts = 1 font sz val = 11 / color theme = 1 / name val = 等线 / family val = 2 / charset val = 134 / scheme val = minor / / font font sz val = 9 / name val = 等线 / family val = 2 / charset val = 134 / scheme val = minor / / font font sz val = 11 / !--字体为11号-- color rgb = FFFF0000 / !--ARGB格式,Alpha R G B各两位-- name val = Microsoft YaHei Light / !--微软雅黑字体-- family val = 2 / !--这两个俺也不了解.-- charset val = 134 / / font / fonts 填充样式集合 xsd:complexType name = CT_Fills xsd:sequence xsd:element name = fill type = CT_Fill minOccurs = 0 maxOccurs = unbounded / / xsd:sequence xsd:attribute name = count type = xsd:unsignedInt use = optional / / xsd:complexType xsd:complexType name = CT_Fill xsd:choice minOccurs = 1 maxOccurs = 1 xsd:element name = patternFill type = CT_PatternFill minOccurs = 0 maxOccurs = 1 / xsd:element name = gradientFill type = CT_GradientFill minOccurs = 0 maxOccurs = 1 / / xsd:choice / xsd:complexType 实际的示例简单: fills count = 3 fill patternFill patternType = none / !--这种无填充-- / fill fill patternFill patternType = gray125 / !--填充样式gray125,内建的样式-- / fill fill patternFill patternType = solid fgColor rgb = FFFF0000 / !--以红色为前景色实心填充-- / patternFill / fill / fills 边框样式集合 !--Borders集合的定义-- xsd:complexType name = CT_Borders xsd:sequence xsd:element name = border type = CT_Border minOccurs = 0 maxOccurs = unbounded / / xsd:sequence xsd:attribute name = count type = xsd:unsignedInt use = optional / / xsd:complexType !--子项 Border是个复合类型,由属性/元素组成.包含了始/终/顶/底/斜线/垂直排列/水平排列等,还有向上斜线,向下斜线,outline等属性-- xsd:complexType name = CT_Border xsd:sequence xsd:element name = start type = CT_BorderPr minOccurs = 0 maxOccurs = 1 / xsd:element name = end type = CT_BorderPr minOccurs = 0 maxOccurs = 1 / xsd:element name = top type = CT_BorderPr minOccurs = 0 maxOccurs = 1 / xsd:element name = bottom type = CT_BorderPr minOccurs = 0 maxOccurs = 1 / xsd:element name = diagonal type = CT_BorderPr minOccurs = 0 maxOccurs = 1 / xsd:element name = vertical type = CT_BorderPr minOccurs = 0 maxOccurs = 1 / xsd:element name = horizontal type = CT_BorderPr minOccurs = 0 maxOccurs = 1 / / xsd:sequence !--子项Border的属性比较简单,都是布尔值-- xsd:attribute name = diagonalUp type = xsd:boolean use = optional / xsd:attribute name = diagonalDown type = xsd:boolean use = optional / xsd:attribute name = outline type = xsd:boolean use = optional default = true / / xsd:complexType !--子项的元素是个复合类型,含有颜色和边框风格两种类型的元素-- xsd:complexType name = CT_BorderPr xsd:sequence xsd:element name = color type = CT_Color minOccurs = 0 maxOccurs = 1 / / xsd:sequence xsd:attribute name = style type = ST_BorderStyle use = optional default = none / / xsd:complexType !--边框的类型ST_BorderStyle是枚举型的字符串,如下 -- xsd:simpleType name = ST_BorderStyle xsd:restriction base = xsd:string xsd:enumeration value = none / xsd:enumeration value = thin / xsd:enumeration value = medium / xsd:enumeration value = dashed / xsd:enumeration value = dotted / xsd:enumeration value = thick / xsd:enumeration value = double / xsd:enumeration value = hair / xsd:enumeration value = mediumDashed / xsd:enumeration value = dashDot / xsd:enumeration value = mediumDashDot / xsd:enumeration value = dashDotDot / xsd:enumeration value = mediumDashDotDot / xsd:enumeration value = slantDashDot / / xsd:restriction / xsd:simpleType 这个太抽象了,三层定义.来个实例看看: borders count = 3 border !--这1种几乎什么都没有规定,采用默认样式-- left / !--这类自封闭的标签表示没有内容-- right / top / bottom / diagonal / / border border !--规定了底边风格/颜色-- left / right / top / bottom style = mediumDashDotDot color rgb = FFFF0000 / / bottom diagonal / / border border diagonalUp = 1 !--规定了斜线方向,底边风格/颜色,斜线风格/颜色-- left / right / top / bottom style = mediumDashDotDot color rgb = FFFF0000 / / bottom diagonal style = double color rgb = FF00B050 / / diagonal / border / borders 其实是很好理解的,只是看起来复杂. applyXXX属性最后,有几个格式如applyXXX的属性.它标识是否启用这一记录中相应的属性是否使用. 假如某子项的属性fontId=2,但是后面还跟了一个applyFont=0,那前面的指定值就不会应用.如果没有指定相应的applyFont=0,就相当于设置了applyFont=1. 规格中话虽然这么说,但我针对 Excel2019 64位的测试表明在cellXfs集合中, 这些applyFont,applyFill无论其值为0还是1,对显示结果都没有影响. 修改xfId指向的cellStyleXfs中的格式,也没有效果. (也就是xfId也是被忽略的). 真正起作用的还是cellXfs子项的fontId, fillId等属性.对它们的修改是马上生效的. applyXXX属性的存在,使得设置一个属性有了两种可能的方式,一种是直接修改如fontId, fillId等的设置;另一种通过否定前面的这一设置来决定不启用这一设置,实质也是设置了另一种选择. MS Office软件在实现这一功能的时候,可能是选择了前一种. 本文的markdown文件和PDF文件在此: 链接： https://pan.baidu.com/s/1nRwozzGm6_7j_SAIualDGA 提取码：h9dd

个人分类: 软件杂谈|4073 次阅读|1 个评论

如何在excel中统计某个单元格中单词的字母个数

Bearjazz 2016-12-10 20:12

#作者信息熊荣川明湖实验室 xiongrongchuan@126.com http://blog.sciencenet.cn/u/Bearjazz 在excel中统计某个单元格中单词的字母个数使用以下函数 =LEN(TRIM(单元格)) 例如如果统计单元格A1 中单词的字母个数使用以下函数 =LEN(TRIM(A1 ))

个人分类: 明湖实验室|20370 次阅读|0 个评论

R语言将单元格内容转化为字符变量

Bearjazz 2014-3-8 15:02

R 语言将单元格内容转化为字符变量 # 作者信息熊荣川六盘水师范学院生物信息学实验室 xiongrongchuan@126.com http://blog.sciencenet.cn/u/Bearjazz # 预装函数发表超过24小时，源代码不再公布。 # 使用方法 A=matrix(1:6,3,2) # 定一个数值矩阵 A A # 查看 A 1 4 2 5 3 6 matchar(A ) # 将 A 第一列的每个单元格都转化为字符变量 1 2 3

个人分类: 我的研究|5742 次阅读|0 个评论

设置单元格垂直居中，并设置单元格高度 latex

orient 2013-2-28 08:27

\begin{table} \centering \begin{tabular}{m{0pt}m{1.7cm}m{1.7cm}m{1.7cm}} \hline \rule{0pt}{8pt} Head1 Head2 Head3 \\\hline \rule{0pt}{8pt} 3 2 3 \\\hline \end{tabular} \end{table} 注释1：m{0pt}设置了一个dummy column，如果不设置这一列，\rule{0pt}{8pt}会使得最靠左的一列垂直居中的设置失效。注释2：\rule{0pt}{8pt} 将表格中单元格撑起来（0pt：线的宽度，8pt：撑起的高度）【这一点参考自：http://suzy8802.blog.163.com/blog/static/2124020642012911325769/】 vertically centered, and adjust row height

33320 次阅读|0 个评论

Excel单元格中的“#########”显示问题

热度 1 Bearjazz 2012-5-4 07:19

Excel 单元格中的“ ######### ”显示问题熊荣川六盘水师范学院生物信息学实验室 xiongrongchuan@126.com http://blog.sciencenet.cn/u/Bearjazz 在 Excel 单元格中输入较长信息是，往往会出现一串“ ######### ”代替原来的文字、数字等内容。这只是软件在显示较长信息时的一种规则，不会改变原来的信息。比如你可以正常的复制该单元格信息。但是往往我们需要单元格显示全部内容。（为了图文并貌，请下载PDF文件观看） Excel单元格中的“#########”显示问题.pdf 具体的操作就是，选择要处理的单元格，点击右键，选择“设置单元格格式” 在弹出来的选项卡中，选择“数字”选项卡中的“常规”；在“对齐”选项卡中选择“自动换行”，然后“确定” 然后就可以了，试试看吧，祝您科研愉快！

个人分类: 我的研究|20496 次阅读|4 个评论

帐号		自动登录	找回密码
密码			注册

关闭安全验证

标签: 单元格

相关帖子

相关日志

关闭 安全验证

标签: 单元格

相关帖子

相关日志

关闭安全验证