Convert To CSV & Excel

CustomConvert.cs

//NPOI.dll
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Data;
using System.Reflection;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using System.IO;

namespace AFSExtensions
{
    /// <summary>
    /// 
    /// </summary>
    public class CustomConvert
    {
        #region CSV

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static string DataTableToCscString(DataTable dt)
        {
            StringBuilder sb = new StringBuilder();
            if (dt.Columns.Count != 0)
            {
                string[] arr = new String[dt.Columns.Count];
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    arr[i] = dt.Columns[i].ColumnName;
                    arr[i] = CsvEncode(arr[i]);
                }

                sb.Append(string.Join(",", arr));
                sb.Append("\r\n");

                foreach (DataRow row in dt.Rows)
                {
                    string[] dataArr = new String[dt.Columns.Count];
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        object o = row[i];
                        dataArr[i] = CsvEncode(o);
                    }
                    sb.Append(string.Join(",", dataArr));
                    sb.Append("\r\n");
                }
            }
            return sb.ToString();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="objects"></param>
        /// <param name="columnsDictionary"></param>
        /// <param name="includeHeaderLine"></param>
        /// <param name="dateFormat"></param>
        /// <param name="doubleFormat"></param>
        /// <returns></returns>
        public static string LinqEntitiesToCsv<T>(ICollection<T> objects,
            IDictionary<string, object> columnsDictionary,
            bool includeHeaderLine,
            string dateFormat,
            string doubleFormat)
        {
            Type type = typeof(T);
            PropertyInfo[] properties = type.GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty);

            StringBuilder sb = new StringBuilder();
            bool isFirst = true;

            if (includeHeaderLine)
            {
                // header row
                foreach (var p in properties)
                {
                    if (columnsDictionary == null)
                    {
                        sb.AppendFormat("{0}{1}", isFirst ? "" : ",", CsvEncode(p.Name));
                        isFirst = false;
                    }
                    else if (columnsDictionary.ContainsKey(p.Name))
                    {
                        sb.AppendFormat("{0}{1}", isFirst ? "" : ",", CsvEncode(columnsDictionary[p.Name]));
                        isFirst = false;
                    }
                }
                sb.Append("\r\n");
            }
            // data rows
            foreach (var o in objects)
            {
                isFirst = true;
                foreach (var p in properties)
                {
                    if (columnsDictionary == null || columnsDictionary.ContainsKey(p.Name))
                    {
                        string csvValue = CsvEncode(o, p, dateFormat, doubleFormat);
                        sb.AppendFormat("{0}{1}", isFirst ? "" : ",", csvValue);
                        isFirst = false;
                    }
                }
                sb.Append("\r\n");
            }

