Sharepoint 2010 and 2013 validating excel document before uploading into document librarry
-
Synopsis:
I created a sharepoint document library for client and one major requirement is to upload only specific formatted excel file to this document library. if the file does not meet the format sharepoint should prevent the client from uploading the file on to the server. if the values in a number fixed column does not exist in a back-end database sharepoint should prevent the upload for the client to correct before uploading
I'm trying to solve the problem using itemadding and itemadded event receiver and cannot find any way to read the file during itemadding event to validate the format and the content of the excel before uploading.
Also the clients need instant message of successful of failed upload since they tend to ignore any email informing them and requesting for error and re-upload.I'm using ItemAdded event to update the back-end database with the valid content of the excel.
Please any idea on how to proceed
Afetele
-
Synopsis:
I created a sharepoint document library for client and one major requirement is to upload only specific formatted excel file to this document library. if the file does not meet the format sharepoint should prevent the client from uploading the file on to the server. if the values in a number fixed column does not exist in a back-end database sharepoint should prevent the upload for the client to correct before uploading
I'm trying to solve the problem using itemadding and itemadded event receiver and cannot find any way to read the file during itemadding event to validate the format and the content of the excel before uploading.
Also the clients need instant message of successful of failed upload since they tend to ignore any email informing them and requesting for error and re-upload.I'm using ItemAdded event to update the back-end database with the valid content of the excel.
Please any idea on how to proceed
Afetele
Event Handler Checking data within Excel file This is quite a complex task and will require some time and effort to create. You would need a way for the event handler to read and inspect the excel file on the server, this would require excel to be installed on the server where your custom event handler and open the file, navigate it and do whatever checking you want on the data. However, as I said, this can be complex. Once the excel file has been validated or rejected, you could then cancel the document upload, via the ItemAdding event /Staus property (https://msdn.microsoft.com/EN-US/library/microsoft.sharepoint.speventpropertiesbase.status.aspx[^]). Some sites to get you started: * Faster MS Excel Reading using Office Interop Assemblies[^] * https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel(v=office.15).aspx[^] NOTES: * The Office Interop can often be a trap for memory leaks * Follow the 'double .' rule, If its more than 1 dot, it belongs in its over variable and its own disposing! i.e
var wb = Excel.WorkSheet.Workbook;
should be:
var WorkSheet = ExcelObj.WorkSheet;
var workbook = WorkShee.WorkBook;This allows for such things as:
workbook.Close();
workbook.Dipose();
WorkSheet.Close();
WorkSheet.Dipose();Which will prevent a bunch of memory leaks and ensure there are not excel.exe's hanging around after your process has finsihed. Memory clean up is important when working with the office interop! NOTE: Code samples as just that, samples and are not expected to work. 2. Instant Messaging This is possible depending on what instant messaging system you use. In the past I have written such code for OCS, LCS and Lync. In essence, you woul