Exporting to excel from a custom class object using C#.NET

We may face a scenario to export the data of custom class object to excel using InterOp.You can get the property name's from the class object using Reflection.Create a work sheet header by reading the each propety of the class object.I have created a separate method to get the all properties into a list collection of string as given below

        ///


        /// Get the list of Properties Name into a list collection.
        ///

        ///
        ///
        public List GetPropeties(Employee objEmployee)
        {
            Type myType = objEmployee.GetType();
            IList props = new List(myType.GetProperties());
            List lstPropertiesNames = new List();

            foreach (PropertyInfo prop in props)
            {
                lstPropertiesNames.Add(prop.Name);
            }
            return lstPropertiesNames;
        }

We need to call this method to create the worksheet header's as given beloe
                List lstProperties = GetPropeties(new Employee());
                Char x = 'A';
                foreach (String eachProperty in lstProperties)
                {
                    // ------------------------------------------------
                    // Creation of header cells
                    // ------------------------------------------------
                    workSheet.Cells[1, x.ToString()] = eachProperty;
                    x = (Char)(Convert.ToUInt32(x) + 1);
                }

               
The complete source code to export to excel is given below
        public void ExportToExcel(List objEmployee)
        {
            // Load Excel application
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

            // Create empty workbook
            excel.Workbooks.Add();

            // Create Worksheet from active sheet
            Microsoft.Office.Interop.Excel._Worksheet workSheet = excel.ActiveSheet;

            // I created Application and Worksheet objects before try/catch,
            // so that i can close them in finnaly block.
            // It's IMPORTANT to release these COM objects!!
            try
            {
                List lstProperties = GetPropeties(new Employee());
                Char x = 'A';
                foreach (String eachProperty in lstProperties)
                {
                    // ------------------------------------------------
                    // Creation of header cells
                    // ------------------------------------------------
                    workSheet.Cells[1, x.ToString()] = eachProperty;
                    x = (Char)(Convert.ToUInt32(x) + 1);
                }



                // ------------------------------------------------
                // Populate sheet with some real data from "employees" list
                // ------------------------------------------------
                int row = 2; // start row (in row 1 are header cells)
                foreach (BusinessModels.Employee emp in objEmployee)
                {
                    workSheet.Cells[row, "A"] = emp.ID;
                    workSheet.Cells[row, "B"] = emp.Name;
                    workSheet.Cells[row, "C"] = emp.Locality;
                    workSheet.Cells[row, "D"] = emp.City;
                    workSheet.Cells[row, "E"] = emp.Country;

                    row++;
                }

                // Apply some predefined styles for data to look nicely :)
                workSheet.Range["A1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatColor1);

                // Define filename
                string fileName = string.Format(@"{0}\ExcelData.xlsx", Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory));

                if (File.Exists(fileName))
                {
                    File.Delete(fileName);
                }
                // Save this data as a file
                workSheet.SaveAs(fileName);

                // Display SUCCESS message
                ExcelMessages.ErrorMessage = (string.Format("The file '{0}' is saved successfully!", fileName));
            }
            catch (Exception exception)
            {
                throw new Exception("There was a PROBLEM saving Excel file!\n" + exception.Message);
            }
            finally
            {
                // Quit Excel application
                excel.Quit();

                // Release COM objects (very important!)
                if (excel != null)
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

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

                // Empty variables
                excel = null;
                workSheet = null;

                // Force garbage collector cleaning
                GC.Collect();
            }
        }

Popular posts from this blog

Get the Clicked Cell Value and Column Name of GridView in ASP.NET

How to hide bootStrap popover when user click(s) outside the popover?