none
Connection String for Excel PivotTable RRS feed

  • Question

  • Hi,

    in my program I open a connection to a MS SQL Server 2008R2 with this connection string:

    IDbConnection connection = new SqlConnection("SERVER=<server>;Trusted_connection=yes;Database=<database>")

    Now, I tried to use this string with a pivot table:

    private string sqlStmnt = "select name, duration from test"; private string connectionString = @"SERVER=.\sqlexpress;Trusted_connection=yes;Database=test"; //private string connectionString = @"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=.\sqlexpress;Initial Catalog=test"; public void Test1() { try { Excel.PivotCache pivotCache = this.Application.ActiveWorkbook.PivotCaches().Add( Excel.XlPivotTableSourceType.xlExternal, missing); pivotCache.Connection = connectionString; pivotCache.MaintainConnection = true; pivotCache.CommandText = sqlStmnt; pivotCache.CommandType = Excel.XlCmdType.xlCmdSql; var sheet = (Excel.Worksheet)this.Application.ActiveSheet; var pivotTables = (Excel.PivotTables)sheet.PivotTables(missing); Excel.PivotTable pivotTable = pivotTables.Add( pivotCache, Cells[2, 2], "PivotTable1", missing, missing); pivotTable.SmallGrid = false; pivotTable.ShowTableStyleRowStripes = true; pivotTable.TableStyle2 = "PivotStyleLight1"; var rowField = (Excel.PivotField)pivotTable.PivotFields("name"); rowField.Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.AddDataField(pivotTable.PivotFields("duration"), "Dauer", Excel.XlConsolidationFunction.xlSum); } catch (Exception ex) { //Trace.WriteLine(ex.Message); Cells[1, 1].Value2 = ex.Message; Cells[1, 10].Value2 = ex.StackTrace; } } public void Test2() { try { using (IDbConnection connection = new SqlConnection(connectionString)) { connection.Open(); if (connection.State == ConnectionState.Open) { //Trace.WriteLine("Connected to database!"); Cells[10, 1].Value2 = "Connected to database!"; } else { throw new Exception("Error: Connection could not be opened!"); } } } catch (Exception ex) { //Trace.WriteLine(ex.Message); Cells[11, 1].Value2 = ex.Message; } }

    private void Sheet1_Startup(object sender, System.EventArgs e)
        {
          Test1();
          Test2();
        }


    Doing this I get the following exception:

    Exception from HRESULT: 0x800A03EC

    Can you tell me what is wrong?


    I also tried to use the following OLEDB connection string, but it does not work with the other connections in my program, because it does not know the keyword "OLEDB;Provider":

    @"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=<server>;Initial Catalog=<database>



    Thursday, May 31, 2012 2:00 PM

