Thursday 30 May 2013

On excel sheet upload read the workbook and populate data to sharepoint list

Requirement in my current project:
In a document library when I upload an excel sheet, a specific workbook has to be read and the contents have to be uploaded to a sharepoint custom list.
The approach followed was create an event receiver and register as a feature. Following is the code for event receiver.
public override void ItemAdded(SPItemEventProperties properties)
        {
            base.ItemAdded(properties);
          
            var list = getSPList("{150301BF-D0BD-452C-90D7-2D6CD082A247}");          
           
          
            SPListItem doc = properties.ListItem;
            doc["Msg"] = "items deleted from req list";
            doc.Update();
            string excelname=doc.File.Name;
           
          
            System.Diagnostics.EventLog.WriteEntry("ExcelUpload", "calling read excel");
            string filepath = doc.File.Url.ToString();
            doc["Msg"] = "excel name" + excelname + filepath;
            doc.Update();
            readExcel(excelname,filepath);
           
        }
       
        private static SPList getSPList(String SPListGuid)
        {
           // SPSite Site = SPContext.Current.Site;
            SPSite Site = new SPSite("http://omistestsrv:32252/sites/OMD");
            SPWeb web = Site.OpenWeb();
            Guid listid = new Guid(SPListGuid);
            web.AllowUnsafeUpdates = true;
            SPList List = web.Lists[listid];
            return List;
        }
       
        private void readExcel(string excelname,string filepath)
        {
            try
            {
                SPSecurity.RunWithElevatedPrivileges(delegate()
                {
                    using (SPSite Site = new SPSite("http://omistestsrv:32252/sites/OMD"))
                    {
                        SPWeb web = Site.OpenWeb();
                        string workbookpath = web.Url + "/" + filepath;
                        web.AllowUnsafeUpdates = true;
                        var _excelApp = new Microsoft.Office.Interop.Excel.Application();
                        System.Diagnostics.EventLog.WriteEntry("ExcelUpload", "iside read excel");
                      
    //my code breaks or fails when cursor reaches this statement. I am not able to open the excel sheet, there is no    //problem related to the permission. same code snippet works in a console application. but when tried as an event    //handler in sharepoint it breaks. can anyone help me to resolve the problem
                        workBook = _excelApp.Workbooks.Open(workbookpath, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing,Type.Missing, Type.Missing);
                                        
                        System.Diagnostics.EventLog.WriteEntry("ExcelUpload", "after excel open");
                      
                          
                            int numSheets = workBook.Sheets.Count;
                            // Iterate through the sheets. They are indexed starting at 1.
                            //
                            System.Diagnostics.EventLog.WriteEntry("ExcelUpload", numSheets.ToString());
                            for (int sheetNum = 12; sheetNum < 13; sheetNum++)
                            {
                                System.Diagnostics.EventLog.WriteEntry("ExcelUpload", "inside first for loop");
                                Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets[sheetNum];
                                Microsoft.Office.Interop.Excel.Range excelRange = sheet.get_Range("A13", "P89") as Microsoft.Office.Interop.Excel.Range;
                                object[,] valueArray = (object[,])excelRange.get_Value(
                                    Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueDefault);
                                var list = getSPList("{150301BF-D0BD-452C-90D7-2D6CD082A247}");
                                for (int L = 1; L <= excelRange.Rows.Count; L++)
                                {
                                    string stringVal = valueArray[L, 1] as string;
                                    if ((valueArray[L, 1] != null) && (!string.IsNullOrEmpty(stringVal)))
                                    {
                                        System.Diagnostics.EventLog.WriteEntry("ExcelUpload", "inside second for loop");
                                        SPListItemCollection listItems = list.Items;
                                        SPListItem item = listItems.Add();
                                        item["Product"] = valueArray[L, 1];
                                        item["App"] = valueArray[L, 2];
                                        web.AllowUnsafeUpdates = true;
                                        item.Update();
                                    }
                                }
                           
                            web.AllowUnsafeUpdates = false;
                            //Or Another Method with valueArray Object like "ProcessObjects(valueArray);"
                            _excelApp.Workbooks.Close();
                        }
                    }
                });
               
                //workBook.Close(false, excelname, null);
                //Marshal.ReleaseComObject(workBook);
            }
            catch (Exception e)
            {
                System.Diagnostics.EventLog.WriteEntry("ExcelUpload", e.Message.ToString());
            }
            finally
            {
                System.Diagnostics.EventLog.WriteEntry("ExcelUpload", "finally block");
            }


        }

No comments:

Post a Comment