DataTableExcelExporter.cs

using System;
using System.Collections.Generic;
using System.Data;
using OfficeOpenXml;

namespace ExcelExport
{
    internal class DataTableExcelExporter
    {
        private readonly IEnumerable<string> _columnsToHide;

        internal DataTableExcelExporter(IEnumerable<string> columnsToHide = null)
        {
            _columnsToHide = new List<string>();

            if (columnsToHide != null)
            {
                _columnsToHide = columnsToHide;
            }
        }


        internal void AddRecordsWorksheetToWorkBook(string workSheetName, DataTable datatable, ExcelPackage excelPackage)
        {
            var workSheet = excelPackage.Workbook.Worksheets[workSheetName];

            if (workSheet == null)
            {
                workSheet = excelPackage.Workbook.Worksheets.Add(workSheetName);
            }

            AddHeaderRowToWorkSheet(workSheet, datatable);

            var lastRowNumber = workSheet.Dimension.End.Row;

            var nextRowNumber = ++lastRowNumber;

            AddDataRowsToWorkSheet(workSheet, datatable, nextRowNumber);

            //This will auto expand the columns
            AutoFitColumns(workSheet);

            HideColumns(workSheet, datatable);

        }

        private void AddHeaderRowToWorkSheet(ExcelWorksheet worksheet, DataTable table)
        {
            var value = worksheet.GetValue(0, 0);
            if (value != null)
            {
                return;
            }

            const int headerRow = 1;


            for (int columnNumber = 0; columnNumber < table.Columns.Count; columnNumber++)
            {
               var headerName = table.Columns[columnNumber].ColumnName;
                var excelColumn = columnNumber+1;
                worksheet.SetValue(headerRow, excelColumn, headerName);
            }
        }

        private void AddDataRowToWorkSheet(ExcelWorksheet workSheet, DataRow row, int rowNumber)
        {
            var numberOfColumns = row.Table.Columns.Count;

            for (var columnNumber = 0; columnNumber < numberOfColumns; columnNumber++)
            {
                var data = GetValue(row, columnNumber);
                var excelColumn = columnNumber + 1;
                workSheet.SetValue(rowNumber, excelColumn, data);

                if (data is DateTime)
                {
                    workSheet.Cells[rowNumber, excelColumn].Style.Numberformat.Format = "mm-dd-yy";
                }

            }
        }

        private void AddDataRowsToWorkSheet(ExcelWorksheet workSheet, DataTable records, int rowNumber = 2)
        {
            foreach (DataRow record in records.Rows)
            {
                AddDataRowToWorkSheet(workSheet, record, rowNumber);

                rowNumber++;
            }
        }

        private static void AutoFitColumns(ExcelWorksheet workSheet)
        {
            workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();
        }

        private void HideColumns(ExcelWorksheet workSheet, DataTable table)
        {
            foreach (string dataColumn in _columnsToHide)
            {
                var columNumber = GetColumNumberFromTableColumnName(dataColumn, table);

                ExcelColumn column = workSheet.Column(columNumber);
                column.Hidden = true;
            }
        }

        private object GetValue(DataRow row, int columnNumber)
        {
            return row[columnNumber];
        }

        private int GetColumNumberFromTableColumnName(string columnName, DataTable dataTable)
        {
            foreach (DataColumn column in dataTable.Columns)
            {
                if (column.ColumnName.ToLowerInvariant() == columnName.ToLowerInvariant())
                {
                    var index = dataTable.Columns.IndexOf(column);
                    return index + 1;
                }
            }

            return 0;
        }
    }
}

ExcelDocumentCreator.cs

using System;
using System.Collections.Generic;

namespace ExcelExport
{
    using System.IO;
    using OfficeOpenXml; // This comes from EEPLUS you can find it on nuget
    using System.Data;
    public static class ExcelDocumentCreator
    {
        public static void SaveRecordsToExcelWorksheet(string fileName, string workSheetName, DataTable dataTable , IEnumerable<string> columnsToIgnore, Action<ExcelPackage> afterCreated)
        {
            if (dataTable == null)
            {
                return;
            }

            using (ExcelPackage document = new ExcelPackage())
            {
                var exporter = new DataTableExcelExporter(columnsToIgnore);

                exporter.AddRecordsWorksheetToWorkBook(workSheetName, dataTable, document);

                afterCreated(document);
            }
        }

        public static void SaveRecordsToExcelWorksheet<TRecordType>(string fileName, string workSheetName, IEnumerable<TRecordType> records, IEnumerable<string> columnsToIgnore, Action<ExcelPackage> afterCreated)
        {
            if (records == null)
            {
                return;
            }

            using (ExcelPackage document = new ExcelPackage())
            {
                ExcelExporter<TRecordType> exporter = new ExcelExporter<TRecordType>(columnsToIgnore);

                exporter.AddRecordsWorksheetToWorkBook(workSheetName, records, document);

                afterCreated(document);
            }
        }

