Post
Ask Question

Download Sharepoint Document Library Files to Local Machine and Export Metadata in Excel

Introduction

 
In this blog, we will learn about downloading all the files from a specific document library to our local machine and exporting all the metadata items in Excel.
 
Nuget Package Required:
  1. SharePointPnPCoreOnline  
  2. Microsoft.Office.Interop.Excel 
Required imports are:
  1. using System;  
  2. using Microsoft.SharePoint.Client;  
  3. using System.IO;  
  4. using File = Microsoft.SharePoint.Client.File;  
  5. using Excel = Microsoft.Office.Interop.Excel;  
Authenticating with Sharepoint:
  1. string siteUrl = "Your Site Collection Url";  
  2. string userName = "UserName of Account";  
  3. string password = "Password";  
  4. OfficeDevPnP.Core.AuthenticationManager authManager = new OfficeDevPnP.Core.AuthenticationManager();  
  5. using (var clientContext= authManager.GetSharePointOnlineAuthenticatedContextTenant(siteUrl, userName, password))  
 
The above code creates authentication with Sharepoint with a valid username and password. It returns a clientcontext object. With the clientcontext object, we will do our required operations in Sharepoint.
 
Creating an Excel file in a specified folder:
  1. var tempLocation = @"D:\files";  
  2.                 
  3. // If directory does not exist, create it.   
  4. if (!Directory.Exists(tempLocation))  
  5. {  
  6.     Directory.CreateDirectory(tempLocation);  
  7. }  
  8. var filename = @"D:\files\myexfile.xlsx";  
  9. FileInfo myfile = new FileInfo(filename);  
  10. if (!myfile.Exists)  
  11. {  
  12.     var wb = MyApp.Workbooks.Add();  
  13.     wb.SaveAs(@"D:\files\myexfile.xlsx");  
  14.     wb.Close();  
  15.     // FileStream fs = myfile.Create();  
  16. }  
The above code creates the blank excel file in the name of myexfile.aspx in the specified folder path 
 
Code for downloading files to the local hard drive:
  1. var folderPath = "/Project Documents";  
  2. var tempLocation = @"D:\files";  
  3. FileCollection files = clientContext.Web.GetFolderByServerRelativeUrl(folderPath).Files;  
  4.   
  5. clientContext.Load(files);  
  6. clientContext.ExecuteQuery();  
  7.   
  8. foreach (File file in files)  
  9. {  
  10.     FileInformation fileInfo = File.OpenBinaryDirect(clientContext, file.ServerRelativeUrl);  
  11.     clientContext.ExecuteQuery();  
  12.     // Console.WriteLine("File Name:: " + file.FieldValues["Title"]);  
  13.     var filePath = tempLocation + "\\" + file.Name;  
  14.     //var absoluteUrl = file.ListItemAllFields["FileRef"];  
  15.     using (var fileStream = new System.IO.FileStream(filePath, System.IO.FileMode.Create))  
  16.     {  
  17.         fileInfo.Stream.CopyTo(fileStream);  
  18.     }  
  19. }  
The above code downloads all files from the document library "Project Documents"  to our local machin with the path "D:\files"
 
Code for exporting metadata to Excel:
  1. string listName = "Project Documents";  
  2. // Retrieves list object using title    
  3. List list = clientContext.Site.RootWeb.GetListByTitle(listName);  
  4. if (list != null)  
  5. {  
  6.     CamlQuery query = CamlQuery.CreateAllItemsQuery(100);  
  7.     ListItemCollection items = list.GetItems(query);  
  8.     clientContext.Load(items);  
  9.     clientContext.ExecuteQuery();  
  10.     MyApp = new Excel.Application();  
  11.     MyApp.Visible = false;  
  12.     MyBook = MyApp.Workbooks.Open(@"D:\files\myexfile.xlsx");  
  13.     MySheet = (Excel.Worksheet)MyBook.Sheets[1]; // Explicit cast is not required here  
  14.     var lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;  
  15.     var i = 1;  
  16.     foreach (ListItem listItem in items)  
  17.     {  
  18.         MySheet.Cells[i, 1].Value = listItem.FieldValues["ID"];  
  19.         MySheet.Cells[i,2].Value = listItem.FieldValues["FileLeafRef"];  
  20.         MySheet.Cells[i, 3].Value = listItem.FieldValues["Status"];  
  21.         i++;  
  22.         Console.WriteLine(listItem.FieldValues["ID"]);  
  23.         // We have all the list item data. For example, Title.   
  24.   
  25.     }  
  26.     MyBook.Save();  
  27.                    
  28.     MyBook.Close();}  
The above code will exports  id, filename, status columns from the Sharepoint document library "Project Document "to Excel in the specified path @"D:\files\myexfile.xlsx"
 
