0

I have a custom list. I have filtered with single item, based on ID.

Now, I want to export that particular list item into Excel by CSOM coding.

Please note, I don't want to export in CSV file format.

2
  • Only export one item, not all items?
    – Jussi Palo
    Dec 31, 2019 at 8:34
  • yes. one item based on filtered id
    – Liz
    Dec 31, 2019 at 9:35

1 Answer 1

2

Instead of going with the coding approach, we can export the particular view items to excel with out of the box option.

Export the SharePoint list items shown in the current view to Excel to work with the data in a spreadsheet.

1.Select Export to Excel Lists enter image description here from the command bar of the SharePoint list.

  1. The classic SharePoint experience shows a ribbon above the list, not a command bar. Click the List tab on the ribbon, and then click Export to Excel.

enter image description here

  1. If needed, click OK > Open after the file download begins.

  2. If prompted and you trust the SharePoint site, click Enable in Excel's security page

5.In Excel's Import Data dialog box, select the How you want to view this data and Where do you want to put the data options.

enter image description here

For detailed steps please refer the below MSDN article:

Export to Excel from SharePoint

Coding approach 1 - directly export to excel:

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

Reference:Similar question is answered here:

Export SharePoint online List all data to excel Programmatically using csom code

Coding approach 2 - export to csv, then save the file as an excel:

static void Main(string[] args)
        {
            string csvFilePath = @"c:\temp\myCSV.csv";
            StringBuilder items = new StringBuilder();
            ClientContext clientContext = new ClientContext("http://sp/sites/dev/");
            List list = clientContext.Web.Lists.GetByTitle("CustomList");
            CamlQuery caml = new CamlQuery();
            ListItemCollection listItems = list.GetItems(caml);
            clientContext.Load(list);
            clientContext.Load(listItems);
            clientContext.ExecuteQuery();
            foreach (ListItem listItem in listItems)
            {
                items.Append(listItem.Id + ",");
                items.Append(listItem.FieldValues["Name"]);
                items.AppendLine();
            }
            System.IO.File.WriteAllText(csvFilePath, items.ToString());
        }

Reference URL:

Exporting sharepoint list items to excel by csom c#

2
  • Thanks for your code. But i am able to fetch the item , not able to update in excel. it is throwing cannot contact site specified url(https://). and not updating values in excel
    – Liz
    Dec 31, 2019 at 9:37
  • .excelworkBook = excel.Workbooks.Add(Type.Missing);Exception from HRESULT: 0x800A03EC' i was getting this exception. some one please help me. code is not running after this.. so excel is not opening.
    – Liz
    Jan 3, 2020 at 8:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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