locked
Export data from DataTable to an excel RRS feed

  • Question

  • Hi,

    I have a requirement where I have to export a SharePoint List's data to an excel/csv. While I have achieved this using this C# code:

    using (SPSite Site = new SPSite("Site URL"))
                {
                    SPWeb web = Site.OpenWeb();
                    SPQuery Query = new SPQuery();
                    Query.Query = "";
    
                    SPList List = web.GetList("List URL");
                    SPListItemCollection listData = List.GetItems(Query);
    
                    DataTable ListData = List.GetItems(List.DefaultView).GetDataTable();
                    
    
                    ExportToExcel(ListData);
                }
            }
    
            private static void ExportToExcel(DataTable dataTable)
            {
    
                //open file
                StreamWriter wr = new StreamWriter(@"C:\\Name.csv");
                try
                {
                    for (int i = 0; i < dataTable.Columns.Count; i++)
                    {
                        wr.Write(dataTable.Columns[i].ToString().ToUpper() + "\t");
                    }
    
                    wr.WriteLine();
    
                    //write rows to excel file
                    for (int i = 0; i < (dataTable.Rows.Count); i++)
                    {
                        for (int j = 0; j < dataTable.Columns.Count; j++)
                        {
                            if (dataTable.Rows[i][j] != null)
                            {
                                wr.Write(Convert.ToString(dataTable.Rows[i][j]) + "\t");
                            }
                            else
                            {
                                wr.Write("\t");
                            }
                        }
                        //go to next line
                        wr.WriteLine();
                    }
                    //close file
                    wr.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

    There is one small problem I am facing in here. One of the columns is a Managed metadata type column. So the value for this column is exported to the excel as VALUE|{GUID}.

    How do I get rid of this. I handled this issue using powershell like this:

    $pname = $_["PName"] -as [Microsoft.SharePoint.Taxonomy.TaxonomyFieldValue]; 

    How do I tackle this in here?

    Thursday, February 13, 2014 8:58 AM

Answers

All replies