Telerik blogs

This is the second post of our “Getting Started with RadSpreadProcessing” blog series, which will demonstrate how to stylize the spreadsheet document we created previously from scratch. While in the first blog we created a workbook containing one worksheet and populated it with data, now we will demonstrate how to add a logo and apply different cell styles and number formats.

Our main goal is to transform the raw data document into a stylish report. You can see the “before/after” illustration on the screenshot below.

The first difference you will notice is the applied colors. The entire workbook has a dark background color and light forefront color. Let's apply this styling. For this purpose, we should apply a theme to the workbook. Each theme contains a color scheme and a font scheme and is represented by the DocumentTheme class. A color scheme has a unique name and contains a number of predefined colors. Every font scheme consists of a name and a number of predefined font families.

Below is a sample code snippet that shows how you can apply a theme to the workbook. Combining themes and styles provides a powerful styling mechanism for achieving a flexible look.

Workbook workbook;
using (FileStream fs = new FileStream(@"..\..\SampleFile.xlsx", FileMode.Open))
{
   XlsxFormatProvider formatProvider = new XlsxFormatProvider();
   workbook = formatProvider.Import(fs);
}
   Worksheet worksheet = workbook.Worksheets["Salary Report Q2 2014"];
ThemeColorScheme colorScheme = new ThemeColorScheme("Colors",
    System.Windows.Media.Color.FromArgb(255, 65, 65, 65),    // Background1
    System.Windows.Media.Color.FromArgb(255, 240, 240, 240), // Text1
    System.Windows.Media.Color.FromArgb(255, 0, 0, 0),       // Background2
    System.Windows.Media.Color.FromArgb(255, 255, 255, 255), // Text2
    System.Windows.Media.Color.FromArgb(255, 116, 202, 218), // Accent1
    System.Windows.Media.Color.FromArgb(255, 146, 204, 70),  // Accent2
    System.Windows.Media.Color.FromArgb(255, 241, 96, 61),   // Accent3
    System.Windows.Media.Color.FromArgb(255, 143, 145, 158), // Accent4
    System.Windows.Media.Color.FromArgb(255, 141, 119, 251), // Accent5
    System.Windows.Media.Color.FromArgb(255, 91, 119, 153),  // Accent6
    System.Windows.Media.Color.FromArgb(255, 255, 255, 255),    // Hyperlink
    System.Windows.Media.Color.FromArgb(255, 149, 79, 114)); // Followed hyperlink
 
ThemeFontScheme fontScheme = new ThemeFontScheme("Salary Report Q2 2014", "Times New Roman", "Arial");
DocumentTheme theme = new DocumentTheme("Salary Report Q2 2014", colorScheme, fontScheme);
this.WorkBook.Theme = theme;


Now we have a full palette of colors and fonts, and we are ready to apply them to our document by modifying the normal style. By default, the normal style is applied to all cells in the worksheet. Any further changes to the normal style will have an immediate effect over the spreadsheet document.

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


This is what we have so far:



Now we will style the company/department name, document title, header cells and total rows. We will create a separate style for each of them, specifying the changes that differ from the normal style.

CellStyle departmentNameStyle = this.WorkBook.Styles.Add("DepartmentNameStyle");
departmentNameStyle.FontFamily = new ThemableFontFamily(ThemeFontType.Major);
departmentNameStyle.FontSize = UnitHelper.PointToDip(48);
departmentNameStyle.HorizontalAlignment = RadHorizontalAlignment.Left;
 
CellStyle salariesPeriodStyle = this.WorkBook.Styles.Add("SalariesPeriodStyle");
salariesPeriodStyle.FontFamily = new ThemableFontFamily("Segoe UI Light");
salariesPeriodStyle.FontSize = UnitHelper.PointToDip(20);
salariesPeriodStyle.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);


Before applying the styles we just created, we will insert a hyperlink associated with the company/department title. For this purpose, we should create a
HyperlinkInfo object and specify a cell range that will contain the hyperlink. Afterward, we will add it to the Worksheet.Hyperlinks collection:

HyperlinkInfo webAddress = HyperlinkInfo.CreateHyperlink("http://www.telerik.com/products/winforms.aspx", "Telerik UI for WinForms");
CellIndex a1Index = new CellIndex(1, 1);
worksheet.Hyperlinks.Add(a1Index, webAddress);


Our new styles are ready. Let’s apply them to the respective cells:

worksheet.Cells[1, 1, 1, 4].SetStyleName("DepartmentNameStyle");
worksheet.Cells[2, 1, 3, 4].SetStyleName("SalariesPeriodStyle");
worksheet.Cells[5, 1, 5, 4].SetStyleName("ColumnHeadersStyle");
worksheet.Cells[16, 1, 18, 4].SetStyleName("DepartmentTotalStyle");
worksheet.Cells[19, 1, 19, 4].SetStyleName("TotalStyle");


Additionally, we will adjust the columns' width to fit the available content in the cells. For this purpose, we use the
SetWidth method, which accepts ColumnWidth as a parameter.

worksheet.Columns[1].SetWidth(new ColumnWidth(180, true));
worksheet.Columns[2].SetWidth(new ColumnWidth(150, true));
worksheet.Columns[3].SetWidth(new ColumnWidth(100, true));
worksheet.Columns[4].SetWidth(new ColumnWidth(230, true));


Here's the result:



Let’s continue with formatting data in the “Hire Date” and “Salary” columns. Doing so provides a better way to present the data, displaying dates as d/MMM/yyyy and salary with the currency symbol. We will then create a CellValueFormat instance for each of the columns to be formatted.

string shortDateFormat = "d/MMM/yyyy";
worksheet.Cells[6, 3, 14, 3].SetFormat(new CellValueFormat(shortDateFormat));
 
string accountingFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)";
worksheet.Cells[6, 4, 19, 4].SetFormat(new CellValueFormat(accountingFormat));



 

The last step is to add a company/department logo on the left side of the “Salaries Report” title. To achieve it, we should use the FloatingImage object and associate it with the desired cell. You can modify the image position by modifying the horizontal and vertical offset. Additionally, we need to specify the image height and width and add it to the Worksheet.Shapes collection.

FloatingImage image = new FloatingImage(worksheet, new CellIndex(2, 1), 35, 10);
FileStream stream = File.Open(@"..\..\..\logo.png", FileMode.Open);
using (stream)
{
    image.ImageSource = new Telerik.Windows.Documents.Media.ImageSource(stream, "jpg");
}
image.Width = 100;
image.Height = 80;         
worksheet.Shapes.Add(image);

 

That’s it. We changed the theme of the workbook, created new styles, inserted a hyperlink and applied cell formats. At the end, we added a logo of the document and now we have a fully styled “Salary Report."

Do not miss the next blog to learn more about the filtering and sorting options of RadSpreadProcessing, and how to protect your document and export it to Excel and as a .pdf file. 

Happy coding!




About the Author

Desislava Yordanova

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.

Related Posts

Comments

Comments are disabled in preview mode.