none
Adding .ODC location to Excel file(not the actual conneciton string) RRS feed

  • Question

  • Hello! I have a problem with OpenXML, Let me try to clearify:

    1. I have a local .xlsx file on a server, this file will eventually contain a pivot table or a powerBI report or something that needs a dataconnection;

    2. I have a SharePoint Online environment(office 365), in this environment is an .odc file present;

    3. Serverside code needs to add an reference string, like (Root/trusted connections/MyConnection.odc) to the local .xlsx file;

    4. When the .xlsx file is uploaded to SharePoint it automatically needs to refresh data from the .odc that was present before my .xlsx was uploaded. (So i am not looking for ways to refresh data, i just need to know how to add the odc file path. I hope my question belongs here) I am using OpenOffice SDK, because the application will be server-side:

    Code i am trying now:

     using (SpreadsheetDocument spreadSheet= SpreadsheetDocument.Open(TempFilePath, true))
                    { 
                            WorkbookPart workbookPart = spreadSheet.WorkbookPart;
                            WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
                            

                            Connection Conec = new Connection();
                            Conec.ConnectionFile = "C:\\Connectionsfiles\\Testing\\TryMe.odc";
                            Conec.Name = "Testing a Dataconnection";
                            Conec.OnlyUseConnectionFile = true;
                            Conec.RefreshOnLoad = true;
                    
     
                            //add this connection to the .xlsx file and save it. Conec.ConnectionFile will be changed      with   a SharePoint Online path later
                        }

    But i'm stuck now, any ideas?

    Monday, April 13, 2015 7:14 AM

Answers

  • Current Solution:

    I just downloaded the .odc file from the user's environment and added the connection string from there...now it all seems to work properly, thanks for the help!

    • Marked as answer by Brianovitch93 Wednesday, April 22, 2015 10:23 AM
    Wednesday, April 22, 2015 10:23 AM

All replies

  • Hi,

    For this requirement, please refer to this code below:

    public  void ChangeConnectionsPart(ConnectionsPart connectionsPart1)
    
            {
    
                Connections connections1 = connectionsPart1.Connections;
    
    
                Connection connection1=connections1.GetFirstChild<Connection>();
    
                connection1.ConnectionFile = "C:\\XX.odc";
    
                connection1.RefreshedVersion = 0;
    
                connection1.New = true;
    
    
                DatabaseProperties databaseProperties1=connection1.GetFirstChild<DatabaseProperties>();
    
                databaseProperties1.Connection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=XX;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=XX;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=ColorData";
    
            }
    

    # WorkbookPart.ConnectionsPart property

    https://msdn.microsoft.com/en-us/library/documentformat.openxml.packaging.workbookpart.connectionspart(v=office.15).aspx

    On the other hand, we could compare the different and generate the code through Open XML SDK Productivity Tool.


    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, April 14, 2015 6:35 AM
    Moderator
  • Hello Starain,

    Thank you for your reaction! My problem is that i don't know the connection string beforehand, this one:


      databaseProperties1.Connection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=XX;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=XX;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=ColorData";

    This string needs to be retrieved and added from the odc dataconnection file the first time the .xlsx file is opened in SharePoint Online. Is this a possibility? or will (variable)=XX, like Data Source=XX, work?

    On the other hand, i do know the query that will be executed once the Excel file is opened. This query needs to be added to the connectionspart too i guess?



    Tuesday, April 14, 2015 11:35 AM
  • Hi,

    >> My problem is that i don't know the connection string beforehand

    This string is store in the ODC file, you could open it through notepad and check it. If you want to read it programing with C# code, you could post this issue to the C# forum for better solution.

    >> This string needs to be retrieved and added from the odc dataconnection file the first time the .xlsx file is opened in SharePoint Online. Is this a possibility?

    The Open XML SDK is used to operate xml files of office, we can’t modify them if the is opened, so you need add the connection part before open it.

    >> will (variable)=XX, like Data Source=XX, work?

    No, I removed some information from my code (replace with XXX).

    On the other hand, as I said that we could get the detail code through Open XML SDK Productivity tool.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, April 15, 2015 1:47 AM
    Moderator
  • Hi Starain,

    Thanks for your help again! I know what you mean, but i don't think you entirely understand what i am trying to do: 

    This string is store in the ODC file, you could open it through notepad and check it. If you want to read it programing with C# code, you could post this issue to the C# forum for better solution.

    I will never have access to the .odc file, its just a given thing. It's location is in the environment of a user. That's why it just needs to be a reference.(without a connection string)

    The Open XML SDK is used to operate xml files of office, we can’t modify them if the is opened, so you need add the connection part before open it.I understand, this is not what i am trying to do. What i want is that Excel Online in SharePoint use the .ODC file, regardless of the content. I want excel to read the connection string and use it, by itself.

    Example of what i am trying to do:

    1. An User specifies a .odc name and location. This location will be in SharePoint Online.

    2. I update my excel file programmatically with this .odc name and location, like; https://Example.SharePoint.com/sites/Dev/Shared Documents/Connections/Example.odc

    3. I upload the excel file to his sharepoint environment and it will automatically use the .odc file.

    I hope i explained it enough!



    • Edited by Brianovitch93 Wednesday, April 15, 2015 10:00 AM added .odc location example
    Wednesday, April 15, 2015 9:54 AM
  • Current Solution:

    I just downloaded the .odc file from the user's environment and added the connection string from there...now it all seems to work properly, thanks for the help!

    • Marked as answer by Brianovitch93 Wednesday, April 22, 2015 10:23 AM
    Wednesday, April 22, 2015 10:23 AM