1. Update the Program.config as below.
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
</startup>
<appSettings>
<add key="siteUrl" value="https://ABC.sharepoint.com/teams/SiteName" />
<add key="username" value="XYZ@Domain.com" />
<add key="password" value="********" />
<add key="spListName" value="testList" />
<add key="viewName" value="All Items" />
<add key="excelName" value="ExportExcel" />
<add key="exportLocation" value="D:\Export\" />
<!--exportLocation example: D:\Export\... Please use the same struture -->
</appSettings>
</configuration>
2. Copy the below code and paste in Program.cs file.
using Microsoft.SharePoint.Client;
using System;
using System.Configuration;
using System.Data;
using System.IO;
using System.Security;
namespace TestExport
{
class Program
{
public static string siteUrl = ConfigurationManager.AppSettings["siteUrl"];
public static string username = ConfigurationManager.AppSettings["username"];
public static string password = ConfigurationManager.AppSettings["password"];
public static string spListName = ConfigurationManager.AppSettings["spListName"];
public static string viewName = ConfigurationManager.AppSettings["viewName"];
public static string excelName = ConfigurationManager.AppSettings["excelName"];
public static string exportLocation = ConfigurationManager.AppSettings["exportLocation"];
public static bool isSuccess = true;
static void Main(string[] args)
{
try
{
DataTable table = new DataTable();
Program p = new Program();
table = p.GetDataTableFromListItemCollection();
#region Export to excel
p.WriteDataTableToExcel(table, spListName, exportLocation + excelName + ".xlsx", "Details");
Console.WriteLine();
Console.WriteLine();
if (isSuccess)
{
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("List data exported to excel completed successfully. Location: " + exportLocation);
}
else
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Alert: Please ensure that the details entered in the config file are correct.");
}
Console.Read();
#endregion
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
Console.Read();
}
}
private DataTable GetDataTableFromListItemCollection()
{
string strWhere = string.Empty;
string filePath = string.Empty;
DataTable dtGetReqForm = new DataTable();
using (var clientContext = new ClientContext(siteUrl))
{
try
{
SecureString passWord = new SecureString();
foreach (char c in password.ToCharArray()) passWord.AppendChar(c);
clientContext.Credentials = new SharePointOnlineCredentials(username, passWord);
Console.WriteLine("Connecting \"" + siteUrl + "\"");
Console.WriteLine("Loading...");
Web Oweb = clientContext.Web;
clientContext.Load(Oweb);
clientContext.ExecuteQuery();
List spList = clientContext.Web.Lists.GetByTitle(spListName);
clientContext.Load(spList);
clientContext.Load(spList.Views);
clientContext.ExecuteQuery();
Console.WriteLine("Getting List: " + spListName);
Console.WriteLine("Loading...");
if (spList != null && spList.ItemCount > 0)
{
View view = spList.Views.GetByTitle(viewName);
clientContext.Load(view);
clientContext.ExecuteQuery();
ViewFieldCollection viewFields = view.ViewFields;
clientContext.Load(viewFields);
clientContext.ExecuteQuery();
CamlQuery query = new CamlQuery();
query.ViewXml = "<View><Query>" + view.ViewQuery + "</Query></View>";
ListItemCollection listItems = spList.GetItems(query);
clientContext.Load(listItems);
clientContext.ExecuteQuery();
if (listItems != null && listItems.Count > 0)
{
foreach (var field in viewFields)
{
dtGetReqForm.Columns.Add(field);
}
foreach (var item in listItems)
{
DataRow dr = dtGetReqForm.NewRow();
#region download attachments
if (Convert.ToBoolean(item["Attachments"].ToString()))
{
Folder folder = Oweb.GetFolderByServerRelativeUrl(Oweb.Url + "/Lists/" + spListName + "/Attachments/" + item["ID"]);
clientContext.Load(folder);
clientContext.ExecuteQuery();
FileCollection attachments = folder.Files;
clientContext.Load(attachments);
clientContext.ExecuteQuery();
foreach (Microsoft.SharePoint.Client.File oFile in attachments)
{
ClientResult<Stream> data = oFile.OpenBinaryStream();
clientContext.ExecuteQuery();
filePath = exportLocation;
string subPath = item["ID"].ToString();
bool exists1 = System.IO.Directory.Exists(filePath + "\\" + subPath);
if (!exists1)
System.IO.Directory.CreateDirectory(filePath + "\\" + subPath);
Console.WriteLine("Downloading file: " + oFile.Name);
var fileName = Path.Combine(filePath + "\\" + subPath, oFile.Name);
using (FileStream fileStream = System.IO.File.OpenWrite(fileName))
{
if (data != null)
{
using (var memory = new MemoryStream())
{
byte[] buffer = new byte[1024 * 64];
int nread = 0;
while ((nread = data.Value.Read(buffer, 0, buffer.Length)) > 0)
{
memory.Write(buffer, 0, nread);
}
memory.Seek(0, SeekOrigin.Begin);
memory.CopyTo(fileStream);
}
}
}
}
}
#endregion
for (int i = 0; i < viewFields.Count; i++)
{
string key = viewFields[i];
string value = string.Empty;
//string type = item.FieldValues[i].GetType().ToString();
if (item[key] != null)
{
if (i == viewFields.Count - 1)
{
}
if (item.FieldValues[key].GetType().Name == "FieldLookupValue" || item.FieldValues[key].GetType().Name == "FieldUserValue")
{
value = ((Microsoft.SharePoint.Client.FieldLookupValue)(item[key])).LookupValue;
}
else if (item.FieldValues[key].GetType().Name == "FieldUserValue[]")
{
if (((Microsoft.SharePoint.Client.FieldUserValue[])(item.FieldValues[key]))[0].GetType().Name == "FieldLookupValue" || ((Microsoft.SharePoint.Client.FieldUserValue[])(item.FieldValues[key]))[0].GetType().Name == "FieldUserValue")
{
value = (((Microsoft.SharePoint.Client.FieldUserValue[])(item.FieldValues[key]))[0]).LookupValue;
}
}
else if (item.FieldValues[key].GetType().Name == "FieldUrlValue")
{
value = ((Microsoft.SharePoint.Client.FieldUrlValue)(item[key])).Description;
}
else
{
value = item[key].ToString();
}
}
dr[key] = value;
}
dtGetReqForm.Rows.Add(dr);
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
isSuccess = false;
}
finally
{
if (clientContext != null)
clientContext.Dispose();
}
}
return dtGetReqForm;
}
public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
try
{
// Delete the existing files
Array.ForEach(Directory.GetFiles(exportLocation),
delegate (string path) { System.IO.File.Delete(path); });
// Start Excel and get Application object.
excel = new Microsoft.Office.Interop.Excel.Application();
// for making Excel visible
excel.Visible = false;
excel.DisplayAlerts = false;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);
// Work sheet
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = worksheetName;
// loop through each row and add values to our sheet
int rowcount = 1;
int finalColumn = 1;
foreach (DataRow datarow in dataTable.Rows)
{
int exclColumn = 1;
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
if (dataTable.Columns[i - 1].ColumnName != "Attachments" && !dataTable.Columns[i - 1].ColumnName.StartsWith("_") && dataTable.Columns[i - 1].ColumnName != "MetaInfo" && dataTable.Columns[i - 1].ColumnName != "owshiddenversion" && dataTable.Columns[i - 1].ColumnName != "FileRef" && dataTable.Columns[i - 1].ColumnName != "UniqueId" && dataTable.Columns[i - 1].ColumnName != "FSObjType" && dataTable.Columns[i - 1].ColumnName != "ContentTypeId" && dataTable.Columns[i - 1].ColumnName != "File_x0020_Type" && dataTable.Columns[i - 1].ColumnName != "SMLastModifiedDate" && dataTable.Columns[i - 1].ColumnName != "SMTotalSize" && dataTable.Columns[i - 1].ColumnName != "ItemChildCount" && dataTable.Columns[i - 1].ColumnName != "FolderChildCount" && dataTable.Columns[i - 1].ColumnName != "ScopeId" && dataTable.Columns[i - 1].ColumnName != "GUID" && dataTable.Columns[i - 1].ColumnName != "WorkflowInstanceID" && dataTable.Columns[i - 1].ColumnName != "FileDirRef" && dataTable.Columns[i - 1].ColumnName != "SortBehavior" && dataTable.Columns[i - 1].ColumnName != "FileLeafRef" && dataTable.Columns[i - 1].ColumnName != "SyncClientId" && dataTable.Columns[i - 1].ColumnName != "ProgId" && dataTable.Columns[i - 1].ColumnName != "AppEditor" && dataTable.Columns[i - 1].ColumnName != "WorkflowVersion" && dataTable.Columns[i - 1].ColumnName != "InstanceID" && dataTable.Columns[i - 1].ColumnName != "Order" && dataTable.Columns[i - 1].ColumnName != "Restricted" && dataTable.Columns[i - 1].ColumnName != "OriginatorId" && dataTable.Columns[i - 1].ColumnName != "AppAuthor")
{
// on the first iteration we add the column headers
if (rowcount == 2)
{
excelSheet.Cells[1, exclColumn] = dataTable.Columns[i - 1].ColumnName;
excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
}
if (datarow[i - 1].ToString() != "")
excelSheet.Cells[rowcount, exclColumn] = datarow[i - 1].ToString();
else
excelSheet.Cells[rowcount, exclColumn] = null;
//for alternate rows
if (rowcount > 2)
{
if (i == dataTable.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, exclColumn]];
FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false);
}
}
}
exclColumn += 1;
finalColumn = exclColumn - 1;
}
}
}
// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, finalColumn]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[1, finalColumn]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
//now save the workbook and exit Excel
excelworkBook.SaveAs(saveAsLocation);
excelworkBook.Close();
excel.Quit();
return true;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
isSuccess = false;
return false;
}
finally
{
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
}
public void FormattingExcelCells(Microsoft.Office.Interop.Excel.Range range, string HTMLcolorCode, System.Drawing.Color fontColor, bool IsFontbool)
{
range.Interior.Color = System.Drawing.ColorTranslator.FromHtml(HTMLcolorCode);
range.Font.Color = System.Drawing.ColorTranslator.ToOle(fontColor);
if (IsFontbool == true)
{
range.Font.Bold = IsFontbool;
}
}
}
}
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
</startup>
<appSettings>
<add key="siteUrl" value="https://ABC.sharepoint.com/teams/SiteName" />
<add key="username" value="XYZ@Domain.com" />
<add key="password" value="********" />
<add key="spListName" value="testList" />
<add key="viewName" value="All Items" />
<add key="excelName" value="ExportExcel" />
<add key="exportLocation" value="D:\Export\" />
<!--exportLocation example: D:\Export\... Please use the same struture -->
</appSettings>
</configuration>
2. Copy the below code and paste in Program.cs file.
using Microsoft.SharePoint.Client;
using System;
using System.Configuration;
using System.Data;
using System.IO;
using System.Security;
namespace TestExport
{
class Program
{
public static string siteUrl = ConfigurationManager.AppSettings["siteUrl"];
public static string username = ConfigurationManager.AppSettings["username"];
public static string password = ConfigurationManager.AppSettings["password"];
public static string spListName = ConfigurationManager.AppSettings["spListName"];
public static string viewName = ConfigurationManager.AppSettings["viewName"];
public static string excelName = ConfigurationManager.AppSettings["excelName"];
public static string exportLocation = ConfigurationManager.AppSettings["exportLocation"];
public static bool isSuccess = true;
static void Main(string[] args)
{
try
{
DataTable table = new DataTable();
Program p = new Program();
table = p.GetDataTableFromListItemCollection();
#region Export to excel
p.WriteDataTableToExcel(table, spListName, exportLocation + excelName + ".xlsx", "Details");
Console.WriteLine();
Console.WriteLine();
if (isSuccess)
{
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("List data exported to excel completed successfully. Location: " + exportLocation);
}
else
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Alert: Please ensure that the details entered in the config file are correct.");
}
Console.Read();
#endregion
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
Console.Read();
}
}
private DataTable GetDataTableFromListItemCollection()
{
string strWhere = string.Empty;
string filePath = string.Empty;
DataTable dtGetReqForm = new DataTable();
using (var clientContext = new ClientContext(siteUrl))
{
try
{
SecureString passWord = new SecureString();
foreach (char c in password.ToCharArray()) passWord.AppendChar(c);
clientContext.Credentials = new SharePointOnlineCredentials(username, passWord);
Console.WriteLine("Connecting \"" + siteUrl + "\"");
Console.WriteLine("Loading...");
Web Oweb = clientContext.Web;
clientContext.Load(Oweb);
clientContext.ExecuteQuery();
List spList = clientContext.Web.Lists.GetByTitle(spListName);
clientContext.Load(spList);
clientContext.Load(spList.Views);
clientContext.ExecuteQuery();
Console.WriteLine("Getting List: " + spListName);
Console.WriteLine("Loading...");
if (spList != null && spList.ItemCount > 0)
{
View view = spList.Views.GetByTitle(viewName);
clientContext.Load(view);
clientContext.ExecuteQuery();
ViewFieldCollection viewFields = view.ViewFields;
clientContext.Load(viewFields);
clientContext.ExecuteQuery();
CamlQuery query = new CamlQuery();
query.ViewXml = "<View><Query>" + view.ViewQuery + "</Query></View>";
ListItemCollection listItems = spList.GetItems(query);
clientContext.Load(listItems);
clientContext.ExecuteQuery();
if (listItems != null && listItems.Count > 0)
{
foreach (var field in viewFields)
{
dtGetReqForm.Columns.Add(field);
}
foreach (var item in listItems)
{
DataRow dr = dtGetReqForm.NewRow();
#region download attachments
if (Convert.ToBoolean(item["Attachments"].ToString()))
{
Folder folder = Oweb.GetFolderByServerRelativeUrl(Oweb.Url + "/Lists/" + spListName + "/Attachments/" + item["ID"]);
clientContext.Load(folder);
clientContext.ExecuteQuery();
FileCollection attachments = folder.Files;
clientContext.Load(attachments);
clientContext.ExecuteQuery();
foreach (Microsoft.SharePoint.Client.File oFile in attachments)
{
ClientResult<Stream> data = oFile.OpenBinaryStream();
clientContext.ExecuteQuery();
filePath = exportLocation;
string subPath = item["ID"].ToString();
bool exists1 = System.IO.Directory.Exists(filePath + "\\" + subPath);
if (!exists1)
System.IO.Directory.CreateDirectory(filePath + "\\" + subPath);
Console.WriteLine("Downloading file: " + oFile.Name);
var fileName = Path.Combine(filePath + "\\" + subPath, oFile.Name);
using (FileStream fileStream = System.IO.File.OpenWrite(fileName))
{
if (data != null)
{
using (var memory = new MemoryStream())
{
byte[] buffer = new byte[1024 * 64];
int nread = 0;
while ((nread = data.Value.Read(buffer, 0, buffer.Length)) > 0)
{
memory.Write(buffer, 0, nread);
}
memory.Seek(0, SeekOrigin.Begin);
memory.CopyTo(fileStream);
}
}
}
}
}
#endregion
for (int i = 0; i < viewFields.Count; i++)
{
string key = viewFields[i];
string value = string.Empty;
//string type = item.FieldValues[i].GetType().ToString();
if (item[key] != null)
{
if (i == viewFields.Count - 1)
{
}
if (item.FieldValues[key].GetType().Name == "FieldLookupValue" || item.FieldValues[key].GetType().Name == "FieldUserValue")
{
value = ((Microsoft.SharePoint.Client.FieldLookupValue)(item[key])).LookupValue;
}
else if (item.FieldValues[key].GetType().Name == "FieldUserValue[]")
{
if (((Microsoft.SharePoint.Client.FieldUserValue[])(item.FieldValues[key]))[0].GetType().Name == "FieldLookupValue" || ((Microsoft.SharePoint.Client.FieldUserValue[])(item.FieldValues[key]))[0].GetType().Name == "FieldUserValue")
{
value = (((Microsoft.SharePoint.Client.FieldUserValue[])(item.FieldValues[key]))[0]).LookupValue;
}
}
else if (item.FieldValues[key].GetType().Name == "FieldUrlValue")
{
value = ((Microsoft.SharePoint.Client.FieldUrlValue)(item[key])).Description;
}
else
{
value = item[key].ToString();
}
}
dr[key] = value;
}
dtGetReqForm.Rows.Add(dr);
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
isSuccess = false;
}
finally
{
if (clientContext != null)
clientContext.Dispose();
}
}
return dtGetReqForm;
}
public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;
try
{
// Delete the existing files
Array.ForEach(Directory.GetFiles(exportLocation),
delegate (string path) { System.IO.File.Delete(path); });
// Start Excel and get Application object.
excel = new Microsoft.Office.Interop.Excel.Application();
// for making Excel visible
excel.Visible = false;
excel.DisplayAlerts = false;
// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);
// Work sheet
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = worksheetName;
// loop through each row and add values to our sheet
int rowcount = 1;
int finalColumn = 1;
foreach (DataRow datarow in dataTable.Rows)
{
int exclColumn = 1;
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
if (dataTable.Columns[i - 1].ColumnName != "Attachments" && !dataTable.Columns[i - 1].ColumnName.StartsWith("_") && dataTable.Columns[i - 1].ColumnName != "MetaInfo" && dataTable.Columns[i - 1].ColumnName != "owshiddenversion" && dataTable.Columns[i - 1].ColumnName != "FileRef" && dataTable.Columns[i - 1].ColumnName != "UniqueId" && dataTable.Columns[i - 1].ColumnName != "FSObjType" && dataTable.Columns[i - 1].ColumnName != "ContentTypeId" && dataTable.Columns[i - 1].ColumnName != "File_x0020_Type" && dataTable.Columns[i - 1].ColumnName != "SMLastModifiedDate" && dataTable.Columns[i - 1].ColumnName != "SMTotalSize" && dataTable.Columns[i - 1].ColumnName != "ItemChildCount" && dataTable.Columns[i - 1].ColumnName != "FolderChildCount" && dataTable.Columns[i - 1].ColumnName != "ScopeId" && dataTable.Columns[i - 1].ColumnName != "GUID" && dataTable.Columns[i - 1].ColumnName != "WorkflowInstanceID" && dataTable.Columns[i - 1].ColumnName != "FileDirRef" && dataTable.Columns[i - 1].ColumnName != "SortBehavior" && dataTable.Columns[i - 1].ColumnName != "FileLeafRef" && dataTable.Columns[i - 1].ColumnName != "SyncClientId" && dataTable.Columns[i - 1].ColumnName != "ProgId" && dataTable.Columns[i - 1].ColumnName != "AppEditor" && dataTable.Columns[i - 1].ColumnName != "WorkflowVersion" && dataTable.Columns[i - 1].ColumnName != "InstanceID" && dataTable.Columns[i - 1].ColumnName != "Order" && dataTable.Columns[i - 1].ColumnName != "Restricted" && dataTable.Columns[i - 1].ColumnName != "OriginatorId" && dataTable.Columns[i - 1].ColumnName != "AppAuthor")
{
// on the first iteration we add the column headers
if (rowcount == 2)
{
excelSheet.Cells[1, exclColumn] = dataTable.Columns[i - 1].ColumnName;
excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
}
if (datarow[i - 1].ToString() != "")
excelSheet.Cells[rowcount, exclColumn] = datarow[i - 1].ToString();
else
excelSheet.Cells[rowcount, exclColumn] = null;
//for alternate rows
if (rowcount > 2)
{
if (i == dataTable.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, exclColumn]];
FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false);
}
}
}
exclColumn += 1;
finalColumn = exclColumn - 1;
}
}
}
// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, finalColumn]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[1, finalColumn]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);
//now save the workbook and exit Excel
excelworkBook.SaveAs(saveAsLocation);
excelworkBook.Close();
excel.Quit();
return true;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
isSuccess = false;
return false;
}
finally
{
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}
}
public void FormattingExcelCells(Microsoft.Office.Interop.Excel.Range range, string HTMLcolorCode, System.Drawing.Color fontColor, bool IsFontbool)
{
range.Interior.Color = System.Drawing.ColorTranslator.FromHtml(HTMLcolorCode);
range.Font.Color = System.Drawing.ColorTranslator.ToOle(fontColor);
if (IsFontbool == true)
{
range.Font.Bold = IsFontbool;
}
}
}
}
----------------------------End-----------------------------------