Getting Started with RadSpreadProcessing Volume 1
With Q2 2014 (version 2014.2.617), we launched a document processing library in Telerik UI for WinForms that gives you the ability to create, manipulate and export files from the most common spreadsheet file formats (xlsx, cvs, txt). If you are not familiar with the RadSpreadProcessing library, this post is for you. It is the first of three blogs, aiming to demonstrate key features of the library so you can to create the desired document. First, we will focus on populating the document with data by creating an employees salary report.
To use the model of the RadSpreadProcessing library in your project, you need to add references to the following assemblies:
- Telerik.Windows.Documents.Core.dll
- Telerik.Windows.Documents.Spreadsheet.dll
- Telerik.Windows.Maths.dll
The following assemblies are required to be able to export to Xlsx and PDF formats:
- Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.dll
- Telerik.Windows.Documents.Spreadsheet.FormatProviders.Pdf.dll
- Telerik.Windows.Zip.dll
We will focus on creating a Workbook, composed of one Worksheet, and fill it with data. To achieve a full employees salary report, we should set up the necessary formulas and cell formats as well.
Let’s start with the detailed employee information we have. The following code snippet illustrates the input data we are going to insert into the document:
List<Employee> employees = new List<Employee>();private void PopulateWithData(List<Employee> employees){ 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));}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}
Now, we are ready to create the Workbook with an appropriate name and add a Worksheet:
Workbook workbook = new Workbook();Worksheet worksheet = workbook.Worksheets.Add();worksheet.Name = "Salary Report Q2 2014";
Next, we should fill the header cells. The sample code snippet below demonstrates how to add four headers in row 6, corresponding to each property for the employee. We will leave some space for the title information and logo, which will be added later.
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");
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);}
Once all input data is entered, we are ready to add a total salary row for each job position and one total row for all salaries. To achieve it, we should use SUMIF function and specify the criteria to the value of the job description. Thus, the function will add only the salaries for the employees belonging to the specified job position group. The next code snippet shows how to calculate the total rows:
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)");
The next step is to add a document title. The code snippet below illustrates how to add the department/company title, the report title and specify the report period. We will also merge the cells to spread the data over four columns. The appropriate horizontal alignment is applied:
CellSelection departmentNameCells = worksheet.Cells[1, 1, 1, 4];departmentNameCells.Merge();departmentNameCells.SetValue("Telerik UI for Winforms");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 2014 period");periodCells.SetHorizontalAlignment(RadHorizontalAlignment.Right);
As you can see, some of the data is cropped due to insufficient column width. To display the whole data and obtain a readable document, we should use the auto fit functionality of the columns containing data. Thus, each column will determine the appropriate width for itself on a way to guarantee that no data will be cropped:
worksheet.Columns[worksheet.UsedCellRange].AutoFitWidth();
That’s it. We have just created our first spreadsheet document from scratch. Do not miss the upcoming blogs to see how you can style a document, filter it and export it to Xlsx.
Happy coding
Desislava Yordanova
Desislava Yordanova is a proactive ambassador of diligent processes and a customer-caring professional. Currently, she is a Technical Support Engineer, Principal in the Document Processing team after a successful journey in the Telerik UI for WinForms team. She joined the company in 2013. Desislava holds a master’s degree in Computer Systems and Technologies from the Technical University in Sofia. Apart from her job, she is keen on snowboarding and travelling. You can find Desislava on LinkedIn.
Related Posts
Comments
All articles
Topics
Latest Stories
in Your Inbox
Subscribe to be the first to get our expert-written articles and tutorials for developers!
All fields are required