Exporting the values in List to excel

Question

Hi I am having a list container which contains the list of values. I wish to export the list values directly to Excel. Is there any way to do it directly?

user_264351 · Answer

OK, here is a step-by-step guide if you want to use COM.

You have to have Excel installed.
Add a reference to your project to the excel interop dll. To do this on the .NET tab select Microsoft.Office.Interop.Excel. There could be multiple assemblies with this name. Select the appropriate for your Visual Studio AND Excel version.
Here is a code sample to create a new Workbook and fill a column with the items from your list.

using NsExcel = Microsoft.Office.Interop.Excel;

public void ListToExcel(List list)
{
    //start excel
    NsExcel.ApplicationClass excapp = new Microsoft.Office.Interop.Excel.ApplicationClass();

    //if you want to make excel visible           
    excapp.Visible = true;

    //create a blank workbook
    var workbook = excapp.Workbooks.Add(NsExcel.XlWBATemplate.xlWBATWorksheet);

    //or open one - this is no pleasant, but yue're probably interested in the first parameter
    string workbookPath = "C:	est.xls";
    var workbook = excapp.Workbooks.Open(workbookPath,
        0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
        true, false, 0, true, false, false);

    //Not done yet. You have to work on a specific sheet - note the cast
    //You may not have any sheets at all. Then you have to add one with NsExcel.Worksheet.Add()
    var sheet = (NsExcel.Worksheet)workbook.Sheets[1]; //indexing starts from 1

    //do something usefull: you select now an individual cell
    var range = sheet.get_Range("A1", "A1");
    range.Value2 = "test"; //Value2 is not a typo

    //now the list
    string cellName;
    int counter = 1;
    foreach (var item in list)
    {
        cellName = "A" + counter.ToString();
        var range = sheet.get_Range(cellName, cellName);
        range.Value2 = item.ToString();
        ++counter;
    }

    //you've probably got the point by now, so a detailed explanation about workbook.SaveAs and workbook.Close is not necessary
    //important: if you did not make excel visible terminating your application will terminate excel as well - I tested it
    //but if you did it - to be honest - I don't know how to close the main excel window - maybee somewhere around excapp.Windows or excapp.ActiveWindow
}

user_47773 · Answer

Using the CSV idea, if it's just a list of Strings. Assuming l is your list:

using System.IO;

using(StreamWriter sw = File.CreateText("list.csv"))
{
  for(int i = 0; i < l.Count; i++)
  {
    sw.WriteLine(l[i]);
  }
}

user_12864053 · Answer

Fast way - ArrayToExcel (github)

byte[] excel = myList.ToExcel();
File.WriteAllBytes("result.xlsx", excel);

user_11809286 · Answer

Exporting values List to Excel

Install in nuget next reference
Install-Package Syncfusion.XlsIO.Net.Core -Version 17.2.0.35
Install-Package ClosedXML -Version 0.94.2

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ClosedXML;
using ClosedXML.Excel;
using Syncfusion.XlsIO;

namespace ExporteExcel
{
    class Program
    {
        public class Auto
        {
            public string Marca { get; set; }

            public string Modelo { get; set; }
            public int Ano { get; set; }

            public string Color { get; set; }
            public int Peronsas { get; set; }
            public int Cilindros { get; set; }
        }
        static void Main(string[] args)
        {
            //Lista Estatica
            List Auto = new List()
            {
                new Auto{Marca = "Chevrolet", Modelo = "Sport", Ano = 2019, Color= "Azul", Cilindros=6, Peronsas= 4 },
                new Auto{Marca = "Chevrolet", Modelo = "Sport", Ano = 2018, Color= "Azul", Cilindros=6, Peronsas= 4 },
                new Auto{Marca = "Chevrolet", Modelo = "Sport", Ano = 2017, Color= "Azul", Cilindros=6, Peronsas= 4 }
            };
            //Inizializar Librerias
            var workbook = new XLWorkbook();
            workbook.AddWorksheet("sheetName");
            var ws = workbook.Worksheet("sheetName");
            //Recorrer el objecto
            int row = 1;
            foreach (var c in Auto)
            {
                //Escribrie en Excel en cada celda
                ws.Cell("A" + row.ToString()).Value = c.Marca;
                ws.Cell("B" + row.ToString()).Value = c.Modelo;
                ws.Cell("C" + row.ToString()).Value = c.Ano;
                ws.Cell("D" + row.ToString()).Value = c.Color;
                ws.Cell("E" + row.ToString()).Value = c.Cilindros;
                ws.Cell("F" + row.ToString()).Value = c.Peronsas;
                row++;

            }
            //Guardar Excel 
            //Ruta = Nombre_Proyecto\bin\Debug
            workbook.SaveAs("Coches.xlsx");
        }
    }
}

