How to: Programmatically Add an Exc...

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Language Filter

How to: Programmatically Add an Excel Web Access Web Part to a Page

This example shows how to programmatically add an Excel Web Access Web Part to a SharePoint page. It also shows you how to display an Excel workbook programmatically in an Excel Web Access Web Part.

The following project uses Microsoft Visual Studio 2005.

Note:
Depending on which settings you use in the Visual Studio integrated development environment (IDE), the process to create a project could be slightly different.

Note:
It is assumed that you have already created a SharePoint document library and made it a trusted location. For more information about this, see How to: Trust a Location and How to: Trust Workbook Locations Using Script.

Adding a Reference

The following steps show how to locate Microsoft.Office.Excel.WebUI.dll and how to add a reference to it.

Note:
It is assumed you have already copied Microsoft.Office.Excel.WebUI.dll from the global assembly cache to a folder of your choice. For more information on how to locate and copy Microsoft.Office.Excel.WebUI.dll, see How to: Locate and Copy Microsoft.Office.Excel.WebUI.dll.

To add a reference to Microsoft.Office.Excel.WebUI.dll

On the Project menu, click Add Reference.
In the Add Reference dialog box, click Browse.

Note:

You can also open the Add Reference dialog box in the Solution Explorer pane by right-clicking References and selecting Add Reference.
Browse to the location of Microsoft.Office.Excel.WebUI.dll.
Select Microsoft.Office.Excel.WebUI.dll and click OK.
Click Add Reference, and a reference to Microsoft.Office.Excel.WebUI.dll will be added to your project.

Note:
You can also open the Add Reference dialog box in the Solution Explorer pane by right-clicking References and selecting Add Reference.

Instantiating a Web Part

To instantiate the Excel Web Access Web Part

Add the Microsoft.Office.Excel.WebUI namespace as a directive to your code so that when you use the types in this namespace, you do not need to fully qualify them:
C#
```
using Microsoft.Office.Excel.WebUI;
```

Instantiate and initialize the Excel Web Access Web Part as follows:

//   class
 ExcelWebRenderer ewaWebPart = new ExcelWebRenderer();

To display a workbook programmatically

In this example, the AddWebPart method takes in the path to an Excel workbook location as an argument. The user provides the path by typing in a Windows form text box and clicking a button:

public bool AddWebPart(string sitename, string book)
{
...
}
            private void AddEWAButton_Click(object sender, 
                EventArgs e)
            {
                siteurl = textBox1.Text;
                bookuri = textBox2.Text;
                succeeded = AddWebPart(siteurl, bookuri);
                if (succeeded)
                {
                    MessageBox.Show(
                        success,
                        appname,
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);
                    progressBar1.Value = 1;
                }
            }

Important:
Make sure the location where the workbook is saved is a trusted location.

Note:
You can get the path to a workbook in Microsoft Office SharePoint Server 2007 by right-clicking the workbook and selecting Copy Shortcut. Alternatively, you can select Properties and copy the path to the workbook from there.

You can display an Excel workbook programmatically by using the following code:

using Microsoft.Office.Excel.WebUI;
namespace AddEWATool
{
    public partial class Form1 : Form
    {
         ...

