在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表单上有关于这个主题的参考资料，Will Gregg提供的答案如下：

外部数据源连接信息存储在XLSX包中的自定义部件中。您可以在包的customXML文件夹下找到自定义部件。例如：customXml\iem1.xml。

item1.xml中包含一个Query element。Query元素的定义可以在[MS-QDEFF]: Query Definition File Format文档中找到(https://msdn.microsoft.com/en-us/library/mt577220(v=office.12).aspx)。

为了使用Query元素的数据，您需要按照[MS-QDEFF]: Query Definition File Format文档中的说明对内容进行解码。

解码完成后，您需要检查PackagePart的内容。在该包中，您将在Formulas\Section1.m部分中找到外部数据连接信息。

这对我有帮助，可以指向customXml文件夹中的item.xml文件，但并未详细说明如何解码DataMashup对象中的信息。回答提到了[MS-QDEFF]: Query Definition File Format文档可从关于查询定义格式的主文章链接（链接1）中获得。该文档中的信息乍一看可能会很冗长和复杂。

在Stack Overflow上，有6个问题提到了DataMashup，其中4个与Power BI相关，虽然类似但并不完全相同。以下列出了每个问题的链接：

• 如何解码/获取文件编码（Power BI桌面文件）
• 如何使用C#编程以编程方式编辑Power BI Desktop文档参数或数据源？
• PBix文件格式是否有文档/ API可用？
• 如何更新客户的Power BI文件而不破坏其报告？

另外两个问题与Excel相关，不是关于Power BI的，我将在下面讨论：

1. 这个问题询问如何使用VBA删除Power Query查询的自定义XML数据。我不想删除查询，而是想更新连接字符串，并且我希望在C＃中完成此操作。该问题展示了使用宏记录器的结果，我不想打开每个Excel文件来运行VBA宏。
2. 这个问题询问如何查找查询信息，并遇到了与我相同的$Workbook$。Axel Richter在评论中说：在*.xlsx/customXml/中，您会找到一个包含DataMashup元素的item1.xml，其中包含一个base64Binary，这是二进制查询定义文件。我不知道如何处理它。这就是为什么只是评论而不是答案。一年多后，Tom Jebo添加了一个答案，指向我找到的Open Specifications详细信息，但没有提供有关如何操作DataMashup对象的解决方案。我将其作为新问题添加，因为该问题正在寻求解决与我略有不同的问题，而且它也在寻找JavaScript的解决方案。

什么是解码 DataMashup 对象、更改服务器名称并将更新后的连接保存回 Excel 文件的最佳方法？

在这篇由 Jeff Atwood 于2011年7月1日发布的博客文章中，鼓励提出并回答自己的问题。此外，此页面来自 Stack Overflow 帮助中心也解决了同样的问题。我决定发布一个完整的 C# 工作解决方案供其他人修改和使用，希望能为他们节省需要繁琐工作的时间。

- ivcubr

Answer 1

如问题所述，最有帮助的文档是 [MS-QDEFF]: Query Definition File Format。我将在此处包含此文档的最相关部分，但如有需要，请参考原始文档。下面展示了由 Microsoft 提供的具有 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，可以参考 维基百科 中的相关文章。解决方案的第一步是打开 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);

注意: 在本答案底部提供的完整示例中，所有操作都在内存中完成。

来自微软定义文档：

Version（4个字节）：无符号整数，必须设置为0。

Package Parts Length（4个字节）：无符号整数，指定Package Parts字段的长度。

Package Parts（可变长度）：可变长度的二进制流（第2.3节）。

Permissions Length（4个字节）：无符号整数，指定Permissions字段的长度。

Permissions（可变长度）：可变长度的二进制流（第2.4节）。

Metadata Length（4个字节）：无符号整数，指定Metadata字段的长度。

Metadata（可变长度）：可变长度的二进制流（第2.5节）。

Permission Bindings Length（4个字节）：无符号整数，指定Permission Bindings字段的长度。

Permission Bindings（可变长度）：可变长度的二进制流（第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[]表示打包部件，它代表了来自System.IO.Packaging命名空间的Package对象。

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文件中的连接的工作示例。随意根据您的需求进行调整。

- ivcubr