private static void UploadExcel(ClientContext clientContext)
{
try
{
// uploading the file which was Updated Succesfully
PathLocation pathLocation = new PathLocation();
FileCreationInformation fileCreation = new FileCreationInformation
{
Content = System.IO.File.ReadAllBytes(pathLocation.LocalExcelfile),
Overwrite = true,
Url = Path.Combine("ExcelUploadDocument/", Path.GetFileName(pathLocation.LocalExcelfile))
};
var list = clientContext.Web.Lists.GetByTitle("ExcelUploadDocument");
var uploadFile = list.RootFolder.Files.Add(fileCreation);
clientContext.Load(uploadFile);
clientContext.ExecuteQuery();
Console.WriteLine("Uploaded Successfully");
Console.ReadKey();
}
catch (Exception ex)
{
throw ex;
}
}
public static void Main(string[] args)
{
try
{
PathLocation pathLocation = new PathLocation();
Console.WriteLine("Enter the Password");
Credentials credentials = new Credentials();
using (ClientContext clientContext = new ClientContext(pathLocation.SiteUrl))
{
clientContext.Credentials = new SharePointOnlineCredentials(credentials.UserName, credentials.password);
Importexcel(clientContext); // method to download the excelfile
ReadExcel(clientContext); // method to read the excel file and upload the files to the Documentlibrary and also update the status and reason columns of the excel file which is downloaded
UploadExcel(clientContext); // method to upload the excel file which is updated
}
}
catch (Exception ex)
{
ErrorLogFile.Errorlog(ex);
Console.WriteLine("Error occured Check LogFile");
Console.Read();
}
}
private static void ReadExcel(ClientContext clientContext)
{
try
{
PathLocation pathLocation = new PathLocation();
List list = clientContext.Web.Lists.GetByTitle(pathLocation.DocumentLibrary);
clientContext.Load(list.RootFolder);
clientContext.ExecuteQuery();
string FileserverUrl = list.RootFolder.ServerRelativeUrl + "/" + pathLocation.ExcelFileName;
Microsoft.SharePoint.Client.File file = clientContext.Web.GetFileByServerRelativeUrl(FileserverUrl);
ClientResult <System.IO.Stream> ExcelData = file.OpenBinaryStream();
clientContext.Load(file);
clientContext.ExecuteQuery();
using (var package = new OfficeOpenXml.ExcelPackage())
{
using (System.IO.MemoryStream Memorystream = new System.IO.MemoryStream())
{
// to read the data of the online excel sheet
if (ExcelData != null)
{
ExcelData.Value.CopyTo(Memorystream);
package.Load(Memorystream);
var WorkSheet = package.Workbook.Worksheets.First();
DataTable table1 = new DataTable();
bool IsHeadingRowAvailable = true;
foreach (var firstRowCell in WorkSheet.Cells[1, 1, 1, WorkSheet.Dimension.End.Column])
{
table1.Columns.Add(IsHeadingRowAvailable ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
}
string FileUploadStatus;
string Reason = "";
//to open the local excel file which was downloaded, Using Excel Service
Excel.Application Excelapplication;
Excel.Workbook ExcelWorkBook;
Excel.Worksheet ExcelWorkSheet;
Excel.Range range;
Excelapplication = new Excel.Application();
var Excellocalpath = System.IO.Path.Combine(pathLocation.ExcelFilePath, pathLocation.ExcelFileName);
ExcelWorkBook = Excelapplication.Workbooks.Open(Excellocalpath);
ExcelWorkSheet = (Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
range = ExcelWorkSheet.UsedRange;
var startRow = IsHeadingRowAvailable ? 2 : 1;// if Row Head Is available Then Row Starts From 2 else 1
for (var RowNumber = startRow; RowNumber <= WorkSheet.Dimension.End.Row; RowNumber++)
{
// getting the rows info which starts from 2
var WorkSheetRow = WorkSheet.Cells[RowNumber, 1, RowNumber, WorkSheet.Dimension.End.Column];
// storing the data based on column number of each row
string filetoupload = WorkSheetRow[RowNumber, 1].Text; //FilePath of File to Be Uploaded
string status = WorkSheetRow[RowNumber, 2].Text; //To read The Status Of The File
string[] values = status.Split(','); //Getting Multiple Status info Storing Seperately By splitting with ,
string CreatedBy = WorkSheetRow[RowNumber, 3].Text; //Getting info of the person who created the File
string deptfilebelongs = WorkSheetRow[RowNumber, 4].Text; //File Belongs To Particular Department
int split = filetoupload.LastIndexOf('.'); //To Get The Type Of the File
string filename = split < 0 ? filetoupload : filetoupload.Substring(0, split);
string type = split < 0 ? "" : filetoupload.Substring(split + 1);
System.IO.FileInfo filesize = new System.IO.FileInfo(filetoupload);// Getting the Size of Each file
long size = filesize.Length;
try
{
if (size >= 1000 && size <= 20000)//uploading files based on the filesize(Bytes)
{
List documentlibrary = clientContext.Web.Lists.GetByTitle("UploadedDocument");
var filecreationinfo = new FileCreationInformation();
filecreationinfo.Content = System.IO.File.ReadAllBytes(filetoupload);
filecreationinfo.Overwrite = true;
filecreationinfo.Url = Path.Combine("UploadedDocument/", Path.GetFileName(filetoupload));
Microsoft.SharePoint.Client.File files = documentlibrary.RootFolder.Files.Add(filecreationinfo);
ListItem listItem = files.ListItemAllFields;
// updating the DocumentLibrary with Following Fields
listItem["Dept"] = deptfilebelongs;
listItem["FileType"] = type;
listItem["Status"] = values;
listItem.Update();
clientContext.Load(files);
clientContext.ExecuteQuery();
Console.WriteLine("FileUploaded");
Reason = "";
}
else
{
Console.WriteLine("FileSizeNotInRange");
Reason = "FileSizeNotInRange";
}
}
catch (Exception ex)
{
Reason = ex.Message;
}
finally
{
FileUploadStatus = String.IsNullOrEmpty(Reason) ? "Uploaded" : "Failed";
range.Cells[RowNumber, 5] = FileUploadStatus;
range.Cells[RowNumber, 6] = Reason;
}
}
//closing the Local Excel File Which Was Updated.
ExcelWorkBook.Save();
ExcelWorkBook.Close();
Excelapplication.Quit();
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
