Format cells

Introduction

Aspose.Cells provides the GetStyle and SetStyle methods of the Cell class, used to get/set the formatting style of a cell. Aspose.Cells also provides a Style class.

Format Cells using GetStyle and SetStyle Methods

Apply different kinds of formatting styles on cells to set background or foreground colors, borders, fonts, horizontal and vertical alignments, indentation level, text direction, rotation angle and much more.

Using the GetStyle and SetStyle Methods

If developers need to apply different formatting styles to different cells then it’s better to get the Style of the cell using Cell.GetStyle method, specify the style attributes and then apply the formatting using Cell.SetStyle method. An example is given below to demonstrate this approach to apply various formatting on a cell.

	// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
	// The path to the documents directory.
	string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

	// Create directory if it is not already present.
	bool IsExists = System.IO.Directory.Exists(dataDir);
	if (!IsExists)
	System.IO.Directory.CreateDirectory(dataDir);

	// Instantiating a Workbook object
	Workbook workbook = new Workbook();

	// Obtaining the reference of the first worksheet
	Worksheet worksheet = workbook.Worksheets[0];

	// Accessing the "A1" cell from the worksheet
	Cell cell = worksheet.Cells["A1"];

	// Adding some value to the "A1" cell
	cell.PutValue("Hello Aspose!");

	// Defining a Style object
	Aspose.Cells.Style style;

	// Get the style from A1 cell
	style = cell.GetStyle();

	// Setting the vertical alignment
	style.VerticalAlignment = TextAlignmentType.Center;

	// Setting the horizontal alignment
	style.HorizontalAlignment = TextAlignmentType.Center;

	// Setting the font color of the text
	style.Font.Color = Color.Green;

	// Setting to shrink according to the text contained in it
	style.ShrinkToFit = true;

	// Setting the bottom border color to red
	style.Borders[BorderType.BottomBorder].Color = Color.Red;

	// Setting the bottom border type to medium
	style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium;

	// Applying the style to A1 cell
	cell.SetStyle(style);

	// Saving the Excel file
	workbook.Save(dataDir + "book1.out.xls");

view raw Examples-CSharp-Formatting-ApproachesToFormatData-UsingGetStyleSetStyle-1.cs hosted with ❤ by GitHub

Using Style Object to Format Different Cells

If developers need to apply the Same formatting style to different cells then they can use Style object. Please follow the steps below to use the Style object:

Add a Style object by calling the CreateStyle method of the Workbook class
Access the newly added Style object
Set the desired properties/attributes of the Style object to apply desired formatting settings
Assign the configured Style object to your desired cells

This approach can greatly improve the efficiency of your applications and save memory too.

	// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
	// The path to the documents directory.
	string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

	// Create directory if it is not already present.
	bool IsExists = System.IO.Directory.Exists(dataDir);
	if (!IsExists)
	System.IO.Directory.CreateDirectory(dataDir);

	// Instantiating a Workbook object
	Workbook workbook = new Workbook();

	// Adding a new worksheet to the Excel object
	int i = workbook.Worksheets.Add();

	// Obtaining the reference of the first worksheet by passing its sheet index
	Worksheet worksheet = workbook.Worksheets[i];

	// Accessing the "A1" cell from the worksheet
	Cell cell = worksheet.Cells["A1"];

	// Adding some value to the "A1" cell
	cell.PutValue("Hello Aspose!");

	// Adding a new Style
	Style style = workbook.CreateStyle();

	// Setting the vertical alignment of the text in the "A1" cell
	style.VerticalAlignment = TextAlignmentType.Center;

	// Setting the horizontal alignment of the text in the "A1" cell
	style.HorizontalAlignment = TextAlignmentType.Center;

	// Setting the font color of the text in the "A1" cell
	style.Font.Color = Color.Green;

	// Shrinking the text to fit in the cell
	style.ShrinkToFit = true;

	// Setting the bottom border color of the cell to red
	style.Borders[BorderType.BottomBorder].Color = Color.Red;

	// Setting the bottom border type of the cell to medium
	style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium;

	// Assigning the Style object to the "A1" cell
	cell.SetStyle(style);


	// Apply the same style to some other cells
	worksheet.Cells["B1"].SetStyle(style);
	worksheet.Cells["C1"].SetStyle(style);
	worksheet.Cells["D1"].SetStyle(style);


	// Saving the Excel file
	workbook.Save(dataDir + "book1.out.xls");

