Import Excel Data in InfoPath 2010 using DocumentFormat.OpenXML.dll
Objective
The purpose of this article is to demonstrate as to how we can import Excel data into InfoPath form. We will make use of the DocumentFormat.OpenXML library to communicate with the Excel file. The excel file will be stored in a SharePoint Document library.
The InfoPath form will be deployed to a Form library and the code will be executed as a sandboxed solution. Most people think that code behind an InfoPath form can only be published to Central Administration and should be admin approved but an InfoPath form can be published to a form library or as a site content type with code behind.
Implementation
- First, create your InfoPath form and then click on Developer's tab.
- Change the Language as required, for our purpose, select C#.
- Click on Code Editor to open the Visual Studio tools for applications (VSTA).
- We cannot use LINQ in VSTA so we have to modify the code as below.
Code
Fetch the Excel file from Library:
Use SharePoint dll to query the Excel file from the library. Use the below code to convert the SPListItem to file and then to byte [] array.
SPFile file = item.File;
byte[] data = file.OpenBinary();
Stream stream = new MemoryStream(data);
Now Create a method to which you will pass the stream object and will process and bind data to InfoPath fields.
requestID is the integer value in cell "A" which is unique for each row in Excel. We will use this value to populate the data of that specific row in our form.
You also need to pass the fileName, sheetName, current class reference object i.e "this" and the XMLNamespace Manager object. The code will fetch the data from C and E column and bind them to our InfoPath fields.
You need to refer the below namespaces in your code:
Make sure related assemblies are added in the project.
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Collections.Generic;
using System.Text.RegularExpressions;
using Microsoft.SharePoint;
public static bool XLGetRequestValues(Stream fileName, string sheetName, int requestID, [YourClass].FormCode objThis, XmlNamespaceManager nameSpaceMang)
{
bool requestIDFound = false;
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart wbPart = document.WorkbookPart;
// Find the sheet with the supplied name, and then use that Sheet
// object to retrieve a reference to the appropriate worksheet.
IEnumerable<Sheet> theSheets = wbPart.Workbook.Descendants<Sheet>();
Sheet wrkSheet = null;
foreach (Sheet sheet in theSheets)
{
if (sheet.Name == sheetName)
{
wrkSheet = sheet;
break;
}
}
if (wrkSheet == null)
{
throw new ArgumentException(sheetName);
}
// Retrieve a reference to the worksheet part, and then use its
// Worksheet property to get a reference to the cell whose
// address matches the address you supplied:
WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(wrkSheet.Id));
IEnumerable<Row> rows = wsPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>();
foreach (Row row in rows)
{
int reqFieldsCount = 0;
IEnumerable<Cell> cells = row.Elements<Cell>();
foreach (Cell cell in cells)
{
try
{
if (requestIDFound == false && GetColumnName(cell.CellReference) == "A")
{
try
{
if (requestID.ToString() == cell.CellValue.Text)
{
requestIDFound = true;
}
}
catch (Exception ex)
{
//The column has invalid values and does not require any action
}
}
else
{
if (requestIDFound)
{
if (reqFieldsCount >= 5) // If E column has been reached then break the loop.
{
break;
}
else
{
try
{
switch (GetColumnName(cell.CellReference))
{
//Project Information Section
case "C":
reqFieldsCount++;
objThis.CreateNavigator().SelectSingleNode("/my:myFields/my:MainForm/my:ProjectInformation/my:ProjectName", nameSpaceMang).SetValue(GetCellValue(cell, wbPart).ToUpper());
break;
case "E":
reqFieldsCount++;
objThis.CreateNavigator().SelectSingleNode("/my:myFields/my:MainForm/my:ProjectInformation/my:ProjectDescription", nameSpaceMang).SetValue(GetCellValue(cell, wbPart));
break;
default:
break;
}
}
catch (ArgumentOutOfRangeException argsOutOfRange)
{
objThis.CreateNavigator().SelectSingleNode("/my:myFields/my:CodeFields/my:Exception", nameSpaceMang).SetValue(argsOutOfRange.Message + " - " + argsOutOfRange.StackTrace);
}
catch (Exception ex)
{
//Unable to evaluate the switch or case expression
}
}
}
else
{
break;
}
}
}
catch (Exception ex)
{
//No need to catch as this is an exception regarding the cell
}
}
if (requestIDFound)
{
break;
}
}
}
return requestIDFound;
}
//This method gets the column name
private static string GetColumnName(string cellReference)
{
Regex ColumnNameRegex = new Regex("[A-Za-z]+");
if (ColumnNameRegex.IsMatch(cellReference))
return ColumnNameRegex.Match(cellReference).Value;
throw new ArgumentOutOfRangeException(cellReference);
}
//This method gets the cell value
public static string GetCellValue(Cell cell, WorkbookPart wbPart)
{
string value = null;
if (cell != null)
{
value = cell.InnerText;
// If the cell represents a numeric value, you are done.
// For dates, this code returns the serialized value that
// represents the date. The code handles strings and Booleans
// individually. For shared strings, the code looks up the
// corresponding value in the shared string table. For Booleans,
// the code converts the value into the words TRUE or FALSE.
if (cell.DataType != null)
{
switch (cell.DataType.Value)
{
case CellValues.SharedString:
// For shared strings, look up the value in the shared
// strings table.
SharedStringTablePart stringTableOrg = null;
IEnumerable<SharedStringTablePart> stringTables = wbPart.GetPartsOfType<SharedStringTablePart>();
foreach (SharedStringTablePart stringTablePart in stringTables)
{
stringTableOrg = stringTablePart;
}
int index = 0;
foreach (SharedStringItem item in stringTableOrg.SharedStringTable.Elements<SharedStringItem>())
{
if (index == int.Parse(value))
{
value = item.InnerText;
break;
}
index++;
}
break;
case CellValues.Boolean:
switch (value)
{
case "0":
value = "FALSE";
break;
default:
value = "TRUE";
break;
}
break;
}
}
}
return value;
}
You can then make a call to the XLGetRequestValues as below on your button click.
public void GetCellValues_Clicked(object sender, ClickedEventArgs e)
{
if (XLGetRequestValues(stream, "Transfer template", requestID, this, NamespaceManager) == false)
{
//Unable to retrieve data
}
}
I hope this article helps!!!
Regards
Paramdeep Singh