/******************Upload Status File******************/ public static void UploadFileToSP(ClientContext clientContext) { try { Console.WriteLine("---------------Uploading file to Share Point--------------"); var NewLocalUpdatedFile = ApplicationConfiguration.LocalFilePath + DiskFilePath; FileCreationInformation NewLocalUpdatedFileInfo = new FileCreationInformation(); NewLocalUpdatedFileInfo.Content = System.IO.File.ReadAllBytes(NewLocalUpdatedFile); NewLocalUpdatedFileInfo.Overwrite = true; NewLocalUpdatedFileInfo.Url = Path.Combine("UserDocuments/", Path.GetFileName(NewLocalUpdatedFile)); List ExcelFileLibrary = clientContext.Web.Lists.GetByTitle("UserDocuments"); ExcelFileLibrary.RootFolder.Files.Add(NewLocalUpdatedFileInfo); clientContext.ExecuteQuery(); } catch (Exception e) { ErrorWriteToLog.WriteToLogFile(e); throw; } }
static void Main(string[] args) { try { Console.WriteLine("Enter User Email : "); string userName = Console.ReadLine(); // "*****@*****.**"; Console.WriteLine("Enter your password."); //SecureString sec = new SecureString(); SecureString Password = GetPassword(); //Url with site name using (var ClientContext = new ClientContext(ApplicationConfiguration.ApplicationUrl)) { ClientContext.Credentials = new SharePointOnlineCredentials(userName, Password); try { GetExcelFileDetails(ClientContext); UploadFilesAndData(ClientContext, ExcelDataTable); UploadFileToSP(ClientContext); } catch (Exception e) { Console.WriteLine("Exception caught : " + e.Message); ErrorWriteToLog.WriteToLogFile(e); } } } catch (Exception exe) { Console.WriteLine("Exception caught : " + exe.Message); ErrorWriteToLog.WriteToLogFile(exe); } Console.ReadKey(); }
/******************Gets Value from each cell of excel file*****************************/ private static string GetCellValue(ClientContext clientContext, SpreadsheetDocument document, Cell cell) { string StrErrorMsg = string.Empty; string CellValue = string.Empty; try { if (cell != null) { SharedStringTablePart StringTablePart = document.WorkbookPart.SharedStringTablePart; if (cell.CellValue != null) { CellValue = cell.CellValue.InnerXml; if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString) { if (StringTablePart.SharedStringTable.ChildElements[Int32.Parse(CellValue)] != null) { return(StringTablePart.SharedStringTable.ChildElements[Int32.Parse(CellValue)].InnerText); } } else { return(CellValue); } } } return(string.Empty); } catch (Exception e) { StrErrorMsg = e.Message; ErrorWriteToLog.WriteToLogFile(e); throw; } }
//Get Excel file Details public static void GetExcelFileDetails(ClientContext ClientContext) { List Empoyeelist = ClientContext.Web.Lists.GetByTitle("UserDocuments"); CamlQuery CamlQuery1 = new CamlQuery(); CamlQuery1.ViewXml = "<View><RowLimit></RowLimit></View>"; ListItemCollection EmpCollection = Empoyeelist.GetItems(CamlQuery1); ClientContext.Load(EmpCollection); ClientContext.ExecuteQuery(); Microsoft.SharePoint.Client.File ExcelFile = EmpCollection[0].File; ClientContext.Load(ExcelFile); ClientContext.ExecuteQuery(); var FilePath1 = EmpCollection[0].File.ServerRelativeUrl; var FileInfo1 = Microsoft.SharePoint.Client.File.OpenBinaryDirect(ClientContext, FilePath1); if (ExcelFile != null) { // DiskFilePath = LocalFilePath + EmpCollection[0].File.Name; try { var fileName = Path.Combine(ApplicationConfiguration.LocalFilePath + DiskFilePath, (string)EmpCollection[0].File.Name); DiskFilePath = EmpCollection[0].File.Name; if (System.IO.File.Exists(fileName)) { System.IO.File.Delete(fileName); } /****************Creates File in the specified path*****************/ using (var FileStream1 = System.IO.File.Create(fileName)) { FileInfo1.Stream.CopyTo(FileStream1); FileInfo1.Stream.Close(); FileStream1.Dispose(); } } catch (Exception exc) { Console.WriteLine("Exception exc : " + exc.Message); ErrorWriteToLog.WriteToLogFile(exc); } } string StrErrorMsg = string.Empty; /*************************DataSet Different Approcach***************************************/ /*************************DataSet Different Approcach***************************************/ try { ExcelDataTable = new System.Data.DataTable("ExcelFileDataTable"); ClientResult <System.IO.Stream> Data = ExcelFile.OpenBinaryStream(); ClientContext.Load(ExcelFile); ClientContext.ExecuteQuery(); using (System.IO.MemoryStream mStream = new System.IO.MemoryStream()) { if (Data != null) { Data.Value.CopyTo(mStream); using (SpreadsheetDocument Document1 = SpreadsheetDocument.Open(mStream, false)) { IEnumerable <Sheet> Sheets1 = Document1.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>(); string RelationshipId = Sheets1.First().Id.Value; WorksheetPart WorksheetPart1 = (WorksheetPart)Document1.WorkbookPart.GetPartById(RelationshipId); Worksheet WorkSheet1 = WorksheetPart1.Worksheet; SheetData SheetData1 = WorkSheet1.GetFirstChild <SheetData>(); IEnumerable <Row> Rows = SheetData1.Descendants <Row>(); foreach (Cell Cell1 in Rows.ElementAt(0)) { string StrCellValue = GetCellValue(ClientContext, Document1, Cell1); ExcelDataTable.Columns.Add(StrCellValue); } foreach (Row RowLoop in Rows) { if (RowLoop != null) { DataRow DataRow1 = ExcelDataTable.NewRow(); for (int iterator = 0; iterator < RowLoop.Descendants <Cell>().Count(); iterator++) { DataRow1[iterator] = GetCellValue(ClientContext, Document1, RowLoop.Descendants <Cell>().ElementAt(iterator)); } ExcelDataTable.Rows.Add(DataRow1); } } ExcelDataTable.Rows.RemoveAt(0); } } } } catch (Exception e) { Console.WriteLine("Exception exx " + e); ErrorWriteToLog.WriteToLogFile(e); } }
/***********************Uploading Data and Files in Specific List Library*******************/ public static void UploadFilesAndData(ClientContext clientContext, System.Data.DataTable dataTable) { Application App1 = new Application(); //if (System.IO.File.Exists(@"D:\FileUploadData.xlsx")) //{ // Console.WriteLine("Exists file"); //} Workbook WorkBook1 = (Microsoft.Office.Interop.Excel.Workbook)(App1.Workbooks._Open(ApplicationConfiguration.LocalFilePath + DiskFilePath, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value)); //int NumberOfWorkbooks = App1.Workbooks.Count; Microsoft.Office.Interop.Excel.Worksheet Sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)WorkBook1.Worksheets[1]; //int NumberOfSheets = WorkBook1.Worksheets.Count; try { if (dataTable.Rows.Count > 0) { Console.WriteLine("-------------------Uploading file--------------------"); List ExcelFile = clientContext.Web.Lists.GetByTitle("FileUpload"); clientContext.Load(ExcelFile); clientContext.ExecuteQuery(); Console.WriteLine("List name " + ExcelFile.Title + " desc :" + ExcelFile.Description); /**************Get All Users of Site**************/ UserCollection SiteUsers = clientContext.Web.SiteUsers; clientContext.Load(SiteUsers); clientContext.ExecuteQuery(); for (int Count = 0; Count < dataTable.Rows.Count; Count++) { try { if (Count > -1) { string LocalFilePath = dataTable.Rows[Count]["FilePath"].ToString(); string StatusList = dataTable.Rows[Count]["Status"].ToString(); string CreatedByEmail = dataTable.Rows[Count]["Created By"].ToString(); string Department = dataTable.Rows[Count]["Dept"].ToString(); string UploadStatus = dataTable.Rows[Count]["Upload Status"].ToString(); string Reason = dataTable.Rows[Count]["Reason"].ToString(); long SizeOfFile = new System.IO.FileInfo(LocalFilePath.Replace(@"\\", @"\")).Length; /****************Get All Users for that site*************/ User CreatedUserObj = SiteUsers.GetByEmail(CreatedByEmail); clientContext.Load(CreatedUserObj); clientContext.ExecuteQuery(); /****************Get All Departments from Department List and filter the data*************/ List DepartmentList = clientContext.Web.Lists.GetByTitle("Department"); clientContext.Load(DepartmentList); clientContext.ExecuteQuery(); CamlQuery DepartmentQuery = new CamlQuery(); DepartmentQuery.ViewXml = "<View><Query><Where><Eq><FieldRef Name='Department'/><Value Type = 'Text'>" + Department.Trim() + "</Value></Eq></Where></Query><RowLimit></RowLimit></View> "; //var fs = new FileStream(filepath, FileMode.Open); try { ListItemCollection GetDepartmentByName = DepartmentList.GetItems(DepartmentQuery); clientContext.Load(GetDepartmentByName); clientContext.ExecuteQuery(); if (GetDepartmentByName != null && GetDepartmentByName.Count > 0) { if (SizeOfFile > 100 && SizeOfFile < 2097150) { FileCreationInformation FileToUpload = new FileCreationInformation(); FileToUpload.Content = System.IO.File.ReadAllBytes(LocalFilePath.Replace(@"\\", @"\")); FileToUpload.Overwrite = true; FileToUpload.Url = Path.Combine("FileUpload/", Path.GetFileName(LocalFilePath.Replace(@"\\", @"\"))); Microsoft.SharePoint.Client.File UploadFile = ExcelFile.RootFolder.Files.Add(FileToUpload); clientContext.Load(UploadFile); clientContext.ExecuteQuery(); ListItem UploadItem = UploadFile.ListItemAllFields; Field Choice = ExcelFile.Fields.GetByInternalNameOrTitle("Status"); clientContext.Load(Choice); clientContext.ExecuteQuery(); FieldChoice StatusFieldChoice = clientContext.CastTo <FieldChoice>(Choice); clientContext.Load(StatusFieldChoice); clientContext.ExecuteQuery(); string[] StatusArray = StatusList.Split(','); string StatusPutSelectedValue = string.Empty; for (int statusCount = 0; statusCount < StatusArray.Length; statusCount++) { if (StatusFieldChoice.Choices.Contains(StatusArray[statusCount])) { if (statusCount == StatusArray.Length - 1) { StatusPutSelectedValue += StatusArray[statusCount]; } else { StatusPutSelectedValue += StatusArray[statusCount] + ";"; } } } UploadItem["CreatedBy"] = CreatedUserObj.Title; UploadItem["SizeOfFile"] = SizeOfFile; UploadItem["FileType"] = Path.GetExtension(LocalFilePath.Replace(@"\\", @"\")); UploadItem["Status"] = StatusPutSelectedValue; UploadItem["Dept"] = GetDepartmentByName[0].Id.ToString();; UploadItem.Update(); clientContext.ExecuteQuery(); Sheet1.Cells[Count + 2, 5] = "Success"; Sheet1.Cells[Count + 2, 6] = "N/A"; } else { Console.WriteLine("File : " + Path.GetFileName(LocalFilePath.Replace(@"\\", @"\")) + " could not be uploaded since file size is not in range"); Sheet1.Cells[Count + 2, 5] = "Error"; Sheet1.Cells[Count + 2, 6] = "File Size Exceeds Specified Range"; /*************Create Custom Exception Object**********/ ApplicationCustomException FileSizeException = new ApplicationCustomException("File Size Exceeds Specified Range"); ErrorWriteToLog.WriteToLogFile(FileSizeException); } } else { Console.WriteLine("Department Not Found in Department List"); Sheet1.Cells[Count + 2, 5] = "Error"; Sheet1.Cells[Count + 2, 6] = "Department Not Found in Department List"; /*************Create Custom Exception Object**********/ ApplicationCustomException DepartmentNotFoundException = new ApplicationCustomException("File Size Exceeds Specified Range"); ErrorWriteToLog.WriteToLogFile(DepartmentNotFoundException); } } catch (Exception exe) { Console.WriteLine("Exception : " + exe.Message); ErrorWriteToLog.WriteToLogFile(exe); } } } catch (Exception ex) { Console.WriteLine("Exception : " + ex); Sheet1.Cells[Count + 2, 5] = "Error"; Sheet1.Cells[Count + 2, 6] = ex.Message; ErrorWriteToLog.WriteToLogFile(ex); } } } } catch (Exception ee) { Console.WriteLine("Exception :" + ee.Message); ErrorWriteToLog.WriteToLogFile(ee); } WorkBook1.Save(); WorkBook1.Close(true, ApplicationConfiguration.LocalFilePath + DiskFilePath, System.Reflection.Missing.Value); App1.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(App1); Marshal.ReleaseComObject(Sheet1); Marshal.ReleaseComObject(WorkBook1); Marshal.ReleaseComObject(App1); }