view raw Examples-CSharp-Formatting-ApproachesToFormatData-UsingStyleObject-1.cs hosted with ❤ by GitHub

Using Microsoft Excel 2007 Predefined Styles

If you need to apply different formatting styles for Microsoft Excel 2007, apply styles using the Aspose.Cells API. An example is given below to demonstrate this approach to apply a predefined style on a cell.

	// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
	// The path to the documents directory.
	string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

	// Create directory if it is not already present.
	bool IsExists = System.IO.Directory.Exists(dataDir);
	if (!IsExists)
	System.IO.Directory.CreateDirectory(dataDir);

	// Instantiate a new Workbook.
	Workbook workbook = new Workbook();

	// Create a style object .
	Style style = workbook.CreateStyle();

	// Input a value to A1 cell.
	workbook.Worksheets[0].Cells["A1"].PutValue("Test");

	// Apply the style to the cell.
	workbook.Worksheets[0].Cells["A1"].SetStyle(style);

	// Save the Excel 2007 file.
	workbook.Save(dataDir + "book1.out.xlsx");

view raw Examples-CSharp-Formatting-ApproachesToFormatData-UsingExcelPredefinedStyles-1.cs hosted with ❤ by GitHub

Formatting Selected Characters in a Cell

Dealing with Font Settings explains how to format text in cells, but it only explains how to format all of the cell content. What if you want to format only selected characters?

Aspose.Cells supports this feature too. This topic explains how to we use this feature effectively.

Formatting Selected Characters

Aspose.Cells provides a class, Workbook that represents a Microsoft Excel file. The Workbook class contains the Worksheets collection that allows access to each worksheet in an Excel file. A worksheet is represented by the Worksheet class. The Worksheet class provides a Cells collection. Each item in the Cells collection represents an object of the Cell class.

The Cell class provides the Characters method that takes the following parameters to select a range of characters inside a cell:

Start Index, the index of the character that the selection starts from.
Number of Characters, the number of characters to select.

The Characters method returns an instance of the FontSetting class that allows developers to format the characters in the same way as they would a cell as shown below in the code example. In the output file, in the A1 cell, the word ‘Visit’ will be formatted with the default font but ‘Aspose!’ is bold and blue.

	// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
	// The path to the documents directory.
	string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

	// Create directory if it is not already present.
	bool IsExists = System.IO.Directory.Exists(dataDir);
	if (!IsExists)
	System.IO.Directory.CreateDirectory(dataDir);

	// Instantiating a Workbook object
	Workbook workbook = new Workbook();

	// Obtaining the reference of the first(default) worksheet by passing its sheet index
	Worksheet worksheet = workbook.Worksheets[0];

	// Accessing the "A1" cell from the worksheet
	Cell cell = worksheet.Cells["A1"];

	// Adding some value to the "A1" cell
	cell.PutValue("Visit Aspose!");

	// Setting the font of selected characters to bold
	cell.Characters(6, 7).Font.IsBold = true;

	// Setting the font color of selected characters to blue
	cell.Characters(6, 7).Font.Color = Color.Blue;

	// Saving the Excel file
	workbook.Save(dataDir + "book1.out.xls");

view raw Examples-CSharp-Formatting-FormattingSelectedCharacters-1.cs hosted with ❤ by GitHub

