在Linux中从Excel数据透视表中提取数据
我有一个基于数据透视表的Excel表格，这个表格每个月都会更新一次，然后上传到我的服务器上。这个表格是由一个不太愿意改变输出内容的团队生成的。我想写一个脚本，通过定时任务来处理和加载数据透视表中的原始数据到我的数据库里。
但是，我不知道怎么才能获取到这些底层数据。现在我每次都得手动打开Windows，打开Excel文件，双击总计单元格，然后会出现一个新表，里面有填充那个单元格的所有原始数据，最后再把这个表保存为CSV格式，这样我才能用某种语言（在我这里是Python）把它加载到数据库里。感觉应该有某种可以编写脚本的方法来提取这些底层数据。
我只有Linux机器（在虚拟机里运行Windows和Office，但我更希望有不涉及Windows的解决方案）。我对一些工具比如xls2csv（这个工具无法访问原始数据）和用python-unoconv从Python编辑OpenOffice文档的工具比较熟悉。不过，即使是手动使用OpenOffice，我也找不到获取底层数据的方法。
补充说明：在花了几个小时没有进展后（在发这个帖子之前），我开始通过unoconv把文件转换成ODS格式，并且可能会用python-odf提取最后一个表（叫做'DPCache'）。
所以现在的问题是如何把ODS中的一个表转换成CSV格式；这对我来说应该不难解决（不过如果有人能帮忙就太好了）。
2 个回答
我以前也遇到过同样的问题。你可以通过解压xlsx文件，然后读取和理解里面的xml文件来解决。最重要的两个文件是：
- xl/pivotCache/pivotCacheDefinition1.xml
- xl/pivotCache/pivotCacheRecords1.xml
第一个文件包含了原始数据和pivotCacheRecords1.xml之间的关系。你需要通过索引号来访问这些数据。简单来说，就是在pivotCacheRecords1.xml中，每一列都有一个标签<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 "PAMPERS" 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>
注意，CacheRecords1标签中的<x>与CacheDefinition1中的<s>标签是有关系的。如果你理解了这一点，制作一个字典来在记录迭代时使用就不难了。
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>时，将列的索引与字典中的键进行匹配。
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放进一个数据框中。
df = pd.DataFrame(dfdata).
剩下的事情就简单了，希望这能帮到你。
祝你编码愉快！！！
你试过 xlrd 吗？另外，你可以看看 python-excel 网站 上的教程。
其实就是这么简单：
>>> import xlrd
>>> book = xlrd.open_workbook('pivot_table_demo.xls')
>>> sheet = book.sheet_by_name('Summary')
>>> for row_index in xrange(sheet.nrows):
... print sheet.row_values(row_index)
...
[u'Sum of sales', u'qtr', '', '', '', '']
[u'person', 1.0, 2.0, 3.0, 4.0, u'Grand Total']
[u'dick', 100.0, 99.0, 95.0, 90.0, 384.0]
[u'harriet', 100.0, 110.0, 121.0, 133.1, 464.1]
[u'tom', 100.0, 101.0, 102.0, 103.0, 406.0]
[u'Grand Total', 300.0, 310.0, 318.0, 326.1, 1254.1]
>>>