$host.Runspace.ThreadOptions = "ReuseThread"

function Get-ListData
{
param ($sCSOMPath, $sSiteUrl,$ListTitle,$sUserName,$sPassword,$FileName)
try
{
    Write-Host "Getting all Items from the list $($ListTitle)"

    #Adding the Client OM Assemblies
    Add-Type -Path "C:\Microsoft.SharePoint.Client.dll"
    Add-Type -Path "C:\Microsoft.SharePoint.Client.Runtime.dll"

    #SPO Client Object Model Context
    $spoCtx = New-Object Microsoft.SharePoint.Client.ClientContext($sSiteUrl)
    $spoCredentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($sUserName, $sPassword)
    #$spoCredentials = New-Object System.Net.NetworkCredential($sUserName, $sPassword)
    $spoCtx.Credentials = $spoCredentials

    $web = $spoCtx.Web
    $List = $web.Lists.GetByTitle($ListTitle);

    $caml="<View Scope='RecursiveAll'></View>"
    $cquery = New-Object Microsoft.SharePoint.Client.CamlQuery
    $cquery.ViewXml=$caml
    $listItems = $List.GetItems($cquery)

    $spoCtx.Load($List)
    $spoCtx.Load($listItems)
    $spoCtx.ExecuteQuery()

    $AllListItems = @()

    foreach ($listitem in $listItems)
    {
        $Title =""
        $CreatedBy =  "System Account"
        $ModifiedBy = "System Account"

        #you can get custom fields of list also
        If([string]::IsNullOrEmpty($listitem.FieldValues["Title"]))
        {
            $Title = "No Title"
        }
        else
        {
            $Title =$listitem.FieldValues["Title"]
        }

        $FieldCreatedBy = [Microsoft.SharePoint.Client.FieldUserValue]$listitem.FieldValues["Author"]
        $CreatedBy = $FieldCreatedBy.LookupValue
        $FieldModifiedBy = [Microsoft.SharePoint.Client.FieldUserValue]$listitem.FieldValues["Editor"]
        $ModifiedBy = $FieldModifiedBy.LookupValue

        $AllListItems += New-Object -TypeName PSObject -Property @{
                            ListTitle = $ListTitle
                            ItemID=$listitem.ID
                            ItemName=$Title
                            CreatedBy = $CreatedBy
                            ModifiedBy = $ModifiedBy
                            CreatedDate = $listitem.FieldValues["Created"]
                            ModifiedDate = $listitem.FieldValues["Modified"]

                            } | Select  ListTitle,ItemID,ItemName,CreatedBy,ModifiedBy,CreatedDate,ModifiedDate
    }
    $AllListItems| Export-CSV ($sCSOMPath+""+$FileName+".csv") -NoTypeInformation -Append #-Encoding UTF8

    $spoCtx.Dispose()
    Read-Host -Prompt "file created at $($sCSOMPath). Please presss any key to close this"


}
    catch [System.Exception]
    {
        Write-Host -ForegroundColor Red $_.Exception.ToString()
        Read-Host -Prompt "Operation failed..! Press any key to close this and re run the script"
    }
}


$FileName = "All_ListItem_Collection"
$sSiteUrl = "https://site collection url"
$sUserName = "userid@domain.com"
$sPassword =  Read-Host -Prompt "Enter SharePoint online site password" -AsSecureString
$ListTitle = "List Title"



$scriptpath = $MyInvocation.MyCommand.Path
$dir = Split-Path $scriptpath



Get-ListData -sCSOMPath $dir -sSiteUrl $sSiteUrl -ListTitle $ListTitle -sUserName $sUserName -sPassword $sPassword -FileName $FileName

public static string siteUrl = "https://contoso.sharepoint.com";
public static string username = "xyz@contoso.onmicrosoft.com";
public static string password = "*******";

public static string spListName = "List1";
public static string viewName = "All Items";

public static string excelName = "ListDataExcel1";
public static string exportLocation = "C:\Export\";

public 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();
        Console.ForegroundColor = ConsoleColor.Green;
        Console.WriteLine("List export to excel completed successfully.");
        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 + """);
                    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);

                    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 == "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(ex.Message);
                }
                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
            {
                // 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);

                // Workk sheet
                excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
                excelSheet.Name = worksheetName;


                //excelSheet.Cells[1, 1] = ReporType;
                //excelSheet.Cells[1, 2] = "Date : " + DateTime.Now.ToShortDateString();

                // 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)
            {
                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;
            }
        }