Calculating date differences in InfoPath using SharePoint Excel Services

by S.Y.M. Wong-A-Ton

Use an Excel workbook published to Excel Services on a Microsoft Office SharePoint Server 2007 to calculate the difference between two dates in InfoPath without writing a single line of code.

Problem

You have an InfoPath form with two date fields. You would like to calculate the difference between the two dates without writing code.

Solution

Create an Excel workbook to calculate the difference between two dates, publish this Excel workbook to Excel Services on a Microsoft Office SharePoint Server 2007, and call Excel Services' web services from within InfoPath to do the date difference calculation without writing a single line of code.

Discussion

You can accomplish this functionality as follows:

  1. Create a new Excel workbook.
  2. Rename cell A1 to StartDate and change its format to Date.
  3. Rename cell B1 to EndDate and change its format to Date.
  4. Rename cell C1 to DateDiff.
  5. Set the formula of cell C1 to =EndDate-StartDate.
  6. Rename sheet Sheet1 to CalcDateDiff.

    Figure 1. Excel workbook to calculate date differences.
  7. Publish the Excel workbook to a Trusted File Location for Excel Services.
  8. Design an InfoPath form as shown in figure 2 with two Date Picker controls named startDate and endDate, one Text Box control named dateDiff, and a Button control.

    Figure 2. The InfoPath form template in design mode.
  9. Open the Data Source pane.
  10. Add a Field (element) called sheetName of type Text (string) and set its Default Value to CalcDateDiff.
  11. Add a Field (element) called rangeNameStartDate of type Text (string) and set its Default Value to StartDate.
  12. Add a Field (element) called rangeNameEndDate of type Text (string) and set its Default Value to EndDate.
  13. Add a Field (element) called rangeNameDateDiff of type Text (string) and set its Default Value to DateDiff.
  14. The Main data source of your form should now resemble the following figure:

    Figure 3. The structure of the Main data source of the form template.
  15. Go to Tools > Data Connections....
  16. Click on Add... to open the Data Connection Wizard.
  17. Select the Receive data option and click on the Next > button.
  18. Select the Web service option and click on the Next > button.
  19. Type in the URL to Excel Services running on your server.
    Example: http://<your_server_name>/_vti_bin/ExcelService.asmx?wsdl
  20. Click on the Next > button.
  21. Select the OpenWorkbook web service and click on the Next > button.
  22. Set the parameters for the web service as follows:
    ParameterValue
    tns:workbookPath\\<your_server_name>\<your_trusted_file_location>\DateDifferenceCalculation.xslx
    tns:uiCultureName 
    dataCultureName 
    where DateDifferenceCalculation.xslx is the name of the Excel workbook doing the date difference calculation.
  23. Click twice on the Next > button.
  24. Uncheck the Automatically retrieve data when form is opened checkbox and click on the Finish button.
  25. Click on Add... to open the Data Connection Wizard.
  26. Select the Submit data option and click on the Next > button.
  27. Select the To a Web service option and click on the Next > button.
  28. Type in the URL to Excel Services running on your server.
    Example: http://<your_server_name>/_vti_bin/ExcelService.asmx?wsdl
  29. Click on the Next > button.
  30. Select the SetCellA1 web service and click on the Next > button.
  31. Set the parameters for the web service as follows:
    ParameterValue
    tns:sessionId/dfs:myFields/dfs:dataFields/tns:OpenWorkbookResponse/tns:OpenWorkbookResult
    tns:sheetName/my:myFields/my:sheetName
    tns:rangeName/my:myFields/my:rangeNameStartDate
    tns:cellValue/my:myFields/my:startDate
  32. Click on the Next > button.
  33. Rename the data connection to SetStartDate.
  34. Click on the Finish button.
  35. Click on Add... to open the Data Connection Wizard.
  36. Select the Submit data option and click on the Next > button.
  37. Select the To a Web service option and click on the Next > button.
  38. Type in the URL to Excel Services running on your server.
    Example: http://<your_server_name>/_vti_bin/ExcelService.asmx?wsdl
  39. Click on the Next > button.
  40. Select the SetCellA1 web service and click on the Next > button.
  41. Set the parameters for the web service as follows:
    ParameterValue
    tns:sessionId/dfs:myFields/dfs:dataFields/tns:OpenWorkbookResponse/tns:OpenWorkbookResult
    tns:sheetName/my:myFields/my:sheetName
    tns:rangeName/my:myFields/my:rangeNameEndDate
    tns:cellValue/my:myFields/my:endDate
  42. Click on the Next > button.
  43. Rename the data connection to SetEndDate.
  44. Click on the Finish button.
  45. Click on Add... to open the Data Connection Wizard.
  46. Select the Receive data option and click on the Next > button.
  47. Select the Web service option and click on the Next > button.
  48. Type in the URL to Excel Services running on your server.
    Example: http://<your_server_name>/_vti_bin/ExcelService.asmx?wsdl
  49. Click on the Next > button.
  50. Select the GetCellA1 web service and click on the Next > button.
  51. Do not set any parameters and click twice on the Next > button.
  52. Uncheck the Automatically retrieve data when form is opened checkbox and rename the data connection to GetDateDiff.
  53. Click on the Finish button.
  54. Click on Add... to open the Data Connection Wizard.
  55. Select the Submit data option and click on the Next > button.
  56. Select the To a Web service option and click on the Next > button.
  57. Type in the URL to Excel Services running on your server.
    Example: http://<your_server_name>/_vti_bin/ExcelService.asmx?wsdl
  58. Click on the Next > button.
  59. Select the CloseWorkbook web service and click on the Next > button.
  60. Set the parameters for the web service as follows:
    ParameterValue
    tns:sessionId/dfs:myFields/dfs:dataFields/tns:OpenWorkbookResponse/tns:OpenWorkbookResult
  61. Click on the Next > button.
  62. Rename the data connection to CloseWorkbook.
  63. Click on the Finish button and close the Data Connections dialog box.
  64. Double-click on the dateDiff field to open its Properties dialog box.
  65. Set its Default Value equal to the GetCellA1Result field of the GetDateDiff secondary data source, i.e., xdXDocument:GetDOM("GetDateDiff")/dfs:myFields/dfs:dataFields/tns:GetCellA1Response/tns:GetCellA1Result.
  66. Double-click on the button to open its Properties dialog box.
  67. Click on the Rules... button and add a Rule with the following Actions:
    Query using a data connection: OpenWorkbook
    Submit using a data connection: SetStartDate
    Submit using a data connection: SetEndDate
    Set a field's value: sessionId = OpenWorkbookResult
    The previous action should set the value of the sessionId field of the GetDateDiff secondary data source equal to the value of the OpenWorkbookResult field of the OpenWorkbook secondary data source.
    Set a field's value: sheetName = sheetName
    The previous action should set the value of the sheetName field of the GetDateDiff secondary data source equal to the value of the sheetName field of the Main data source.
    Set a field's value: rangeName = rangeNameDateDiff
    The previous action should set the value of the rangeName field of the GetDateDiff secondary data source equal to the value of the rangeNameDateDiff field of the Main data source.
    Query using a data connection: GetDateDiff
    Submit using a data connection: CloseWorkbook
  68. Close all open dialog boxes by clicking on the OK button on each open dialog box.
  69. Give your InfoPath form Full Trust by opening the Form Options dialog box via the Tools > Form Options... menu item, selecting the Security and Trust category, unchecking the Automatically determine security level checkbox, selecting the Full Trust option, checking the Sign this form template checkbox, and creating or selecting a digital certificate.

You should now have a fully functional InfoPath form with a button that when clicked on will calculate and display the amount of days between two dates entered. But you need not stop here; you can use the technique described in this article to leverage using any formula from Excel within InfoPath.

Related InfoPath Articles:

Copyright: This article may not be used on web sites (whether personal or otherwise), copied, disseminated, altered, printed, published, broadcasted, or reproduced in any way without an expressed written consent of S.Y.M. Wong-A-Ton. Usage of techniques demonstrated in this article may be used within any Microsoft InfoPath project. This article is provided without any warranties. Copyright for this article is non-transferrable and remains with the author, S.Y.M. Wong-A-Ton.

Working with InfoPath