变化的Excel＆QUOT;外部数据＆QUOT;连接字符串 [英] Change Excel "External Data" Connection String

问题描述

我们目前使用的数据透视表在Excel 2003中做我们的报告。这些数据透视表使用内置的导入外部数据Excel功能从SQL养活报告（SQL Server 2008中为precise）。

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秒preadsheets我希望会有，我可以用它来改变连接字符串中的每一S preadsheets的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＃），但我会很感激任何帮助，无论语言或方法（它没有被COM）。

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. 如果您已经使用了透视表向导则连接字符串 将被存储由返回集合中 <一href="http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel._workbook.pivotcaches.aspx"相对=nofollow> Workbook.PivotCaches（） 功能（PivotCache对象 返回有一个连接属性，它包含连接 字符串）。

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).

如果您使用导入外部数据连接字符串会 存储由返回集合中 <一href="http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel._worksheet.querytables.aspx"相对=nofollow> Worksheet.QueryTables 财产（的的QueryTable对象 返回有一个连接属性，它包含连接 字符串）。

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＆QUOT;外部数据＆QUOT;连接字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！