user_15369 · Answer

You could output them to a .csv file and open the file in excel. Is that direct enough?

user_244184 · Answer

The most straightforward way (in my opinion) would be to simply put together a CSV file. If you want to get into formatting and actually writing to a *.xlsx file, there are more complicated solutions (and APIs) to do that for you.

user_150144 · Answer

the one easy way to do it is to open Excel create sheet containing test data you want to export then say to excel save as xml open the xml see the xml format excel is expecting and generate it by head replacing the test data with export data

SpreadsheetML Markup Spec

@lan this is xml fo a simle execel file with one column value i genereted with office 2003 this format is for office 2003 and above




 
  Dancho
  Dancho
  2010-02-05T10:15:54Z
  cc
  11.9999
 
 
  13800
  24795
  480
  105
  False
  False
 
 
  
 
 
  
    Value1
   
    Value2
   
    Value3
   
    Value4
   
    Value5
   
    Value6
   
   
   
   
   
   
   
  
  
   
   
    
     3
     5
    
   
   False
   False
  
 
 
  
   False
   False
  
 
 
  
   False
   False

user_21061 · Answer

Depending on the environment you're wanting to do this in, it is possible by using the Excel Interop. It's quite a mess dealing with COM however and ensuring you clear up resources else Excel instances stay hanging around on your machine.

Checkout this MSDN Example if you want to learn more.

Depending on your format you could produce CSV or SpreadsheetML yourself, thats not too hard. Other alternatives are to use 3rd party libraries to do it. Obviously they cost money though.

user_1838462 · Answer

I know, I am late to this party, however I think it could be helpful for others.

Already posted answers are for csv and other one is by Interop dll where you need to install excel over the server, every approach has its own pros and cons. Here is an option which will give you

Perfect excel output [not csv]
With perfect excel and your data type match
Without excel installation
Pass list and get Excel output :)

you can achieve this by using NPOI DLL, available for both .net as well as for .net core

Steps :

Import NPOI DLL
Add Section 1 and 2 code provided below
Good to go

Section 1

This code performs below task :

Creating New Excel object - _workbook = new XSSFWorkbook();
Creating New Excel Sheet object - _sheet =_workbook.CreateSheet(_sheetName);
Invokes WriteData() - explained later Finally, creating and
returning MemoryStream object

=============================================================================

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;

namespace GenericExcelExport.ExcelExport
{
    public interface IAbstractDataExport
    {
        HttpResponseMessage Export(List exportData, string fileName, string sheetName);
    }

    public abstract class AbstractDataExport : IAbstractDataExport
    {
        protected string _sheetName;
        protected string _fileName;
        protected List _headers;
        protected List _type;
        protected IWorkbook _workbook;
        protected ISheet _sheet;
        private const string DefaultSheetName = "Sheet1";

        public HttpResponseMessage Export
              (List exportData, string fileName, string sheetName = DefaultSheetName)
        {
            _fileName = fileName;
            _sheetName = sheetName;

            _workbook = new XSSFWorkbook(); //Creating New Excel object
            _sheet = _workbook.CreateSheet(_sheetName); //Creating New Excel Sheet object

            var headerStyle = _workbook.CreateCellStyle(); //Formatting
            var headerFont = _workbook.CreateFont();
            headerFont.IsBold = true;
            headerStyle.SetFont(headerFont);

            WriteData(exportData); //your list object to NPOI excel conversion happens here

            //Header
            var header = _sheet.CreateRow(0);
            for (var i = 0; i < _headers.Count; i++)
            {
                var cell = header.CreateCell(i);
                cell.SetCellValue(_headers[i]);
                cell.CellStyle = headerStyle;
            }

            for (var i = 0; i < _headers.Count; i++)
            {
                _sheet.AutoSizeColumn(i);
            }

            using (var memoryStream = new MemoryStream()) //creating memoryStream
            {
                _workbook.Write(memoryStream);
                var response = new HttpResponseMessage(HttpStatusCode.OK)
                {
                    Content = new ByteArrayContent(memoryStream.ToArray())
                };

                response.Content.Headers.ContentType = new MediaTypeHeaderValue
                       ("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                response.Content.Headers.ContentDisposition = 
                       new ContentDispositionHeaderValue("attachment")
                {
                    FileName = $"{_fileName}_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"
                };

                return response;
            }
        }

        //Generic Definition to handle all types of List
        public abstract void WriteData(List exportData);
    }
}

=============================================================================

Section 2

In section 2, we will be performing below steps :

Converts List to DataTable Reflection to read property name, your
Column header will be coming from here
Loop through DataTable to Create excel Rows

=============================================================================

using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Text.RegularExpressions;

namespace GenericExcelExport.ExcelExport
{
    public class AbstractDataExportBridge : AbstractDataExport
    {
        public AbstractDataExportBridge()
        {
            _headers = new List();
            _type = new List();
        }

