4

I want to export SharePoint Online List data programmatically using CSOM code in console application.

Can anyone send me CSOM code for the same.

  • You can use SharePoint's OOTB functionality. It is in list's ribbon "Export to Excel" – Dikesh Gandhi Jul 14 '16 at 10:10
  • Client do want want to use SharePoint's OOTB functionality, he wants without SharePoint's OOTB functionality – user57089 Jul 14 '16 at 10:16
  • @user57089 he's asking how to do it programatically, not manually. There is scant little info on this on Google. – SvdSinner Dec 14 '16 at 20:51
1

Here is the CSOM colsole code to export SharePoint Online list to excel:

Define static parameters and Main method:

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();
    }
}

Methods to get data from SP Online and write to excel:

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;
            }
        }       

It will also download the attachments.

0

Use the below csom powershell to run and get all the list items you need to use the reference of below dlls properly in script Microsoft.SharePoint.Client.dll Microsoft.SharePoint.Client.Runtime.dll

  1. you copy below powershell code in a file and save as .ps1
  2. after saving right click and run with powershell
  3. it will export the data into a csv file where ps file exists.

here is the ps code

$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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.