Save a List Data to Excel Dot Net (.Net) c#

Code Snippets 4 U
public class SaveToEx<T> where T : new()
    {
        public void SaveToExcel(List<T> listOfOutput, string path)
        {
            // check if path is correct
            if (!IsValidPath(path)) return;

            List<string> columnNames = new List<string>();

            // get the property names from model (or any object) and save them for column names
            T tObj = new T();
            PropertyInfo[] pInfo = tObj.GetType().GetProperties(); // using reflection
            pInfo.ToList().ForEach(item => columnNames.Add(item.Name));

            // save the data to excel
            Application excel = new Application();

            // Add a new workbook
            Workbooks wbs = excel.Workbooks;
            wbs.Add();

            _Worksheet workSheet = excel.ActiveSheet;

            char columnIndex = 'A';
            try
            {
                // create header columns
                columnNames.ForEach(item =>
                {
                    workSheet.Cells[1, columnIndex.ToString()] = item;
                    columnIndex++;
                });

                int row = 2;
                // enter data
                listOfOutput.ForEach(item =>
                {
                    columnIndex = 'A';
                    columnNames.ForEach(item2 =>
                    {
                        workSheet.Cells[row, columnIndex.ToString()] = item.GetType().GetProperty(item2).GetValue(item);
                        columnIndex++;
                    });
                    row++;
                });

                // Save this data as a file
                workSheet.SaveAs(@path);
                Console.WriteLine("Saved");
                Console.ReadKey();
            }
            catch (Exception) { }
            finally
            {
                // release all com objects memory
                excel.Quit();
                try
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
                }
                catch (Exception) { }
                excel = null;
                wbs = null;
                workSheet = null;

                GC.Collect();
            }
        }

        private bool IsValidPath(string path, bool allowRelativePaths = false)
        {
            bool isValid = true;

            try
            {
                string fullPath = Path.GetFullPath(path);

                if (allowRelativePaths)
                {
                    isValid = Path.IsPathRooted(path);
                }
                else
                {
                    string root = Path.GetPathRoot(path);
                    isValid = string.IsNullOrEmpty(root.Trim(new char[] { '\\', '/' })) == false;
                }
            }
            catch (Exception)
            {
                isValid = false;
            }

            return isValid;
        }
    }

Example Usage :

 class Program
    {
        static void Main(string[] args)
        {
            List<Output> outputs = new List<Output> { new Output { UnitNumber = 1 }, new Output { UnitNumber = 1 }, new Output { UnitNumber = 1 }, };

            SaveToEx<Output> saveToEx = new SaveToEx<Output>();
            saveToEx.SaveToExcel(outputs, @"E:\output.xlsx");
        }
    }

Where Output Class is :

public class Output
    {
        public int UnitNumber { get; set; }
        public string ContentId { get; set; }
        public string Url { get; set; }
        public string title { get; set; }
        public string statusCode { get; set; }
        public string name { get; set; }
    }

Leave a Reply

Your email address will not be published. Required fields are marked *

31 − twenty six =