New to Telerik Document Processing? Download free 30-day trial

Generating Excel Documents from IEnumerable Collections

Environment

Version Product Author
2024.2.426 RadSpreadProcessing Desislava Yordanova

Description

This article demonstrates a sample approach how to generate Excel documents from IEnumerable collections.

Solution

RadSpreadProcessing is the perfect fit for the requirement. It is possible to iterate a collection of any custom objects and populate the worksheet's cells with the values coming from the respective fields in the custom objects. A good example how to do it is available in the following code snippet which fills the data in a worksheet from a collection of Employee objects. This approach can be adapted to any custom collection that can be obtained as an input.

        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            Worksheet worksheet = workbook.Worksheets.Add();
            worksheet.Name = "Salary Report Q2 2024";
            worksheet.Cells[5, 1].SetValue("Employee's name");
            worksheet.Cells[5, 2].SetValue("Job Position");
            worksheet.Cells[5, 3].SetValue("Hire Date");
            worksheet.Cells[5, 4].SetValue("Salary");
            List<Employee> employees = PopulateWithData();

            //Let’s fill the document with the employee data:

            int startRowIndex = 6;
            for (int i = 0; i < employees.Count; i++)
            {
                worksheet.Cells[startRowIndex + i, 1].SetValue(employees[i].Name);
                worksheet.Cells[startRowIndex + i, 2].SetValue(employees[i].JobPosition.ToString());
                worksheet.Cells[startRowIndex + i, 3].SetValue(employees[i].HireDate);
                worksheet.Cells[startRowIndex + i, 4].SetValue(employees[i].Salary);
            }

            //Add a total salary row for each job position and one total row for all salaries. 
            worksheet.Cells[16, 1].SetValue("Developers' salaries");
            worksheet.Cells[17, 1].SetValue("QAs' salaries");
            worksheet.Cells[18, 1].SetValue("Managers' salaries");
            worksheet.Cells[19, 1].SetValue("Total salaries");

            worksheet.Cells[16, 4].SetValue("=SumIf(C7:C15,\"" + JobPosition.Developer.ToString() + "\",E7:E15)");
            worksheet.Cells[17, 4].SetValue("=SumIf(C7:C15,\"" + JobPosition.QA.ToString() + "\",E7:E15)");
            worksheet.Cells[18, 4].SetValue("=SumIf(C7:C15,\"" + JobPosition.Manager.ToString() + "\",E7:E15)");
            worksheet.Cells[19, 4].SetValue("=Sum(E7:E15)");

            //Add a document title. 
            CellSelection departmentNameCells = worksheet.Cells[1, 1, 1, 4];
            departmentNameCells.Merge();
            departmentNameCells.SetValue("Telerik Document Processing");
            departmentNameCells.SetHorizontalAlignment(RadHorizontalAlignment.Left);

            CellSelection salaryReportCells = worksheet.Cells[2, 1, 2, 4];
            salaryReportCells.Merge();
            salaryReportCells.SetValue("Salaries Report");
            salaryReportCells.SetHorizontalAlignment(RadHorizontalAlignment.Right);

            CellSelection periodCells = worksheet.Cells[3, 1, 3, 4];
            periodCells.Merge();
            periodCells.SetValue("for the Q2 2024 period");
            periodCells.SetHorizontalAlignment(RadHorizontalAlignment.Right);

            worksheet.Columns[worksheet.UsedCellRange].AutoFitWidth();

            IWorkbookFormatProvider formatProvider = new XlsxFormatProvider();
            string filePath = @"FinalSpreadsheet.xlsx";
            using (FileStream output = new FileStream(filePath, FileMode.Create))
            {
                formatProvider.Export(workbook, output);
            }
            Process.Start(new ProcessStartInfo() { FileName = filePath, UseShellExecute = true });
        } 
        private static List<Employee> PopulateWithData()
        {
            List<Employee> employees = new List<Employee>();
            employees.Add(new Employee("Michael Brown", JobPosition.Developer, new DateTime(2005, 1, 20), 3400));
            employees.Add(new Employee("James Hammer", JobPosition.Manager, new DateTime(2001, 2, 14), 4800));
            employees.Add(new Employee("Amanda Johns", JobPosition.QA, new DateTime(2008, 7, 30), 2600));
            employees.Add(new Employee("Daniel Bolton", JobPosition.Developer, new DateTime(2012, 4, 24), 2900));
            employees.Add(new Employee("Lucas Smith", JobPosition.Manager, new DateTime(2007, 2, 12), 4100));
            employees.Add(new Employee("George Stones", JobPosition.QA, new DateTime(2009, 5, 6), 2850));
            employees.Add(new Employee("Victoria Sloane", JobPosition.Manager, new DateTime(2004, 8, 31), 3150));
            employees.Add(new Employee("Patrick Holmes", JobPosition.Developer, new DateTime(2001, 9, 18), 4200));
            employees.Add(new Employee("Thomas Porter", JobPosition.Developer, new DateTime(2006, 4, 12), 3450));
            return employees;
        }

        public class Employee
        {
            public string Name { get; set; }

            public JobPosition JobPosition { get; set; }

            public DateTime HireDate { get; set; }

            public double Salary { get; set; }

            public Employee(string name, JobPosition jobPosition, DateTime hireDate, double salary)
            {
                this.Name = name;
                this.JobPosition = jobPosition;
                this.HireDate = hireDate;
                this.Salary = salary;
            }
        }

        public enum JobPosition
        {
            Developer,
            QA,
            Manager
        }

See Also

In this article