Skip to content

Latest commit

 

History

History
453 lines (380 loc) · 17.2 KB

how-to-programmatically-add-an-excel-web-access-web-part-to-a-page.md

File metadata and controls

453 lines (380 loc) · 17.2 KB
titledescriptionms.datekeywordsf1_keywordsms.assetidms.localizationpriority
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.
01/05/2020
how to,howdoi,howto,webpart
how to,howdoi,howto,webpart
858bb0f6-654a-4f12-ba0b-4776bda5ff6d
medium

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.

Note

Depending on the Visual Studio version and the Visual Studio integrated development environment (IDE) settings that you are using, the process and steps to create a Visual Studio project could be slightly different from the procedures shown in this topic.

Note

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

Adding a Reference

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

Note

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

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

  1. On the Project menu, click Add Reference.

  2. 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.

  3. Browse to the location of Microsoft.Office.Excel.WebUI.dll.

  4. Select Microsoft.Office.Excel.WebUI.dll, and then click OK.

  5. Click Add Reference. A reference to Microsoft.Office.Excel.WebUI.dll is added to your project.

Instantiating a web part

To instantiate the Excel Web Access web part

  1. 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:

    usingMicrosoft.Office.Excel.WebUI;
    ImportsMicrosoft.Office.Excel.WebUI

  2. Instantiate and initialize the Excel Web Access web part, as follows:

    ExcelWebRendererewaWebPart=newExcelWebRenderer();
    DimewaWebPartAsNewExcelWebRenderer()

To display a workbook programmatically

  1. 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 Forms text box and clicking a button.

    Sample code provided by: Daniel Mullowney, Microsoft Corporation

    publicboolAddWebPart(stringsitename,stringbook){ ...}privatevoidAddEWAButton_Click(objectsender,EventArgse){siteurl=textBox1.Text;bookuri=textBox2.Text;succeeded=AddWebPart(siteurl,bookuri);if(succeeded){MessageBox.Show(success,appname,MessageBoxButtons.OK,MessageBoxIcon.Information);progressBar1.Value=1;}}
    PublicFunctionAddWebPart(ByValsitenameAsString,ByValbookAsString)AsBoolean...EndFunctionPrivateSubAddEWAButton_Click(ByValsenderAsObject,ByValeAsEventArgs)siteurl=textBox1.Textbookuri=textBox2.Textsucceeded=AddWebPart(siteurl,bookuri)IfsucceededThenMessageBox.Show(success,appname,MessageBoxButtons.OK,MessageBoxIcon.Information)progressBar1.Value=1EndIfEndSub

    [!IMPORTANT] Ensure that the location where the workbook is saved is a trusted location.

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

    Sample code provided by: Daniel Mullowney, Microsoft Corporation

    ...// Instantiate Excel Web Access web part.// Add an Excel Web Access web part in a shared view.ExcelWebRenderer ewaWebPart =newExcelWebRenderer();ewaWebPart.WorkbookUri=book;progressBar1.PerformStep();try{webPartManager.AddWebPart(ewaWebPart,"Left",0);}catch(Exceptionexc){MessageBox.Show(addWebPartError+"\\n"+exc.Message,appName,MessageBoxButtons.OK,MessageBoxIcon.Asterisk);progressBar1.Value=1;returnb;
    'Instantiate Excel Web Access web part.'Add an Excel Web Access web part in a shared view.DimewaWebPartAsNewExcelWebRenderer()ewaWebPart.WorkbookUri=bookprogressBar1.PerformStep()TrywebPartManager.AddWebPart(ewaWebPart,"Left",0)CatchexcAsExceptionMessageBox.Show(addWebPartError& vbLf& exc.Message,appName,MessageBoxButtons.OK,MessageBoxIcon.Asterisk)progressBar1.Value=1ReturnbEndTry

Example

The following example is a Windows Forms application that enables a user to enter information on a SharePoint site and display an Excel workbook saved in a trusted location programmatically. It programmatically creates an Excel Web Access web part on the default.aspx page of the specified site and displays the specified Excel workbook.

