public static void ReadData(ClientContext context)
{
Excel.Application ExcelApp;
Excel.Workbook ExcelWorkBook;
Excel.Worksheet ExcelWorkSheet;
Excel.Range ExcelRange;
ExcelApp = new Excel.Application();
ExcelWorkBook = ExcelApp.Workbooks.Open(@"D:\harsha853\SharePointAssessment.xlsx");
ExcelWorkSheet = (Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
int MaximumRows = ExcelWorkSheet.UsedRange.Rows.Count;
int MaximumColumns = ExcelWorkSheet.UsedRange.Columns.Count;
ExcelRange = ExcelWorkSheet.UsedRange;
string Reason;
string UploadStatus;
for (int Row = 2; Row < MaximumRows; Row++)
{
string FilePath = (ExcelRange.Cells[Row, 1] as Excel.Range).Value2;
string Status = (ExcelRange.Cells[Row, 2] as Excel.Range).Value2;
string CreatedBy = (ExcelRange.Cells[Row, 3] as Excel.Range).Value2;
string Department = (ExcelRange.Cells[Row, 6] as Excel.Range).Value2;
AddFilesFromExcel(context, FilePath, CreatedBy, Status, Department, out Reason);
UploadStatus = String.IsNullOrEmpty(Reason) ? "File Uploaded Successfully" : "Failed to Upload File";
ExcelRange.Cells[Row, 4] = UploadStatus;
ExcelRange.Cells[Row, 5] = Reason;
}
ExcelWorkBook.Save();
ExcelWorkBook.Close();
ExcelApp.Quit();
}
static public void DeleteManager(int code)
{
int j = 0;
for (int i = 0; i < dataManagers.Rows.Count; i++)
{
if (Convert.ToInt32(dataManagers[0, i].Value) == code)
{
j = i;
dataManagers.Rows.RemoveAt(i);
break;
}
}
string filename = "C:\\Users\\Angela\\Documents\\visual studio 2015\\Projects\\turfirm\\turfirm\\bin\\Debug\\Managers.xlsx";
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook ExcelWorkBook;
Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet;
ExcelWorkBook = ExcelApp.Workbooks.Open(filename);
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
Microsoft.Office.Interop.Excel.Range rg = (Microsoft.Office.Interop.Excel.Range)ExcelWorkSheet.Rows[j + 1];
rg.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
ExcelWorkBook.Save();
ExcelApp.Quit();
}
static public void ShowNewTour(Tour newTour)
{
dataTours.Rows.Add(newTour._code.Value, newTour._name.Name, newTour._type.Name,
newTour._country.Name, newTour._visa.Value, newTour._hotel.Name,
newTour._transport.Name, newTour._food.Name, newTour._cost.Value, newTour._valuta.Name);
string filename = "C:\\Users\\Angela\\Documents\\visual studio 2015\\Projects\\turfirm\\turfirm\\bin\\Debug\\Tours.xlsx";
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook ExcelWorkBook;
Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet;
ExcelWorkBook = ExcelApp.Workbooks.Open(filename);
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelWorkSheet.Cells[dataTours.Rows.Count - 1, 1] = newTour._code.Value;
ExcelWorkSheet.Cells[dataTours.Rows.Count - 1, 2] = newTour._name.Name;
ExcelWorkSheet.Cells[dataTours.Rows.Count - 1, 3] = newTour._type.Name;
ExcelWorkSheet.Cells[dataTours.Rows.Count - 1, 4] = newTour._country.Name;
ExcelWorkSheet.Cells[dataTours.Rows.Count - 1, 5] = newTour._visa.Value;
ExcelWorkSheet.Cells[dataTours.Rows.Count - 1, 6] = newTour._hotel.Name;
ExcelWorkSheet.Cells[dataTours.Rows.Count - 1, 7] = newTour._transport.Name;
ExcelWorkSheet.Cells[dataTours.Rows.Count - 1, 8] = newTour._food.Name;
ExcelWorkSheet.Cells[dataTours.Rows.Count - 1, 9] = newTour._cost.Value;
ExcelWorkSheet.Cells[dataTours.Rows.Count - 1, 10] = newTour._valuta.Name;
ExcelWorkBook.Save();
ExcelApp.Quit();
}
static public void DeleteManager(int code)
{
int j = 0;
for (int i = 0; i < dataManagers.Rows.Count; i++)
{
if (Convert.ToInt32(dataManagers[0, i].Value) == code)
{
j = i;
dataManagers.Rows.RemoveAt(i);
break;
}
}
string filename = "E:\\Shop\\Данные\\Managers.xlsx";
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook ExcelWorkBook;
Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet;
ExcelWorkBook = ExcelApp.Workbooks.Open(filename);
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
Microsoft.Office.Interop.Excel.Range rg = (Microsoft.Office.Interop.Excel.Range)ExcelWorkSheet.Rows[j + 1];
rg.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
ExcelWorkBook.Save();
ExcelApp.Quit();
}
static public void ShowNewManager(Manager newManager)
{
dataManagers.Rows.Add(newManager.Code, newManager.Name, newManager.Tel, newManager._tpy.Type, newManager.NameUser);
string filename = "C:\\Users\\Angela\\Documents\\visual studio 2015\\Projects\\turfirm\\turfirm\\bin\\Debug\\Managers.xlsx";
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook ExcelWorkBook;
Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet;
ExcelWorkBook = ExcelApp.Workbooks.Open(filename);
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelWorkSheet.Cells[dataManagers.Rows.Count - 1, 1] = newManager.Code;
ExcelWorkSheet.Cells[dataManagers.Rows.Count - 1, 2] = newManager.Name;
ExcelWorkSheet.Cells[dataManagers.Rows.Count - 1, 3] = newManager.Tel;
ExcelWorkSheet.Cells[dataManagers.Rows.Count - 1, 4] = newManager._tpy.Type;
ExcelWorkSheet.Cells[dataManagers.Rows.Count - 1, 5] = newManager.NameUser;
ExcelWorkBook.Save();
ExcelApp.Quit();
}
static public void ShowNewManager(Manager newManager)
{
dataManagers.Rows.Add(newManager.Code, newManager.Name, newManager.Tel, newManager._tpy.Type, newManager.NameUser, newManager.hireDate);
string filename = "E:\\Shop\\Данные\\Managers.xlsx";
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook ExcelWorkBook;
Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet;
ExcelWorkBook = ExcelApp.Workbooks.Open(filename);
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelWorkSheet.Cells[dataManagers.Rows.Count - 1, 1] = newManager.Code;
ExcelWorkSheet.Cells[dataManagers.Rows.Count - 1, 2] = newManager.Name;
ExcelWorkSheet.Cells[dataManagers.Rows.Count - 1, 3] = newManager.Tel;
ExcelWorkSheet.Cells[dataManagers.Rows.Count - 1, 4] = newManager._tpy.Type;
ExcelWorkSheet.Cells[dataManagers.Rows.Count - 1, 5] = newManager.NameUser;
ExcelWorkSheet.Cells[dataManagers.Rows.Count - 1, 6] = newManager.hireDate;
ExcelWorkBook.Save();
ExcelApp.Quit();
}
static public void ShowNewClient(Client newClient)
{
dataClients.Rows.Add(newClient.Code, newClient.Name, newClient.Email, newClient.Tel, newClient.DateBd, newClient._tpy.Type);
string filename = "E:\\Shop\\Данные\\Clients.xlsx";
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook ExcelWorkBook;
Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet;
ExcelWorkBook = ExcelApp.Workbooks.Open(filename);
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 1] = newClient.Code;
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 2] = newClient.Name;
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 3] = newClient.Email;
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 4] = newClient.Tel;
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 5] = newClient.DateBd;
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 6] = newClient._tpy.Type;
ExcelWorkBook.Save();
ExcelApp.Quit();
}
static public void ShowNewOrder(Order ord)
{
dataOrders.Rows.Add(ord.client, ord.manager, ord.DateOrder, ord.Code, ord._goods._name.Name);
string filename = "E:\\Shop\\Данные\\Orders.xlsx";
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook ExcelWorkBook;
Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet;
ExcelWorkBook = ExcelApp.Workbooks.Open(filename);
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelWorkSheet.Cells[dataOrders.Rows.Count - 1, 1] = ord.client;
ExcelWorkSheet.Cells[dataOrders.Rows.Count - 1, 2] = ord.manager;
ExcelWorkSheet.Cells[dataOrders.Rows.Count - 1, 3] = ord.DateOrder;
ExcelWorkSheet.Cells[dataOrders.Rows.Count - 1, 4] = ord.Code;
ExcelWorkSheet.Cells[dataOrders.Rows.Count - 1, 5] = ord._goods._name.Name;
ExcelWorkBook.Save();
ExcelApp.Quit();
}
static public void ShowNewClient(Client newClient)
{
dataClients.Rows.Add(newClient.Code, newClient.Name, newClient.Email, newClient.Tel, newClient.DateBd, newClient.Passport, newClient._tpy.Type);
string filename = "C:\\Users\\Angela\\Documents\\visual studio 2015\\Projects\\turfirm\\turfirm\\bin\\Debug\\Clients.xlsx";
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook ExcelWorkBook;
Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet;
ExcelWorkBook = ExcelApp.Workbooks.Open(filename);
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 1] = newClient.Code;
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 2] = newClient.Name;
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 3] = newClient.Email;
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 4] = newClient.Tel;
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 5] = newClient.DateBd;
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 6] = newClient.Passport;
ExcelWorkSheet.Cells[dataClients.Rows.Count - 1, 7] = newClient._tpy.Type;
ExcelWorkBook.Save();
ExcelApp.Quit();
}
static public void ShowNewOrder(Order ord)
{
dataOrders.Rows.Add(ord.client.Name, ord.manager.Name, ord.DateOrder, ord.Code, ord._tour._name.Name);
string filename = "C:\\Users\\Angela\\Documents\\visual studio 2015\\Projects\\turfirm\\turfirm\\bin\\Debug\\Orders.xlsx";
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook ExcelWorkBook;
Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet;
ExcelWorkBook = ExcelApp.Workbooks.Open(filename);
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
ExcelWorkSheet.Cells[dataOrders.Rows.Count - 1, 1] = ord.client.Name;
ExcelWorkSheet.Cells[dataOrders.Rows.Count - 1, 2] = ord.manager.Name;
ExcelWorkSheet.Cells[dataOrders.Rows.Count - 1, 3] = ord.DateOrder;
ExcelWorkSheet.Cells[dataOrders.Rows.Count - 1, 4] = ord.Code;
ExcelWorkSheet.Cells[dataOrders.Rows.Count - 1, 5] = ord._tour._name.Name;
ExcelWorkBook.Save();
ExcelApp.Quit();
}
public void Save()
{
ExcelWorkBook.Save();
}
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;
}
}