In Excel report generation and data templating, distinguishing titles, headers, data rows, and key metrics via font styles is essential for readability. Varying font names, sizes, colors, and typefaces helps guide the reader’s eye and emphasizes critical information. This article demonstrates how to programmatically apply rich font formatting to Excel cells using C# and the Spire.XLS for .NET library.
1. Setting Up the Development Environment
Before writing any code, add the Spire.XLS library to your project. You can install it via the NuGet Package Manager (search for Spire.XLS) or run the following command in the Package Manager Console:
Install-Package Spire.XLS
The library supports .NET Framework, .NET 5+, and other modern .NET platforms. Once installed, reference the Spire.Xls namespace to get started.
2. Key Font Properties
The ExcelFont object provides the following configurable properties for styling cell text:
| Property | Description |
|---|---|
FontName |
Font family name (e.g., Microsoft YaHei, Arial). |
Size |
Font size in points. |
Color |
RGB color value. |
IsBold / IsItalic
|
Toggle bold or italic emphasis. |
Underline |
Underline style. |
IsStrikethrough |
Toggle strikethrough. |
IsSuperscript / IsSubscript
|
Toggle superscript or subscript. |
3. Common Font Application Scenarios
3.1 Styling a Single Cell
The most basic usage—ideal for setting a title or emphasizing a single data point—is achieved via the CellRange.Style.Font property.
using Spire.Xls;
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Range["A1"].Text = "Sales Data Summary";
CellStyle style = sheet.Range["A1"].Style;
style.Font.FontName = "Microsoft YaHei";
style.Font.Size = 16;
style.Font.IsBold = true;
style.Font.Color = System.Drawing.Color.DarkBlue;
workbook.SaveToFile("BasicFontSettings.xlsx", ExcelVersion.Version2016);
}
}
3.2 Applying Different Fonts Across Cell Ranges
In practical reports, headers, data bodies, and summary rows usually follow distinct style guidelines. You can apply fonts to entire ranges in bulk.
// Header range (A2:D2)
CellRange headerRange = sheet.Range["A2:D2"];
headerRange.Style.Font.FontName = "Arial";
headerRange.Style.Font.Size = 12;
headerRange.Style.Font.IsBold = true;
headerRange.Style.Font.Color = System.Drawing.Color.White;
headerRange.Style.Color = System.Drawing.Color.SteelBlue; // Background for contrast
// Data range (A3:D6)
CellRange dataRange = sheet.Range["A3:D6"];
dataRange.Style.Font.FontName = "Arial";
dataRange.Style.Font.Size = 11;
// Summary row (A7:D7)
CellRange totalRange = sheet.Range["A7:D7"];
totalRange.Style.Font.FontName = "Arial";
totalRange.Style.Font.Size = 12;
totalRange.Style.Font.IsBold = true;
totalRange.Style.Font.Color = System.Drawing.Color.DarkRed;
3.3 Mixing Multiple Fonts Inside a Single Cell
To emphasize specific words or numbers within a cell (e.g., "Amount: 1200 RMB"), use the RichText feature. It allows you to assign independent font styles to different character segments.
sheet.Range["A9"].Text = "Note: Monthly sales increased by 15% year-on-year, achieving quarterly target";
RichText richText = sheet.Range["A9"].RichText;
// Create reusable font objects via the workbook
ExcelFont fontNormal = workbook.CreateFont();
fontNormal.FontName = "Arial";
fontNormal.Size = 11;
fontNormal.Color = Color.Black;
ExcelFont fontRed = workbook.CreateFont();
fontRed.FontName = "Arial";
fontRed.Size = 11;
fontRed.IsBold = true;
fontRed.Color = Color.Red;
ExcelFont fontItalic = workbook.CreateFont();
fontItalic.FontName = "Arial";
fontItalic.Size = 11;
fontItalic.IsItalic = true;
fontItalic.Color = Color.Green;
// Apply a default style to the entire text first
richText.SetFont(0, 22, fontNormal);
// Override specific segments (startIndex, length)
richText.SetFont(0, 3, fontNormal); // "Note:" (indices 0–2)
richText.SetFont(12, 3, fontRed); // "15%" (indices 12–14)
richText.SetFont(16, 6, fontItalic); // "achieving quarterly target" (indices 16–21)
Note: The
SetFontmethod takes a zero-based start index and a character length.
3.4 Bulk Styling for Entire Rows or Columns
To apply a uniform style across a full row or column without iterating through individual cells, target the Rows or Columns collection directly.
// Style the entire first row
sheet.Rows[0].Style.Font.FontName = "Microsoft YaHei";
sheet.Rows[0].Style.Font.Size = 14;
// Style the entire third column
sheet.Columns[2].Style.Font.FontName = "Arial Narrow";
sheet.Columns[2].Style.Font.Size = 10;
4. Complete Code Example
The following comprehensive example combines all the techniques above into a fully runnable demo:
using Spire.Xls;
using System.Drawing;
namespace ExcelFontDemo
{
class Program
{
static void Main(string[] args)
{
using (Workbook workbook = new Workbook())
{
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Sales Report";
// 1. Title
sheet.Range["A1"].Text = "Product Sales Summary - Q2 2024";
sheet.Range["A1:D1"].Merge();
sheet.Range["A1"].Style.Font.FontName = "Microsoft YaHei";
sheet.Range["A1"].Style.Font.Size = 18;
sheet.Range["A1"].Style.Font.IsBold = true;
sheet.Range["A1"].Style.Font.Color = Color.DarkSlateGray;
sheet.Range["A1"].HorizontalAlignment = HorizontalAlignType.Center;
sheet.SetRowHeight(1, 25);
// 2. Headers
string[] headers = { "Product Name", "Quantity Sold", "Unit Price (RMB)", "Sales Amount (RMB)" };
for (int i = 0; i < headers.Length; i++)
{
sheet.Range[2, i + 1].Text = headers[i];
}
sheet.Range["A2:D2"].Style.Font.FontName = "Arial";
sheet.Range["A2:D2"].Style.Font.Size = 12;
sheet.Range["A2:D2"].Style.Font.IsBold = true;
sheet.Range["A2:D2"].Style.Font.Color = Color.White;
sheet.Range["A2:D2"].Style.Color = Color.Teal;
// 3. Data rows (using InsertArray)
object[,] data = {
{ "Product A", 120, 89.9, 10788 },
{ "Product B", 256, 45.5, 11648 },
{ "Product C", 89, 199.0, 17711 }
};
sheet.InsertArray(data, 3, 1);
sheet.Range["A3:D5"].Style.Font.FontName = "Arial";
sheet.Range["A3:D5"].Style.Font.Size = 11;
// 4. Summary row
sheet.Range["A6"].Text = "Total";
sheet.Range["D6"].Formula = "=SUM(D3:D5)";
sheet.Range["A6:D6"].Style.Font.FontName = "Arial";
sheet.Range["A6:D6"].Style.Font.Size = 12;
sheet.Range["A6:D6"].Style.Font.IsBold = true;
sheet.Range["A6:D6"].Style.Font.Color = Color.DarkRed;
// 5. Remarks with RichText
string remark = "Note: Red-highlighted data indicates over-achievement; quarterly target completion rate is 112%";
sheet.Range["A8"].Text = remark;
RichText richText = sheet.Range["A8"].RichText;
ExcelFont fontGray = workbook.CreateFont();
fontGray.FontName = "Arial";
fontGray.Size = 10;
fontGray.Color = Color.Gray;
ExcelFont fontRed = workbook.CreateFont();
fontRed.FontName = "Arial";
fontRed.Size = 10;
fontRed.Color = Color.Red;
fontRed.IsBold = true;
ExcelFont fontGreen = workbook.CreateFont();
fontGreen.FontName = "Arial";
fontGreen.Size = 10;
fontGreen.Color = Color.Green;
fontGreen.IsBold = true;
richText.SetFont(0, remark.Length, fontGray);
richText.SetFont(2, 3, fontRed);
richText.SetFont(17, 5, fontGreen);
// Auto-fit columns for a clean look
sheet.AllocatedRange.AutoFitColumns();
workbook.SaveToFile("ExcelMultiFontExample.xlsx", ExcelVersion.Version2016);
}
}
}
}
By mastering the CellRange.Style.Font property and the RichText functionality, you can address a wide variety of business requirements—from basic report formatting to advanced, visually layered data presentations. This flexible approach enables you to consistently produce clean, professional Excel outputs while keeping your C# code concise and maintainable.
Top comments (0)