如何在 XSLT 中获取特定 .ODS 单元格的任何类型的内容 [英] How to get any type of content of specific .ODS cells in XSLT

问题描述

我正在尝试使用 XSLT 转换 .ods 文件(来自 zip 的 content.xml 文件)以生成所需的 .xml文件.

I am trying to transform an .ods file (content.xml file from the zip) with XSLT in order to produce a desired .xml file.

XSLT 使用元素的固定"位置来获取内容，但在我的 .ods 文件中，我有许多空白字段，我不知道如何在 XSLT 中使它们计数.

XSLT uses "fixed" positions of the elements to get the content, but in my .ods file I have many blank fields and I don't know how to make them count in XSLT.

此外，我对 content.xml 做了一些实验，以确定这些空白(空)单元格是否被保存.

Furthermore, I did some experiments with content.xml to find out if these blank(empty) cells are saved or not.

在 content.xml 中，我发现了类似的东西:

In content.xml I found something like:

<table:table-column table:style-name="co1" table:number-columns-repeated="16384" table:default-cell-style-name="ce1"/>
<table:table-row table:number-rows-repeated="1048576" table:style-name="ro1">

这些值是否以某种方式(例如通过数学计算)表示 .ods 文件中值的空单元格或实际位置?

Are these values somehow (for example with mathematical calculation) representing the empty cells or actual location of the values in the .ods file?

我在这里分享我的文档，让你有更清晰的想法

I share my documents here for you to have more clear ideas

.ods 示例:

这是我的 XSLT 文件:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
xmlns:xs="http://www.w3.org/2001/XMLSchema" 
xmlns:fn="http://www.w3.org/2005/xpath-functions" 
xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" 
xmlns:style="urn:oasis:names:tc:opendocument:xmlns:style:1.0" 
xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" 
xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0" 
xmlns:espd="urn:com:grow:espd:02.00.00" xmlns:cac="urn:X-test:UBL:Pre-
award:CommonAggregate" xmlns:cbc="urn:X-test:UBL:Pre-award:CommonBasic" 


<xsl:output method="html" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="office:spreadsheet/table:table">
<xsl:variable name="test" select="table:table-row/table:table-cell"/>
<p><xsl:value-of select="$test/text:p[1]"/></p>
</xsl:template>

输出是:

burak burak5 burak6 burak2 burak3 burak4 burak7 burak9 burak8 burak10

burak burak5 burak6 burak2 burak3 burak4 burak7 burak9 burak8 burak10

问题:

如何通过对 content.xml 应用转换来从单元格中获取单个值?
(例如:如何仅到达单元格 D4?)

How to get the single values from the cells by applying a transformation on content.xml?
(For example: how to reach just cell D4?)

推荐答案

如何通过对 content.xml 应用转换来从单元格中获取单个值?

How to get the single values from the cells by applying a transformation on content.xml?

.ods 文件的 content.xml 中的 XML 数据以这种方式编码(始终以 table: 命名空间为前缀):

The XML data in the .ods file's content.xml is encoded in this way (always prefixed with the table: namespace):

• 每个table-cell都包含在table-rows
中
• 空 table-cell 使用 RLE(运行-Length-Encoding) 由 number-columns-repeated="..." 属性指示，必须跳过但计数
• 空 table-row 也用 RLE 编码，由 number-rows-repeated="..." 属性表示
• table-columns 好像只用在开头

• Each table-cell is contained in table-rows
• Empty table-cells are encoded empty with RLE (Run-Length-Encoding) indicated by number-columns-repeated="..." attributes and must be skipped but counted
• Empty table-rows are encoded with RLE, too, indicated by the number-rows-repeated="..." attribute
• table-columns seem to be used only at the beginning

因此，要获取特定的单元格，例如D4=4:4，包括跳过的table-rows 必须计算:

So, to get a specific cell, e.g. D4=4:4, the table-rows including the skipped ones have to be counted:

D4 = 4:4 = Get the fourth `table-row`, add one cell D1, then add number-columns-repeated="2"

这是一些 XSLT-1.0 代码(也可用于 2.0 和 3.0)和 GetCellValue 示例:

This is some XSLT-1.0 code (also usable with 2.0 and 3.0) with GetCellValue examples:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
xmlns:xs="http://www.w3.org/2001/XMLSchema" 
xmlns:fn="http://www.w3.org/2005/xpath-functions" 
xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" 
xmlns:style="urn:oasis:names:tc:opendocument:xmlns:style:1.0" 
xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" 
xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0" 
xmlns:espd="urn:com:grow:espd:02.00.00" 
xmlns:cac="urn:X-test:UBL:Pre-award:CommonAggregate" 
xmlns:cbc="urn:X-test:UBL:Pre-award:CommonBasic" exclude-result-prefixes="xs fn office style table text espd cac cbc"> 

<xsl:output method="html" version="4.0" encoding="UTF-8" indent="yes"/>
<xsl:variable name="str" select="'x:1 y:4'" />             <!-- define some coord system -->


  <xsl:template match="/office:document-content/office:body/office:spreadsheet/table:table">
    Table dimensions: <xsl:call-template name="GetDimensions" />
    Value at 5x8: <xsl:call-template name="GetCellValue">
      <xsl:with-param name="x" select="5" />
      <xsl:with-param name="y" select="8" />
    </xsl:call-template>
    Value at 1x4: <xsl:call-template name="GetCellValue">  <!-- use string defined above -->
      <xsl:with-param name="x" select="substring-after(substring-before($str,' '),'x:')" />
      <xsl:with-param name="y" select="substring-after($str,'y:')" />
    </xsl:call-template>
  </xsl:template>

  <xsl:template name="GetCellValue">
    <xsl:param name="x" />
    <xsl:param name="y" />
    <xsl:variable name="targetRow" select="table:table-row[sum(preceding-sibling::*/@table:number-rows-repeated) + position() - count(preceding-sibling::*/@table:number-rows-repeated)= $y]" />
    <xsl:variable name="targetCell" select="$targetRow/table:table-cell[sum(preceding-sibling::*/@table:number-columns-repeated) + position() - count(preceding-sibling::*/@table:number-columns-repeated) &lt;= $x]" />
    <xsl:copy-of select="$targetCell[last()]/text:p/text()" />
  </xsl:template>

  <xsl:template name="GetDimensions">
    <xsl:variable name="firstRow" select="table:table-row[1]/table:table-cell" />
    <xsl:variable name="firstColumn" select="table:table-row" />
    <xsl:variable name="width" select="count($firstRow)+ sum($firstRow/@table:number-columns-repeated) - count($firstRow/@table:number-columns-repeated)" />
    <xsl:variable name="height" select="count($firstColumn)+ sum($firstColumn/@table:number-rows-repeated) - count($firstColumn/@table:number-rows-repeated)" />
    <xsl:value-of select="concat($width,'x',$height)" />
  </xsl:template>

</xsl:stylesheet>

输出为:

Table dimensions: 5x12
Value at 5x8: burak9
Value at 1x4: burak4

我修改了一个 xsl:call-template 以与自定义格式的字符串输入一起使用，例如x:1 y:4.

I modified one xsl:call-template to be used with a string input of a custom format, e.g. x:1 y:4.

Edit2:
可以在在此答案中找到可以一次检索多个单元格作为 XML 元素的 XSLT-2.0 版本.

An XSLT-2.0 version which can retrieve multiple cells at once as XML elements can be found in this answer.

这篇关于如何在 XSLT 中获取特定 .ODS 单元格的任何类型的内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！