Telerik blogs

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:

draft-styled worksheets differences



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:

ThemeColorScheme colorScheme = new ThemeColorScheme("ExpenseReport",
            Color.FromArgb(255, 65, 65, 65),    // Background1
            Color.FromArgb(255, 240, 240, 240), // Text1
            Color.FromArgb(255, 0, 0, 0),       // Background2
            Color.FromArgb(255, 255, 255, 255), // Text2
            Color.FromArgb(255, 116, 202, 218), // Accent1
            Color.FromArgb(255, 146, 204, 70),  // Accent2
            Color.FromArgb(255, 241, 96, 61),   // Accent3
            Color.FromArgb(255, 143, 145, 158), // Accent4
            Color.FromArgb(255, 141, 119, 251), // Accent5
            Color.FromArgb(255, 91, 119, 153),  // Accent6
            Color.FromArgb(255, 5, 99, 193),    // Hyperlink
            Color.FromArgb(255, 149, 79, 114)); // Followed hyperlink
ThemeFontScheme fontScheme = new ThemeFontScheme("ExpenseReport", "Cambria", "Segoe UI");
DocumentTheme theme = new DocumentTheme("ExpenseReport", colorScheme, fontScheme);
this.workbook.Theme = theme;


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.

CellStyle normalStyle = this.workbook.Styles["Normal"];
normalStyle.Fill = PatternFill.CreateSolidFill(new ThemableColor(ThemeColorType.Background1));
normalStyle.FontFamily = new ThemableFontFamily(ThemeFontType.Minor);
normalStyle.FontSize = UnitHelper.PointToDip(10);
normalStyle.VerticalAlignment = RadVerticalAlignment.Center;


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

normal workbook style modified


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.

CellStyle companyNameStyle = this.workbook.Styles.Add("CompanyNameStyle");
companyNameStyle.FontFamily = new ThemableFontFamily(ThemeFontType.Major);
companyNameStyle.FontSize = UnitHelper.PointToDip(48);
companyNameStyle.HorizontalAlignment = RadHorizontalAlignment.Left;
CellStyle expensePeriodStyle = this.workbook.Styles.Add("ExpensePeriodStyle");
expensePeriodStyle.FontFamily = new ThemableFontFamily("Segoe UI Light");
expensePeriodStyle.FontSize = UnitHelper.PointToDip(20);
expensePeriodStyle.HorizontalAlignment = RadHorizontalAlignment.Right;
CellStyle columnHeadersStyle = this.workbook.Styles.Add("ColumnHeadersStyle");
columnHeadersStyle.BottomBorder = new CellBorder(CellBorderStyle.Thick, new ThemableColor(ThemeColorType.Accent2));
columnHeadersStyle.FontSize = UnitHelper.PointToDip(14);
CellStyle departmentTotalStyle = this.workbook.Styles.Add("DepartmentTotalStyle");
departmentTotalStyle.FontSize = UnitHelper.PointToDip(14);
departmentTotalStyle.FontFamily = new ThemableFontFamily("Segoe UI Light");
CellStyle totalStyle = this.workbook.Styles.Add("TotalStyle");
totalStyle.Fill = PatternFill.CreateSolidFill(new ThemableColor(ThemeColorType.Accent2));
totalStyle.FontSize = UnitHelper.PointToDip(14);
totalStyle.ForeColor = new ThemableColor(ThemeColorType.Background1);


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

worksheet.Cells[1, 1, 1, 4].SetStyleName("CompanyNameStyle");
worksheet.Cells[2, 1, 3, 4].SetStyleName("ExpensePeriodStyle");
worksheet.Cells[5, 1, 5, 4].SetStyleName("ColumnHeadersStyle");
worksheet.Cells[28, 1, 30, 4].SetStyleName("DepartmentTotalStyle");
worksheet.Cells[31, 1, 31, 4].SetStyleName("TotalStyle");


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

workbook styles applied


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:

string shortDateFormat = "d MMM yyyy";
worksheet.Cells[6, 3, 31, 3].SetFormat(new CellValueFormat(shortDateFormat));
string currencyFormat = "$#,##0.00";
worksheet.Cells[6, 4, 31, 4].SetFormat(new CellValueFormat(currencyFormat));

number formats applied to worksheet cells


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.

FloatingImage image = new FloatingImage(worksheet, new CellIndex(1, 4), 25, 10);
using (Stream stream = GetResourceStream("Resources/MyCompanyLogo.jpg"))
    image.ImageSource = new Telerik.Windows.Documents.Media.ImageSource(stream, "jpg");
image.Width = 65;
image.Height = 65;

adding floating image in 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.

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 are disabled in preview mode.