           /// <param name="sitename">URL of the 
           ///Windows SharePoint Services site</param>
           /// <param name="book">URI to the workbook</param>
        public bool AddWebPart(string sitename, string book)
        {
            ...
            ExcelWebRenderer ewaWebPart = new ExcelWebRenderer();
            ewaWebPart.WorkbookUri = book;

Example

using System;
using System.Collections.Generic;
using System.Windows.Forms;
namespace AddEWATool
{
    /// <summary>
    /// Program class 
    /// </summary>
    internal static class Program
    {
        /// <summary>
        /// The main entry point for the application. 
        /// </summary>
        /// <param name="args">arguments</param>
        /// <returns>int</returns>
        [STAThread]
        public static int Main(string[] args)
        {
            //Application.EnableVisualStyles();
            if (args.Length == 0)
            {
                Application.Run(new Form1());
                return 1;
            }
            else 
            {
                Commandline comm = new Commandline();
                int worked = comm.CommandLineAddWebPart(args);
                return worked;
            }
        }
    }
} 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
using Microsoft.Office.Excel.WebUI;
namespace AddEWATool
{
    /// <summary>
    /// Form1 class derived from System.Windows.Forms
    /// </summary>
    public partial class Form1 : Form
    {
        private string siteurl;
        private string bookuri;
        private bool succeeded;

        private string appname = "AddEWATool";
        private string specifyinput = "Please add a site URL, 
            for example, http://myserver/site/";
        private string siteproblem = "There was a problem with
            the site name. Please check that the site exists.";
        private string addproblem = "There was a problem adding the 
            Web Part.";
        private string success = "Web Part successfully added.";
        private SPSite site;
        private SPWeb TargetWeb;
        private SPWebPartCollection sharedWebParts;
        /// <summary>
        /// Class Constructor
        /// </summary>
        public Form1()
        {
            InitializeComponent();
        }
        /// <summary>
        /// Method to add the Excel Web Access Web Part
        /// </summary>
        /// <param name="sitename">URL of the 
        ///Windows SharePoint Services site</param>
        /// <param name="book">URI to the workbook</param>
        /// <returns>bool</returns>"
        public bool AddWebPart(string sitename, string book)
        {
            bool b = false;
            progressBar1.Visible = true;
            progressBar1.Minimum = 1;
            progressBar1.Maximum = 4;
            progressBar1.Value = 1;
            progressBar1.Step = 1;

            if (String.IsNullOrEmpty(sitename))
            {
                MessageBox.Show(
                    specifyinput,
                    appname,
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Asterisk);
                return b;
            }
                try
            {
                site = new SPSite(sitename);
                TargetWeb = site.OpenWeb();
            }
            catch (Exception exc)
            {
                MessageBox.Show(
                    siteproblem + "\n" + exc.Message,
                    appname,
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Asterisk);
                    progressBar1.Value = 1;
                 return b;
                }
                progressBar1.PerformStep();
                //Get the collection of shared Web Parts 
                //on the home page
                //Log.Comment("Get the collection of 
                //personal Web Parts on default.aspx");
                try
                {
                   sharedWebParts = 
                     TargetWeb.GetWebPartCollection("Default.aspx", 
                     Microsoft.SharePoint.WebPartPages.Storage.Shared);
                }
                catch (Exception exc)
                {
                    MessageBox.Show(
                        siteproblem + "\n" + exc.Message,
                        appname,
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Asterisk);
                    progressBar1.Value = 1;
                    return b;
                }
                progressBar1.PerformStep();
                //Instantiate Excel Web Access Web Part
                //Add an Excel Web Access Web Part in a shared view
                ExcelWebRenderer ewaWebPart = new ExcelWebRenderer();
                progressBar1.PerformStep();
                ewaWebPart.ZoneID = "Left";
                ewaWebPart.WorkbookUri = book;
                try
                {
                    sharedWebParts.Add(ewaWebPart);
                }
                catch (Exception exc)
                {
                    MessageBox.Show(
                        addproblem + "\n" + exc.Message,
                        appname,
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Asterisk);
                    progressBar1.Value = 1;

                    return b;
                }
                    progressBar1.PerformStep();
                    b = true;
                    return b;
                }
                /// <summary>
                /// Button1 click handler
                /// </summary>
                /// <param name="sender">caller</param>
                /// <param name="e">event</param>
            private void AddEWAButton_Click(object sender, 
                EventArgs e)
            {
                siteurl = textBox1.Text;
                bookuri = textBox2.Text;
                succeeded = AddWebPart(siteurl, bookuri);
                if (succeeded)
                {
                    MessageBox.Show(
                        success,
                        appname,
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);
                    progressBar1.Value = 1;
                }
            }
        
    }

Robust Programming

The Excel workbook that you are using must be in a trusted location.