            return sb.ToString();
        }

        public static string LinqEntitiesToCsv2<T>(ICollection<T> objects,
           string[] propertiesNames,
           bool includeHeaderLine)
        {
            return LinqEntitiesToCsv<T>(objects,
           propertiesNames,
             true,
            null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="objects"></param>
        /// <param name="propertiesNames"></param>
        /// <param name="includeHeaderLine"></param>
        /// <param name="dateFormat"></param>
        /// <param name="doubleFormat"></param>
        /// <returns></returns>
        public static string LinqEntitiesToCsv<T>(ICollection<T> objects,
           string[] propertiesNames,
           bool includeHeaderLine,
           string dateFormat,
           string doubleFormat)
        {
            Type type = typeof(T);

            StringBuilder sb = new StringBuilder();
            bool isFirst = true;

            if (includeHeaderLine)
            {
                // header row
                foreach (var propertyName in propertiesNames)
                {

                    sb.AppendFormat("{0}{1}", isFirst ? "" : ",", CsvEncode(propertyName));
                    isFirst = false;
                }
                sb.Append("\r\n");
            }
            // data rows
            foreach (var o in objects)
            {
                isFirst = true;
                foreach (var propertyName in propertiesNames)
                {
                    PropertyInfo p = o.GetType().GetProperty(propertyName);
                    string csvValue = CsvEncode(o, p, dateFormat, doubleFormat);
                    sb.AppendFormat("{0}{1}", isFirst ? "" : ",", csvValue);
                    isFirst = false;

                }
                sb.Append("\r\n");
            }

            return sb.ToString();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="objects"></param>
        /// <param name="columnsDictionary"></param>
        /// <param name="includeHeaderLine"></param>
        /// <returns></returns>
        public static string LinqEntitiesToCsv<T>(ICollection<T> objects, IDictionary<string, object> columnsDictionary, bool includeHeaderLine)
        {
            return LinqEntitiesToCsv(objects, columnsDictionary, includeHeaderLine, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="objects"></param>
        /// <param name="columnsDictionary"></param>
        /// <returns></returns>
        public static string LinqEntitiesToCsv<T>(ICollection<T> objects, IDictionary<string, object> columnsDictionary)
        {
            return LinqEntitiesToCsv(objects, columnsDictionary, true, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="objects"></param>
        /// <param name="columnsTransformation"></param>
        /// <returns></returns>
        public static string LinqEntitiesToCsv<T>(ICollection<T> objects, object columnsTransformation)
        {
            return LinqEntitiesToCsv(objects, columnsTransformation, true);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="objects"></param>
        /// <param name="columnsTransformation"></param>
        /// <param name="includeHeaderLine"></param>
        /// <returns></returns>
        public static string LinqEntitiesToCsv<T>(ICollection<T> objects, object columnsTransformation, bool includeHeaderLine)
        {
            return LinqEntitiesToCsv<T>(objects, columnsTransformation.TransformToDictionary(), includeHeaderLine, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="objects"></param>
        /// <param name="columnsTransformation"></param>
        /// <param name="includeHeaderLine"></param>
        /// <param name="dateFormat"></param>
        /// <param name="doubleFormat"></param>
        /// <returns></returns>
        public static string LinqEntitiesToCsv<T>(ICollection<T> objects,
            object columnsTransformation,
            bool includeHeaderLine,
            string dateFormat,
            string doubleFormat)
        {
            return LinqEntitiesToCsv<T>(objects, columnsTransformation.TransformToDictionary(), includeHeaderLine, dateFormat, doubleFormat);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="objects"></param>
        /// <returns></returns>
        public static string LinqEntitiesToCsv<T>(ICollection<T> objects)
        {
            return LinqEntitiesToCsv<T>(objects, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static byte[] DataTableToExcel(DataTable dt)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            Sheet sheet = workbook.CreateSheet("Data");

            short color = HSSFColor.BLUE.index;
            CellStyle cellStyle = workbook.CreateCellStyle();
            NPOI.SS.UserModel.Font fnt = workbook.CreateFont();
            fnt.Color = color;
            cellStyle.SetFont(fnt);

            if (dt.Columns.Count != 0)
            {
                Int32 r = 0;
                Row newRow = sheet.CreateRow(r++);
                string[] arr = new String[dt.Columns.Count];
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    Cell newCell = newRow.CreateCell(i);
                    newCell.SetCellValue(dt.Columns[i].ColumnName);
                    newCell.CellStyle = cellStyle;
                }

                foreach (DataRow row in dt.Rows)
                {
                    newRow = sheet.CreateRow(r++);
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        Cell newCell = newRow.CreateCell(i);
                        newCell.SetCellValue(String.Format("{0}", row[i]));
                    }
                }
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    sheet.AutoSizeColumn(i);
                }
            }

            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            return stream.ToArray();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="objects"></param>
        /// <param name="columnsDictionary"></param>
        /// <returns></returns>
        public static byte[] LinqEntitiesToExcel<T>(ICollection<T> objects, IDictionary<string, object> columnsDictionary)
        {
            PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty);

            var propIndexes = new List<int>();
            var propNames = new List<string>();

            if (columnsDictionary == null)
            {
                for (int i = 0; i < properties.Length; i++)
                {
                    propIndexes.Add(i);
                    propNames.Add(properties[i].Name);
                }
            }
            else
            {
                for (int i = 0; i < properties.Length; i++)
                {
                    if (!columnsDictionary.ContainsKey(properties[i].Name))
                        continue;
                    propIndexes.Add(i);
                    propNames.Add(columnsDictionary[properties[i].Name].ToString());
                }
            }

            HSSFWorkbook workbook = new HSSFWorkbook();
            Sheet sheet = workbook.CreateSheet("Data");

            CellStyle cellStyle = workbook.CreateCellStyle();
            NPOI.SS.UserModel.Font fnt = workbook.CreateFont();
            fnt.Boldweight = (short)FontBoldWeight.BOLD;
            cellStyle.SetFont(fnt);

            Int32 rowNo = 0;

            // header
            Row headRow = sheet.CreateRow(rowNo++);
            string[] arr = new String[properties.Length];
            for (int i = 0; i < propNames.Count; i++)
            {
                Cell newCell = headRow.CreateCell(i);

                newCell.SetCellValue(propNames[i]);
                newCell.CellStyle = cellStyle;
            }

            // data
            string value; ;
            double doubleResult;
            foreach (T o in objects)
            {
                Row newRow = sheet.CreateRow(rowNo++);
                for (int i = 0; i < propIndexes.Count; i++)
                {
                    Cell newCell = newRow.CreateCell(i);
                    value = String.Format("{0}", properties[propIndexes[i]].GetValue(o, null));
                    if (Double.TryParse(value, out doubleResult))
                        newCell.SetCellValue(doubleResult);
                    else
                        newCell.SetCellValue(value);

                }
            }

            for (int i = 0; i < properties.Length; i++)
                sheet.AutoSizeColumn(i);

            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            return stream.ToArray();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="objects"></param>
        /// <param name="columnsDictionary"></param>
        /// <returns></returns>
        public static byte[] LinqEntitiesToExcelUserOrder<T>(ICollection<T> objects, IDictionary<string, object> columnsDictionary)
        {
            return LinqEntitiesToExcelUserOrder(objects, columnsDictionary, null);
        }


        public static byte[] LinqEntitiesToExcelUserOrder<T>(ICollection<T> objects, IDictionary<string, object> columnsDictionary, string dateFormat)
        {
            //PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty);

            var propIndexes = new List<int>();
            var propNames = new List<string>();
            var propKeys = new List<string>();
            int k = 0;
            foreach (var item in columnsDictionary)
            {
                propIndexes.Add(k);
                propNames.Add(item.Value.ToString());
                propKeys.Add(item.Key);
                k++;
            }

            HSSFWorkbook workbook = new HSSFWorkbook();
            Sheet sheet = workbook.CreateSheet("Data");

            CellStyle cellStyle = workbook.CreateCellStyle();
            NPOI.SS.UserModel.Font fnt = workbook.CreateFont();
            fnt.Boldweight = (short)FontBoldWeight.BOLD;
            cellStyle.SetFont(fnt);

            Int32 rowNo = 0;

            // header
            Row headRow = sheet.CreateRow(rowNo++);
            //string[] arr = new String[propNames.Length];
            for (int i = 0; i < propNames.Count; i++)
            {
                Cell newCell = headRow.CreateCell(i);

                newCell.SetCellValue(propNames[i]);
                newCell.CellStyle = cellStyle;
            }

            // data
            string value; ;
            double doubleResult;
            DateTime dateTimeResult;
            foreach (T o in objects)
            {
                Row newRow = sheet.CreateRow(rowNo++);
                for (int i = 0; i < propIndexes.Count; i++)
                {
                    Cell newCell = newRow.CreateCell(i);
                    PropertyInfo p = o.GetType().GetProperty(propKeys[i]);
                    value = p == null ? null : String.Format("{0}", p.GetValue(o, null));
                    if (Double.TryParse(value, out doubleResult))
                        newCell.SetCellValue(doubleResult);
                    else if (dateFormat.IsNotNullOrEmpty() && DateTime.TryParse(value, out dateTimeResult))
                    {
                        newCell.SetCellValue(dateTimeResult.ToString(dateFormat));
                    }
                    else
                    {
                        newCell.SetCellValue(value);
                    }

                }
            }

            for (int i = 0; i < propIndexes.Count; i++)
                sheet.AutoSizeColumn(i);

            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            return stream.ToArray();
        }


        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="objects"></param>
        /// <param name="columnsTransformation"></param>
        /// <returns></returns>
        public static byte[] LinqEntitiesToExcel<T>(ICollection<T> objects, object columnsTransformation)
        {
            return LinqEntitiesToExcel<T>(objects, columnsTransformation.TransformToDictionary());
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="objects"></param>
        /// <returns></returns>
        public static byte[] LinqEntitiesToExcel<T>(ICollection<T> objects)
        {
            return LinqEntitiesToExcel<T>(objects, null);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        private static String CsvEncode(object o)
        {
            if (o == null || o == Convert.DBNull)
                return "";
            else
                return String.Format("\"{0}\"", o.ToString().Replace("\r\n", "\n").Replace("\"", "\"\""));
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="o"></param>
        /// <param name="p"></param>
        /// <param name="dateFormat"></param>
        /// <param name="doubleFormat"></param>
        /// <returns></returns>
        private static String CsvEncode(object o, PropertyInfo p, string dateFormat, string doubleFormat)
        {
            if (o == null || o == Convert.DBNull || p.GetValue(o, null) == null)
                return "";

            string csvValue = "";
            if (dateFormat.IsNotNullOrEmpty()
                && ((p.PropertyType == typeof(DateTime)) || (p.PropertyType == typeof(DateTime?))))
            {
                DateTime dt = (DateTime)p.GetValue(o, null);
                csvValue = dt.ToString(dateFormat);
            }
            else if (doubleFormat.IsNotNullOrEmpty()
                        && (p.PropertyType == typeof(Double)
                            || p.PropertyType == typeof(Double?)
                            || p.PropertyType == typeof(Decimal)
                            || p.PropertyType == typeof(Decimal?)))
            {
                if (p.PropertyType == typeof(Double) || p.PropertyType == typeof(Double?))
                {
                    Double db = (Double)p.GetValue(o, null);
                    csvValue = db.ToString(doubleFormat);
                }
                else if (p.PropertyType == typeof(Decimal) || p.PropertyType == typeof(Decimal?))
                {
                    Decimal dc = (Decimal)p.GetValue(o, null);
                    csvValue = dc.ToString(doubleFormat);
                }
                else //should never happen
                {
                    csvValue = (p.GetValue(o, null) ?? (Object)"").ToString();
                }
            }
            else
            {
                csvValue = CsvEncode((p.GetValue(o, null) ?? (Object)"").ToString());
            }
            return csvValue;
        }

        #endregion
        
            #region CSV

    public static string ToCSV(DataTable dt)
    {
        StringBuilder sb = new StringBuilder();
        if (dt.Columns.Count != 0)
        {
            string[] arr = new String[dt.Columns.Count];
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                arr[i] = dt.Columns[i].ColumnName;
                arr[i] = GetWriteableValue(arr[i]);
            }

            sb.Append(string.Join(",", arr));
            sb.Append("\r\n");

            foreach (DataRow row in dt.Rows)
            {
                string[] dataArr = new String[dt.Columns.Count];
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    object o = row[i];
                    dataArr[i] = GetWriteableValue(o);
                }
                sb.Append(string.Join(",", dataArr));
                sb.Append("\r\n");
            }
        }
        return sb.ToString();
    }

    public static String GetWriteableValue(object o)
    {
        if (o == null || o == Convert.DBNull)
            return "";
        else
            return "\"" + o.ToString().Replace("\"", "\"\"") + "\"";
    }

    #endregion
    
    	//in most cases there will be ony one DataSet but when you need more for one excel it will be helpful
        public static byte[] DataSetsToExcel(System.Data.DataSet[] dsTable, string[] sheetNames)
        {
            int maxRowNo = 10000;
            int dataRowsNo;
            int sheetsNo;

            NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();

            string sheetDefaultName;    //sheet name passed in @sheetNames for each sheet or default name
            string sheetName;           //actual sheet name used in  workbook.CreateSheet(sheetName)
            short color = NPOI.HSSF.Util.HSSFColor.BLUE.index;
            NPOI.SS.UserModel.CellStyle cellStyle = workbook.CreateCellStyle();
            NPOI.SS.UserModel.Font fnt = workbook.CreateFont();
            fnt.Color = color;
            cellStyle.SetFont(fnt);
            int sheetTotalCounter = 0;
            int sheetCounter = 0;
            for (int ids = 0; ids < dsTable.Length; ids++) //loop DataSet
            {
                System.Data.DataSet dataSet = dsTable[ids];

                for (int idt = 0; idt < dataSet.Tables.Count; idt++) //loop DataTable in DataSet
                {
                    //setup sheet name if not specified (Sheet1, Sheet2, etc...)
                    if (String.IsNullOrEmpty(sheetNames[sheetTotalCounter]))
                        sheetDefaultName = "Sheet" + (sheetTotalCounter + 1);
                    else
                        sheetDefaultName = sheetNames[sheetTotalCounter];
                    sheetTotalCounter++;

                    System.Data.DataTable dataTable = dataSet.Tables[idt];
                    int columnsCount = dataTable.Columns.Count;

                    if (columnsCount != 0)
                    {

                        dataRowsNo = dataTable.Rows.Count; 
                        sheetsNo = (int)Math.Ceiling(dataRowsNo / (maxRowNo * 1.0));
                        sheetsNo = sheetsNo == 0 ? 1 : sheetsNo;
                        sheetCounter = 0;
                        //loop sheets - create more sheet if number of rows in a given @dataTable is greated than @maxRowNo
                        for (int sdx = 0; sdx < sheetsNo; sdx++) 
                        {
                            int rowNo = 0;
                            
                            sheetCounter++;
                            sheetName = sheetsNo > 1 ? sheetDefaultName + "_" + sheetCounter : sheetDefaultName;
                            NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet(sheetName);

                            NPOI.SS.UserModel.Row newRow = sheet.CreateRow(rowNo++);

                            //create header row
                            string[] arr = new String[columnsCount];
                            for (int ic = 0; ic < columnsCount; ic++)
                            {
                                NPOI.SS.UserModel.Cell newCell = newRow.CreateCell(ic);
                                newCell.SetCellValue(dataTable.Columns[ic].ColumnName);
                                newCell.CellStyle = cellStyle;
                            }
                            //create rows
                            int startRowIndex = sdx * maxRowNo;
                            int endRowIndex = (maxRowNo * (sdx + 1));
                            endRowIndex = Math.Min(endRowIndex, dataRowsNo);
                            for (int r = startRowIndex; r < endRowIndex; r++)
                            {
                                System.Data.DataRow row = dataTable.Rows[r];
                                newRow = sheet.CreateRow(rowNo++);
                                for (int ic = 0; ic < columnsCount; ic++)
                                {
                                    NPOI.SS.UserModel.Cell newCell = newRow.CreateCell(ic);
                                    newCell.SetCellValue(String.Format("{0}", row[ic]));
                                }
                            }

                            //foreach (System.Data.DataRow row in dataTable.Rows)
                            //{
                            //    newRow = sheet.CreateRow(rowNo++);
                            //    for (int ic = 0; ic < columnsCount; ic++)
                            //    {
                            //        NPOI.SS.UserModel.Cell newCell = newRow.CreateCell(ic);
                            //        newCell.SetCellValue(String.Format("{0}", row[ic]));
                            //    }
                            //}
                            for (int ic = 0; ic < columnsCount; ic++)
                            {
                                sheet.AutoSizeColumn(ic);
                            }
                        }
                    }
                }
            }

            System.IO.MemoryStream stream = new System.IO.MemoryStream();
            workbook.Write(stream);
            return stream.ToArray();
        }
    }
    
    public static byte[] LinqEntitiesToExcelUserOrder<T>(ICollection<T> objects, IDictionary<string, object> columnsDictionary, string dateFormat)
    {
        int maxRowNo = MaxExcelSheetRows;
        T[] dataArr = objects.ToArray();
        int dataRowsNo = dataArr.Length;
        int sheetsNo = (int)Math.Ceiling(dataRowsNo / (maxRowNo * 1.0));

        //PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty);
        string value; ;
        double doubleResult;
        DateTime dateTimeResult;

        var propIndexes = new List<int>();
        var propNames = new List<string>();
        var propKeys = new List<string>();
        int k = 0;
        foreach (var item in columnsDictionary)
        {
            propIndexes.Add(k);
            propNames.Add(item.Value.ToString());
            propKeys.Add(item.Key);
            k++;
        }

        HSSFWorkbook workbook = new HSSFWorkbook();
        //Sheet sheet = workbook.CreateSheet("Data");

        CellStyle cellStyle = workbook.CreateCellStyle();
        NPOI.SS.UserModel.Font fnt = workbook.CreateFont();
        fnt.Boldweight = (short)FontBoldWeight.BOLD;
        cellStyle.SetFont(fnt);


        int sheetCounter = 0;
        for (int sdx = 0; sdx < sheetsNo; sdx++) //loop sheets 
        {

            Int32 rowNo = 0;
            sheetCounter++;
            Sheet sheet = workbook.CreateSheet("Sheet" + sheetCounter);
            // header
            Row headRow = sheet.CreateRow(rowNo++);
            //string[] arr = new String[propNames.Length];
            for (int i = 0; i < propNames.Count; i++)
            {
                Cell newCell = headRow.CreateCell(i);

                newCell.SetCellValue(propNames[i]);
                newCell.CellStyle = cellStyle;
            }

            //foreach (T o in objects)
            int startRowIndex = sdx * maxRowNo;
            int endRowIndex = (maxRowNo * (sdx + 1));
            endRowIndex = Math.Min(endRowIndex, dataRowsNo);
            for (int r = startRowIndex; r < endRowIndex; r++)
            {
                T o = dataArr[r];
                Row newRow = sheet.CreateRow(rowNo++);
                for (int i = 0; i < propIndexes.Count; i++)
                {
                    Cell newCell = newRow.CreateCell(i);
                    PropertyInfo p = o.GetType().GetProperty(propKeys[i]);
                    value = p == null ? null : String.Format("{0}", p.GetValue(o, null));
                    if (Double.TryParse(value, out doubleResult))
                        newCell.SetCellValue(doubleResult);
                    else if (dateFormat.IsNotNullOrEmpty() && DateTime.TryParse(value, out dateTimeResult))
                    {
                        newCell.SetCellValue(dateTimeResult.ToString(dateFormat));
                    }
                    else
                    {
                        newCell.SetCellValue(value);
                    }

                }
            }

            for (int i = 0; i < propIndexes.Count; i++)
                sheet.AutoSizeColumn(i);
        }

        MemoryStream stream = new MemoryStream();
        workbook.Write(stream);
        return stream.ToArray();
    }
}




//USING EXAMPLE
protected void btnExport_Click(object sender, EventArgs e)
{
    SetExportParams();
    var data = cda.GetMasterScheduleList(exp_dateFrom, exp_dateTo, exp_ignoreDate, exp_clientCode, exp_childCode, null, exp_includeMissed, exp_regionID, exp_countyName, exp_postCode, exp_dmlId);
    //GetExportData();
    String fileName = String.Format("{0} - {1:yyyy-MM-dd}.xls", "Master Schedule Report", DateTime.Now);
    var columns = new
    {
        ServiceDate = "Date of Service",
        VisitNo = "Visit No",
        ContractRefNo = "DML ID",
        PMStartDate = "Date received",
        ContractDescription = "Comments",
        AdditionalInformation = "Additional Infomation",
        SageAccount = "Sage Account",
        IsEnabledValidLabel = "Status",
        ParentCompanyName = "Client",
        CompanyName = "Site Reference",
        Address = "Address",
        Contact = "Contact",
        Phone = "Phone",
        Comments = "System Description",
        FireStr = "Fire List",
        LightingStr = "Lighting List",
        ExtinguisherStr = "Extinguisher List",
        RegionName = "Region",
        Area = "Area",
        ContractValuePreviusYear = "Overall Annual Prev",
        ContractValueCurrYear = "Overall Annual",
        ContractValue = "Contract value",
        Q1date = "Qtr 1",
        Q2date = "Qtr 2",
        Q3date = "Qtr 3",
        Q4date = "Qtr 4",
        VisitDurationHours = "Duration",
    };
    byte[] fileData = AFSExtensions.CustomConvert.LinqEntitiesToExcelUserOrder<GetMasterScheduleListResult>(data, columns.TransformToDictionary(), "dd/MM/yyyy");
    Response.WriteAsAttachment("application/vnd.ms-excel", fileName, fileData);
}