In SharePoint applications, Custom Lists are used to store business data and Document Libraries to store the documents. But for data manupulation and analysis, Microsoft Excel provides very rich features as compared to SharePoint Lists. That's why people still loves to work on Microsoft Excel Sheets.

SharePoint also provides an out of the box feature to export custom lists into excel sheets. You just need to open the list and click on "Actions->Export to Spreadsheet", which asks to save a query file. This query file does not contain list data, but if you open his query file, it will fetch the data from SharePoint list and displays in Microsoft Excel sheet.

In my last project, client had come up with a requirement to download the list data directly in the Ecxel sheet , not in the query file and then save to excel sheet. I failed to do it using out of the box "export to spreadsheet" functionality. So I have done it programmatically using C#. I have added a Button in the List and on click of that Button, called following function:

public void ListToExcelConvertor()
{
  using (SPSite site = new SPSite("SharePointSiteURL"))
  {
    using (SPWeb web = site.OpenWeb())
    {
      DirectoryInfo dir = new DirectoryInfo(@"C:\List Data");
      dir.Create(); 
      
      FileInfo file = new FileInfo(@"C:\List Data\ExcelFileName.xls");
      StreamWriter streamWriter = file.CreateText();
     
      StringWriter stringWriter = new StringWriter();
      HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);
     
      Table tblData = GetListTableControl("SharePointListURL", "SharePointListName");
      tblData.RenderControl(htmlTextWriter);
      streamWriter.Write(stringWriter.ToString()); 
     
      htmlTextWriter.Close();
      streamWriter.Close(); 
      stringWriter.Close();
    }
  }
}

private static Table GetListTableControl(string strListURL, string strListName)
{
  Table tblListView = new Table();
  tblListView.ID = "_tblListView";
  tblListView.BorderStyle = BorderStyle.Solid;
  tblListView.BorderWidth = Unit.Pixel(1);
  tblListView.BorderColor = Color.Silver; 

  using (SPSite site = new SPSite(strListURL.Trim()))
  {
    using (SPWeb web = site.OpenWeb())
    {
      SPList list = web.Lists[strListName.Trim()]; 

      SPView wpView = list.Views["All Items"];
      wpView.RowLimit = 2147483647;
     
      SPQuery query = new SPQuery(wpView);
      SPListItemCollection items = list.GetItems(query);

      if (items != null && items.Count > 0)
      {
        DataTable tbListViewData = items.GetDataTable();
        DataView dvListViewData = tbListViewData.DefaultView;
        if (dvListViewData != null && dvListViewData.Count > 0)
        {
          tblListView.Rows.Add(new TableRow());
          tblListView.Rows[0].BackColor = Color.Gainsboro;
          tblListView.Rows[0].Font.Bold = true;

          for (int i = 0; i < wpView.ViewFields.Count; i++)
          {
            tblListView.Rows[0].Cells.Add(new TableCell());
            tblListView.Rows[0].Cells[i].Text = list.Fields.GetFieldByInternalName(wpView.ViewFields[i].ToString()).Title;
          }

          for (int i = 0; i < dvListViewData.Count; i++)
          {
            tblListView.Rows.Add(new TableRow());

            for (int j = 0; j < wpView.ViewFields.Count; j++)
            {
              tblListView.Rows[i + 1].Cells.Add(new TableCell());
            
              if (tbListViewData.Columns.Contains(wpView.ViewFields[j].ToString()))
              {
                tblListView.Rows[i + 1].Cells[j].Text = dvListViewData[i][wpView.ViewFields[j].ToString()].ToString(); 
              }
            } 
          }
        }
      }
    }
  }
}