Aug 15

Export Data to CSV using C#

In this article I would like to show how to export datatable and list to CSV File (Comma Separated Values). CSV file is a text based file which contains data are separated by comma.
CSV file can also open’s in Microsoft Excel. To create CSV file, we need to write data to Response object of HttpContext. The content type used should be “text/csv”
Other Export Articles
 
So let’s start with implementation in C# now,
Step 1: Create plain employee class with some fields in it.
 
public class Employee
    {
        public int EmployeeId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public bool Gender { get; set; }
        public DateTime Dob { get; set; }
        public string Title { get; set; }
        public string Department { get; set; }
    }
Step 2: Create helper class which defines overloaded functions to export data from datatable and list to CSV format.
 
 
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
public class CsvExportHelper
    {
        public static StringBuilder ExportDataTable(DataTable dataTable)
        {
            var stringBuilder = new StringBuilder();
            for (int column = 0; column < dataTable.Columns.Count; column++)
            {
                //add separator
                stringBuilder.Append(dataTable.Columns[column].ColumnName + ‘,’);
            }
            //append new line
            stringBuilder.Append(“rn”);
            for (int rows = 0; rows < dataTable.Rows.Count; rows++)
            {
                for (int column = 0; column < dataTable.Columns.Count; column++)
                {
                    //add separator
                    stringBuilder.Append(dataTable.Rows[rows][column].ToString().Replace(“,”, “;”) + ‘,’);
                }
                //append new line
                stringBuilder.Append(“rn”);
            }
            return stringBuilder;
        }
        public static StringBuilder ExportList<T>(IEnumerable<T> list)
        {
            var stringBuilder = new StringBuilder();
            //Create Header Part
            var headerProperties = typeof(T).GetProperties();
            for (int i = 0; i < headerProperties.Length – 1; i++)
            {
                stringBuilder.Append(headerProperties[i].Name + “,”);
            }
            var lastProp = headerProperties[headerProperties.Length – 1].Name;
            stringBuilder.Append(lastProp + Environment.NewLine);
            //Create Rows
            foreach (var item in list)
            {
                var rowValues = typeof(T).GetProperties();
                for (int i = 0; i < rowValues.Length – 1; i++)
                {
                    var prop = rowValues[i];
                    stringBuilder.Append(prop.GetValue(item) + “,”);
                }
                stringBuilder.Append(rowValues[rowValues.Length – 1].GetValue(item) + Environment.NewLine);
            }
            return stringBuilder;
        }
    }
 
 
Step 3: Question comes to your mind, how to use this. Call below functions from your ASP.NET event handler or ASP.NET MVC action method.
Below example shows how to export datatable to csv
private void ExportTableToCsv()
        {
            var _employeeBal = new EmployeeBal();
            var employeeDataTable = _employeeBal.GetAllDataTable();
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader(“content-disposition”, “attachment;filename=Employee.csv”);
            Response.Charset = “”;
            Response.ContentType = “application/text”;
            Response.Output.Write(CsvExportHelper.ExportDataTable(employeeDataTable).ToString());
            Response.Flush();
            Response.End();
        }
Below example shows how to export list of objects to csv
        private void ExportListToCsv()
        {
     var _employeeBal = new EmployeeBal();
            var employees = _employeeBal.GetAll();
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader(“content-disposition”, “attachment;filename=Employee.csv”);
            Response.Charset = “”;
            Response.ContentType = “application/text”;
            Response.Output.Write(CsvExportHelper.ExportList<Employee>(employees).ToString());
            Response.Flush();
            Response.End();
        }
 
 
I hope you enjoyed this article and you can use same code in your assignment or project.

 

Leave a Reply