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?

Familist

I wish to export it to excel. Can any one of you give me a sample code? – Familist

I gave you a link to an MSDN Example within my post, that provides sample code on how to create and write data to an Excel file through the Interop. – Lubber

Answer 1

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

1. You have to have Excel installed.
2. 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.
3. 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<string> 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:\test.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
}

Irradiation

Just something to note: Usually you won't want excel to be visible until the entire spreadsheet is ready. In that case, you should move excapp.Visible = true; to after you are done setting up the sheet. – Solipsism

Just for someone like me having issue with ApplicationClass. Here is the answer to your problem ( set "Embed Interop Types" to "False" for Interop.Excel Reference): https://mcmap.net/q/27133/-interop-type-cannot-be-embedded – Torbart

Answer 2

Using ClosedXML edit library( there is no need to install MS Excel

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 (object item in itemList)
    {
        ws.Cell("A" + row.ToString()).Value = item.ToString();
        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);

Antons

The most simple and elegant solution I have ever seen regarding Excel-writing, cheers! – Outline

Getting "This site can’t be reached", Can I get a none broken link please? – Bandler

old link can't be reach today, I think this can be helpful: github.com/modulexcite/ClosedXML – Antons

Is it the same here: github.com/ClosedXML/ClosedXML ? – Cirro

Answer 3

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

Hypanthium

custom object will write the class name – Odo

I just mention that csv is not excel file. If you need xls file use something else that Matthew answer ;) – Niigata

Answer 4

Fast way - ArrayToExcel (github)

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

Pardoes

Unfortunately there is no updates from one year. So it seems it has few users and is not maintenanced well – Niigata

I found it in March 2022 and there was a recent update. But it doesn't matter how old it is, if it works - and it worked for me beautifully . I'm using it from Blazor WASM in the browser since EPPlus went commercial with version 5. – Nahuatl

I RECOMMEND this 1000%! Best one I've used so far! – Astolat

Does anyone have one that works well for large excel files? I have 400k lines and it uses up nearly 9gigs memory trying to perform the conversion – Monophthong

Answer 5

The simplest way using ClosedXml.

Imports ClosedXML.Excel

var dataList = new List<string>() { "a", "b", "c" };
var workbook = new XLWorkbook();     //creates the workbook
var wsDetailedData = workbook.AddWorksheet("data"); //creates the worksheet with sheetname 'data'
wsDetailedData.Cell(1, 1).InsertTable(dataList); //inserts the data to cell A1 including default column name
workbook.SaveAs(@"C:\data.xlsx"); //saves the workbook

For more info, you can also check wiki of ClosedXml. https://github.com/closedxml/closedxml/wiki

Feune

Simple and elegant! Works like a charm. – Ama

Answer 6

Exporting values List to Excel

1. Install in nuget next reference
2. Install-Package Syncfusion.XlsIO.Net.Core -Version 17.2.0.35
3. 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> Auto = new List<Program.Auto>()
            {
                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");
        }
    }
}

Arthropod

This works great but how can you add Column headers? – Sextant

Answer 7

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

Brahe

Answer 8

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.

Dartmouth

Answer 9

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

1. Perfect excel output [not csv]
2. With perfect excel and your data type match
3. Without excel installation
4. 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 :

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

Section 1

This code performs below task :

1. Creating New Excel object - _workbook = new XSSFWorkbook();
2. Creating New Excel Sheet object - _sheet =_workbook.CreateSheet(_sheetName);
3. Invokes WriteData() - explained later Finally, creating and
4. 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 :

1. Converts List to DataTable Reflection to read property name, your
2. Column header will be coming from here
3. 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<string>();
            _type = new List<string>();
        }

        public override void WriteData<T>(List<T> 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.

Sciolism

what is the performance of this like? Just browsing the code I would guess it's very slow? – Tufts

We are using this in 3 applications and performance is quite satisfactory, But yeah it does have some areas of improvement – Sciolism

Answer 10

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.

Lubber

Answer 11

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

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>Dancho</Author>
  <LastAuthor>Dancho</LastAuthor>
  <Created>2010-02-05T10:15:54Z</Created>
  <Company>cc</Company>
  <Version>11.9999</Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>13800</WindowHeight>
  <WindowWidth>24795</WindowWidth>
  <WindowTopX>480</WindowTopX>
  <WindowTopY>105</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="6" x:FullColumns="1"
   x:FullRows="1">
   <Row>
    <Cell><Data ss:Type="String">Value1</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Value2</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Value3</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Value4</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Value5</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Value6</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>5</ActiveRow>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet2">
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet3">
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>

Gangland

Office Open XML is actually a zip file containing multiple XML files. This is not nearly as straight-forward as you're implying. – Hypanthium

Matthew, there is SpreadsheetML which was introduced in Office 2003. This is not a zip file and actually isn't all that hard to work with, I wrote a parser for it recently. – Lubber

I was reffering to Microsoft excel where when you create sheet you can just go to Save as menu and select to save it as xml file. The open the xml file copy the part that is hard coded and just replace the test data with real data it is easy i have done it million times with MS Office 2000, 2003 with 2007 i haven't tested but it must be similar if you think it is very hard just give me excel file and i can write some cind of test program. – Gangland

@lordanTanev: I've added the link to the spec for the XML you're talking about. – Lubber

@lan i have added a sample from the xml i am talking about it is from Excel 2003 – Gangland

@lordanTanev : I know... It is Matthew who was confused. I was trying to assist you in pointing that out to him. – Lubber

Answer 12

List<"classname"> getreport = cs.getcompletionreport(); 

var getreported = getreport.Select(c => new { demographic = c.rName);   

where cs.getcompletionreport() reference class file is Business Layer for App
I hope this helps.

Flanch

because it looked like this i.imgur.com/5ngtPac.png I hope you agree it's easier to read and to visually separate the code from rest of the answer this way – Voe

Answer 13

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<T>(IEnumerable<T> 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();

        }

Niagara