Formatting plays an important role in making Excel spreadsheets easier to read, analyze, and present. Whether you are generating reports, invoices, financial statements, or dashboards, raw data often needs proper styling before it can be shared with end users.
In C#, Excel formatting tasks may include changing fonts, applying colors, aligning content, formatting numbers and dates, adding borders, creating tables, and configuring page layouts. Performing these tasks manually can be time-consuming, especially when dealing with large volumes of spreadsheets.
Spire.XLS for .NET provides a comprehensive set of APIs for creating, editing, formatting, and converting Excel documents without requiring Microsoft Excel to be installed. In this article, you will learn how to apply various types of formatting to Excel files in C# using Spire.XLS for .NET.
Table of Contents:
- Prepare Your C# Project for Excel Formatting
- Part 1. Format Cell Appearance
- Part 2. Format Cell Values
- Part 3. Format Ranges and Layout
- Part 4. Apply Advanced Formatting
- Part 5. Format Excel Tables and Worksheets
- Part 6. Create a Professional Report Example
- Conclusion
- FAQs
Prepare Your C# Project for Excel Formatting
Spire.XLS for .NET is a powerful Excel library that enables developers to work with XLS, XLSX, XLSM, CSV, and other spreadsheet formats programmatically. Besides formatting operations, it also supports formula calculation, chart creation, pivot tables, worksheet management, printing, and file conversion.
To install Spire.XLS for .NET, run the following NuGet command:
Install-Package Spire.XLS
Before applying formatting, load an existing Excel workbook (or create a new one) and access the worksheet you want to modify. Once all formatting operations are complete, save the result to a new Excel file using the SaveToFile() method.
using Spire.Xls;
using System.Drawing;
// Create a workbook object
Workbook workbook = new Workbook();
// Load an existing Excel file
workbook.LoadFromFile("input.xlsx");
// Get a specific worksheet
Worksheet sheet = workbook.Worksheets[0];
// Apply formatting
...
// Save the result
workbook.SaveToFile("output.xlsx", ExcelVersion.Version2016);
Note: The following examples assume that the workbook has already been loaded and the worksheet object has been obtained.
Part 1. Format Cell Appearance
Cell appearance settings control how data looks inside a worksheet. Proper formatting can significantly improve readability and help users quickly identify important information.
Format Cell Fonts
Font formatting allows you to customize the visual style of cell content. Common options include font family, font size, bold, italic, underline, and font color. These settings are frequently used for report titles, section headers, and highlighted values.
CellStyle style = workbook.Styles.Add("FontStyle");
style.Font.FontName = "Calibri";
style.Font.Size = 14;
style.Font.IsBold = true;
style.Font.IsItalic = true;
style.Font.Underline = FontUnderlineType.Single;
style.Font.Color = Color.Blue;
sheet.Range["A1"].Text = "Formatted Text";
sheet.Range["A1"].Style = style;
Set Cell Background Colors
Background colors help distinguish different sections of a worksheet and draw attention to key cells. For example, you may use a colored header row or highlight summary data with a contrasting fill color.
sheet.Range["A2"].Text = "Background Color";
sheet.Range["A2"].Style.Color = Color.LightSkyBlue;
Align Cell Content
Excel provides horizontal alignment, vertical alignment, indentation, and text rotation options. Proper alignment improves the overall layout of a worksheet and makes tabular data easier to scan.
sheet.Range["B2"].Text = "Centered Text";
CellStyle style = sheet.Range["B2"].Style;
style.HorizontalAlignment = HorizontalAlignType.Center;
style.VerticalAlignment = VerticalAlignType.Center;
style.Rotation = 45;
sheet.SetRowHeight(2, 40);
sheet.SetColumnWidth(2, 20);
Add Cell Borders
Borders are useful for separating rows and columns and defining table structures. Depending on the scenario, you can apply borders to individual cells or entire ranges and customize their styles and colors.
CellRange range = sheet.Range["A4:D6"];
range.Text = "Border";
range.Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
range.Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
range.Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
range.Style.Borders[BordersLineType.EdgeTop].Color = Color.Black;
Wrap Text
When cell content exceeds the available column width, wrapping text allows multiple lines to be displayed within the same cell, preventing important information from being truncated.
sheet.Range["A8"].Text = "This is a very long sentence that will automatically wrap within the cell.";
sheet.Range["A8"].Style.WrapText = true;
sheet.SetColumnWidth(1, 20);
sheet.SetRowHeight(8, 60);
Part 2. Format Cell Values
Value formatting changes how data is displayed without modifying the underlying values. This is particularly important for business and financial spreadsheets.
Format Numbers
Numeric formats can control decimal places, thousands separators, scientific notation, and other display rules. Choosing the right format improves accuracy and readability.
sheet.Range["A10"].NumberValue = 1234567.891;
sheet.Range["A10"].NumberFormat = "#,##0.00";
Format Currency
Currency formatting automatically displays monetary symbols and decimal precision according to your requirements. This is commonly used in invoices, budgets, and financial reports.
sheet.Range["A11"].NumberValue = 5999.95;
sheet.Range["A11"].NumberFormat = "$#,##0.00";
Format Dates
Date formatting allows the same date value to be displayed in different styles, such as short dates, long dates, or custom patterns. Consistent date formats make reports easier to interpret.
sheet.Range["A12"].DateTimeValue = DateTime.Now;
sheet.Range["A12"].NumberFormat = "yyyy-MM-dd";
Part 3. Format Ranges and Layout
Instead of formatting cells one by one, you can apply styles to larger worksheet areas to improve efficiency and maintain consistency.
Format Ranges
A range may contain multiple rows and columns. Applying formatting to a range ensures that all cells share the same appearance and reduces repetitive code.
CellRange range = sheet.Range["A14:D18"];
range.Style.Color = Color.LightYellow;
range.Style.Font.IsBold = true;
range.Style.HorizontalAlignment = HorizontalAlignType.Center;
Merge Cells
Merged cells are often used for report titles and section headers. After merging, the content can be centered and styled to create a more professional appearance.
sheet.Range["A20:D20"].Merge();
sheet.Range["A20"].Text = "Monthly Sales Report";
sheet.Range["A20"].Style.Font.Size = 18;
sheet.Range["A20"].Style.Font.IsBold = true;
sheet.Range["A20"].Style.HorizontalAlignment = HorizontalAlignType.Center;
Format Rows and Columns
Formatting entire rows or columns is useful when all cells in a specific area should follow the same style, such as a header row or a currency column.
sheet.Rows[21].Style.Font.IsBold = true;
sheet.Rows[21].Style.Color = Color.LightGray;
sheet.Columns[1].Style.NumberFormat = "$#,##0.00";
AutoFit Rows and Columns
AutoFit automatically adjusts row heights and column widths based on cell content. This helps prevent clipped text and improves the presentation of generated spreadsheets.
sheet.AllocatedRange.AutoFitColumns();
sheet.AllocatedRange.AutoFitRows();
Part 4. Apply Advanced Formatting
Conditional formatting enables Excel to apply styles automatically based on cell values. Instead of manually highlighting data, rules can be configured to identify trends, exceptions, or important values.
For example, you can highlight numbers above a threshold, display data bars, apply color scales, or use icon sets to visualize performance indicators. These features make large datasets easier to analyze and understand.
sheet.Range["A25"].NumberValue = 1200;
sheet.Range["A26"].NumberValue = 800;
sheet.Range["A27"].NumberValue = 1500;
XlsConditionalFormats formats = sheet.ConditionalFormats.Add();
formats.AddRange(sheet.Range["A25:A27"]);
IConditionalFormat format = formats.AddCondition();
format.FormatType = ConditionalFormatType.CellValue;
format.FirstFormula = "1000";
format.Operator = ComparisonOperatorType.Greater;
format.BackColor = Color.LightGreen;
Part 5. Format Excel Tables and Worksheets
Formatting can also be applied at the worksheet level to improve the overall structure and appearance of a workbook.
Format Excel Tables
Excel tables provide built-in styling options such as header formatting, alternating row colors, and predefined themes. Converting a data range into a table can instantly enhance readability and organization.
sheet.Range["A30"].Text = "Product";
sheet.Range["B30"].Text = "Sales";
sheet.Range["A31"].Text = "Laptop";
sheet.Range["B31"].NumberValue = 5000;
sheet.Range["A32"].Text = "Monitor";
sheet.Range["B32"].NumberValue = 2000;
IListObject table = sheet.ListObjects.Create("SalesTable", sheet.Range["A30:B32"]);
table.BuiltInTableStyle = TableBuiltInStyles.TableStyleMedium2;
Configure Page Layout
Page layout settings determine how worksheets appear when printed or exported. Common options include page orientation, margins, print areas, scaling, and repeating header rows.
Proper page setup ensures that reports look professional both on screen and on paper.
sheet.PageSetup.Orientation = PageOrientationType.Landscape;
sheet.PageSetup.LeftMargin = 0.5;
sheet.PageSetup.RightMargin = 0.5;
sheet.PageSetup.TopMargin = 0.75;
sheet.PageSetup.BottomMargin = 0.75;
sheet.PageSetup.FitToPagesWide = 1;
sheet.PageSetup.FitToPagesTall = 1;
Part 6. Create a Professional Report Example
In real-world scenarios, multiple formatting techniques are often used together. A typical report may include a merged title, custom fonts, colored headers, borders, number formats, conditional formatting, and optimized page settings.
By combining these features, you can generate polished Excel documents that are ready for distribution without requiring manual editing.
using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.Collections;
using Spire.Xls.Core;
using System.Drawing;
class Program
{
static void Main()
{
// Create a new workbook
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Sales Summary Report";
// Title Row
CellRange title = sheet.Range["A1:E1"];
title.Merge();
title.Text = "Sales Summary Report";
title.Style.Font.FontName = "Arial";
title.Style.Font.Size = 16;
title.Style.Font.Color = Color.White;
title.Style.Color = Color.DarkBlue;
title.Style.HorizontalAlignment = HorizontalAlignType.Center;
title.Style.VerticalAlignment = VerticalAlignType.Center;
sheet.Rows[0].RowHeight = 30;
// Headers
string[] headers = { "Order ID", "Product", "Region", "Order Date", "Sales Amount" };
for (int i = 0; i < headers.Length; i++)
{
CellRange cell = sheet.Range[2, i + 1];
cell.Text = headers[i];
cell.Style.Font.IsBold = true;
cell.Style.Color = Color.LightGray;
cell.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;
cell.Style.Borders[BordersLineType.EdgeBottom].Color = Color.DarkBlue;
}
// Data
object[][] data =
{
new object[] { 1001, "Laptop", "North", "2024-01-15", 15000 },
new object[] { 1002, "Monitor", "West", "2024-02-10", 12000 },
new object[] { 1003, "Keyboard", "East", "2024-03-05", 13500 },
new object[] { 1004, "Mouse", "South", "2024-04-12", 16000 }
};
for (int r = 0; r < data.Length; r++)
{
for (int c = 0; c < data[r].Length; c++)
{
CellRange cell = sheet.Range[r + 3, c + 1];
var value = data[r][c];
if (c == 3) // Order Date
{
cell.DateTimeValue = DateTime.Parse(value.ToString());
cell.NumberFormat = "yyyy-MM-dd";
}
else if (c == 4) // Sales Amount
{
cell.NumberValue = Convert.ToDouble(value);
cell.NumberFormat = "$#,##0.00";
}
else
{
cell.Text = value.ToString();
}
// Alternate row colors
cell.Style.Color = (r % 2 == 0)
? Color.LightYellow
: Color.LightCyan;
}
}
// Borders
CellRange range = sheet.Range["A2:E6"];
range.BorderAround(LineStyleType.Medium, Color.Black);
range.BorderInside(LineStyleType.Thin, Color.Gray);
// Auto Fit Columns
for (int i = 1; i <= 5; i++)
{
sheet.AutoFitColumn(i);
}
// Conditional Formatting
XlsConditionalFormats formats = sheet.ConditionalFormats.Add();
formats.AddRange(sheet.Range["E3:E6"]);
IConditionalFormat condition = formats.AddCondition();
condition.FormatType = ConditionalFormatType.CellValue;
condition.Operator = ComparisonOperatorType.Greater;
condition.FirstFormula = "14000";
condition.FontColor = Color.Red;
condition.IsBold = true;
// Align + Layout Formatting
CellRange all = sheet.AllocatedRange;
for (int r = 1; r < all.RowCount; r++)
{
all.Rows[r].HorizontalAlignment = HorizontalAlignType.Center;
// all.Rows[r].VerticalAlignment = VerticalAlignType.Center;
all.Rows[r].RowHeight = 20;
}
for (int c = 0; c < all.ColumnCount; c++)
{
all.Columns[c].ColumnWidth = (c == 1) ? 19 : 14;
}
// Save
workbook.SaveToFile("SalesSummaryReport.xlsx", ExcelVersion.Version2016);
workbook.Dispose();
}
}
Output:
Conclusion
Formatting is an essential step in creating professional Excel documents. With Spire.XLS for .NET, you can efficiently customize cell appearance, control number and date formats, manage worksheet layouts, apply conditional formatting, and build visually appealing reports entirely in C#.
By using the techniques covered in this guide, you can automate Excel formatting tasks and generate polished spreadsheets suitable for business, reporting, and data analysis scenarios.
FAQs
Can I format Excel files without Microsoft Excel installed?
Yes. Spire.XLS for .NET works independently of Microsoft Excel and can create, edit, and format spreadsheets directly through code.
Does formatting change the actual cell values?
No. Most formatting operations only affect how data is displayed. The underlying values remain unchanged unless explicitly modified.
Can I apply the same style to multiple cells at once?
Yes. Styles can be applied to ranges, rows, columns, or entire worksheets, making it easy to maintain consistent formatting.
Does Spire.XLS support conditional formatting?
Yes. The library supports common conditional formatting features, including highlighting rules, data bars, color scales, and icon sets.
Which Excel formats are supported?
Spire.XLS supports XLS, XLSX, XLSM, CSV, and several other spreadsheet formats for both reading and writing.
