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