c# - 在 C# 中更改 Excel Power Query 连接字符串

Question

在 Excel Power Query 文件中，数据连接可以来自 SQL 服务器。我们有大量按名称指定 SQL 服务器的文件，并且该服务器将被停用。我们需要更新连接以用新的服务器名称替换旧的服务器名称。这可以通过打开 Excel 文件、浏览查询并手动编辑服务器名称来实现。由于文件数量众多，因此需要使用 C# 来执行此操作。下图显示了您将在其中手动更新的输入字段(已删除名称)。

首先解压 Excel 文件并浏览文件夹 xl > connections.xml 下的内容我原以为它会在那里指定连接，但它只说 $Workbook$

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<connections xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <connection id="1" keepAlive="1" name="Query" description="Connection to the query in the workbook." type="5" refreshedVersion="6" background="1" saveData="1">
    <dbPr connection="Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=&quot;table&quot;" command="SELECT * FROM [table]"/>
  </connection>
</connections>

在 MDSN forms有对该主题的引用，Will Gregg 提供的答案说:

External data source connection information is stored in the XLSX package in a custom part. You can locate the custom part under the customXML folder of the package. For example: customXml\iem1.xml.

Contained in item1.xml is a element. The definition for the element can be found in the [MS-QDEFF]: Query Definition File Format document (https://msdn.microsoft.com/en-us/library/mt577220(v=office.12).aspx).

In order to work with the data of the element you will need to decode the contents as described in the [MS-QDEFF]: Query Definition File Format document.

Once the data is decoded, you will need to examine the contents of the PackagePart. Within that package you will find the external data connection information in the Forumlas\Section1.m part.

这有助于将我指向 item.xml customXml 中的文件文件夹，但没有详细说明如何解码 DataMashup 中的信息。目的。答案确实提到了 [MS-QDEFF]: Query Definition File Format文档可在此 link来自 main article关于查询定义格式。乍一看，本文档中的信息可能看起来密集而复杂。
在 Stack Overflow 上有 6 个问题提到 DataMashup其中4个与Power BI有关，虽然与此问题相似，但并不相同。下面列出了每个问题的链接:

how to decode/ get encoding of file (Power BI desktop file)

How to edit Power BI Desktop document parameters or data sources programmatically with C#?

Is there documentation/an API for the PBix file format?

How to update clients' Power BI files without ruining their reports?

其他 2 个问题更相关，因为他们询问的是 Excel 而不是 Power BI，我将在下面讨论:

This question询问如何使用 VBA 删除 Power Query 查询的自定义 XML 数据。我不想删除查询，而是更新连接字符串，我想在 C# 而不是 VBA 中执行此操作。这些问题显示了使用宏记录器的结果，我不想打开每个 Excel 文件来运行 VBA 宏。

This question询问如何找到查询信息并遇到相同的$Workbook$我做了。在 Axel Richter 的评论中，他说 In *.xlsx/customXml/ you will find a item1.xml which contains a DataMashup element which contains a base64Binary which is the binary query definition file. I have no clue how to work with that. That's why only a comment and not a answer.一年多后，Tom Jebo 添加了一个答案，指出我也发现了开放规范的详细信息，但没有提供有关如何操作 DataMashup 的解决方案。目的。我将此添加为一个新问题，因为此问题正在寻求解决与我有所不同的问题，并且它也在寻找 JavaScript 中的解决方案。

解码 DataMashup 的最佳方法是什么？对象，更改服务器名称，然后将更新的连接保存回 Excel 文件？
在这个 blog post作者 Jeff Atwood 于 2011 年 7 月 1 日，鼓励提出和回答您自己的问题。另外 this page形成 Stack Overflow 帮助中心解决同样的问题。我决定在 C# 中发布一个完整的工作解决方案供其他人修改和使用，希望可以节省他们需要通过我所做的所有工作来处理的时间。

Answer 1

如问题中所述，最有用的文档是 [MS-QDEFF]: Query Definition File Format .我将在此处包含本文档最相关的部分，但如果需要，请参阅原始文档。下面显示了带有 DataMashup 的示例 XML微软提供。这是一个简短的查询，但如果您打开 customXml > item1.xml，预计会有类似的结果。文件。

<DataMashup sqmid="7690c5d6-5698-463c-a560-a0093d4f6332"
    xmlns="http://schemas.microsoft.com/DataMashup">
  AAAAAEUDAABQSwMEFAACAAgAta0pR62KRJynAAAA+QAAABIAHABDb25maWcvUGFja2FnZS54bWwgohgA
  KKAUAAAAAAAAAAAAAAAAAAAAAAAAAAAhY9NDoIwGESvQrqnP4jGkI+ycCuJCdG4bUqFRiiGFsvdXHgkr
  yCJYti5nMmb5M3r8YRsbJvgrnqrO5MihikKlJFdqU2VosFdwi3KOByEvIpKBRNsbDJanaLauVtCiPce+
  xXu+opElDJyzveFrFUrQm2sE0Yq9FuV/1eIw+kjwyMcxTimmzVmMWVA5h5ybRbMpIwpkEUJu6FxQ6+4M
  uGxADJHIN8b/A1QSwMEFAACAAgAta0pRw/K6aukAAAA6QAAABMAHABbQ29udGVudF9UeXBlc10ueG1sI
  KIYACigFAAAAAAAAAAAAAAAAAAAAAAAAAAAAG2OSw7CMAxErxJ5n7qwQAg1ZQHcgAtEwf2I5qPGReFsL
  DgSVyBtd4ilZ+Z55vN6V8dkB/GgMfbeKdgUJQhyxt961yqYuJF7ONbV9Rkoihx1UUHHHA6I0XRkdSx8I
  Jedxo9Wcz7HFoM2d90Sbstyh8Y7JseS5x9QV2dq9DSwuKQsr7UZB3Fac3OVAqbEuMj4l7A/eR3C0BvN2
  cQkbZR2IXEZXn8BUEsDBBQAAgAIALWtKUdi3rmEPAAAAEsAAAATABwARm9ybXVsYXMvU2VjdGlvbjEub
  SCiGAAooBQAAAAAAAAAAAAAAAAAAAAAAAAAAAArTk0uyczPUwiG0IbWvFy8XMUZiUWpKQqBpalFlYYKt
  go5qSW8XApAEJxfWpScChQx1Dbk5crMQxa1BgBQSwECLQAUAAIACAC1rSlHrYpEnKcAAAD5AAAAEgAAA
  AAAAAAAAAAAAAAAAAAAQ29uZmlnL1BhY2thZ2UueG1sUEsBAi0AFAACAAgAta0pRw/K6aukAAAA6QAAA
  BMAAAAAAAAAAAAAAAAA8wAAAFtDb250ZW50X1R5cGVzXS54bWxQSwECLQAUAAIACAC1rSlHYt65hDwAA
  ABLAAAAEwAAAAAAAAAAAAAAAADkAQAARm9ybXVsYXMvU2VjdGlvbjEubVBLBQYAAAAAAwADAMIAAABtA
  gAAAAA0AQAA77u/PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0idXRmLTgiPz48UGVybWlzc2lvb
  kxpc3QgeG1sbnM6eHNpPSJodHRwOi8vd3d3LnczLm9yZy8yMDAxL1hNTFNjaGVtYS1pbnN0YW5jZSIge
  G1sbnM6eHNkPSJodHRwOi8vd3d3LnczLm9yZy8yMDAxL1hNTFNjaGVtYSI+PENhbkV2YWx1YXRlRnV0d
  XJlUGFja2FnZXM+ZmFsc2U8L0NhbkV2YWx1YXRlRnV0dXJlUGFja2FnZXM+PEZpcmV3YWxsRW5hYmxlZ
  D50cnVlPC9GaXJld2FsbEVuYWJsZWQ+PFdvcmtib29rR3JvdXBUeXBlIHhzaTpuaWw9InRydWUiIC8+P
  C9QZXJtaXNzaW9uTGlzdD7LBwAAAAAAAKkHAADvu788P3htbCB2ZXJzaW9uPSIxLjAiIGVuY29kaW5nP
  SJ1dGYtOCI/PjxMb2NhbFBhY2thZ2VNZXRhZGF0YUZpbGUgeG1sbnM6eHNpPSJodHRwOi8vd3d3LnczL
  m9yZy8yMDAxL1hNTFNjaGVtYS1pbnN0YW5jZSIgeG1sbnM6eHNkPSJodHRwOi8vd3d3LnczLm9yZy8yM
  DAxL1hNTFNjaGVtYSI+PEl0ZW1zPjxJdGVtPjxJdGVtTG9jYXRpb24+PEl0ZW1UeXBlPkFsbEZvcm11b
  GFzPC9JdGVtVHlwZT48SXRlbVBhdGggLz48L0l0ZW1Mb2NhdGlvbj48U3RhYmxlRW50cmllcyAvPjwvS
  XRlbT48SXRlbT48SXRlbUxvY2F0aW9uPjxJdGVtVHlwZT5Gb3JtdWxhPC9JdGVtVHlwZT48SXRlbVBhd
  Gg+U2VjdGlvbjEvUXVlcnkxPC9JdGVtUGF0aD48L0l0ZW1Mb2NhdGlvbj48U3RhYmxlRW50cmllcz48R
  W50cnkgVHlwZT0iSXNQcml2YXRlIiBWYWx1ZT0ibDAiIC8+PEVudHJ5IFR5cGU9IlJlc3VsdFR5cGUiI
  FZhbHVlPSJzTnVtYmVyIiAvPjxFbnRyeSBUeXBlPSJGaWxsRW5hYmxlZCIgVmFsdWU9ImwxIiAvPjxFb
  nRyeSBUeXBlPSJGaWxsVG9EYXRhTW9kZWxFbmFibGVkIiBWYWx1ZT0ibDAiIC8+PEVudHJ5IFR5cGU9I
  kZpbGxDb3VudCIgVmFsdWU9ImwxIiAvPjxFbnRyeSBUeXBlPSJGaWxsRXJyb3JDb3VudCIgVmFsdWU9I
  mwwIiAvPjxFbnRyeSBUeXBlPSJGaWxsQ29sdW1uVHlwZXMiIFZhbHVlPSJzQlE9PSIgLz48RW50cnkgV
  HlwZT0iRmlsbENvbHVtbk5hbWVzIiBWYWx1ZT0ic1smcXVvdDtRdWVyeTEmcXVvdDtdIiAvPjxFbnRye
  SBUeXBlPSJGaWxsRXJyb3JDb2RlIiBWYWx1ZT0ic1Vua25vd24iIC8+PEVudHJ5IFR5cGU9IkZpbGxMY
  XN0VXBkYXRlZCIgVmFsdWU9ImQyMDE1LTA5LTEwVDA0OjQ1OjQxLjkyNzU5MDBaIiAvPjxFbnRyeSBUe
  XBlPSJSZWxhdGlvbnNoaXBJbmZvQ29udGFpbmVyIiBWYWx1ZT0ic3smcXVvdDtjb2x1bW5Db3VudCZxd
  W90OzoxLCZxdW90O2tleUNvbHVtbk5hbWVzJnF1b3Q7OltdLCZxdW90O3F1ZXJ5UmVsYXRpb25zaGlwc
  yZxdW90OzpbXSwmcXVvdDtjb2x1bW5JZGVudGl0aWVzJnF1b3Q7OlsmcXVvdDtTZWN0aW9uMS9RdWVye
  TEvQXV0b1JlbW92ZWRDb2x1bW5zMS57UXVlcnkxLDB9JnF1b3Q7XSwmcXVvdDtDb2x1bW5Db3VudCZxd
  W90OzoxLCZxdW90O0tleUNvbHVtbk5hbWVzJnF1b3Q7OltdLCZxdW90O0NvbHVtbklkZW50aXRpZXMmc
  XVvdDs6WyZxdW90O1NlY3Rpb24xL1F1ZXJ5MS9BdXRvUmVtb3ZlZENvbHVtbnMxLntRdWVyeTEsMH0mc
  XVvdDtdLCZxdW90O1JlbGF0aW9uc2hpcEluZm8mcXVvdDs6W119IiAvPjxFbnRyeSBUeXBlPSJGaWxsZ
  WRDb21wbGV0ZVJlc3VsdFRvV29ya3NoZWV0IiBWYWx1ZT0ibDEiIC8+PEVudHJ5IFR5cGU9IkFkZGVkV
  G9EYXRhTW9kZWwiIFZhbHVlPSJsMCIgLz48RW50cnkgVHlwZT0iUmVjb3ZlcnlUYXJnZXRTaGVldCIgV
  mFsdWU9InNTaGVldDIiIC8+PEVudHJ5IFR5cGU9IlJlY292ZXJ5VGFyZ2V0Q29sdW1uIiBWYWx1ZT0ib
  DEiIC8+PEVudHJ5IFR5cGU9IlJlY292ZXJ5VGFyZ2V0Um93IiBWYWx1ZT0ibDEiIC8+PEVudHJ5IFR5c
  GU9Ik5hbWVVcGRhdGVkQWZ0ZXJGaWxsIiBWYWx1ZT0ibDAiIC8+PEVudHJ5IFR5cGU9IkZpbGxUYXJnZ
  XQiIFZhbHVlPSJzUXVlcnkxIiAvPjxFbnRyeSBUeXBlPSJCdWZmZXJOZXh0UmVmcmVzaCIgVmFsdWU9I
  mwxIiAvPjxFbnRyeSBUeXBlPSJGaWxsU3RhdHVzIiBWYWx1ZT0ic0NvbXBsZXRlIiAvPjxFbnRyeSBUe
  XBlPSJRdWVyeUlEIiBWYWx1ZT0iczdlMDQzNjJlLTkyZjUtNGQ4Mi04YjA3LTI3NjFlYWY2OGFlNSIgL
  z48L1N0YWJsZUVudHJpZXM+PC9JdGVtPjxJdGVtPjxJdGVtTG9jYXRpb24+PEl0ZW1UeXBlPkZvcm11b
  GE8L0l0ZW1UeXBlPjxJdGVtUGF0aD5TZWN0aW9uMS9RdWVyeTEvU291cmNlPC9JdGVtUGF0aD48L0l0Z
  W1Mb2NhdGlvbj48U3RhYmxlRW50cmllcyAvPjwvSXRlbT48L0l0ZW1zPjwvTG9jYWxQYWNrYWdlTWV0Y
  WRhdGFGaWxlPhYAAABQSwUGAAAAAAAAAAAAAAAAAAAAAAAA2gAAAAEAAADQjJ3fARXREYx6AMBPwpfrA
  QAAACLWGAG5O6FHjkAGtB+m5EQAAAAAAgAAAAAAA2YAAMAAAAAQAAAAaH8KNe2ciHwfVosIvSCr6gAAA
  AAEgAAAoAAAABAAAAA40fOKWe6kmTAWJSBXs4cYUAAAAPNy7uF6Dtr9PvADu+eZdeV7JutpIQTh41qqT
  3QnFoWPwE0Xyrur5N6Q2s2TEzjlBDfkEmNaGtr3htemOjWZYXKQHP+R5u/90zHWiwOwjjowFAAAAF2UC
  6Jm8C98hVmJBo638e4Qk65V
</DataMashup>

该对象的值被编码为 Base64字符串。如果您不熟悉 Base 64， this Wikipedia文章将是一个很好的起点。解决方案的第一步是打开 XML 文档并将其转换为 byte。表示。这可以按如下方式完成:

string file = @"\customXml\item1.xml"; // or wherever your xml file is
XDocument doc = XDocument.Load(file);

byte[] dataMashup = Convert.FromBase64String(doc.Root.Value);

注意:在此答案底部提供的完整示例中，所有操作均在内存中完成。
来自 Microsoft 定义文档:

Version (4 bytes): Unsigned integer that MUST be set to 0.

Package Parts Length (4 bytes): Unsigned integer that specifies the length of the Package Parts field.

Package Parts (variable): Variable-length binary stream (section 2.3).

Permissions Length (4 bytes): Unsigned integer that specifies the length of the Permissions field.

Permissions (variable): Variable-length binary stream (section 2.4).

Metadata Length (4 bytes): Unsigned integer that specifies the length of the Metadata field.

Metadata (variable): Variable-length binary stream (section 2.5).

Permission Bindings Length (4 bytes): Unsigned integer that specifies the length of the Permission Bindings field.

Permission Bindings (variable): Variable-length binary stream (section 2.6).

由于定义其内容长度的每个字段都是 4 个字节，因此我定义了一个常量

private const int FIELDS_LENGTH = 4;

然后可以找到本节中定义的每个值(引用自 Microsoft)，如下所示:

int version = BitConverter.ToUInt16(dataMashup.Take(FIELDS_LENGTH).ToArray(), 0);

int packagePartsLength = BitConverter.ToUInt16(dataMashup.Skip(FIELDS_LENGTH).Take(FIELDS_LENGTH).ToArray(), 0);
byte[] packageParts = dataMashup.Skip(FIELDS_LENGTH * 2).Take(packagePartsLength).ToArray();

int permissionsLength = BitConverter.ToUInt16(dataMashup.Skip(FIELDS_LENGTH  * 2 + packagePartsLength).Take(FIELDS_LENGTH).ToArray(), 0);
byte[] permissions = dataMashup.Skip(FIELDS_LENGTH * 3).Take(permissionsLength).ToArray();

int metadataLength = BitConverter.ToUInt16(dataMashup.Skip(FIELDS_LENGTH * 3 + packagePartsLength + permissionsLength).Take(FIELDS_LENGTH).ToArray(), 0);
byte[] metadata = dataMashup.Skip(FIELDS_LENGTH * 4 + packagePartsLength + permissionsLength).Take(metadataLength).ToArray();

int permissionsBindingLength = BitConverter.ToUInt16(dataMashup.Skip(FIELDS_LENGTH * 4 + packagePartsLength + permissionsLength + metadataLength).Take(FIELDS_LENGTH).ToArray(), 0);
byte[] permissionsBinding = dataMashup.Skip(FIELDS_LENGTH * 5 + packagePartsLength + permissionsLength + metadataLength).Take(permissionsBindingLength).ToArray();

使用 byte[]对于包装部件，它代表 Package来自 System.IO.Packaging 的对象命名空间。

using (MemoryStream ms = new MemoryStream(packageParts)) {
    using (Package package = Package.Open(ms, FileMode.Open, FileAccess.ReadWrite)) {
        PackagePart section = package.GetParts().Where(x => x.Uri.OriginalString == "/Formulas/Section1.m").FirstOrDefault();

        string query;
        using (StreamReader reader = new StreamReader(section.GetStream())) {
            query = reader.ReadToEnd();
            // do other replacing, removing of query here
        }
        using (BinaryWriter writer = new BinaryWriter(section.GetStream())) {
            // write updated query back to package part
            writer.Write(Encoding.ASCII.GetBytes(query));
        }
    }

    packageParts = ms.ToArray();
}

最后我需要更新原来的 byte[]使用更新包中的新信息。

bytes = BitConverter.GetBytes(version)
            .Concat(BitConverter.GetBytes(packageParts.Length))
            .Concat(packageParts)
            .Concat(BitConverter.GetBytes(permissionsLength))
            .Concat(permissions)
            .Concat(BitConverter.GetBytes(metadataLength))
            .Concat(metadata)
            .Concat(BitConverter.GetBytes(permissionsBindingLength))
            .Concat(permissionsBinding);
doc.Root.Value = Convert.ToBase64String(bytes.ToArray());
entryStream.SetLength(0);
doc.Save(entryStream);

以下是完整性的完整示例。它是一个控制台应用程序，它接收要更新的文件目录作为命令行参数，然后用新的服务器名称替换旧的服务器名称。

using System;
using System.Collections.Generic;
using System.Linq;
using System.IO;
using System.IO.Compression;
using System.Xml.Linq;
using System.IO.Packaging;
using System.Text;

namespace MyApp {
    class Program {
        private const int FIELDS_LENGTH = 4;

        static void Main(string[] args) {
            if (args.Length != 1) {
                Console.WriteLine("specify one directory to update");
            }
            if (!Directory.Exists(args[0])) {
                Console.WriteLine("directory does not exist");
            }

            IEnumerable<FileInfo> files = Directory.GetFiles(args[0]).Where(x => Path.GetExtension(x) == ".xlsx").Select(x => new FileInfo(x));

            foreach (FileInfo file in files) {
                using (FileStream fileStream = File.Open(file.FullName, FileMode.OpenOrCreate)) {
                    using (ZipArchive archive = new ZipArchive(fileStream, ZipArchiveMode.Update)) {

                        ZipArchiveEntry entry = archive.GetEntry("customXml/item1.xml");

                        IEnumerable<byte> bytes;
                        using (Stream entryStream = entry.Open()) {
                            XDocument doc = XDocument.Load(entryStream);

                            byte[] dataMashup = Convert.FromBase64String(doc.Root.Value);
                            int version = BitConverter.ToUInt16(dataMashup.Take(FIELDS_LENGTH).ToArray(), 0);

                            int packagePartsLength = BitConverter.ToUInt16(dataMashup.Skip(FIELDS_LENGTH).Take(FIELDS_LENGTH).ToArray(), 0);
                            byte[] packageParts = dataMashup.Skip(FIELDS_LENGTH * 2).Take(packagePartsLength).ToArray();

                            int permissionsLength = BitConverter.ToUInt16(dataMashup.Skip(FIELDS_LENGTH * 2 + packagePartsLength).Take(FIELDS_LENGTH).ToArray(), 0);
                            byte[] permissions = dataMashup.Skip(FIELDS_LENGTH * 3).Take(permissionsLength).ToArray();

                            int metadataLength = BitConverter.ToUInt16(dataMashup.Skip(FIELDS_LENGTH * 3 + packagePartsLength + permissionsLength).Take(FIELDS_LENGTH).ToArray(), 0);
                            byte[] metadata = dataMashup.Skip(FIELDS_LENGTH * 4 + packagePartsLength + permissionsLength).Take(metadataLength).ToArray();

                            int permissionsBindingLength = BitConverter.ToUInt16(dataMashup.Skip(FIELDS_LENGTH * 4 + packagePartsLength + permissionsLength + metadataLength).Take(FIELDS_LENGTH).ToArray(), 0);
                            byte[] permissionsBinding = dataMashup.Skip(FIELDS_LENGTH * 5 + packagePartsLength + permissionsLength + metadataLength).Take(permissionsBindingLength).ToArray();

                            // use double memory stream to solve issue as memory stream will change
                            // size when re-saving the data mashup object
                            using (MemoryStream packagePartsStream = new MemoryStream(packageParts)) {
                                using (MemoryStream ms = new MemoryStream()) {
                                    packagePartsStream.CopyTo(ms);
                                    using (Package package = Package.Open(ms, FileMode.Open, FileAccess.ReadWrite)) {
                                        PackagePart section = package.GetParts().Where(x => x.Uri.OriginalString == "/Formulas/Section1.m").FirstOrDefault();

                                        string query;
                                        using (StreamReader reader = new StreamReader(section.GetStream())) {
                                            query = reader.ReadToEnd();
                                            // do other replacing, removing of query here
                                            query = query.Replace("old-server", "new-server");
                                        }
                                        using (BinaryWriter writer = new BinaryWriter(section.GetStream())) {
                                            writer.Write(Encoding.ASCII.GetBytes(query));
                                        }
                                    }

                                    packageParts = ms.ToArray();
                                }

                                bytes = BitConverter.GetBytes(version)
                                            .Concat(BitConverter.GetBytes(packageParts.Length))
                                            .Concat(packageParts)
                                            .Concat(BitConverter.GetBytes(permissionsLength))
                                            .Concat(permissions)
                                            .Concat(BitConverter.GetBytes(metadataLength))
                                            .Concat(metadata)
                                            .Concat(BitConverter.GetBytes(permissionsBindingLength))
                                            .Concat(permissionsBinding);
                                doc.Root.Value = Convert.ToBase64String(bytes.ToArray());
                                entryStream.SetLength(0);
                                doc.Save(entryStream);
                            }
                        }
                    }
                }
            }
        }
    }
}

注意:因为我只需要更新 Package Parts部分，我可以确认此解码/编码有效，但我没有测试 Permissions 的解码/编码, Metadata , 或 Permissions Binding .如果你需要使用这些，这至少应该让你开始。
注意:此代码不会捕获错误或处理所有情况。它旨在成为如何更新 Power Query 文件中的连接的工作示例。随意根据需要进行调整。