If you are interested in formatting a portion of Rich Text in a cell, consider using the Cell.GetCharacters & Cell.SetCharacters methods. The [Cell.GetCharacters method is to be used to access the portions of the text and then amendments can be done using the Cell.SetCharacters method whereas the Get method returns an array of FontSetting objects which can be manipulated to set various properties such as font name, font color, boldness, etc. and Set method can be used to apply the changes.

Formatting Rows and Columns

Sometimes, developers need to apply the same formatting on rows or columns. Applying formatting on cells one by one often takes longer and is not a good solution. To address this issue, Aspose.Cells provides a simple, fast way discussed in detail in this article.

Formatting Rows & Columns

Aspose.Cells provides a class, the Workbook that represents a Microsoft Excel file. The Workbook class contains a Worksheets collection that allows access to each worksheet in the Excel file. A worksheet is represented by the Worksheet class. The Worksheet class provides a Cells collection. The Cells collection provides a Rows collection.

Formatting a Row

Each item in the Rows collection represents a Row object. The Row object offers the ApplyStyle method used to set the row’s formatting. To apply the same formatting to a row, use the Style object. The steps below show how to use it.

Add a Style object to the Workbook class by calling its CreateStyle method.
Set the Style object’s properties to apply formatting settings.
Make the relevant attributes ON for the StyleFlag object.
Assign the configured Style object to the Row object.

	// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
	// The path to the documents directory.
	string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

	// Create directory if it is not already present.
	bool IsExists = System.IO.Directory.Exists(dataDir);
	if (!IsExists)
	System.IO.Directory.CreateDirectory(dataDir);

	// Instantiating a Workbook object
	Workbook workbook = new Workbook();

	// Obtaining the reference of the first (default) worksheet by passing its sheet index
	Worksheet worksheet = workbook.Worksheets[0];

	// Adding a new Style to the styles
	Style style = workbook.CreateStyle();

	// Setting the vertical alignment of the text in the "A1" cell
	style.VerticalAlignment = TextAlignmentType.Center;

	// Setting the horizontal alignment of the text in the "A1" cell
	style.HorizontalAlignment = TextAlignmentType.Center;

	// Setting the font color of the text in the "A1" cell
	style.Font.Color = Color.Green;

	// Shrinking the text to fit in the cell
	style.ShrinkToFit = true;

	// Setting the bottom border color of the cell to red
	style.Borders[BorderType.BottomBorder].Color = Color.Red;

	// Setting the bottom border type of the cell to medium
	style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium;

	// Creating StyleFlag
	StyleFlag styleFlag = new StyleFlag();
	styleFlag.HorizontalAlignment = true;
	styleFlag.VerticalAlignment = true;
	styleFlag.ShrinkToFit = true;
	styleFlag.Borders = true;
	styleFlag.FontColor = true;

	// Accessing a row from the Rows collection
	Row row = worksheet.Cells.Rows[0];

	// Assigning the Style object to the Style property of the row
	row.ApplyStyle(style, styleFlag);

	// Saving the Excel file
	workbook.Save(dataDir + "book1.out.xls");

view raw Examples-CSharp-Formatting-FormatRowsColumns-FormattingARow-1.cs hosted with ❤ by GitHub

Formatting a Column

The Cells collection also provides a Columns collection. Each item in the Columns collection represents a Column object. Similar to a Row object, the Column object also offers the ApplyStyle method for formatting a column.

	// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
	// The path to the documents directory.
	string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

	// Create directory if it is not already present.
	bool IsExists = System.IO.Directory.Exists(dataDir);
	if (!IsExists)
	System.IO.Directory.CreateDirectory(dataDir);

	// Instantiating a Workbook object
	Workbook workbook = new Workbook();

	// Obtaining the reference of the first (default) worksheet by passing its sheet index
	Worksheet worksheet = workbook.Worksheets[0];

	// Adding a new Style to the styles
	Style style = workbook.CreateStyle();

	// Setting the vertical alignment of the text in the "A1" cell
	style.VerticalAlignment = TextAlignmentType.Center;

	// Setting the horizontal alignment of the text in the "A1" cell
	style.HorizontalAlignment = TextAlignmentType.Center;

	// Setting the font color of the text in the "A1" cell
	style.Font.Color = Color.Green;

	// Shrinking the text to fit in the cell
	style.ShrinkToFit = true;

	// Setting the bottom border color of the cell to red
	style.Borders[BorderType.BottomBorder].Color = Color.Red;

	// Setting the bottom border type of the cell to medium
	style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium;

	// Creating StyleFlag
	StyleFlag styleFlag = new StyleFlag();
	styleFlag.HorizontalAlignment = true;
	styleFlag.VerticalAlignment = true;
	styleFlag.ShrinkToFit = true;
	styleFlag.Borders = true;
	styleFlag.FontColor = true;

	// Accessing a column from the Columns collection
	Column column = worksheet.Cells.Columns[0];

	// Applying the style to the column
	column.ApplyStyle(style, styleFlag);

	// Saving the Excel file
	workbook.Save(dataDir + "book1.out.xls");

view raw Examples-CSharp-Formatting-FormatRowsColumns-FormattingAColumn-1.cs hosted with ❤ by GitHub

Advance topics

Managing data of Excel files. Manage formulas of Excel files