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";
  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;
  5. clientContext.Load(files);
  6. clientContext.ExecuteQuery();
  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.
  25. }
  26. MyBook.Save();
  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();
  19. try {
  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";
  27. // If directory does not exist, create it.
  28. if (!Directory.Exists(tempLocation)) {
  29. Directory.CreateDirectory(tempLocation);
  30. }
  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. }
  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.
  65. }
  66. MyBook.Save();
  68. MyBook.Close();
  69. // Displays required result
  70. Console.WriteLine("List Title : " + list.Title);
  71. FileCollection files = clientContext.Web.GetFolderByServerRelativeUrl(folderPath).Files;
  73. clientContext.Load(files);
  74. clientContext.ExecuteQuery();
  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 :)