SharePoint 2010: Connect InfoPath Form Web part with Excel Web Part

In this article, we will see how to connect an InfoPath form to Excel Web Access Web part and pass the values from InfoPath Form fields to the Excel workbook. For this demonstration, we will use the following - Microsoft InfoPath Designer 2010, Microsoft Excel 2010 and Microsoft SharePoint Designer 2010

Let us start by creating an Excel workbook, as shown below:  

Now as shown above in the figure, choose the ‘Formulas’ ribbon and click on ‘Name Manager’ button. Make sure that you select the exact next cell of ‘BasicSalary’. This will open a dialog box ‘Name Manager’. Using this dialog box, we will name our cell. Do this exercise for all the 6 cells which are shown above. Finally your ‘Name Manager window should look similar to the following:

Now let’s create a document library with the name ‘Calculations’. To create a document library, click on the ‘Library’ link button from the left navigation pane and then click on ‘Create’ button as shown below:

This will bring up a ‘Create’ dialog box. Choose a ‘Document’ library from the dialog box and name it as ‘Calculations’ as shown below –

Now since our library is ready, let’s publish our workbook to this library using ‘Excel Services option’. To publish the document, go to ‘File’ in excel workbook and click on ‘Save and Send’ option as shown below –

Now click on ‘Publish Option’ button and choose a ‘Parameter’ tab. Go to the parameters tab and click on the ‘Add’ button. Choose all the parameters as shown below –

Click ‘OK’ button. Now choose the path of the SharePoint library (which we created few steps back) and in the excel workbook, click on ‘Save As’ button. Paste the URL and then hit the enter key to open that path. Now type your excel workbook name and click on ‘Save’ button as shown below –

 

Designing an InfoPath form for Entering data

Now we are ready with our excel workbook. Let’s design an InfoPath form for entering the data. For designing the InfoPath form, let’s open ‘Microsoft InfoPath Designer 2010’ and design a new blank form template as shown below –

In this blank template, you will see the ‘Fields’ task pane on the right hand side. Right click to ‘myFields’ and go to ‘Properties’ menu. Rename the ‘myFields’ group with ‘SalaryCalci’ as shown below –

Now right click the ‘SalaryCalci’ group shown above and click on ‘Add’. This will pop up the ‘Add Field or Group’ dialog box. Write ‘BasicSalary’ in the name field and click ‘OK’. Repeat this steps for ‘HRA’, ‘TA’ ,’DA’, ‘PF’. Sample is shown below –

Now as the fields are ready, let’s add a table in our form, to add the fields as ‘Textbox’ in our InfoPath form. Go to ‘Insert’ ribbon and add ‘2-Columns’ table as shown below and add five rows in that table –

Drag and drop each field in front of each title, which will create a textbox for us. Now go to ‘Home’ ribbon and from the ‘Input’ group, add a button on the form as shown below –

Right click the button and go to properties. Rename the button as ‘Send Data’. Now keep the button selected and click on the ‘Add Rule’ drop button from the ‘Home’ ribbon and choose an option to ‘Submit data’ as shown below –

Now this will display the ‘Rule Details’ dialog box. From the ‘Action’ drop down box, choose ‘Send Data to Web Part’. Now on the same dialog box, click on ‘Property Promotion’ as shown below –

You will see the ‘Form options’ window. Click on ‘Add’ button from the section ‘The fields below will be available as SharePoint Web Part connection parameters’ and choose the first parameter. Set this parameter type to output. Repeat this step for all the parameters. A sample output is shown below –

Click the ‘OK’ button. Now save your form on the local machine and publish it to a SharePoint site and the same document library, where we have published our excel workbook earlier. To publish this InfoPath form, click ‘File’ tab and click on ‘Publish’ and then click on ‘SharePoint Server’. You should see a  ‘PublishWizard’. Follow these steps:

• In the first step of the wizard, copy the site path where we want to publish this form and click on the ‘Next’ button.
• Now in this step, select Form Library option and check the checkbox ‘Enable this form to be filled out by using a browser’. Click on the ‘Next’ button.
• In this step, make a choice of ‘Create a new form library’ and click on ‘Next’ button.
• Now in this step, give a library name as ‘SalaryCalculationForm’ and click on Next. In the next step, keep all the settings default and click on next to publish the form.

Add a Web Part for InfoPath and Excel Service

Now your InfoPath form is successfully published, the next step is to create a web page and add web part for ‘InfoPath’ and ‘Excel Service’.

To create a new web page under your SharePoint site click on ‘Site Action’ and ‘More Options’ as shown below –

This will show you a ‘Create’ dialog box. Choose Pages and ‘Web Part’ page option and click ‘Create’ button as shown below –

Name the page as ‘SalaryCalculation’ and keep all the options as default and click on ‘Create’ button. Now let’s insert the ‘InfoPath form web part’ and ‘Excel Web Access’ web path in to web part zones as shown below:

To add the web path to the left zone, click ‘Add a web part’ link and from categories section, choose ‘Forms’ and from the web parts section, choose ‘InfoPath Form Web Part’, as shown below –

Now repeat the same step for adding ‘Excel Web Access Web part’ to the middle column zone. This time choose 'Business Data’ from categories section and choose the ‘Excel Web Access’ web part from the web parts section.

Now your page is ready with InfoPath form Web Part and Excel web access Web part. Let’s add the InfoPath Template to the InfoPath Form web part and Excel workbook to Excel Web access web part respectively.

Now click on ‘Click here to open the tool pane’ link in InfoPath form web part as shown below –

And change the web part properties as shown below –  

Set the ‘List or Library’ to InfoPath forms library and choose content type ‘Form’ as shown above.

Now click on Excel web part ‘Click here to open the tool pane’ link button as shown below -

Now change the Web Part property to set the path of workbook as shown below –  

 

Now click on ‘Stop Editing’ button and open ‘Microsoft Office SharePoint Designer 2010’. Open the SharePoint site in which we have created ‘Excel workbook’, ‘InfoPath form’ and ‘Web part page’.

Now click on ‘Site Assets’ and choose the page ‘SalaryCalculation.aspx’. Right click on the page and click on ‘Open’. Switch to ‘Design’ view. Now right click on the ‘InfoPath Form Web Part’ and click on ‘Add Connection’ as shown below –

This will show you a ‘Web Part Connection’ wizard. Now from the drop down list, make a choice of ‘Send Data To’ and click on ‘Next’ button. In the next step, choose the option ‘Connect to a Web Part on this page’ and click on the ‘Next’ Button.

Now choose the Target web part as ‘Excel Web Access’ web part and Target action as ‘Get values for Multiple Parameters From’ and click on the ‘Next’ button.

In this next step, map the InfoPath form fields to Excel parameters as shown below and click ‘Next’ button and ‘Finish’ the wizard.

  

Let’s save our changes and test our steps in our SharePoint site. Enter the values in our InfoPath form textboxes and click on the ‘Send Data’ button. These values will now be sent to the Excel web access web part and the Excel workbook will perform the calculation and displays the result as shown below –  

Summary – In this article, we have seen how to design an InfoPath form and Excel workbook with parameters. We have also seen how to add an InfoPath form into ‘InfoPath form Web part’ and add Excel workbook to ‘Excel Web Access web part’ and connect these two web parts using Microsoft Office SharePoint Designer 2010.