Thursday, July 11, 2013

WPF - Export to excel using EPPlus

Following code helpful to create a excel file from WPF

Prerequesties:-
EPPlus DLL   (Ref: http://epplus.codeplex.com/releases/view/89923)

Step 1: Create a WPF project

Step 2:  Add reference of EPPlus DLL

Step 3: Following is the sample class to export

        public class Employee
        {
            public string EmployeeName;
            public int EmployeeAge;
            public string Designation;
        }


Step 4: Create Data with values

            List<Employee> lstEmployee = new List<Employee>();
            lstEmployee.Add(new Employee() { EmployeeName = "Balaji", EmployeeAge = 16, Designation = "DE" });
            lstEmployee.Add(new Employee() { EmployeeName = "prasad", EmployeeAge = 16, Designation = "TA" });

           
Step 5: Create Header Data for excel
           List<string> lstHeader = new List<string>() { "Employee Name", "Employee Age", "Designation" };
Step 6: Create Excel Package and add Header and Column details

            ExcelPackage pck = new ExcelPackage();
            pck.Workbook.Properties.Author = "Balajiprasad";
            pck.Workbook.Properties.Title = "EPPlus in WPF";
            pck.Workbook.Properties.Company = "For Aditi Technologies";

            var ws = pck.Workbook.Worksheets.Add("Employee Details");

            //Header Section
            for (int i = 0; i < lstHeader.Count; i++)
            {
                ws.Cells[1, i + 1].Value = lstHeader[i];
                ws.Cells[1, i + 1].Style.Font.Bold = true;
            }

            //Column Value Section
            for (int i = 0; i < lstEmployee.Count; i++)
            {
                ws.Cells[i + 2, 1].Value = lstEmployee[i].EmployeeName;
                ws.Cells[i + 2, 2].Value = lstEmployee[i].EmployeeAge;
                ws.Cells[i + 2, 3].Value = lstEmployee[i].Designation;
            }


Step 7: Save as the byte array as excel

            byte[] fileText = pck.GetAsByteArray();

            SaveFileDialog dialog = new SaveFileDialog()
            {
                Filter = "Excel Worksheets (*.xlsx)|*.xlsx"
            };

            if (dialog.ShowDialog() == true)
            {
                File.WriteAllBytes(dialog.FileName, fileText);
            }


Following is the entire code

        private void GenerateExcel()
        {
            List<Employee> lstEmployee = new List<Employee>();
            lstEmployee.Add(new Employee() { EmployeeName = "Balaji", EmployeeAge = 16, Designation = "DE" });
            lstEmployee.Add(new Employee() { EmployeeName = "prasad", EmployeeAge = 16, Designation = "TA" });

            List<string> lstHeader = new List<string>() { "Employee Name", "Employee Age", "Designation" };


            ExcelPackage pck = new ExcelPackage();
            pck.Workbook.Properties.Author = "Balajiprasad";
            pck.Workbook.Properties.Title = "EPPlus in WPF";
            pck.Workbook.Properties.Company = "For Aditi Technologies";

            var ws = pck.Workbook.Worksheets.Add("Employee Details");

            //Header Section
            for (int i = 0; i < lstHeader.Count; i++)
            {
                ws.Cells[1, i + 1].Value = lstHeader[i];
                ws.Cells[1, i + 1].Style.Font.Bold = true;
            }

            //Column Value Section
            for (int i = 0; i < lstEmployee.Count; i++)
            {
                ws.Cells[i + 2, 1].Value = lstEmployee[i].EmployeeName;
                ws.Cells[i + 2, 2].Value = lstEmployee[i].EmployeeAge;
                ws.Cells[i + 2, 3].Value = lstEmployee[i].Designation;
            }

            byte[] fileText = pck.GetAsByteArray();

            SaveFileDialog dialog = new SaveFileDialog()
            {
                Filter = "Excel Worksheets (*.xlsx)|*.xlsx"
            };

            if (dialog.ShowDialog() == true)
            {
                File.WriteAllBytes(dialog.FileName, fileText);
            }

        }


public class Employee
        {
            public string EmployeeName;
            public int EmployeeAge;
            public string Designation;
        }