ExtensionMethod.NET Home of 880 C#, Visual Basic, F# and Javascript extension methods

IList<T> to Excel file

An extension method that produce a excel file of List<T>. This would be useful if you want to automatically generate a Excel out of any other primative data type collection I provided 1 overloads of this method, that accepts a Path as string to save excel file to location on disk.

Source

/// <summary>
        /// Extension method to write list data to excel.
        /// </summary>
        /// <typeparam name="T">Ganeric list</typeparam>
        /// <param name="list"></param>
        /// <param name="PathToSave">Path to save file.</param>
        public static void ToExcel<T>(this List<T> list, string PathToSave)
        {
            #region Declarations

            if (string.IsNullOrEmpty(PathToSave))
            {
                throw new Exception("Invalid file path.");
            }
            else if(PathToSave.ToLower().Contains("")==false)
            {
                throw new Exception("Invalid file path.");
            }

            if (list == null)
            {
                throw new Exception("No data to export.");
            }
          
            Excel.Application excelApp = null;
            Excel.Workbooks books = null;
            Excel._Workbook book = null;
            Excel.Sheets sheets = null;
            Excel._Worksheet sheet = null;
            Excel.Range range = null;
            Excel.Font font = null;
            // Optional argument variable
            object optionalValue = Missing.Value;

            string strHeaderStart = "A2";
            string strDataStart = "A3";
            #endregion

            #region Processing
            
            
            try
            {
                #region Init Excel app.
                
                
                excelApp = new Excel.Application();
                books = (Excel.Workbooks)excelApp.Workbooks;
                book = (Excel._Workbook)(books.Add(optionalValue));
                sheets = (Excel.Sheets)book.Worksheets;
                sheet = (Excel._Worksheet)(sheets.get_Item(1));

                #endregion

                #region Creating Header


                Dictionary<string, string> objHeaders = new Dictionary<string, string>();

                PropertyInfo[] headerInfo = typeof(T).GetProperties();


                foreach (var property in headerInfo)
                {
                    var attribute = property.GetCustomAttributes(typeof(DisplayNameAttribute), false)
                                            .Cast<DisplayNameAttribute>().FirstOrDefault();
                    objHeaders.Add(property.Name, attribute == null ?
                                        property.Name : attribute.DisplayName);
                }


                range = sheet.get_Range(strHeaderStart, optionalValue);
                range = range.get_Resize(1, objHeaders.Count);

                range.set_Value(optionalValue, objHeaders.Values.ToArray());
                range.BorderAround(Type.Missing, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);

                font = range.Font;
                font.Bold = true;
                range.Interior.Color = Color.LightGray.ToArgb();

                #endregion

                #region Writing data to cell
                
                
                int count = list.Count;
                object[,] objData = new object[count, objHeaders.Count];

                for (int j = 0; j < count; j++)
                {
                    var item = list[j];
                    int i = 0;
                    foreach (KeyValuePair<string, string> entry in objHeaders)
                    {
                        var y = typeof(T).InvokeMember(entry.Key.ToString(), BindingFlags.GetProperty, null, item, null);
                        objData[j, i++] = (y == null) ? "" : y.ToString();
                    }
                }


                range = sheet.get_Range(strDataStart, optionalValue);
                range = range.get_Resize(count, objHeaders.Count);

                range.set_Value(optionalValue, objData);
                range.BorderAround(Type.Missing, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);

                range = sheet.get_Range(strHeaderStart, optionalValue);
                range = range.get_Resize(count + 1, objHeaders.Count);
                range.Columns.AutoFit();

                #endregion

                #region Saving data and Opening Excel file.
                         

                if (string.IsNullOrEmpty(PathToSave) == false)
                    book.SaveAs(PathToSave);

                excelApp.Visible = true;
               
                #endregion

                #region Release objects

                try
                {
                    if (sheet != null)
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                    sheet = null;

                    if (sheets != null)
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
                    sheets = null;

                    if (book != null)
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
                    book = null;

                    if (books != null)
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
                    books = null;

                    if (excelApp != null)
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;
                }
                catch (Exception ex)
                {
                    sheet = null;
                    sheets = null;
                    book = null;
                    books = null;
                    excelApp = null;
                }
                finally
                {
                    GC.Collect();
                }

                #endregion
            }
            catch (Exception ex)
            {
                throw ex;
            }

            #endregion
        }

Example

public class DummyData
    {

[DisplayName("# ID")]
public string ID { get; set; }

[DisplayName("Full Name")]
public string Name { get; set; }


public string Address { get; set; }


public string Age { get; set; }


    }



private void button1_Click(object sender, EventArgs e)
{
    List<DummyData> dataList = new List<DummyData>();

    for (int i = 0; i < 10; i++)
    {
        dataList.Add(new DummyData()
        {
            Address = "Addresss " + i,
            Age = "Age " + i,
            ID = "ID " + i,
            Name = "Name " + i
        });
    }

    dataList.ToExcel("C:\\test.xls");
}

Author: Saurabh dubey

Submitted on: 5 dec. 2011

Language: C#

Type: System.Collections.Generic.IList<T>

Views: 15878