        public override void WriteData(List exportData)
        {
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));

            DataTable table = new DataTable();

            foreach (PropertyDescriptor prop in properties)
            {
                var type = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
                _type.Add(type.Name);
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? 
                                  prop.PropertyType);
                string name = Regex.Replace(prop.Name, "([A-Z])", " $1").Trim(); //space separated 
                                                                           //name by caps for header
                _headers.Add(name);
            }

            foreach (T item in exportData)
            {
                DataRow row = table.NewRow();
                foreach (PropertyDescriptor prop in properties)
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                table.Rows.Add(row);
            }

            IRow sheetRow = null;

            for (int i = 0; i < table.Rows.Count; i++)
            {
                sheetRow = _sheet.CreateRow(i + 1);
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    ICell Row1 = sheetRow.CreateCell(j);

                    string type = _type[j].ToLower();
                    var currentCellValue = table.Rows[i][j];

                    if (currentCellValue != null && 
                        !string.IsNullOrEmpty(Convert.ToString(currentCellValue)))
                    {
                        if (type == "string")
                        {
                            Row1.SetCellValue(Convert.ToString(currentCellValue));
                        }
                        else if (type == "int32")
                        {
                            Row1.SetCellValue(Convert.ToInt32(currentCellValue));
                        }
                        else if (type == "double")
                        {
                            Row1.SetCellValue(Convert.ToDouble(currentCellValue));
                        }
                    }
                    else
                    {
                        Row1.SetCellValue(string.Empty);
                    }
                }
            }
        }
    }
}

=============================================================================

Now you just need to call WriteData() function by passing your list, and it will provide you your excel.

I have tested it in WEB API and WEB API Core, works like a charm.

user_9950751 · Answer

Pass List to "Write" Method, which will convert the list to buffer and return buffer, a file will be downloaded

byte[] buffer = Write(ListData, true, "AttendenceSummary"); return File(buffer, "application/excel", reportTitle + ".xlsx");

      public static byte[] Write(IEnumerable list, bool xlsxExtension, string sheetName = "ExportData")
        {
            if (list == null)
            {
                throw new ArgumentNullException("list");
            }

            XSSFWorkbook hssfworkbook = new XSSFWorkbook();
            int Rowspersheet = 15000;
            int TotalRows = list.Count();
            int TotalSheets = TotalRows / Rowspersheet;

            for (int i = 0; i <= TotalSheets; i++)
            {
                ISheet sheet1 = hssfworkbook.CreateSheet(sheetName + "_" + i);
                IRow row = sheet1.CreateRow(0);
                int index = 0;
                foreach (PropertyInfo property in typeof(T).GetProperties())
                {
                    ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
                    IFont cellFont = hssfworkbook.CreateFont();

                    cellFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
                    cellStyle.SetFont(cellFont);

                    ICell cell = row.CreateCell(index++);
                    cell.CellStyle = cellStyle;
                    cell.SetCellValue(property.Name);
                }

                int rowIndex = 1;
                // int rowIndex2 = 1;

                foreach (T obj in list.Skip(Rowspersheet * i).Take(Rowspersheet))
                {

                    row = sheet1.CreateRow(rowIndex++);
                    index = 0;

                    foreach (PropertyInfo property in typeof(T).GetProperties())
                    {
                        ICell cell = row.CreateCell(index++);
                        cell.SetCellValue(Convert.ToString(property.GetValue(obj)));
                    }

                }
            }

            MemoryStream file = new MemoryStream();
            hssfworkbook.Write(file);
            return file.ToArray();

        }

user_1460243 · Answer

Three useful related code snippets:

A C# Extension method for ListToExcel with EPPlus:

