你知道吗？原来普通的Word文档里的表格也能用Power Query直接读？？！！！

大海Power

发布于 2021-08-31 10:24:57

2.2K00

代码可运行

文章被收录于专栏：用户8950297的专栏用户8950297的专栏

运行总次数：0

代码可运行

昨天的文章《将WORD简历数据汇总到Excel，用Power Query竟然这么简单？》里提到，有的word文档因为是一些从网络下载的格式化文档，本身带了html的标记，所以可以直接用Web.Page函数来进行解析。

但大多数普通的word文档，是不能直接用Web.Page函数来解析的，一般需要另存为html文件，才能用Power Query来读取。

实际上，这——

因为，Power Query实在太强大，完全可以直接将普通的word文件通过二进制数据的方式读取后给（替换）加上html标记，然后通过Web.Page函数来读取！

也就是说——

普通Word文档的表格

也能用PQ直接读！！！

不过，这个过程比较复杂，对于我等普通的用户来说，只要学会抱大腿就行了（本文核心代码参考pqfans文章https://pqfans.com/2447.html）。

将下面的代码复制到高级编辑器里：

let
    docx=(path as text,optional index as number)=>
    let
        DecompressFiles = (ZIPFile, Position, FileToExtract, XMLSoFar) => 
            let 
                MyBinaryFormat = try BinaryFormat.Record([DataToSkip=BinaryFormat.Binary(Position),MiscHeader=BinaryFormat.Binary(18),FileSize=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),UnCompressedFileSize=BinaryFormat.Binary(4),FileNameLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),ExtrasLen=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16,ByteOrder.LittleEndian),TheRest=BinaryFormat.Binary()]) otherwise null,
                MyCompressedFileSize = try MyBinaryFormat(ZIPFile)[FileSize]+1 otherwise null,
                MyFileNameLen = try MyBinaryFormat(ZIPFile)[FileNameLen] otherwise null,
                MyExtrasLen = try MyBinaryFormat(ZIPFile)[ExtrasLen] otherwise null,
                MyBinaryFormat2 = try BinaryFormat.Record([DataToSkip=BinaryFormat.Binary(Position), Header=BinaryFormat.Binary(30), Filename=BinaryFormat.Text(MyFileNameLen), Extras=BinaryFormat.Binary(MyExtrasLen), Data=BinaryFormat.Binary(MyCompressedFileSize), TheRest=BinaryFormat.Binary()]) otherwise null,
                MyFileName = try MyBinaryFormat2(ZIPFile)[Filename] otherwise null,
                GetDataToDecompress = try MyBinaryFormat2(ZIPFile)[Data] otherwise null,
                DecompressData = try Binary.Decompress(GetDataToDecompress, Compression.Deflate) otherwise null,
                NewPosition = try Position + 30 + MyFileNameLen + MyExtrasLen + MyCompressedFileSize - 1 otherwise null,
                ImportedXML = DecompressData,
                AddedCustom = try Table.AddColumn(ImportedXML, "Filename", each MyFileName) otherwise ImportedXML,
                AppendedQuery = if ImportedXML = null then XMLSoFar else if (MyFileName = FileToExtract) then AddedCustom else if (FileToExtract = "") and Position <> 0 then Table.Combine({AddedCustom, XMLSoFar}) else AddedCustom   
            in
                if  (MyFileName = FileToExtract) or (AppendedQuery = XMLSoFar) then AppendedQuery else @DecompressFiles(ZIPFile, NewPosition, FileToExtract, AppendedQuery),
        MyXML = Lines.FromBinary(DecompressFiles(File.Contents(path), 0, "word/document.xml", "")){1},
        ReplaceList = {{"<w:tbl>","<table>"},{"</w:tbl>","</table>"},{"<w:tr>","<tr>"},{"</w:tr>","</tr>"},{"<w:tc>","<td>"},{"</w:tc>","</td>"}},
        RemoveList = List.Select(List.Transform({0..List.Count(Text.PositionOf(MyXML,"<",2))-1},each "<"&Text.BetweenDelimiters(MyXML,"<",">",_)&">"),each not List.Contains(List.Zip(ReplaceList){0},_)),
        Result = List.RemoveLastN(Web.Page(List.Accumulate(List.Transform(RemoveList,each {_}&{""})&ReplaceList,MyXML,(s,c)=>Text.Replace(s,c{0},c{1})))[Data])
    in
        try Result{index-1} otherwise Result
in
    docx