Sign in
 
HomeOnline20132010Other VersionsRelated ProductsLibraryForumsGallery
TechCenter >
> 
> 
Refresh Excel data connections programmatically using MOSS API

Odpovědět Refresh Excel data connections programmatically using MOSS API

  • Monday, May 16, 2011 10:39 AM
     
     
    Sign In to Vote
    0

    Hi,

    I have seen similar question on forums, but could not find the expecting answer, so I'm posting here.

    I have a Excel document in SharePoint 2007 document library, the Excel document contains connection with external database and I'm showing this Excel 2007 document in Excel web access webpart.

    Now the problem every time I have to manually refresh the data connections by opening the Excel file from document library (Data >  Connections > Refresh) Now I want to to automate the process with the help of timer job, for that I require some help on how to refresh the data connection programmatically using MOSS API

    Reply's appreciated, thank you.

    Best Regards, G Vijai Kumar | My Sharepoint Blog
     

All Replies

  • Tuesday, May 17, 2011 3:10 AM
     
     Answered
    Sign In to Vote
    0

    Hi G Vijai Kumar,

     

    Thanks for your post.

    You can use the ” Refresh()” method in Excel Web Service to refresh the data connection.

    Here is a demo about “How to: refresh data”:

    http://msdn.microsoft.com/en-us/library/ms497403(v=office.12).aspx

     

    Thanks,

    Wayne

    TechNet Subscriber Support in forum

    If you have any feedback on our support, please contact tngfb@microsoft.com

    • Marked As Answer by Wayne Fan Thursday, May 26, 2011 12:22 PM
    •  
     
  • Tuesday, May 17, 2011 6:17 AM
     
     
    Sign In to Vote
    0
    Thanks Wayne, will look into the link and update you soon, thanks for your support.
    Best Regards, G Vijai Kumar | My Sharepoint Blog
     
  • Wednesday, May 18, 2011 9:20 AM
     
      Has Code
    Sign In to Vote
    0

     

     

    Hi Wayne,

    I have tried code from the link which you posted, but I'm facing with the error on line binaryWriter.Write(workbook); and unable to execute the code.

    Error: Not enough storage is available to process this command.
     

    Here is the code what I'm trying to execute

     

    using System;
using System.Collections.Generic;
using System.Text;

using System.IO;
using System.Web.Services;
using Microsoft.SharePoint;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Excel.Server.WebServices;

