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!
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.