        public static void SaveRecordsToExcelWorksheet<TRecordType>(string fileName, string workSheetName, DataTable dataTable, IEnumerable<string> columnsToIgnore)
        {
            if (dataTable == null)
            {
                return;
            }

            FileInfo file = new FileInfo(fileName);

            ExcelPackage document = new ExcelPackage(file);

            DataTableExcelExporter exporter = new DataTableExcelExporter(columnsToIgnore);

            exporter.AddRecordsWorksheetToWorkBook(workSheetName, dataTable, document);

            document.Save();
        }


        public static void SaveRecordsToExcelWorksheet<TRecordType>(string fileName, string workSheetName, IEnumerable<TRecordType> records, IEnumerable<string> columnsToIgnore)
        {
            if (records == null)
            {
                return;
            }

            FileInfo file = new FileInfo(fileName);

            ExcelPackage document = new ExcelPackage(file);

            ExcelExporter<TRecordType> exporter = new ExcelExporter<TRecordType>(columnsToIgnore);

            exporter.AddRecordsWorksheetToWorkBook(workSheetName, records, document);

            document.Save();
        }

        public static void SaveRecordToExcelWorksheet<TRecordType>(string fileName, string workSheetName, TRecordType record, IEnumerable<string> columnsToIgnore)
        {
            FileInfo file = new FileInfo(fileName);

            ExcelPackage document = new ExcelPackage(file);

            ExcelExporter<TRecordType> exporter = new ExcelExporter<TRecordType>(columnsToIgnore);

            exporter.AddRecordToToWorkBookWorkSheet(workSheetName, record, document);

            document.Save();
        }
    }
}

ExcelExporter.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ExcelExport
{
    using OfficeOpenXml;
    internal class ExcelExporter<TRecordType>
    {
        private readonly Dictionary<int, string> _indexedPropertyNames;

        private readonly IEnumerable<string> _propertiesToHide;

        internal ExcelExporter(IEnumerable<string> propertiesToHide = null)
        {
            _propertiesToHide = new List<string>();

            if (propertiesToHide != null)
            {
                _propertiesToHide = propertiesToHide;
            }

            _indexedPropertyNames = GetPropertyNamesIndex();
        }

        internal void AddRecordsWorksheetToWorkBook(string workSheetName, IEnumerable<TRecordType> records, ExcelPackage excelPackage)
        {
            var workSheet = excelPackage.Workbook.Worksheets[workSheetName];

            if (workSheet == null)
            {
                workSheet = excelPackage.Workbook.Worksheets.Add(workSheetName);
            }

            AddHeaderRowToWorkSheet(workSheet);

            var lastRowNumber = workSheet.Dimension.End.Row;

            var nextRowNumber = ++lastRowNumber;

            AddDataRowsToWorkSheet(workSheet, records, nextRowNumber);

            //This will auto expand the columns
            AutoFitColumns(workSheet);

            HideColumns(workSheet);

        }

        private static void AutoFitColumns(ExcelWorksheet workSheet)
        {
            workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();
        }

        private void HideColumns(ExcelWorksheet workSheet)
        {
            foreach (var property in _propertiesToHide)
            {
                var columNumber = GetColumNumberFromPropertyName(property);
                ExcelColumn column = workSheet.Column(columNumber);
                column.Hidden = true;
            }
        }

        internal void AddRecordToToWorkBookWorkSheet(string workSheetName, TRecordType record, ExcelPackage excelPackage)
        {
            var workSheet = excelPackage.Workbook.Worksheets[workSheetName];

            if (workSheet == null)
            {
                workSheet = excelPackage.Workbook.Worksheets.Add(workSheetName);
            }

            AddHeaderRowToWorkSheet(workSheet);

            var lastRowNumber = workSheet.Dimension.End.Row;

            var nextRowNumber = ++lastRowNumber;

            AddDataRowToWorkSheet(workSheet, record, nextRowNumber);

        }

        private void AddHeaderRowToWorkSheet(ExcelWorksheet worksheet)
        {
            var value = worksheet.GetValue(0, 0);
            if (value != null)
            {
                return;
            }

            const int headerRow = 1;


            for (int columnNumber = 1; columnNumber <= _indexedPropertyNames.Count; columnNumber++)
            {
                var headerName = GetHeaderName(columnNumber);

                worksheet.SetValue(headerRow, columnNumber, headerName);
            }
        }

        private string GetHeaderName(int columnNumber)
        {
            var propertyName = _indexedPropertyNames[columnNumber];

            var headerName = propertyName.SeparateCamelCasingBySpaces();
            return headerName;
        }



        private void AddDataRowsToWorkSheet(ExcelWorksheet workSheet, IEnumerable<TRecordType> records, int rowNumber = 2)
        {

            foreach (var record in records)
            {
                AddDataRowToWorkSheet(workSheet, record, rowNumber);

                rowNumber++;
            }
        }



        private void AddDataRowToWorkSheet(ExcelWorksheet workSheet, TRecordType record, int rowNumber)
        {
            for (var columnNumber = 1; columnNumber <= _indexedPropertyNames.Count; columnNumber++)
            {
                var data = GetPropertyValue(record, columnNumber);

                workSheet.SetValue(rowNumber, columnNumber, data);

                if (data is DateTime)
                {
                    workSheet.Cells[rowNumber, columnNumber].Style.Numberformat.Format = "mm-dd-yy";
                }

            }
        }

        private object GetPropertyValue(TRecordType record, int columnNumber)
        {
            var propertyName = _indexedPropertyNames[columnNumber];

            var data = record.GetValue(propertyName);
            return data;
        }

        private Dictionary<int, string> GetPropertyNamesIndex()
        {
            var propertyNames = typeof(TRecordType).GetProperties().Select(p => p.Name).ToList();

            var indexedProperties = new Dictionary<int, string>();

            for (var index = 0; index < propertyNames.Count; index++)
            {
                var propertynameIndex = index + 1;

                var propertyName = propertyNames[index];

                indexedProperties.Add(propertynameIndex, propertyName);
            }

            return indexedProperties;
        }

        private int GetColumNumberFromPropertyName(string propertyName)
        {
            return _indexedPropertyNames.FirstOrDefault(p => p.Value.ToLower() == propertyName.ToLower()).Key;
        }
    }
}