namespace RefreshDataConnections
{
 class Program
 {
  static void Main(string[] args)
  {
   // Instantiate the ExcelService class.
   excel.ExcelService xlservice = new RefreshDataConnections.excel.ExcelService();
   xlservice.Credentials = System.Net.CredentialCache.DefaultCredentials;
   
   excel.Status[] outStatus;
   RangeCoordinates rangeCoordinates = new RangeCoordinates();
   string sheetName = "Sheet1";

   // Set the path to the workbook to open.
   // TODO: Change the path to the workbook
   // to point to a workbook you have access to.
   // The workbook must be in a trusted location.
   string targetWorkbookPath = "http://server/Shared%20Documents/BillofMaterials.xlsx";

   // Call the open workbook, and point to the trusted 
   // location of the workbook to open.   
   string sessionId = xlservice.OpenWorkbook(targetWorkbookPath,string.Empty,string.Empty, out outStatus);

   // Set the cell located in the first row and 
   // ninth column to 300.   
   xlservice.Refresh(sessionId, "AdventureWorks%20BillOfMaterials");

   byte[] workbook = xlservice.GetWorkbook(sessionId, RefreshDataConnections.excel.WorkbookType.FullWorkbook, out outStatus);

   // Write the resulting Excel file to stdout 
   // as a binary stream.
   BinaryWriter binaryWriter = new BinaryWriter(Console.OpenStandardOutput());
   binaryWriter.Write(workbook);
   binaryWriter.Close();
  }
 }
}

     

    To rectify the above error, I have also tried the steps mentioned on link http://support.microsoft.com/kb/285089 and modified the registry IRPStackSize but still I'm facing with Not enough storage is available to process this command.

    Reply's really appreciated, thanks

    • Edited by SP RAJ Wednesday, May 18, 2011 2:26 PM modified error message
    •  
     
  • Thursday, May 19, 2011 2:23 AM
     
     
    Sign In to Vote
    1

    Hi G Vijai Kumar,

     

    Glad to receive your reply.

    According to your code,  I know you have created a console application. In that case, you have to “call the Refresh method using Excel Web Services”, instead of “link directly to Microsoft.Office.Excel.Server.WebServices.dll”.

     

    Here is some code example:

    public class FlyExcelService

        {

            #region field

           

            ExcelService es = null;

            Status[] outStatus;

            string sessionId = string.Empty;

     

            string sheetName = "MyData";       

            private const string siteUrl = "Http://www.contoso.com/";

            private string targetExcelPath = siteUrl + "ESlib/Test.xlsx";

            #endregion

     

            #region Contructor method

     

            public FlyExcelService()

            {

                es = new ExcelService();

     

                ///For subcode error message

                es.SoapVersion = SoapProtocolVersion.Soap12;

     

                es.Url =siteUrl+"_vti_bin/excelservice.asmx";

                es.Credentials = new NetworkCredential("useraccount", "userpassword!", "domainname");

            }

     

            #endregion

     

            #region General method

           

            private void OpenExcel()

            {

                sessionId =

                        es.OpenWorkbook(targetExcelPath,

                        "en-US", "en-US",

                        out outStatus);

            }

     

            private void CloseExcel()

            {

                if (es != null)

                {

                    es.CloseWorkbook(sessionId);

                }

            }

     

            private void CloseExcelAsync()

            {

                if (es != null)

                {

                    es.CloseWorkbookAsync(sessionId);

                    es.CloseWorkbookCompleted +=

                        new CloseWorkbookCompletedEventHandler(es_CloseWorkbookCompleted);

                }

            }

     

            void es_CloseWorkbookCompleted(object sender, CloseWorkbookCompletedEventArgs e)

            {

                Console.WriteLine("The Workbook closed completed.");

            }

     

            /// <summary>

            /// Save the byte[] to the server file system

            /// </summary>

            /// <param name="bits">the byte[] array</param>

            /// <param name="savePath">the target full path</param>

            private void SaveByteFile(byte[] bits,string savePath)

            {

                BinaryWriter bw = new BinaryWriter(File.Create(savePath));

                bw.Write(bits);

                bw.Close();

            }

            #endregion

     

            public void RefreshData()

            {

                string connectName = ". TestDB T_Sales";

                try

                {

                    OpenExcel();

                    es.Refresh(sessionId, connectName);

     

                    //verfy whether it works or not

                    byte[] fullSnapShot =

                        es.GetWorkbook(sessionId, WorkbookType.PublishedItemsSnapshot, out outStatus);

                    SaveByteFile(fullSnapShot, "c:\\Test\\aa.xlsx");

                }

                catch (SoapException ex)

                {

                    Console.WriteLine("Error: {0}",

                        ex.Message);

                }

                finally

                {

                    CloseExcel();

                }

            }

        }

     

     

    Thanks,

    Wayne

    TechNet Subscriber Support in forum

    If you have any feedback on our support, please contact tngfb@microsoft.com


     
  • Thursday, May 19, 2011 2:26 PM
     
     
    Sign In to Vote
    0
    Hi Wayne,

    Thanks for your code sample.

    I have executed your code without any problem, the code published the excel file as snapshot and saved in c:\\Test\\ but it unable to refresh the connection.

    To trace the problem I did debug then found the message on below line:

    sessionId = es.OpenWorkbook(targetExcelPath, "en-US", "en-US", out outStatus);

    At outStatus argument shows the below message:

    "Unable to retrieve external data for the following connections:\n\nowssvr\n\nThe data sources may be unreachable, may not be responding, or may have denied you access."

    Reply's always appreciated, thank you.
    Best Regards, G Vijai Kumar | My Sharepoint Blog
     
  • Monday, May 23, 2011 1:58 AM
     
     
    Sign In to Vote
    0

    Hi G Vijai Kumar,

     

    1.       Please mark sue you have saved the Data Connection Files in the Data connection library in SharePoint 2007;

    2.       Have you specified correct connectName in the code below:

    string connectName = ". TestDB T_Sales";

                try

                {

                    OpenExcel();

                    es.Refresh(sessionId, connectName);

     

    Thanks,

    Wayne

    TechNet Subscriber Support in forum

    If you have any feedback on our support, please contact tngfb@microsoft.com

     
  • Tuesday, May 24, 2011 4:40 AM
     
     
    Sign In to Vote
    0

    Hi Wayne,

    Yes, the .odc file is in Data Connection library in SharePoint 2007 and my connection name is nowssvr and I specified the correct connection name int he code as mentioned in your previous reply's.

    Please do let me know if I'm missing something, thanks for your support
    Best Regards, G Vijai Kumar | My Sharepoint Blog
     
  • Thursday, May 26, 2011 11:15 AM
     
     Answered Has Code
    Sign In to Vote
    0

    Hi Wayne,

    I got to solve the issue by using  Microsoft.Office.Interop.Excel PIA by following code:

    Microsoft.Office.Interop.Excel.Application excelsheet = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelsheet.DisplayAlerts = false;        
excelsheet.Visible = true;
string workbookURL = "http://<servername>/<DocLibName>/ExcelfileName.xlsx";
Workbook excelbook = excelsheet.Workbooks.Open(workbookPath,
           System.Reflection.Missing.Value,
           System.Reflection.Missing.Value,
           System.Reflection.Missing.Value,
           System.Reflection.Missing.Value,
           System.Reflection.Missing.Value,
           System.Reflection.Missing.Value,
           System.Reflection.Missing.Value,
           System.Reflection.Missing.Value,
           System.Reflection.Missing.Value,
           System.Reflection.Missing.Value,
           System.Reflection.Missing.Value,
           System.Reflection.Missing.Value,
           System.Reflection.Missing.Value,
           System.Reflection.Missing.Value);

        foreach (PivotCache pivotcache in excelbook.PivotCaches())
        {
          pivotcache.BackgroundQuery = false;
        }
        excelbook.RefreshAll();

    Thanks Wayne for your support

    Best Regards, G Vijai Kumar | My Sharepoint Blog
    • Marked As Answer by Wayne Fan Thursday, May 26, 2011 12:21 PM
    •