We can achieve this by creating an ItemAdded event receiver. To extract the metadata values from Excel cells, we need OpenXML. In my case, I used a solution called ExcelPackage from CodePlex (http://www.codeplex.com/ExcelPackage/). I modified the source code of ExcelPackage a little. The reason is ExcelPackage expects the source excel file located in a physical file path. But in our case the files will be in a Document Library or in SP content DB. So I used filestream instead of filepath.
I modified the ExcelPackage.cs file and added this new constructor:
public ExcelPackage(Stream fileContent)
_outputFolderPath = “”;
_package = Package.Open(fileContent, FileMode.Open, FileAccess.ReadWrite);
I added my modified ExcelPackage.dll in my own project. I have used a event receiver and feature receiver combination. In the event receiver I used this within ItemAdded:
public override void ItemAdded(SPItemEventProperties properties)
MemoryStream fileStream = new MemoryStream();
fileStream.Write(properties.ListItem.File.OpenBinary(), 0, Convert.ToInt32(properties.ListItem.File.TotalLength));
using (ExcelPackage xlPackage = new ExcelPackage(fileStream))
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets;
properties.ListItem[“Amount”] = worksheet.Cell(3, 5).Value;
properties.ListItem[“Rate”] = worksheet.Cell(5, 5).Value;
properties.ListItem[“Time”] = worksheet.Cell(7, 5).Value;
properties.ListItem[“Monthly Payment”] = worksheet.Cell(10, 5).Value;
properties.ListItem[“Client Name”] = worksheet.Cell(2, 3).Value;
string assignDate = worksheet.Cell(4, 3).Value;
properties.ListItem[“Contract Date”] = DateTime.Parse(assignDate);
Now I created a document Library with MS Excel as Document Template and with additional fields like Amount, Rate, Time etc. I also created an Excel Workbook with this structure and where Monthly Payment has a function to calculate the payment for our mortgage calculation.
Once done, I published this excel file as the template file of our document library. I also implemented our event receiver and feature receiver combo for this document Library. If required, you need to add the ExcelPackage.dll into GAC. Now add a new file in this Document Library and you will see the values from respective cell values will appear in relevant metadata columns.