python - 在 Linux 中从 Excel 数据透视表电子表格中提取数据

Question

我有一个基于数据透视表的 Excel 电子表格，该表格定期(每月)更新并上传到我的服务器(由一个对更改输出中的任何内容都非常犹豫的小组生成)。我希望能够编写一个脚本，我可以通过 cron 作业运行它来处理数据透视表中的原始数据并将其加载到我的数据库中。

但是，如果不手动进入窗口、在 excel 中打开文件、双击总计单元格、获取包含所有用于填充的原始数据的新工作表，我无法弄清楚如何获取基础数据该单元格，并将该工作表保存为 csv，然后我可以通过某种语言(在我的例子中是 python)将其加载到我的数据库中。看起来它们应该是一些可编写脚本的方式来提取底层数据。

我只有 linux 机器(在 VM 中运行 windows/office；但我更喜欢不涉及 windows 的解决方案)。我熟悉 xls2csv(不访问原始数据)等工具，并使用 python-unoconv 等工具从 python 编辑 openoffice 文档。但是，即使手动使用 openoffice，我也看不到获取基础数据的方法。

编辑:在花了好几个小时没有取得任何进展之后(在发布这个之前)，我没有开始通过 unoconv 将它转换为 ODS 来制作一些东西，并且可能能够使用 python-odf 来提取最后一张纸(称为“DPCache”)。

所以现在的问题是将 ODS 中的工作表转换为 CSV；这对我来说应该不难理解(尽管非常感谢帮助)。

Answer 1

我也遇到过同样的问题。您可以通过解压缩 xlsx 并读取/解释 xml 文件来解决。比较重要的两个文件就是这些。

• xl/pivotCache/pivotCacheDefinition1.xml
• xl/pivotCache/pivotCacheRecords1.xml

第一个，在 pivotCacheRecords1.xml 中具有原始数据的关系，您需要通过索引号访问，我的意思是，通过 pivotCacheRecords1.xml 中具有标签 <x> 的每一列需要通过标签的索引号<x>获取pivotCacheDefinition1.xml中的数据，为了更好地理解，您需要查看 xml 文件。

pivotCacheDefinition1.xml

    <?xml version="1.0" encoding="UTF-8"?>
<pivotCacheDefinition xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" r:id="rId1" refreshedBy="ADNLatam" refreshedDate="42972.64919178241" createdVersion="5" refreshedVersion="6" recordCount="1923161">
   <cacheSource type="external" connectionId="1" />
   <cacheFields count="26">
      <cacheField name="C - Cadenas" numFmtId="0" sqlType="-9">
         <sharedItems count="3">
            <s v="superA" />
            <s v="superB" />
            <s v="superC" u="1" />
         </sharedItems>
      </cacheField>
      <cacheField name="C - Locales" numFmtId="0" sqlType="-9"><span data-mce-type="bookmark" style="display: inline-block; width: 0px; overflow: hidden; line-height: 0;" class="mce_SELRES_start"></span>
         <sharedItems count="80">
            <s v="Itaugua" />
            <s v="Denis Roa" />
            <s v="Total" />
            <s v="Los Laureles" />
            <s v="CDE" />
            <s v="S6 Fdo." />
            <s v="Central" u="1" />
            <s v="Unicompra" u="1" />
            <s v="San Lorenzo Centro" u="1" />
         </sharedItems>
      </cacheField>
   </cacheFields>
</pivotCacheDefinition>
</xml>

pivotCacheRecords1.xml

<pivotCacheRecords
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" count="246209">
<r>
    <x v="0"/> 
    <x v="0"/> 
    <x v="0"/> 
    <x v="0"/> 
    <s v="PAÐAL &quot;PAMPERS&quot; BABYSAN REGULAR GDE 9UN"/> #Z - Sku / Descripcion
    <s v="07501006720341"/> 
    <x v="0"/> 
    <x v="0"/> 
    <x v="0"/> 
    <x v="0"/> 
    <x v="0"/> 
    <x v="0"/> 
    <n v="1"/> 
    <n v="11990"/> 
    <n v="2.3199999999999998"/> 
    <n v="10900"/> 
    <n v="11990"/> 
    <n v="1"/> 
    <d v="2012-02-03T00:00:00"/> 
    <x v="0"/> 
    <x v="0"/> 
    <n v="3"/> 
    <n v="6"/> 
    <x v="0"/> 
    <x v="0"/> 
    <x v="0"/> 
    <x v="0"/> 
    <x v="0"/> 
    <x v="0"/> 
</r>

看到<x>在 CacheRecords1 标记中是 <s> 的关系CacheDefinition1 中的标记，现在，如果您理解了这一点，那么制作一个在记录迭代中使用它的字典就不那么困难了。

      definitions = '/tmp/scantrack_tmp/xl/pivotCache/pivotCacheDefinition1.xml'
      defdict = {}
      columnas = []
      e = xml.etree.ElementTree.parse(definitions).getroot()
      for fields in e.findall('{http://schemas.openxmlformats.org/spreadsheetml/2006/main}cacheFields'):
          for cidx, field in enumerate(fields.getchildren()):
              columna = field.attrib.get('name')
              defdict[cidx] = []
              columnas.append(columna)
              for value in field.getchildren()[0].getchildren():
                  tagname = value.tag
                  defdict[cidx].append(value.attrib.get('v', 0))

我们最终得到了这条命令。

{
  0: ['supera', 'superb', u'superc'],
  1: ['Terminal',
     'CDE',
     'Brasilia',
     ]
  3: ['PANTENE', 'DOVE']
  ...
}

然后你所要做的就是遍历 CacheRecords1 并在标记为 <x> 时将列的索引与 defdict 中的键匹配。

  dfdata = []


  bdata = '/tmp/scantrack_tmp/xl/pivotCache/pivotCacheRecords1.xml'

  for event, elem in xml.etree.ElementTree.iterparse(bdata, events=('start', 'end')):
    if elem.tag == '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}r' and event == 'start':
       tmpdata = []
       for cidx, valueobj in enumerate(elem.getchildren()):
           tagname = valueobj.tag
           vattrib = valueobj.attrib.get('v')
           rdata = vattrib
           if tagname == '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}x':
                try:
                  rdata = defdict[cidx][int(vattrib)]
                except:
                  logging.error('this it not should happen index cidx = {} vattrib = {} defaultidcts = {} tmpdata for the time = {} xml raw {}'.format(
                                                                                                                                                cidx, vattrib, defdict, tmpdata,
                                                                                                                                                xml.etree.ElementTree.tostring(elem, encoding='utf8', method='xml')
                                                                                                                                                ))
           tmpdata.append(rdata)
       if tmpdata:
           dfdata.append(tmpdata)
       elem.clear()

然后你可以把dfdata放在一个dataframe中

df = pd.DataFrame(dfdata).

剩下的就是历史了，我希望这对你有所帮助。

编码愉快!!!