Answers

  • Hi Matthias,

    My settings are:

    1. VS2008 Professional SP1.
    2. Office 2007 SP3.
    3. SQL Server Express 2005.

    The steps I used to add an Excel pivot table that connects to an SQL Server database are

    1. Create a workbook connection.
    2. Add a pivot table based on this connection.

    I have 2 classes:

    • TablesManager, in charge of adding the pivot table.
    • ConnectionsManager, in charge of adding the workbook connection.

    The code for the TablesManager class is:

    using Excel = Microsoft.Office.Interop.Excel;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    
    namespace SqlServerConnectionExcel
    {
        class TablesManager
        {
            private object Missing = System.Type.Missing;
    
            public void AddFriendsTable(Excel.Workbook wbk)
            {
                Excel.PivotCaches caches = wbk.PivotCaches();
    
                Excel.XlPivotTableSourceType xlExternal = Excel.XlPivotTableSourceType.xlExternal;
                Excel.PivotCache cache = caches.Create(xlExternal, Missing, Missing);
    
                ConnectionsManager connMgr = new ConnectionsManager();
                Excel.WorkbookConnection friendsConn = connMgr.GetConnection(wbk, Properties.Resources.FriendsConnName);
                cache.Connection = friendsConn.OLEDBConnection.Connection;
                cache.CommandText = friendsConn.OLEDBConnection.CommandText;
                cache.CommandType = friendsConn.OLEDBConnection.CommandType;
    
                Excel.Worksheet sheet1 = (Excel.Worksheet)wbk.Worksheets[1];
                Excel.PivotTables tables = (Excel.PivotTables)sheet1.PivotTables(Missing);
                Excel.Range a5 = (Excel.Range)sheet1.Cells[5, 1];
                tables.Add(cache, a5, Properties.Resources.FriendsConnName, Missing, Missing);
            }
        }
    }
    

    The code for the ConnectionsManager class is:

    using Excel = Microsoft.Office.Interop.Excel;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    
    namespace SqlServerConnectionExcel
    {
        class ConnectionsManager
        {
            public Excel.WorkbookConnection GetConnection(
                Excel.Workbook wbk,
                string connectionName)
            {
                Excel.WorkbookConnection returnValue = null;
                foreach (Excel.WorkbookConnection c in wbk.Connections)
                {
                    if (c.Name == connectionName)
                    {
                        returnValue = c;
                        break;
                    }
                }
    
                return returnValue;
            }
    
            public void AddFriendsConnection(Excel.Workbook wbk)
            {
                string connectionName = Properties.Resources.FriendsConnName;
                string connectionDesc = Properties.Resources.FriendsConnDescription;
                string connectionString = 
                    "OLEDB;" +
                    "Provider=SQLOLEDB.1;" +
                    "Integrated Security=SSPI;" +
                    "InitialCatalog=MySampleDbs;" +
                    @"Data Source=Osy98\SQLEXPRESS;";
                string selectCmdText = "SELECT * FROM MySampleDbs.dbo.Friends";
                Excel.XlCmdType xlCmdSql = Excel.XlCmdType.xlCmdSql;
    
                // Delete the connection if it already exists.
                Excel.WorkbookConnection existingConnection = GetConnection(wbk, connectionName);
                if (existingConnection != null)
                {
                    existingConnection.Delete();
                }
    
                // Add the connection.
                wbk.Connections.Add(connectionName, connectionDesc, connectionString, selectCmdText, xlCmdSql);
            }
        }
    }
    

    The code at workbook start up is:

            private void ThisWorkbook_Startup(object sender, System.EventArgs e)
            {
                try
                {
                    Excel.Workbook wbk = Application.ActiveWorkbook;
                    
                    ConnectionsManager connMgr = new ConnectionsManager();
                    connMgr.AddFriendsConnection(wbk);
    
                    TablesManager tablesMgr = new TablesManager();
                    tablesMgr.AddFriendsTable(wbk);
                }
                catch (Exception ex)
                {
                    Debug.Print(ex.Message);
                }
            }
    

    Hope this helps,

    Carlos Mallen

    Saturday, June 2, 2012 4:15 PM
  • Hi Matthias,

    On a final note, I don't see the Add method your'e using in the pivot caches object. I'm referring to this line:

    Excel.PivotCache pivotCache = this.Application.ActiveWorkbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal, missing);
    I browsed the Excel object model and the members that appear are:
    • Application
    • Count
    • Create
    • Creator
    • Item
    • Parent

    Kind regards,

    Carlos Mallen

    Saturday, June 2, 2012 5:23 PM