public static void ListToExcel(this IList list, string filename, bool isRtl)
    {
        var response = HttpContext.Current.Response;

        var path = HttpUtility.UrlEncode(filename + ".xlsx", System.Text.Encoding.UTF8);

        using (ExcelPackage pck = new ExcelPackage())
        {
            ExcelWorksheet worksheet = pck.Workbook.Worksheets.Add(filename);
            worksheet.Cells["A1"].LoadFromCollection(list, true);

            //--------------------------------------------Create Rtl

            if (isRtl)
                worksheet.View.RightToLeft = true;

            //--------------------------------------------Create AutoFilter

            worksheet.Cells[worksheet.Dimension.Address].AutoFilter = true;

            //--------------------------------------------Create Format as table
            //create a range for the table
            ExcelRange range = worksheet.Cells[1, 1, worksheet.Dimension.End.Row, worksheet.Dimension.End.Column];
            //add a table to the range
            var tab = worksheet.Tables.Add(range, "Table1");
            //format the table
            tab.TableStyle = TableStyles.Medium2;

            //--------------------------------------------

            var ms = new System.IO.MemoryStream();
            pck.SaveAs(ms);
            ms.WriteTo(response.OutputStream);

            response.Clear();
            //response.ContentType = "application/vnd.ms-excel"; //xls
            response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; //xlsx                
            response.AddHeader("content-disposition", String.Format("attachment;filename={0}", Path.GetFileName(path)));
            response.BinaryWrite(pck.GetAsByteArray());
            response.Flush();
            response.End();
        }

Note: Install EPPlus with "Install-Package EPPlus"

A C# Extension method for ListToExcel with Microsoft.Office.Interop.Excel:

 public static void ListToExcel(this IList list, string filename)
    {
        Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
        Microsoft.Office.Interop.Excel._Worksheet worksheet = null;

        app.Visible = false;
        worksheet = workbook.Sheets["Sheet1"];

        worksheet = workbook.ActiveSheet;
        worksheet.Name = filename;


        PropertyInfo[] properties = typeof(T).GetProperties();

        for (int i = 0; i < properties.Length; i++)
        {
            worksheet.Cells[1, i + 1] = properties[i].Name;
            worksheet.Cells[1, i + 1].AutoFilter();
        }

        for (int i = 0; i < list.Count; i++)
        {
            var item = list[i];

            for (int j = 0; j < properties.Length; j++)
            {
                var prop = properties[j];
                worksheet.Cells[i + 2, j + 1] = prop.GetValue(item);
            }
        }

        var path = System.Web.HttpContext.Current.Server.MapPath("~/" + filename + ".xlsx");

        if (File.Exists(path))
            File.Delete(path);

        worksheet.SaveAs(path);
        workbook.Close();
        app.Quit();

        var response = HttpContext.Current.Response;

        using (FileStream fs = File.OpenRead(path))
        {
            int length = (int)fs.Length;
            byte[] buffer;

            using (BinaryReader br = new BinaryReader(fs))
            {
                buffer = br.ReadBytes(length);
            }

            response.Clear();
            response.Buffer = true;
            //response.ContentType = "application/vnd.ms-excel"; //xls
            response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; //xlsx                
            response.AddHeader("content-disposition", String.Format("attachment;filename={0}", Path.GetFileName(path)));
            response.BinaryWrite(buffer);
            response.Flush();
            response.End();
        }
    }

Note: if you use Microsoft.Office.Interop.Excel must be install Office in client.

A C# Extension method for ListToCsv:

public static void ListToCsv(this IList list, string filename)
    {
        string StringHeaders = "";
        string StringRows = "";

        PropertyInfo[] properties = typeof(T).GetProperties();
        for (int i = 0; i < properties.Length - 1; i++)
        {
            StringHeaders += properties[i].Name + ",";
        }
        var lastPropName = properties[properties.Length - 1].Name;
        StringHeaders += lastPropName + Environment.NewLine;


        foreach (var item in list)
        {
            for (int i = 0; i < properties.Length - 1; i++)
            {
                var prop = properties[i];
                StringRows += prop.GetValue(item) + ",";
            }
            var lastPropInfo = properties[properties.Length - 1];
            StringRows += lastPropInfo.GetValue(item) + Environment.NewLine;
        }

        var response = HttpContext.Current.Response;
        response.Clear();
        response.AddHeader("content-disposition", "attachment; filename=" + filename + ".csv");
        response.AddHeader("content-type", "text/csv");

        using (StreamWriter writer = new StreamWriter(response.OutputStream, Encoding.UTF8))
        {
            writer.WriteLine(StringHeaders + StringRows);
        }

        response.Flush();
        response.End();
    }

Exporting the values in List to excel

14 Answers

Section 1

Section 2