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 · 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 · 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  lt   headers Count  i                                  var cell   header CreateCell i                   cell SetCellValue  headers i                    cell CellStyle   headerStyle                             for  var i   0  i  lt   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 lt string gt                  type   new List lt string gt                        public override void WriteData lt T gt  List lt T gt  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  lt  table Rows Count  i                                  sheetRow    sheet CreateRow i   1                   for  int j   0  j  lt  table Columns Count  j                                          ICell Row1   sheetRow CreateCell j                        string type    type j  ToLower                        var currentCellValue   table Rows i  j                        if  currentCellValue    null  amp  amp                            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 · 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 lt string gt  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

User · Answer

The simplest way using ClosedXml   Imports ClosedXML Excel  var dataList   new List lt string gt       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

User · Answer

Using ClosedXML 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

User · Answer

Fast way - ArrayToExcel  github   byte   excel   myList ToExcel    File WriteAllBytes  result xlsx   excel

User · 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  lt  l Count  i            sw WriteLine l i

User · 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    lt  xml version  1 0   gt   lt  mso-application progid  Excel Sheet   gt   lt 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  gt    lt DocumentProperties xmlns  urn schemas-microsoft-com office office  gt     lt Author gt Dancho lt  Author gt     lt LastAuthor gt Dancho lt  LastAuthor gt     lt Created gt 2010-02-05T10 15 54Z lt  Created gt     lt Company gt cc lt  Company gt     lt Version gt 11 9999 lt  Version gt    lt  DocumentProperties gt    lt ExcelWorkbook xmlns  urn schemas-microsoft-com office excel  gt     lt WindowHeight gt 13800 lt  WindowHeight gt     lt WindowWidth gt 24795 lt  WindowWidth gt     lt WindowTopX gt 480 lt  WindowTopX gt     lt WindowTopY gt 105 lt  WindowTopY gt     lt ProtectStructure gt False lt  ProtectStructure gt     lt ProtectWindows gt False lt  ProtectWindows gt    lt  ExcelWorkbook gt    lt Styles gt     lt Style ss ID  Default  ss Name  Normal  gt      lt Alignment ss Vertical  Bottom   gt      lt Borders  gt      lt Font  gt      lt Interior  gt      lt NumberFormat  gt      lt Protection  gt     lt  Style gt    lt  Styles gt    lt Worksheet ss Name  Sheet1  gt     lt Table ss ExpandedColumnCount  1  ss ExpandedRowCount  6  x FullColumns  1     x FullRows  1  gt      lt Row gt       lt Cell gt  lt Data ss Type  String  gt Value1 lt  Data gt  lt  Cell gt      lt  Row gt      lt Row gt       lt Cell gt  lt Data ss Type  String  gt Value2 lt  Data gt  lt  Cell gt      lt  Row gt      lt Row gt       lt Cell gt  lt Data ss Type  String  gt Value3 lt  Data gt  lt  Cell gt      lt  Row gt      lt Row gt       lt Cell gt  lt Data ss Type  String  gt Value4 lt  Data gt  lt  Cell gt      lt  Row gt      lt Row gt       lt Cell gt  lt Data ss Type  String  gt Value5 lt  Data gt  lt  Cell gt      lt  Row gt      lt Row gt       lt Cell gt  lt Data ss Type  String  gt Value6 lt  Data gt  lt  Cell gt      lt  Row gt     lt  Table gt     lt WorksheetOptions xmlns  urn schemas-microsoft-com office excel  gt      lt Selected  gt      lt Panes gt       lt Pane gt        lt Number gt 3 lt  Number gt        lt ActiveRow gt 5 lt  ActiveRow gt       lt  Pane gt      lt  Panes gt      lt ProtectObjects gt False lt  ProtectObjects gt      lt ProtectScenarios gt False lt  ProtectScenarios gt     lt  WorksheetOptions gt    lt  Worksheet gt    lt Worksheet ss Name  Sheet2  gt     lt WorksheetOptions xmlns  urn schemas-microsoft-com office excel  gt      lt ProtectObjects gt False lt  ProtectObjects gt      lt ProtectScenarios gt False lt  ProtectScenarios gt     lt  WorksheetOptions gt    lt  Worksheet gt    lt Worksheet ss Name  Sheet3  gt     lt WorksheetOptions xmlns  urn schemas-microsoft-com office excel  gt      lt ProtectObjects gt False lt  ProtectObjects gt      lt ProtectScenarios gt False lt  ProtectScenarios gt     lt  WorksheetOptions gt    lt  Worksheet gt   lt  Workbook gt

User · Answer

List lt  classname  gt  getreport   cs getcompletionreport      var getreported   getreport Select c   gt  new   demographic   c rName        where cs getcompletionreport   reference class file is Business Layer for App I hope this helps

User · Answer

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

User · 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 lt Auto gt  Auto   new List lt Program Auto gt                                  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 · 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

[c#] Exporting the values in List to excel

Examples related to c#

Examples related to excel

Examples related to ms-office

Examples related to office-interop

Examples related to excel-interop