ExcelExtentions.cs

using System.Text;
using System.Linq;
public static class ExcelExtentions
{
    public static string SeparateCamelCasingBySpaces(this string value)
    {
        var chars = value.ToCharArray();
        var spacedStringBuilder = new StringBuilder();
        bool isFirstCharacter = true;
        for (int i = 0; i < chars.Count(); i++)
        {
            var currentCharacter = chars[i];

            if (isFirstCharacter)
            {
                isFirstCharacter = false;

                spacedStringBuilder.Append(currentCharacter);

                continue;
            }

            if (char.IsUpper(currentCharacter))
            {
                spacedStringBuilder.Append(' ');
            }
            spacedStringBuilder.Append(currentCharacter);
        }

        var spacedString = spacedStringBuilder.ToString();

        return spacedString;
    }

    public static object GetValue(this object o, string propertyName)
    {
        var value = o.GetType().GetProperty(propertyName).GetValue(o, null);
        return value;
    }
}

HttpResponseExcelWriter.cs

using System;
using System.Collections.Generic;
using System.Web;
using OfficeOpenXml;
using System.Data;

namespace ExcelExport
{
    public static class HttpResponseExcelWriter
    {
        public static void WriteExcelDocumenToResponse<TRecordType>(this HttpResponse response, string fileName,
            IEnumerable<TRecordType> records, string worksheetName = "Sheet1", IEnumerable<string> columnsToIgnore = null)
        {
            var saveToResponseAction = SaveToResponse(response, fileName);

            ExcelDocumentCreator.SaveRecordsToExcelWorksheet(fileName, worksheetName, records, columnsToIgnore, saveToResponseAction);

        }

        public static void WriteExcelDocumenToResponse(this HttpResponse response, string fileName,
          DataTable records, string worksheetName = "Sheet1", IEnumerable<string> columnsToIgnore = null)
        {
            var saveToResponseAction = SaveToResponse(response, fileName);

            ExcelDocumentCreator.SaveRecordsToExcelWorksheet(fileName, worksheetName, records, columnsToIgnore, saveToResponseAction);

        }

        private static Action<ExcelPackage> SaveToResponse(HttpResponse response, string fileName)
        {
            Action<ExcelPackage> saveToResponse = (pkg) =>
            {
                SaveToResponse(pkg, response, fileName);
            };

            return saveToResponse;
        }

        private static void SaveToResponse(ExcelPackage package,HttpResponse response,string fileName)
        {
            var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

            var excelHeader = new KeyValuePair<string,string>("content-disposition",string.Format("attachment;  filename={0}", fileName));

            var bytes = package.GetAsByteArray();
             
            response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            response.AddHeader(excelHeader.Key,excelHeader.Value);
            response.BinaryWrite(bytes);
            response.Flush();
            response.End();
        }
    }
}