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

 

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:

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.CopyPropertiesFrom(normalStyle);
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;
 
worksheet.Shapes.Add(image);

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.

Download a free trial of UI for WPF


About the Author

Deyan Yosifov

Deyan Yosifov is Software Developer in Telerik XAML Team

Related Posts

Comments

Comments are disabled in preview mode.