Full Console Code:
  1. using System;  
  2. using Microsoft.SharePoint.Client;  
  3. using System.IO;  
  4. using File = Microsoft.SharePoint.Client.File;  
  5. using Excel = Microsoft.Office.Interop.Excel;  
  6. namespace PnpCsom {  
  7.  class Program {  
  8.   static void Main(string[] args) {  
  9.    string siteUrl = "";  
  10.    string userName = "";  
  11.    string password = "";  
  12.    // Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();    
  13.    Excel.Workbook MyBook = null;  
  14.    Excel.Application MyApp = null;  
  15.    Excel.Worksheet MySheet = null;  
  16.    // PnP component to set context      
  17.    OfficeDevPnP.Core.AuthenticationManager authManager = new OfficeDevPnP.Core.AuthenticationManager();  
  18.   
  19.    try {  
  20.   
  21.     // Get and set the client context      
  22.     // Connects to SharePoint online site using inputs provided      
  23.     using(var clientContext = authManager.GetSharePointOnlineAuthenticatedContextTenant(siteUrl, userName, password)) {  
  24.      var folderPath = "/Project Documents";  
  25.      var tempLocation = @ "D:\files";  
  26.   
  27.      // If directory does not exist, create it.     
  28.      if (!Directory.Exists(tempLocation)) {  
  29.       Directory.CreateDirectory(tempLocation);  
  30.      }  
  31.   
  32.   
  33.      // List Name input      
  34.      string listName = "Project Documents";  
  35.      // Retrieves list object using title      
  36.      List list = clientContext.Site.RootWeb.GetListByTitle(listName);  
  37.      if (list != null) {  
  38.       CamlQuery query = CamlQuery.CreateAllItemsQuery(100);  
  39.       ListItemCollection items = list.GetItems(query);  
  40.       clientContext.Load(items);  
  41.       clientContext.ExecuteQuery();  
  42.       MyApp = new Excel.Application();  
  43.       MyApp.Visible = false;  
  44.       var filename = @ "D:\files\myexfile.xlsx";  
  45.       FileInfo myfile = new FileInfo(filename);  
  46.       if (!myfile.Exists) {  
  47.        var wb = MyApp.Workbooks.Add();  
  48.        wb.SaveAs(@ "D:\files\myexfile.xlsx");  
  49.        wb.Close();  
  50.        // FileStream fs = myfile.Create();    
  51.       }  
  52.   
  53.       MyBook = MyApp.Workbooks.Open(@ "D:\files\myexfile.xlsx");  
  54.       MySheet = (Excel.Worksheet) MyBook.Sheets[1]; // Explicit cast is not required here    
  55.       var lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;  
  56.       var i = 1;  
  57.       foreach(ListItem listItem in items) {  
  58.        MySheet.Cells[i, 1].Value = listItem.FieldValues["ID"];  
  59.        MySheet.Cells[i, 2].Value = listItem.FieldValues["FileLeafRef"];  
  60.        MySheet.Cells[i, 3].Value = listItem.FieldValues["Status"];  
  61.        i++;  
  62.        Console.WriteLine(listItem.FieldValues["ID"]);  
  63.        // We have all the list item data. For example, Title.     
  64.   
  65.       }  
  66.       MyBook.Save();  
  67.   
  68.       MyBook.Close();  
  69.       // Displays required result      
  70.       Console.WriteLine("List Title : " + list.Title);  
  71.       FileCollection files = clientContext.Web.GetFolderByServerRelativeUrl(folderPath).Files;  
  72.   
  73.       clientContext.Load(files);  
  74.       clientContext.ExecuteQuery();  
  75.   
  76.       foreach(File file in files) {  
  77.        FileInformation fileInfo = File.OpenBinaryDirect(clientContext, file.ServerRelativeUrl);  
  78.        clientContext.ExecuteQuery();  
  79.        // Console.WriteLine("File Name:: " + file.FieldValues["Title"]);    
  80.        var filePath = tempLocation + "\\" + file.Name;  
  81.        //var absoluteUrl = file.ListItemAllFields["FileRef"];    
  82.        using(var fileStream = new System.IO.FileStream(filePath, System.IO.FileMode.Create)) {  
  83.         fileInfo.Stream.CopyTo(fileStream);  
  84.        }  
  85.       }  
  86.      } else {  
  87.       Console.WriteLine("List is not available on the site");  
  88.      }  
  89.      Console.ReadKey();  
  90.     }  
  91.    } catch (Exception ex) {  
  92.     Console.WriteLine("Error Message: " + ex.Message);  
  93.     Console.ReadKey();  
  94.    }  
  95.   }  
  96.  }  
  97. }

Conclusion

 
Hence, we learned about downloading all the files from a SharePoint document library using CSOM and also exporting metadata to Excel using CSOM. Hope this helps someone, Happy coding :)