All replies

  • Hi Matthias,

    Tahnks for posting in the MSDN Forum.

    According to your description I'm wondering whether you have no enough disk space to support your business.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Friday, June 1, 2012 6:03 AM
    Moderator
  • Hi Tom,

    why do you think I haven´t got enough disk space? I changed the code so you can feel free to try it :).

    I tried it on different computers, but it is still not working...

    In the first method, the error occurs at the line

    pivotCache.Connection = connectionString;

    The following StackTrace is given: 

       bei System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)

       bei Microsoft.Office.Interop.Excel.PivotCache.set_Connection(Object value)

    If I use the OLEDB connection string the first method works fine, but the second method throws an error.

    Thank you,

    Matthias

    Friday, June 1, 2012 6:08 PM
  • Hi Matthias,

    My settings are:

    1. VS2008 Professional SP1.
    2. Office 2007 SP3.
    3. SQL Server Express 2005.

    The steps I used to add an Excel pivot table that connects to an SQL Server database are

    1. Create a workbook connection.
    2. Add a pivot table based on this connection.

    I have 2 classes:

    • TablesManager, in charge of adding the pivot table.
    • ConnectionsManager, in charge of adding the workbook connection.

    The code for the TablesManager class is:

    using Excel = Microsoft.Office.Interop.Excel;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    
    namespace SqlServerConnectionExcel
    {
        class TablesManager
        {
            private object Missing = System.Type.Missing;
    
            public void AddFriendsTable(Excel.Workbook wbk)
            {
                Excel.PivotCaches caches = wbk.PivotCaches();
    
                Excel.XlPivotTableSourceType xlExternal = Excel.XlPivotTableSourceType.xlExternal;
                Excel.PivotCache cache = caches.Create(xlExternal, Missing, Missing);
    
                ConnectionsManager connMgr = new ConnectionsManager();
                Excel.WorkbookConnection friendsConn = connMgr.GetConnection(wbk, Properties.Resources.FriendsConnName);
                cache.Connection = friendsConn.OLEDBConnection.Connection;
                cache.CommandText = friendsConn.OLEDBConnection.CommandText;
                cache.CommandType = friendsConn.OLEDBConnection.CommandType;
    
                Excel.Worksheet sheet1 = (Excel.Worksheet)wbk.Worksheets[1];
                Excel.PivotTables tables = (Excel.PivotTables)sheet1.PivotTables(Missing);
                Excel.Range a5 = (Excel.Range)sheet1.Cells[5, 1];
                tables.Add(cache, a5, Properties.Resources.FriendsConnName, Missing, Missing);
            }
        }
    }
    

    The code for the ConnectionsManager class is:

    using Excel = Microsoft.Office.Interop.Excel;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    
    namespace SqlServerConnectionExcel
    {
        class ConnectionsManager
        {
            public Excel.WorkbookConnection GetConnection(
                Excel.Workbook wbk,
                string connectionName)
            {
                Excel.WorkbookConnection returnValue = null;
                foreach (Excel.WorkbookConnection c in wbk.Connections)
                {
                    if (c.Name == connectionName)
                    {
                        returnValue = c;
                        break;
                    }
                }
    
                return returnValue;
            }
    
            public void AddFriendsConnection(Excel.Workbook wbk)
            {
                string connectionName = Properties.Resources.FriendsConnName;
                string connectionDesc = Properties.Resources.FriendsConnDescription;
                string connectionString = 
                    "OLEDB;" +
                    "Provider=SQLOLEDB.1;" +
                    "Integrated Security=SSPI;" +
                    "InitialCatalog=MySampleDbs;" +
                    @"Data Source=Osy98\SQLEXPRESS;";
                string selectCmdText = "SELECT * FROM MySampleDbs.dbo.Friends";
                Excel.XlCmdType xlCmdSql = Excel.XlCmdType.xlCmdSql;
    
                // Delete the connection if it already exists.
                Excel.WorkbookConnection existingConnection = GetConnection(wbk, connectionName);
                if (existingConnection != null)
                {
                    existingConnection.Delete();
                }
    
                // Add the connection.
                wbk.Connections.Add(connectionName, connectionDesc, connectionString, selectCmdText, xlCmdSql);
            }
        }
    }
    

    The code at workbook start up is:

            private void ThisWorkbook_Startup(object sender, System.EventArgs e)
            {
                try
                {
                    Excel.Workbook wbk = Application.ActiveWorkbook;
                    
                    ConnectionsManager connMgr = new ConnectionsManager();
                    connMgr.AddFriendsConnection(wbk);
    
                    TablesManager tablesMgr = new TablesManager();
                    tablesMgr.AddFriendsTable(wbk);
                }
                catch (Exception ex)
                {
                    Debug.Print(ex.Message);
                }
            }
    

    Hope this helps,

    Carlos Mallen

    Saturday, June 2, 2012 4:15 PM
  • Hi Matthias,

    On a final note, I don't see the Add method your'e using in the pivot caches object. I'm referring to this line:

    Excel.PivotCache pivotCache = this.Application.ActiveWorkbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal, missing);
    I browsed the Excel object model and the members that appear are:
    • Application
    • Count
    • Create
    • Creator
    • Item
    • Parent

    Kind regards,

    Carlos Mallen

    Saturday, June 2, 2012 5:23 PM