Converting InfoPath Forms to Excel in SharePoint

In this guide you’ll learn how to convert InfoPath Forms to Excel files in SharePoint using Muhimbi PDF Converter. The InfoPath to MS Excel facility can generate output in xls, xlsx, txt, html and ods formats.

There are some limitations to these conversions due to the nature of the non-PDF-based destination formats:

1. Attachments: When converting an InfoPath form to MS Word, the software does not work in the same way as PDF which also converts all attachments and merges them into the main PDF document.

2. View Selection: The software provides a number of ways to specify which view or views to convert. When converting to MS-Word it is only possible to convert a single view as these file formats don’t support merging like how PDF supports them.A workaround for getting all views in a single MS Word document is to create a ‘conversion specific view’ and then to combine the content of multiple views in it.

   Note: Print Views are ignored when converting to MS Word. You can use Muhimbi’s View Selection facilities if you wish to convert any view other than the default View.

3. Formatting: PDF is a very flexible format that allows any content to be placed anywhere on the page. However MS-Word is not necessarily as flexible as PDF and you may need to optimize your InfoPath form for conversion, or create a ‘conversion specific view’.

   Note: MS Excel uses a ‘cell based approach to display content. If an InfoPath form is not specifically designed for export to Excel i.e. it uses nested tables or different column widths across a page, then you may need to optimize your InfoPath form for conversion, or create a ‘conversion specific view’.

Selecting InfoPath Views to Export to MS Excel

Muhimbi PDF Converter allows you to select specific views to export. However when converting to MS Excel, it is only possible to convert a single view as these file formats don’t support merging like how PDF supports them. A workaround for getting all views in a single MS Excel document is to create a ‘conversion specific view’ and then to combine the content of multiple views in it.

In this scenario we have an Employee Expense Review form with the following three views:

1. Employee Data Entry (default view): This is a view used for populating the data in the MS SharePoint List using the InfoPath client or Forms Services. This is the default view which is usually selected for conversion to MS Excel.

2. Employee Data Entry (print view): A special view that is optimized for printing to a network laser printer. This is specified as the print view. However as Print Views are ignored when converting to MS Excel, you can use Muhimbi’s View Selection facilities to select this view to convert.

3. _MuhimbiView Employee Data Entry (XLS Export view): A conversion specific separate view containing all views that is used to export the InfoPath form to MS Excel format. When converting to MS Excel format it is only possible to convert a single view as MS Excel doesn’t support merging like how PDF supports them. A workaround for getting all views in a single MS Excel document is to create a ‘conversion specific view’ like this one combining the content of multiple views in it. You will need to start the name of the view with “_MuhimbiView”. The Muhimbi PDF Converter will detect this view that starts with this name, and export them into a MS Excel spreadsheet.

   Note: You can hide these views from the end user by marking them as such in the properties of the view.

This is a handy feature of Muhimbi PDF Converter if you know beforehand that you will always be exporting the same view(s) to MS Excel format.

Determine at Runtime InfoPath Views to Export

There are times when you need to export different views depending on the state of the data. In order to achieve this, you can do the following steps:

Let us design an Employee Expense Claim form, where we need a MS Excel spreadsheet to be generated once the employee creates the expense claim. A second MS Excel spreadsheet needs to be generated once the manager approves the claim.

In this scenario, our Expense Claim form consists of the following Views:

1. Expense Claim (default): Used by the employee to report expenses.

2. Expense Claim (approve claim): Used by the manager to add comments and additional information for approval.

3. Expense Claim (XLS Export report):A conversion specific separate view that is used to export the form to MS Excel spreadsheet after the employee has submitted, but before the manager has reviewed the form.

4. Expense Claim (XLS Export approve):A conversion specific separate view that is used to export the form to MS Excel spreadsheet after the manager has reviewed the form.

We can implement this by adding a (hidden) text box named “_MuhimbiViews” (case sensitive and using the default ‘my’ namespace) to any of the views and populating it with the name of the view name. The Muhimbi PDF Converter will automatically pick up this view and export it to MS Excel spreadsheet.

Note: You cannot specify multiple views, and even if they are specified, only the first view in the list is converted.

