SpreadProcessing - Complete Solution to Create, Modify and Export Spreadsheets - Part II

This is part 2 of blog series that aim to give you a kickoff and show how to use RadSpreadProcessing like a pro. While in part 1 we created a workbook, entered all the expense data and set formulas for the total rows, now we will focus on applying styles, using number formats, adding images. In case you have missed the first blog post, don’t worry. You can catch up by going through the blog post 1 here or download the source code from our SDK example.

Our aim for this post is to transform the raw data document into a stylish report. Here is how we will change the worksheet:

Ready?

One conspicuous difference between the two documents above is the colors, so let’s start by setting appropriate colors. Specifically, let’s make sure our document has a dark background and light fore color. To achieve this, we need to set a theme of the workbook and create styles that fit our needs. A theme serves as a palette of colors and fonts that will be used throughout the workbook, while a style applies the colors and fonts of the theme to the cells. The combination of themes and styles provide a powerful styling mechanism that allow you to create flexible look and feel. So, let’s set a custom theme that specify the default colors and fonts we will use throughout the document:

Now that we have the full palette of colors and fonts, let’s apply them to our document. One way to achieve the desired result is through modifying the Normal style. Since all cells in the worksheet are by default applied the Normal style, any changes done to the style will affect the whole worksheet. Thus, setting the font size or font family of the Normal style will have immediate effect over the worksheet.

Through setting the theme and tuning the Normal style we have achieved the following result.

Further, we need to tweak a bit the cells containing the company name and title of the document, the header cells and the total rows. To do so, we will create separate styles for each of these. Note that each of the styles need only specify the changes that differ from the Normal style.

Now that all styles are ready, let’s apply them to the cells:

The result from setting the styles may be seen in the image below:

To make the data in the Date and Amount columns more presentable, let’s format the data, so that the dates appears as d/MMM/yyyy and amounts appear with the currency symbol. To do so, we have to create two CellValueFormat instances and apply them to the columns. The following snippet illustrates how this can be achieved:

Finally, we need to add the company logo above the Expense Report title. To do so, we should create a FloatingImage object and provide the worksheet and the index of the cell it should be positioned. Also, if you would like to tune the position of the image in the worksheet, you can use the horizontal and vertical offset properties in the FloatingImage constructor. Further, we need to specify the width and height of the image and add it to the Shapes collection of the worksheet.

In this blog we changed the theme, created and applied styles and number formats, and added an image to our document. Stay tuned for blog post 3 that will show you how to use filtering and export to PDF features.

Now let's continue to part 3.