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:

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