'Exporting the values in List to excel
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?
Solution 1:[1]
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);
Solution 2:[2]
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]);
}
}
Solution 3:[3]
Fast way - ArrayToExcel (github)
byte[] excel = myList.ToExcel();
File.WriteAllBytes("result.xlsx", excel);
Solution 4:[4]
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
Solution 5:[5]
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> 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");
}
}
}
Solution 6:[6]
You could output them to a .csv file and open the file in excel. Is that direct enough?
Solution 7:[7]
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.
Solution 8:[8]
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
@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>
Solution 9:[9]
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.
Solution 10:[10]
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.
Solution 11:[11]
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
MemoryStreamobject
=============================================================================
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<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.
Solution 12:[12]
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();
}
Solution 13:[13]
Three useful related code snippets:
- A C# Extension method for ListToExcel with EPPlus:
public static void ListToExcel<T>(this IList<T> 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<T>(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<T>(this IList<T> 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<T>(this IList<T> 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();
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow