更改Excel“外部数据”连接字符串

本文关键字：连接 字符串 外部数据 数据 Excel 外部 更改 | 更新日期: 2023-09-27 18:07:17

我们目前使用Excel 2003中的数据透视表来做报告。这些数据透视表使用内置的"导入外部数据"Excel功能从SQL(准确地说是SQL server 2008)提供报告。

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

与其辛苦地浏览近100个电子表格，我更希望有一点COM自动化，我可以用它来改变每个电子表格中的连接字符串。

有人知道从COM改变外部数据源连接字符串的方法吗?

我正在使用。net(特别是c#)，但我很感激任何帮助，无论语言或方法(它不一定是COM)。

更改Excel“外部数据”连接字符串

在查看了各种VBA示例和MSDN COM文档后，我已经找到了如何做到这一点。

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

  1. 如果您使用了数据透视表向导，那么连接字符串将存储在集合中，由Workbook.PivotCaches()函数(PivotCache对象)返回一个包含连接的Connection属性字符串)。

  2. 如果你使用"导入外部数据"连接字符串将是返回的集合中存储Worksheet.QueryTables属性(QueryTable对象)返回一个包含连接的Connection属性字符串)。

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

这里有一个很好的注释完整的c#示例，以帮助其他人遇到这个问题:

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;
}
相关文章：