This is part 3 of the “Getting Started with RadSpreadProcessing” blog series. With this last blog post we want to show you how you can use the RadSpreadProcessing library to prepare a complete salary report.

In part 1 we populated a workbook with raw data. In part 2 we turned the document into a stylish report. Now we will focus on the filtering, sorting and exporting functionalities and prepare a full report.



To begin with, we will start with the
filtering feature, which allows the user to hide and show certain rows of a range, based on different criteria. For this purpose, we will use the CustomFilter class, which can contain more than one criterion. The sample code snippet below demonstrates how to load the previously created workbook and extract those employees with a salary greater than $3,000. The criterion is represented by the CustomFilterCriteria class. Note that even though the FilterValue is of type string, internally the filter will attempt to parse it.

Workbook workbook;
using (FileStream fs = new FileStream(@"..\..\StyledSpreadsheet.xlsx", FileMode.Open))
{
    XlsxFormatProvider formatProvider = new XlsxFormatProvider();
    workbook = formatProvider.Import(fs);
}
    Worksheet worksheet = workbook.Worksheets["Salary Report Q2 2014"];
CustomFilterCriteria criteria = new CustomFilterCriteria(ComparisonOperator.GreaterThan , "3000");
CustomFilter filter = new CustomFilter(0, criteria);
CellRange filterRange = new CellRange(5,4,14,4);           
worksheet.Filter.FilterRange = filterRange;         
worksheet.Filter.SetFilter(filter); 


Now, we will continue with
sorting the filtered data by “Hire Date” column. To do this we should create a ValuesSortCondition instance and specify the column index and the SortOrder. Afterward, we will use the Worksheet.SortState property to set the sort condition over the selected cell range.

ValuesSortCondition sortCondition = new ValuesSortCondition(3, Telerik.Windows.Documents.Spreadsheet.Model.Sorting.SortOrder.Ascending);
CellRange sortRange = new CellRange(6, 0, 14, 4);
worksheet.SortState.Set(sortRange,sortCondition);



The RadSpreadProcessing document model allows you to easily open and save files of different formats. Currently, the supported formats include Xlsx, Csv and Txt. The document model exposes three format providers that work with each of the mentioned formats. Now, we will use the XlsxFormatProvider to save the constructed salary report into an xlsx file. You can find the code snippet below:

IWorkbookFormatProvider formatProvider = new XlsxFormatProvider();
string filePath = @"..\..\..\FinalSpreadsheet.xlsx";
using (FileStream output = new FileStream(filePath, FileMode.Create))
{
    formatProvider.Export(this.WorkBook, output);
}


There are some cases, such as printing, when you need to display the document's content in a specific way. The
WorksheetPageSetup API allows you to specify the page orientation, scale factor, margin, print area and other print settings that will present the worksheet in the desired look. The sample code snippet below demonstrates how to set the print area to the cell range that contains data only. The print document will be scaled with factor 1.5 and the page orientation will be changed to landscape:

WorksheetPageSetup pageSetup = workbook.ActiveWorksheet.WorksheetPageSetup;
pageSetup.ScaleFactor = new System.Windows.Size(1.5, 1.5);
pageSetup.PageOrientation = PageOrientation.Landscape;
pageSetup.Margins = new PageMargins(5);
PrintArea printArea = workbook.ActiveWorksheet.WorksheetPageSetup.PrintArea;
 
printArea.SetPrintArea(new CellRange[]
{
    new CellRange(1, 1, 20, 4)
});



The last print setting that we are going to use is the WorksheetPageSetup.PageBreaks. With it, we can semantically split the document content into several pages. Here is a code example which aims to show how to split the worksheet horizontally at the fifth row:

PageBreaks pageBreaks = workbook.ActiveWorksheet.WorksheetPageSetup.PageBreaks;
pageBreaks.TryInsertHorizontalPageBreak(5, 0);





And that’s it. We have constructed a completely styled salary report from scratch and saved it as an xlsx file using the RadSpreadProcessing library.

Hope you liked it. If you have any comments regarding RadSpreadProcessing, let us know in the comment section below and we will do our best to get back to you.

To see RadSpreadProcessing in action, download a free 30-day trial of Telerik UI for WinForms

Happy coding!



About the Author

Desislava Yordanova

Desislava Yordanova is currently a support officer in the Telerik UI for WinForms team. She joined the company in 2013 after graduating from the Telerik Academy. 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

Comments are disabled in preview mode.