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

In this blog, we will learn how to download all the files from a specific document library to our local machine and export all the metadata items 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:

SharePointPnPCoreOnline
Microsoft.Office.Interop.Excel

Required imports are:

using System;
using Microsoft.SharePoint.Client;
using System.IO;
using File = Microsoft.SharePoint.Client.File;
using Excel = Microsoft.Office.Interop.Excel;

Authenticating with Sharepoint:

string siteUrl = "Your Site Collection Url";
string userName = "UserName of Account";
string password = "Password";
OfficeDevPnP.Core.AuthenticationManager authManager = new OfficeDevPnP.Core.AuthenticationManager();
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:

var tempLocation = @"D:\files";

// If directory does not exist, create it.
if (!Directory.Exists(tempLocation))
{
    Directory.CreateDirectory(tempLocation);
}
var filename = @"D:\files\myexfile.xlsx";
FileInfo myfile = new FileInfo(filename);
if (!myfile.Exists)
{
    var wb = MyApp.Workbooks.Add();
    wb.SaveAs(@"D:\files\myexfile.xlsx");
    wb.Close();
    // FileStream fs = myfile.Create();
}

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:

var folderPath = "/Project Documents";
var tempLocation = @"D:\files";
FileCollection files = clientContext.Web.GetFolderByServerRelativeUrl(folderPath).Files;

clientContext.Load(files);
clientContext.ExecuteQuery();

foreach (File file in files)
{
    FileInformation fileInfo = File.OpenBinaryDirect(clientContext, file.ServerRelativeUrl);
    clientContext.ExecuteQuery();
    // Console.WriteLine("File Name:: " + file.FieldValues["Title"]);
    var filePath = tempLocation + "\\" + file.Name;
    //var absoluteUrl = file.ListItemAllFields["FileRef"];
    using (var fileStream = new System.IO.FileStream(filePath, System.IO.FileMode.Create))
    {
        fileInfo.Stream.CopyTo(fileStream);
    }
}

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:

string listName = "Project Documents";
// Retrieves list object using title
List list = clientContext.Site.RootWeb.GetListByTitle(listName);
if (list != null)
{
    CamlQuery query = CamlQuery.CreateAllItemsQuery(100);
    ListItemCollection items = list.GetItems(query);
    clientContext.Load(items);
    clientContext.ExecuteQuery();
    MyApp = new Excel.Application();
    MyApp.Visible = false;
    MyBook = MyApp.Workbooks.Open(@"D:\files\myexfile.xlsx");
    MySheet = (Excel.Worksheet)MyBook.Sheets[1]; // Explicit cast is not required here
    var lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
    var i = 1;
    foreach (ListItem listItem in items)
    {
        MySheet.Cells[i, 1].Value = listItem.FieldValues["ID"];
        MySheet.Cells[i,2].Value = listItem.FieldValues["FileLeafRef"];
        MySheet.Cells[i, 3].Value = listItem.FieldValues["Status"];
        i++;
        Console.WriteLine(listItem.FieldValues["ID"]);
        // We have all the list item data. For example, Title.

    }
    MyBook.Save();

    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:

using System;
using Microsoft.SharePoint.Client;
using System.IO;
using File = Microsoft.SharePoint.Client.File;
using Excel = Microsoft.Office.Interop.Excel;
namespace PnpCsom {
 class Program {
  static void Main(string[] args) {
   string siteUrl = "";
   string userName = "";
   string password = "";
   // Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
   Excel.Workbook MyBook = null;
   Excel.Application MyApp = null;
   Excel.Worksheet MySheet = null;
   // PnP component to set context
   OfficeDevPnP.Core.AuthenticationManager authManager = new OfficeDevPnP.Core.AuthenticationManager();

   try {

    // Get and set the client context
    // Connects to SharePoint online site using inputs provided
    using(var clientContext = authManager.GetSharePointOnlineAuthenticatedContextTenant(siteUrl, userName, password)) {
     var folderPath = "/Project Documents";
     var tempLocation = @ "D:\files";

     // If directory does not exist, create it.
     if (!Directory.Exists(tempLocation)) {
      Directory.CreateDirectory(tempLocation);
     }


     // List Name input
     string listName = "Project Documents";
     // Retrieves list object using title
     List list = clientContext.Site.RootWeb.GetListByTitle(listName);
     if (list != null) {
      CamlQuery query = CamlQuery.CreateAllItemsQuery(100);
      ListItemCollection items = list.GetItems(query);
      clientContext.Load(items);
      clientContext.ExecuteQuery();
      MyApp = new Excel.Application();
      MyApp.Visible = false;
      var filename = @ "D:\files\myexfile.xlsx";
      FileInfo myfile = new FileInfo(filename);
      if (!myfile.Exists) {
       var wb = MyApp.Workbooks.Add();
       wb.SaveAs(@ "D:\files\myexfile.xlsx");
       wb.Close();
       // FileStream fs = myfile.Create();
      }

      MyBook = MyApp.Workbooks.Open(@ "D:\files\myexfile.xlsx");
      MySheet = (Excel.Worksheet) MyBook.Sheets[1]; // Explicit cast is not required here
      var lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
      var i = 1;
      foreach(ListItem listItem in items) {
       MySheet.Cells[i, 1].Value = listItem.FieldValues["ID"];
       MySheet.Cells[i, 2].Value = listItem.FieldValues["FileLeafRef"];
       MySheet.Cells[i, 3].Value = listItem.FieldValues["Status"];
       i++;
       Console.WriteLine(listItem.FieldValues["ID"]);
       // We have all the list item data. For example, Title.

      }
      MyBook.Save();

      MyBook.Close();
      // Displays required result
      Console.WriteLine("List Title : " + list.Title);
      FileCollection files = clientContext.Web.GetFolderByServerRelativeUrl(folderPath).Files;

      clientContext.Load(files);
      clientContext.ExecuteQuery();

      foreach(File file in files) {
       FileInformation fileInfo = File.OpenBinaryDirect(clientContext, file.ServerRelativeUrl);
       clientContext.ExecuteQuery();
       // Console.WriteLine("File Name:: " + file.FieldValues["Title"]);
       var filePath = tempLocation + "\\" + file.Name;
       //var absoluteUrl = file.ListItemAllFields["FileRef"];
       using(var fileStream = new System.IO.FileStream(filePath, System.IO.FileMode.Create)) {
        fileInfo.Stream.CopyTo(fileStream);
       }
      }
     } else {
      Console.WriteLine("List is not available on the site");
     }
     Console.ReadKey();
    }
   } catch (Exception ex) {
    Console.WriteLine("Error Message: " + ex.Message);
    Console.ReadKey();
   }
  }
 }
}

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 :)