DotNetCurry Logo

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

Posted by: Pravinkumar Dabade , on 4/2/2011, in Category SharePoint
Views: 76980
Abstract: 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

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:  

excel workbook

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:

excel name manager

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:

Document Library

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

Sharepoint Document Library

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 –

Excel Services

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 –

Sharepoint Publish Options

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 –

SharePoint Path Library

 

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 –

InfoPath Template

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 –

Fields Task Pane

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 –

Add Field or Group

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 –

Infopath Form

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 –

InfoPath Home Ribbon

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 –

InfoPath Home Ribbon

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 –

InfoPath Property Promotion

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 –

Infopath Select Field

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 –

Sharepoint Site Actions

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

Web Part Page

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 –

Infopath Form WebPart

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 –

Infopath Form WebPart

And change the web part properties as shown below –  

Infopath Form

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 -

Excel Web Access

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

WorkBook Path 

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 –

InfoPath Add Connection

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.

InfoPath Excel Mapping  

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 –  

InfoPath Excel Mapping

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.

Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+
Further Reading - Articles You May Like!
Author
Pravinkumar, works as a freelance trainer and consultant on Microsoft Technologies. He is having over 10 years of experience in IT and is also a Microsoft Certified Trainer(MCT). He has conducted various corporate trainings on all versions of .NET Technologies including .NET, SharePoint Server, Microsoft SQL Server, Silverlight, ASP.NET, Microsoft PerformancePoint Server 2007 (Monitoring). He is passionate about learning new technologies from Microsoft. You can contact Pravinkumar at dabade[dot]pravinkumar [attherate] gmail[dot]com


Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!
Comment posted by Shmick on Tuesday, June 7, 2011 9:25 AM
Great article! Everything works until I get to the connections. I don't have the option to get multiple connections, a pop up window shows up and only allows me to make one connection. Any idea what I should do?

Thanks,
Shmick
Comment posted by Shmick on Tuesday, June 7, 2011 10:22 AM
Never mind, found out my error. Was trying to edit connections within the browser, not SPD.

Once again,

Thank You

Shmick
Comment posted by Ashan on Wednesday, November 9, 2011 1:23 AM
I dont have the "Send Data To Webpart" option in my rule details dialog box. Thats where I lost the grip. Please respond. Thanks.
Comment posted by Please help ASAP on Monday, January 23, 2012 4:13 PM
Hey! I managed to complete all the steps except the last step one about "map the InfoPath form fields to Excel parameters".

I have used my own Infopath form, but used your tutorial excel workbook. And my problem is that the list of "Colums in Webpart for Infopath" and "Input to Excel Web Access" is just empty? I cant create any connection between these two becuase i simply dont have any to connect. (sorry bad english, from a non-speaking English country)
Comment posted by Please help ASAP on Tuesday, January 24, 2012 7:22 AM
Hey! I managed to complete all the steps except the last step one about "map the InfoPath form fields to Excel parameters".

I have used my own Infopath form, but used your tutorial excel workbook. And my problem is that the list of "Colums in Webpart for Infopath" and "Input to Excel Web Access" is just empty? I cant create any connection between these two becuase i simply dont have any to connect. (sorry bad english, from a non-speaking English country)
Comment posted by Please help ASAP on Tuesday, January 24, 2012 7:48 AM
Hey! I managed to complete all the steps except the last step one about "map the InfoPath form fields to Excel parameters".

I have used my own Infopath form, but used your tutorial excel workbook. And my problem is that the list of "Colums in Webpart for Infopath" and "Input to Excel Web Access" is just empty? I cant create any connection between these two becuase i simply dont have any to connect. (sorry bad english, from a non-speaking English country)
Comment posted by Please help ASAP on Tuesday, January 24, 2012 7:48 AM
Hey! I managed to complete all the steps except the last step one about "map the InfoPath form fields to Excel parameters".

I have used my own Infopath form, but used your tutorial excel workbook. And my problem is that the list of "Colums in Webpart for Infopath" and "Input to Excel Web Access" is just empty? I cant create any connection between these two becuase i simply dont have any to connect. (sorry bad english, from a non-speaking English country)
Comment posted by Example works but that's it on Monday, October 1, 2012 11:35 AM
Thank you for creating this. When I walked through the example you laid out, the form updates the spreadsheet. However, I tried to use the same method for my own form/spreadsheet and the spreadsheet is not updating. At first I thought it was because I used a Wiki page instead of Web Part page but when that was fixed, the form still does not update the spreadsheet. Would you be able to respond to help trouble-shoot? Thank you!
Comment posted by JoeB on Tuesday, January 29, 2013 11:19 AM
Hey, this is just what I needed.  Thank you.
I only have one problem now: I need to do exactly what you are describing here, except I need the input to Excel to move to the first open cell.
For example, I have Name; Date; Type; as columns in excel as a1,b1,c1
The first time the user uses the infopath form to update, the data:
John; 01/29/13; HR
would be sent to a2,b2,c2
Then the next time:
Mary; 01/29/13; Finance
would be sent to a3,b3,c3
and so on.  Any pointers on how to accomplish that?

sincerely,
Joe
Comment posted by Mike on Tuesday, July 23, 2013 8:35 AM
Now that you have passed all of the values from InfoPath to Excel...how can you get multiple, calculated values back into InfoPath?
Comment posted by Steven Bonera on Wednesday, August 14, 2013 8:24 AM
Great article, Is there a way that when the data is sent to excel it sends it to the next row, i.e  i have a column called customer name and each time the form is submitted it adds to a new row
Comment posted by Ref Please help ASAP on Thursday, September 5, 2013 5:24 AM
From "Please help ASAP" posted, when I walked to last step as map the InfoPath form fields to Excel parameters as "Add connection", I have not see last step one screen as "Web Part Connection Wizard" from --->
"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’ "

I see the connection only one field between Infopath form fields and Excel Parameter fileds.
Could you help me to solve this ASAP.



Comment posted by JOe on Friday, September 6, 2013 3:57 AM
Hi Ref Please help ASAP

Could you please install Microsoft SharePoint Designer 2010 to fix you problem.. as link below.

- SharePoint Designer 2010 64-bit:
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=566d3f55-77a5-4298-bb9c-f55f096b125d

- SharePoint Designer 2010 32-bit:
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=d88a1505-849b-4587-b854-a7054ee28d66

Cheers,
Comment posted by HaHa on Monday, September 16, 2013 2:43 AM
When submitted to Excel and how to create new Excel file to some library?
Comment posted by tia on Thursday, October 10, 2013 6:17 PM
This is almost exactly what I have been looking for - however, when I am setting the parameter for output from my infopath form, I cannot add the primary cells I need in to output to Excel because they are a part of a repeating table/section.  Do you have a solution for repeating tables/sections?

Thank you!
Comment posted by tia on Friday, October 11, 2013 11:18 AM
This is almost exactly what I have been looking for - however, when I am setting the parameter for output from my infopath form, I cannot add the primary cells I need in to output to Excel because they are a part of a repeating table/section.  Do you have a solution for repeating tables/sections?

Thank you!
Comment posted by Pranjali Bhandari on Thursday, March 19, 2015 5:51 AM
Hello Sir,
Can we create web part like chioce filter and execl web access connected  using visual studio?
As we are having OOB Choice filter and excel web access web part connect on site page .But is is not supported for Safari browser on ipad.
Comment posted by Pranjali Bhandari on Thursday, March 19, 2015 6:04 AM
Hello Sir,
Can we create web part like chioce filter and execl web access connected  using visual studio?
As we are having OOB Choice filter and excel web access web part connect on site page .But is is not supported for Safari browser on ipad.