Create an Excel 2010 spreadsheet from InfoPath repeating table data in SharePoint
Learn how you can go about writing code that takes data from a repeating table on an InfoPath 2010 form, creates an Excel 2010 spreadsheet using this data, and then finally saves the Excel workbook in a SharePoint 2010 document library.
The same way you can use the Open XML SDK to programmatically create a Word document on the server in SharePoint 2010, you can use the Open XML SDK to programmatically create an Excel workbook on the server in SharePoint 2010.
The only difference is that you would have to write code that makes use of SpreadsheetDocument objects instead of WordprocessingDocument objects.
And as already shown in recipes 80 and 101 of InfoPath 2010 Cookbook 3, you can write such code either inside or outside of InfoPath.
You can refer to an article entitled Generating Excel 2010 Workbooks by using the Open XML SDK 2.0 on MSDN to get started with using the Open XML SDK to write code that can create an Excel workbook. In addition, the documentation of the Open XML SDK 2.0 itself contains code samples for getting cool things done, such as for example programmatically adding a chart to an Excel spreadsheet.
Once you know the basics of creating an Excel 2010 workbook using either C# or Visual Basic with the Open XML SDK, you can extend that knowledge for use in or with InfoPath 2010.
Using data from a repeating table on an InfoPath form to create an Excel workbook that contains this data on one of its spreadsheets then becomes a matter of programmatically looping through the rows and cells of the repeating table in InfoPath and then creating corresponding rows and cells with data on the Excel spreadsheet.
The solution shown in the InfoPath video below combines techniques discussed in recipes 70 and 98, combined with a couple of "stolen ideas" from recipe 80 of InfoPath 2010 Cookbook 3 to create an InfoPath browser-enabled form with which you can programmatically create and save an Excel workbook in a SharePoint document library based on data stored in a repeating table on the InfoPath form.
The code was written solely in the InfoPath form template (so no SharePoint programming is required) and the InfoPath form template did not require Full Trust to run, so the form template was not published as an administrator-approved form template to SharePoint, but rather as a sandboxed solution through the normal publish to a SharePoint form library option in InfoPath 2010.
The solution consists of approximately 22 lines of code to create and fill the Excel spreadsheet and then another couple of lines of code to save the Excel workbook in the SharePoint document library. So all in all, the amount of code you require for such functionality can be kept to a minimum.
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. The 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.