How to publish MS Excel cell values in Metadata columns as in QuickParts for MS Word

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 (https://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[1];

                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);

            }

            fileStream.Close();

            properties.ListItem.SystemUpdate();

        }

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.