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.

Comments

• Anonymous
  May 14, 2009
  PingBack from http://microsoft-sharepoint.simplynetdev.com/how-to-publish-ms-excel-cell-values-in-metadata-columns-as-in-quickparts-for-ms-word/

• Anonymous
  August 04, 2009
  Hi, thanks for this post. I'm attempting something similar, however I need to write data to one of the worksheets. Seems to be OK except the file contents don't update. Have you implemented anything like this? What I'm doing: Add a new file to the library based on a template.-OK Open the file's binary and create a new xlpackage.-OK Get some data and fill one of the worksheets with it.-OK put the updated package stream data back into the SPFile object with the new worksheet data. -NOT OK. New file is in the library but the worksheet has no data. Any ideas?