XLSX文件中查询定义的位置 [英] Location of query definition in XLSX file
问题描述
使用以下自定义查询创建xlsx文件后(从 typicode.com 从jsonplaceholder服务加载数据)
After creating an xlsx file with the following custom query (loading data from the jsonplaceholder service from typicode.com)
let
Source = Json.Document(Web.Contents("https://jsonplaceholder.typicode.com/posts")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"userId", "id", "title", "body"}, {"userId", "id", "title", "body"})
in
#"Expanded Column1"
并将其解压缩，我希望上面的字符串(查询定义)以某种形式在生成的文件夹结构中的某处找到.目的是以编程方式替换URL，但是我能找到的唯一查询定义是
and unzipping it I would expect the above string (the query definition) to be found somewhere in the resulting folder structure in some form. The goal is to programatically replace the URL, but somehow the only query definition I can find is
<connection id="1" keepAlive="1" name="Query - posts" description="Connection to the 'posts' query in the workbook." type="5" refreshedVersion="6" background="1" saveData="1">
<dbPr connection="Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=posts;Extended Properties=""" command="SELECT * FROM [posts]"/>
</connection>
尽管我不建议任何人下载&运行来自陌生人的办公文件，我确实将xlsx上传到 nofile.io .
Although I wouldn't recommend anybody to download & run office files from strangers, I did upload the xlsx to nofile.io.
我希望在queryTable上有一些formula属性，但是查询表定义看起来像
I would expect some formula property on the queryTable, but the query table definition just looks like
<queryTable xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" name="ExternalData_1" connectionId="1" autoFormatId="16" applyNumberFormats="0" applyBorderFormats="0" applyFontFormats="0" applyPatternFormats="0" applyAlignmentFormats="0" applyWidthHeightFormats="0">
<queryTableRefresh nextId="5">
<queryTableFields count="4">
<queryTableField id="1" name="userId" tableColumnId="5" />
<queryTableField id="2" name="id" tableColumnId="2" />
<queryTableField id="3" name="title" tableColumnId="3" />
<queryTableField id="4" name="body" tableColumnId="4" />
</queryTableFields>
</queryTableRefresh>
</queryTable>
理想情况下，我只想要有关规范如何存储此信息的信息(因为我想在浏览器的前端中手动编辑此信息)，尽管任何允许我生成此信息的解决方案都是完美的.
Ideally I would like just the information about how the spec stores this information (as I want to manually edit this on the frontend in the browser), although any solution which allows me to generate this would be perfect.
推荐答案
尽管这是一篇过时的文章，但我添加了对开放规范"的引用，这些引用详细说明了如何在Excel(.xlsx)Office中准确找到查询信息.打开XML文件格式.如前所述，customXml\item[x].xml部分将包含DataMashup格式信息.这些包装和零件数据的规格可在以下两个链接中找到:
Although this is an old post, I'm adding references to the Open Specifications that detail exactly how the query information can be found in Excel (.xlsx) Office Open XML file format. As was already noted, the customXml\item[x].xml parts will contain DataMashup format information. The specifications for those packaging and part data can be found in the following two links:
要注意的一件事是，顶级二进制流(在base64解码DataMashup元素的值之后)在每个Package的开头都有一个4字节版本字段(为0)，在每个Package之前都有一个4字节大小的字段. ，权限和元数据片段.提取每件作品后，就可以将其用作独立的PK邮包(实际上是OPC-开放包装公约).
One thing to note is that the top level binary stream (after base64 decoding the value from the DataMashup element) has a 4 byte version field at the beginning (which is 0) and a 4 byte size field before each of the Package, Permissions and Metadata pieces. Once each piece is extracted, it can be used as an independent PK Zip package (actually an OPC - Open Packaging Convention).
这篇关于XLSX文件中查询定义的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