The code sample is the code from the Form1.cs and Form1.vb example files described in the previous procedures. The code sample uses two text boxes, a progress bar, and a button. The code is only a portion of the Windows Forms project. For example, the code involving the layout of the form is not shown.

Sample code provided by: Daniel Mullowney, Microsoft Corporation

namespaceAddEWATool{usingSystem;usingSystem.Windows.Forms;usingMicrosoft.Office.Excel.WebUI;usingMicrosoft.SharePoint;usingMicrosoft.SharePoint.WebPartPages;/// <summary>/// Form1 class derived from System.Windows.Forms./// </summary>publicpartialclassForm1:Form{privatestringappName="AddEWATool";privatestringspecifyInputError="Please add a site URL, for example: http://myserver/site/";privatestringopenSiteError="There was a problem with the site name. Please check that the site exists.";privatestringaddWebPartError="There was a problem adding the web part.";privatestringsuccessMessage="web part successfully added.";/// <summary>/// Add the Excel Web Access web part to the Default.aspx page of the specified site./// </summary>/// <param name="siteName">URL of the SharePoint site</param>/// <param name="book">URI to the workbook</param>/// <returns>Returns true if the WebPart was successfully added; otherwise, false.</returns>publicboolAddWebPart(stringsiteName,stringbook){SPSitesite=null;SPWebtargetWeb=null;SPLimitedWebPartManagerwebPartManager=null;boolb=false;progressBar1.Visible=true;progressBar1.Minimum=1;progressBar1.Maximum=4;progressBar1.Value=1;progressBar1.Step=1;if(String.IsNullOrEmpty(siteName)){MessageBox.Show(specifyInputError,appName,MessageBoxButtons.OK,MessageBoxIcon.Asterisk);returnb;}try{try{site=newSPSite(siteName);targetWeb=site.OpenWeb();}catch(Exceptionexc){MessageBox.Show(openSiteError+"\\n"+exc.Message,appName,MessageBoxButtons.OK,MessageBoxIcon.Asterisk);progressBar1.Value=1;returnb;}progressBar1.PerformStep();try{// Get the shared web part manager on the Default.aspx page.webPartManager=targetWeb.GetLimitedWebPartManager("Default.aspx",System.Web.UI.WebControls.WebParts.PersonalizationScope.Shared);}catch(Exceptionexc){MessageBox.Show(openSiteError+"\\n"+exc.Message,appName,MessageBoxButtons.OK,MessageBoxIcon.Asterisk);progressBar1.Value=1;returnb;}progressBar1.PerformStep();// Instantiate Excel Web Access web part.// Add an Excel Web Access web part in a shared view.ExcelWebRendererewaWebPart=newExcelWebRenderer();ewaWebPart.WorkbookUri=book;progressBar1.PerformStep();try{webPartManager.AddWebPart(ewaWebPart,"Left",0);}catch(Exceptionexc){MessageBox.Show(addWebPartError+"\\n"+exc.Message,appName,MessageBoxButtons.OK,MessageBoxIcon.Asterisk);progressBar1.Value=1;returnb;}}finally{if(site!=null){site.Dispose();}if(targetWeb!=null){targetWeb.Dispose();}if(webPartManager!=null){webPartManager.Dispose();}}progressBar1.PerformStep();b=true;returnb;}/// <summary>/// AddEWAButton click handler./// </summary>/// <param name="sender">caller</param>/// <param name="e">event</param>privatevoidAddEWAButton_Click(objectsender,EventArgse){stringsiteUrl=textBox1.Text;stringbookUri=textBox2.Text;boolsucceeded=AddWebPart(siteUrl,bookUri);if(succeeded){MessageBox.Show(successMessage,appName,MessageBoxButtons.OK,MessageBoxIcon.Information);progressBar1.Value=1;}}}}
ImportsSystemImportsSystem.Windows.FormsImportsMicrosoft.Office.Excel.WebUIImportsMicrosoft.SharePointImportsMicrosoft.SharePoint.WebPartPagesNamespaceAddEWATool''' <summary>''' Form1 class derived from System.Windows.Forms.''' </summary>PartialPublicClassForm1InheritsFormPrivateappNameAsString="AddEWATool"PrivatespecifyInputErrorAsString="Please add a site URL, for example, http://myserver/site/"PrivateopenSiteErrorAsString="There was a problem with the site name. Please check that the site exists."PrivateaddWebPartErrorAsString="There was a problem adding the web part."PrivatesuccessMessageAsString="web part successfully added."''' <summary>''' Add the Excel Web Access web part to the Default.aspx page of the specified site.''' </summary>''' <param name="siteName">URL of the SharePoint site</param>''' <param name="book">URI to the workbook</param>''' <returns>Returns true if the WebPart was successfully added; otherwise, false.</returns>PublicFunctionAddWebPart(ByValsiteNameAsString,ByValbookAsString)AsBooleanDimsiteAsSPSite=NothingDimtargetWebAsSPWeb=NothingDimwebPartManagerAsSPLimitedWebPartManager=NothingDimbAsBoolean=FalseprogressBar1.Visible=TrueprogressBar1.Minimum=1progressBar1.Maximum=4progressBar1.Value=1progressBar1.Step=1IfString.IsNullOrEmpty(siteName)ThenMessageBox.Show(specifyInputError,appName,MessageBoxButtons.OK,MessageBoxIcon.Asterisk)ReturnbEndIfTryTrysite=NewSPSite(siteName)targetWeb=site.OpenWeb()CatchexcAsExceptionMessageBox.Show(openSiteError&amp; vbLf&amp; exc.Message,appName,MessageBoxButtons.OK,MessageBoxIcon.Asterisk)progressBar1.Value=1ReturnbEndTryprogressBar1.PerformStep()Try' Get the shared web part manager on the Default.aspx page.webPartManager=targetWeb.GetLimitedWebPartManager(_"Default.aspx",_System.Web.UI.WebControls.WebParts.PersonalizationScope.Shared)CatchexcAsExceptionMessageBox.Show(openSiteError&amp; vbLf&amp; exc.Message,appName,MessageBoxButtons.OK,MessageBoxIcon.Asterisk)progressBar1.Value=1ReturnbEndTryprogressBar1.PerformStep()'Instantiate Excel Web Access web part.'Add an Excel Web Access web part in a shared view.DimewaWebPartAsNewExcelWebRenderer()ewaWebPart.WorkbookUri=bookprogressBar1.PerformStep()TrywebPartManager.AddWebPart(ewaWebPart,"Left",0)CatchexcAsExceptionMessageBox.Show(addWebPartError&amp; vbLf&amp; exc.Message,appName,MessageBoxButtons.OK,MessageBoxIcon.Asterisk)progressBar1.Value=1ReturnbEndTryFinallyIfNotIsNothing(site)Thensite.Dispose()EndIfIfNotIsNothing(targetWeb)ThentargetWeb.Dispose()EndIfIfNotIsNothing(webPartManager)ThenwebPartManager.Dispose()EndIfEndTryprogressBar1.PerformStep()b=TrueReturnbEndFunction''' <summary>''' AddEWAButton click handler.''' </summary>''' <param name="sender">caller</param>''' <param name="e">event</param>PrivateSubAddEWAButton_Click(ByValsenderAsObject,ByValeAsEventArgs)DimsiteUrlAsString=textBox1.TextDimbookUriAsString=textBox2.TextDimsucceededAsBoolean=AddWebPart(siteUrl,bookUri)IfsucceededThenMessageBox.Show(successMessage,appName,MessageBoxButtons.OK,MessageBoxIcon.Information)progressBar1.Value=1EndIfEndSubEndClassEndNamespace

Robust programming

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

See also

Tasks

Concepts

close