更改 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#)，但无论语言或方法如何，我都将不胜感激(它不必是 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. 如果您使用过数据透视表向导，那么连接字符串将存储在由返回的集合中Workbook.PivotCaches() 函数(PivotCache 对象返回有一个包含连接的 Connection 属性字符串).

如果您使用导入外部数据"，则连接字符串将为存储在由返回的集合中Worksheet.QueryTables 属性(QueryTable 对象返回有一个包含连接的 Connection 属性字符串).

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屋！