In addition to adding the “_MuhimbiViews” text field to the form, all the developer of the form needs to do is add a little bit of logic by creating multiple Submit buttons and enabling one of these buttons when the employee submits the expense report and enabling another Submit button when the manager reviews and approves the expense report. When one submit button is enabled, the other can be disabled and hidden.The developer of the form needs to also add a little bit of logic to each of the Submit events of the form to specify in the “_MuhimbiViews” field which specific view name to export.

Note: The export to MS Excel spreadsheet does not export attachments like converting to PDF. MS Excel uses a ‘cell based approach to display content. If an InfoPath form is not specifically designed for export to MS Excel i.e. it uses nested tables or different column widths across a page, then you may need to optimize your InfoPath form for conversion, or create a ‘conversion specific view’.

Converting InfoPath to MS Excel Using MS SharePoint Designer

One of the more popular features of the Muhimbi PDF Converter for SharePoint is the ability to control which view to convert to MS Excel. It is even possible to specify which view to convert depending on the current status of the form.

The view that needs to be converted can be specified during design time, or at run time using a little bit of custom code or InfoPath rules. You can also control which views to convert using MS SharePoint Designer workflow. The example provided below uses MS SharePoint Designer workflow. But this works equally well in the Nintex workflow as well.

Creating the InfoPath Form

1. Navigate to the MS SharePoint List where you want to design the Infopath form.

2. Go to Site Settings → Form Options and click Customize the current form using Microsoft Infopath and click OK button.

   

3. InfoPath Designer for the list opens.

4. Create a Text box and name it '_MuhimbiViews'.

   

5. On Page Design tab select New View and name the new view ‘FormView’. Change the Title at the top to 'Form View'.

6. Select the fields you like to see in this view and design the Form View accordingly.

7. On Page Design tab select New View again and name this view as ‘XLSView’. Change the Title at the top to 'Spreadsheet View'.

8. This will be a conversion specific separate view. Select the fields you like to see in the converted MS Excel spreadsheet in this view and design this Spreadsheet View accordingly.

9. Click File → Publish.

Creating the Workflow

1. From the Library ribbon in MS SharePoint select Workflow Settings → Create a workflow in SharePoint Designer.

2. Name the workflow whatever you like.

In this workflow you will need to set the value of the _MuhimbiViews field before carrying out the conversion. The workflow does not persist the value of the changed field before executing the conversion step, and so we need to ensure the values are updated accordingly before this step. You can do this in two ways:

Using an Impersonation Step

1. Position the cursor before Step 1 and click Impersonation Step button in the ribbon.

2. Position the cursor inside the Impersonation Step and add a ‘set field in current item' action.

3. ‘Use this action to set the value of the ‘_MuhimbiViews’ field to 'XLSView’.

4. Position the cursor after the Impersonation Step and click Step button to add a new step.

5. In this new step add the 'Convert Document' action and fill in the blanks.

6. Publish the workflow and execute it on a previously filled out form.

The resulting MS Excel spreadsheet should contain XLSView in the MS Excel spreadsheet.

Using Pause for Duration

The steps below assume the default, empty, workflow state and not the workflow entered as part of the previous use case.

1. Position the cursor inside the first Step and add a 'set field in current item' action.

2. ‘Use this action to set the value of the ‘_MuhimbiViews’ field to 'XLSView’.

3. Add the 'Pause for duration' action as the next step and let it pause for one minute.

4. Add the 'Convert Document' action and fill in the blanks.

5. Publish the workflow and execute it on a previously filled out form.

It may take a while to execute due to the Pause action, but the resulting MS Excel spreadsheet should contain the XLSView in the MS Excel spreadsheet.

A third option exists, which is to specify Converter Specific Settings using the XML Override facility in the ‘Convert Document’ Workflow activity.

Note: When converting an InfoPath form to MS Excel using any of the above methods, the software does not work in the same way as PDF. It can only convert one view specified to a MS Excel spreadsheet. If more than one view is specified in the Convert Document action, then the first view is only converted to the MS Excel spreadsheet.

Additional Resources

Knowledge Base Articles

Troubleshooting Articles

Blog articles