How to export list to excel without Microsoft.Office.Interop.Excel.dll

Question

I have list of foo as the below. How to export it to excel without Microsoft.Office.Interop.Excel.dll

public class Foo
{
    public DateTime Column1 { get; set; }
    public string Column2 { get; set; }
    public decimal Column3 { get; set; }
    public bool Column4 { get; set; }
}

Answer 1

You can do this with ClosedXML library( https://closedxml.codeplex.com/ )

I just write a simple example to show you how you can name the file, the worksheet and select cells:

        var workbook = new XLWorkbook();
        workbook.AddWorksheet("sheetName");
        var ws = workbook.Worksheet("sheetName");

        int row = 1;
        foreach (Foo f in fooList)
        {
            string rowString = row.ToString();
            ws.Cell("A" + rowString).Value = f.Column1;
            ws.Cell("B" + rowString).Value = f.Column2;
            ws.Cell("C" + rowString).Value = f.Column3;
            ws.Cell("D" + rowString).Value = f.Column4;
            row++;
        }

        workbook.SaveAs("yourExcel.xlsx");

If you prefer you can create a System.Data.DataSet or a System.Data.DataTable with all data and then just add it as a workseet with workbook.AddWorksheet(yourDataset) or workbook.AddWorksheet(yourDataTable);

Answer 2

Super easy way to export your list to excel using c#

How to install ClosedXML with NuGet Packager Manager Console: PM> Get-Project [ProjectName] | Install-Package ClosedXML

        using (var conn = new DB.UpdatesEntities())
        {
            var stories = (from a in conn.Subscribers
                           orderby a.DT descending
                           select a).Take(100).ToList();

            var ShowHeader = true;
            PropertyInfo[] properties = stories.First().GetType().GetProperties();
            List<string> headerNames = properties.Select(prop => prop.Name).ToList();                
            var wb = new XLWorkbook();
            var ws = wb.Worksheets.Add("Subscribers");
            if (ShowHeader)
            {
                for (int i = 0; i < headerNames.Count; i++)                    
                    ws.Cell(1, i + 1).Value = headerNames[i];

                ws.Cell(2, 1).InsertData(stories);
            }
            else
            {
                ws.Cell(1, 1).InsertData(stories);
            }

            wb.SaveAs(@"C:\Testing\yourExcel.xlsx");
        }

Answer 3

For Desktop app

    public static void WriteListToExcel<T>(List<T> list, string fulllPath)
    {
        try
        {
            List<string> result = new List<string>();
            result.Add(String.Join(String.Empty, typeof(T).GetProperties().Select(i => String.Format("{0}\t", i.Name)))); // Headers
            result.AddRange(list.Select(i => String.Join("\t", i.GetType().GetProperties().Select(t => t.GetValue(i, null))))); // Lines
            File.WriteAllLines(fulllPath, result);
        }
        catch (Exception e)
        {
            // Error do what you want....
        }
    }

For Web app

    public static void WriteListToExcel<T>(HttpResponseBase Response, List<T> list, string fileName)
    {
        try
        {
            Response.Clear();
            Response.AddHeader("content-disposition", String.Format("attachment;filename={0}.xls", fileName));
            Response.Charset = String.Empty;
            Response.Cache.SetCacheability(HttpCacheability.NoCache);
            Response.ContentType = "application/vnd.ms-excel";

            List<string> result = new List<string>();
            result.Add(String.Format("{0}\n", String.Join(String.Empty, typeof(T).GetProperties().Select(i => String.Format("{0}\t", i.Name))))); // Headers
            result.AddRange(list.Select(i => String.Format("{0}\n",String.Join("\t", i.GetType().GetProperties().Select(t => t.GetValue(i, null)))))); // Lines 
            result.ForEach(i => Response.Write(i));

            Response.Flush();
            Response.End();
        }
        catch (Exception e)
        {
            // Error..
        }
    }