Telerik blogs

This is the final part of the blog series that shows how to use RadSpreadProcessing in a complete Spreadsheet scenario. In parts 1 and 2 we created an Expense report from scratch, entered a document’s data and beautified the content using workbook themes, styles and images. In case you missed the previous posts, make sure to catch up and read Part 1 and Part 2, or download the source code from the SDK example.

In this post, our aim is to generate PDF reports that contain the expenses of each department individually. So, we will produce three documents: Sales, Marketing and Engineering reports. To achieve this, we can filter the worksheet by different criteria and export it to PDF files.

Different worksheet filters applied



Let’s get started!

Before we apply any filters, we need to specify the range of cells to be filtered. In our case, this is the range between the column headers row and the total expenses row. After setting this range to the worksheet FilterRange property, we should apply a ValuesCollectionFilter to the first column of our range. This type of filter is used  to specify the values you want visible. For example, to show only the expenses of the Sales department and the total rows, you need to add the values “Sales,” “Sales Expenses” and “Total Expenses” to the ValuesCollectionFilter instance. As a result, all rows that contain different values in the department column will be hidden. Here is how our document looks with the filter applied:

Sales department filter applied.



To filter easily by any department name, we can create a FilterByDepartment() method. The following code shows a sample implementation:

public void FilterByDepartment(string departmentName)
{
    worksheet.Filter.FilterRange = new CellRange(5, 1, 31, 4);
 
    string[] valuesToShow = new string[]
    {
        departmentName,
        String.Format("{0} Expenses", departmentName),
        "Total Expenses"
    };
 
    IFilter departmentFilter = new ValuesCollectionFilter(0, valuesToShow, true);
 
    worksheet.Filter.SetFilter(departmentFilter);
}

If later we want to clear the filter and see all document rows, we can set the filter range to null. Resetting the range will remove all applied filters and display previously hidden rows.

worksheet.Filter.FilterRange = null;

We already know how to display only the expenses of a single department. Now, how can we export this data to PDF documents? Well, with RadSpreadProcessing this really is a piece of cake.

First, set the page size and the centering option of the worksheet using the WorksheetPageSetup property. The next snippet sets the size of the pages to A4 and makes the page content centered horizontally.

worksheet.WorksheetPageSetup.PaperType = PaperTypes.A4;
worksheet.WorksheetPageSetup.CenterHorizontally = true;

Using the PdfFormatProvider, we can pass a file stream and export the report to a PDF file. The following method can be used to save a file with a specific name:

public void ExportToPdf(string fileName)
{
    using (Stream fileStream = new FileStream(fileName, FileMode.OpenOrCreate))
    {
        PdfFormatProvider provider = new PdfFormatProvider();
        provider.Export(workbook, fileStream);
    }
}

That's it!  We have all we need to create expense reports for each department. Now, we can apply filters and export the workbook:

FilterByDepartment("Sales");
ExportToPdf("SalesExpenses.pdf");
 
FilterByDepartment("Marketing");
ExportToPdf("MarketingExpenses.pdf");
 
FilterByDepartment("Engineering");
ExportToPdf("EngineeringExpenses.pdf");
 
this.worksheet.Filter.FilterRange = null;

The result from the code above is three separate PDF files with the file names: “SalesExpenses.pdf,” “MarketingExpenses.pdf” and “EngineeringExpenses.pdf.” Each of the files contains the expense data for the corresponding department. The result PDF reports may be seen in the image below:

Exported PDF reports with RadSpreadProcessing




In this blog post we learned how to filter and export the contents of the worksheet to PDF. The combination of powerful spreadsheet features with the UI-independent PDF export makes RadSpreadProcessing a perfect solution for both client- and server-side scenarios.

If you need a more detailed view on the source code of this blog post series, please download it from our SDK repository here.

Stay tuned for more RadSpreadProcessing tips and tricks!

Download a free trial of UI for WPF


Deyan Yosifov 164x164
About the Author

Deyan Yosifov

Deyan is an architect, principal software developer and mathematics enthusiast. He joined the Telerik team in 2013 and has since participated in the development of several different projects—Document Processing Libraries, RadPdfViewer and RadSpreadProcessing WPF controls, and most recently in Telerik AR/VR. He is passionate about 3D technologies and loves solving challenging problems.

Related Posts

Comments

Comments are disabled in preview mode.