更改Excel“外部数据”连接字符串 [英] Change Excel "External Data" Connection String

问题描述

我们目前使用Excel 2003中的数据透视表进行报表。这些数据透视表使用内置的导入外部数据Excel功能从SQL（SQL Server 2008准确）提供报告。

We currently use pivot tables in Excel 2003 to do our reporting. These pivot tables use the inbuilt "Import External Data" Excel functionality to feed the reports from SQL (SQL server 2008 to be precise).

报告目前指向我们的英国数据库，但我们现在要制作一份指向我们的新美国数据库（其具有与英国数据库相同的模式）的每个报告的副本。

The reports currently point at our UK database, but we'd now like to make a copy of each report that point at our new USA database (which has the same schema as the UK database).

比起拼命地通过近100个电子表格，我希望有一个很好的COM自动化，我可以用来更改每个电子表格中的连接字符串。

Rather than painstakingly go through nearly 100 spreadsheets I was hoping that there would be a nice bit of COM automation that I could use to change the connection strings in each of the spreadsheets.

有没有人知道一个方法来改变外部数据源连接字符串从COM？

Does anyone know of a way to change the external data source connection string from COM?

我使用.Net（特别是C＃），但我会感激任何

I'm using .Net (specifically C#) but I'd be grateful for any help regardless of language or method (it doesn't have to be COM).

推荐答案

查看各种VBA示例和MSDN COM文档我已经弄清楚如何做。

After looking at various VBA examples and the MSDN COM documentation I've figured out how to do it.

重要的是，连接字符串保存在两个地方之一，取决于你如何创建工作表。

The important part is that Connection strings are kept in one of two places depending on how you created your worksheet.

1. 如果您已使用数据透视表向导，那么连接字符串
   将存储在
   Workbook.PivotCaches （） 函数（返回的PivotCache对象
   有一个 Connection 属性，其中包含连接
   string） 。

1. If you've used the pivot table wizard then the connection strings will be stored in the collection returned by the Workbook.PivotCaches() function (the PivotCache objects returned have a Connection property which contains the connection string).

如果您使用导入外部数据，连接字符串将为
存储在
返回的集合 Worksheet.QueryTables 属性（返回的QueryTable对象
有 Connection 属性，其中包含连接
string）。

If you used "Import External Data" the connection strings will be stored in the collection returned by the Worksheet.QueryTables property (the QueryTable objects returned have a Connection property which contains the connection string).

可能有更多的地方可以存储连接字符串，这些是我知道迄今为止唯一的两个。如果你知道更多，请在评论中留下一些信息，我将添加到答案。

There may be more places that Connection strings can be stored, these are the only two that I'm aware of so far. If you know of any more please leave some information in the comments and I'll add to the answer.

这是一个很好的评论全工作C＃示例，以帮助任何人遇到这个问题：

Here's a nicely commented full working C# example to help anyone else that comes across this problem:

static void ChangeConnectionStrings(string directoryName, string oldServerName, string newServerName)
{            
    var directory = new DirectoryInfo(directoryName);
    //get all the excel files from the directory
    var files = directory.GetFiles("*.xls", SearchOption.AllDirectories);

    Microsoft.Office.Interop.Excel.Application application = null;

    try
    {
        //create a new application
        application = new Microsoft.Office.Interop.Excel.Application();

        //go through each excel file
        foreach (var file in files)
        {
            //open the file
            application.Workbooks.Open(file.FullName);

            //get the query tables from the worksheets
            var sheets = application.Sheets.OfType<Worksheet>();
            var queryTables = sheets.SelectMany(s => GetQueryTables(s));

            //change the connection string for any query tables
            foreach (var queryTable in queryTables)
            {
                queryTable.Connection = queryTable.Connection.Replace(oldServerName, newServerName);
            }

            //get the pivot table data from the workbooks
            var workbooks = application.Workbooks.Cast<Workbook>();
            var pivotCaches = workbooks.SelectMany(w => GetPivotCaches(w));

            //change the connection string for any pivot tables
            foreach (var pivotCache in pivotCaches)
            {
                pivotCache.Connection = pivotCache.Connection.Replace(oldServerName, newServerName);
            }

            Console.WriteLine("Saving " + file.Name);

            //save the changes
            foreach (var workbook in workbooks)
            {
                workbook.Save();
                workbook.Close();
            }
        }
    }
    finally
    {
        //make sure we quit the application
        if (application != null)
            application.Quit();
    }
}

//PivotCaches isn't Enumerable so we can't just use Cast<PivotCache>, therefore we need a helper function
static IEnumerable<PivotCache> GetPivotCaches(Workbook workbook)
{
    foreach (PivotCache pivotCache in workbook.PivotCaches())
        yield return pivotCache;
}

//QueryTables isn't Enumerable so we can't just use Cast<QueryTable>, therefore we need a helper function
static IEnumerable<QueryTable> GetQueryTables(Worksheet worksheet)
{
    foreach (QueryTable queryTable in worksheet.QueryTables)
        yield return queryTable;
}

这篇关于更改Excel“外部数据”连接字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！