1

First off I am new to SharePoint Online. (or any form of SharePoint)

I have a Excel Document in the documents section of the SharePoint website that was shared with me. I am trying to access certain Columns (A and E) from that file I can use it in my MVC application, quite frankly I can't even seem to figure out how to navigate to the file let alone grab the information from it.

Updated (4/24/2018): I figured out how to navigate to the file location and save the file into file collection then into a file. Just need to read the data now, code now provided below.

Updated (4/25/2018): I ended up just using ClosedXML to pull and read the data by dumping the file onto the local desktop then deleting said file. I would appreciate an alternative solution that would be less of a security risk. (I tried using a stream, but canSeek option wasn't enabled.)

Things I have accomplished:

  • Using Microsoft.SharePoint.Client
  • Authentication
  • List of Documents/Folders on root folder
  • Navigated to the file
  • Saving file to local desktop
  • Reading file
  • Deleting local file

Any point in the right direction would be great.

        string siteURL = ConfigurationManager.AppSettings["siteURL"];
        string userName = ConfigurationManager.AppSettings["userName"];
        string password = ConfigurationManager.AppSettings["password"];
        #region ConnectTo O365  
        //Create the client context object and set the credentials  
        ClientContext clientContext = new ClientContext(siteURL);
        SecureString securePassword = new SecureString();
        foreach (char c in password.ToCharArray()) securePassword.AppendChar(c);
        clientContext.Credentials = new SharePointOnlineCredentials(userName, securePassword);

        var list = clientContext.Web.Lists.GetByTitle("Documents");
        var listItem = list.GetItemById(1);
        clientContext.Load(list);
        clientContext.Load(listItem, ii => ii.File);
        clientContext.ExecuteQuery();

        var fileRef = listItem.File.ServerRelativeUrl;
        var fileInfo = Microsoft.SharePoint.Client.File.OpenBinaryDirect(clientContext, fileRef);
        string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);

        var fileName = Path.Combine(path, (string)listItem.File.Name);
        using (var fileStream = System.IO.File.Create(fileName))
        {
            fileInfo.Stream.CopyTo(fileStream);
        }

        var workbook = new XLWorkbook(path + "\\" + (string)listItem.File.Name);
        var ws = workbook.Worksheet(1);

        List<SelectListItem> selectList = new List<SelectListItem>();
        selectList.Add(new SelectListItem { Value = "0", Text = "Other" });

        for (int num = startRow; num <= rowSize; num++)
        {
            selectList.Add(new SelectListItem { Value = ws.Row(num).Cell(ABC).GetValue<string>(), Text = ws.Row(num).Cell(customerName).GetValue<string>() });
        }
        if (System.IO.File.Exists(path + "\\" + (string)listItem.File.Name))
        {
            System.IO.File.Delete(path + "\\" + (string)listItem.File.Name);
        }
        #endregion
        CompanyNameList = selectList;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.