Alignment Settings

Configuring Alignment Settings

Alignment settings in Microsoft Excel

Anyone who has used Microsoft Excel to format cells will be familiar with the alignment settings in Microsoft Excel.

As you can see from the above figure, there are different kinds of alignment options:

  • Text alignment(horizontal & vertical)
  • Indentation.
  • Orientation.
  • Text control.
  • Text direction.

All of these alignment settings are fully supported by Aspose.Cells and are discussed in more detail below.

Alignment settings in Aspose.Cells

Aspose.Cells provides a class, Workbook, that represents an 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. Each item in the Cells collection represents an object of the Cell class.

Aspose.Cells provides GetStyle and SetStyle methods for the Cell class that are used to get and set a cell’s formatting. The Style class provides useful properties for configuring alignment settings.

Select any text alignment type using the TextAlignmentType enumeration. The pre-defined text alignment types in the TextAlignmentType enumeration are:

Text Alignment Types Description
Bottom Represents bottom text alignment
Center Represents center text alignment
CenterAcross Represents center across text alignment
Distributed Represents distributed text alignment
Fill Represents fill text alignment
General Represents general text alignment
Justify Represents justify text alignment
Left Represents left text alignment
Right Represents right text alignment
Top Represents top text alignment
JustifiedLow Aligns the text with an adjusted kashida length for Arabic text.
ThaiDistributed Distributes Thai text especially, because each character is treated as a word.

Horizontal Alignment

Use the Style object’s HorizontalAlignment property to align the text horizontally.

// 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 worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Accessing the "A1" cell from the worksheet
Aspose.Cells.Cell cell = worksheet.Cells["A1"];
// Adding some value to the "A1" cell
cell.PutValue("Visit Aspose!");
// Setting the horizontal alignment of the text in the "A1" cell
Style style = cell.GetStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
cell.SetStyle(style);
// Saving the Excel file
workbook.Save(dataDir + "book1.out.xls", SaveFormat.Excel97To2003);

Vertical Alignment

Similar to horizontal alignment, use the Style object’s VerticalAlignment property to align the text vertically.

// 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();
// Clearing all the worksheets
workbook.Worksheets.Clear();
// Adding a new worksheet to the Excel object
int i = workbook.Worksheets.Add();
// Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[i];
// Accessing the "A1" cell from the worksheet
Aspose.Cells.Cell cell = worksheet.Cells["A1"];
// Adding some value to the "A1" cell
cell.PutValue("Visit Aspose!");
// Setting the horizontal alignment of the text in the "A1" cell
Style style = cell.GetStyle();
// Setting the vertical alignment of the text in a cell
style.VerticalAlignment = TextAlignmentType.Center;
cell.SetStyle(style);
// Saving the Excel file
workbook.Save(dataDir + "book1.out.xls", SaveFormat.Excel97To2003);

Indentation

It is possible to set the indentation level of the text in a cell with the Style object’s IndentLevel property.

// 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 worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Accessing the "A1" cell from the worksheet
Aspose.Cells.Cell cell = worksheet.Cells["A1"];
// Adding some value to the "A1" cell
cell.PutValue("Visit Aspose!");
// Setting the horizontal alignment of the text in the "A1" cell
Style style = cell.GetStyle();
// Setting the indentation level of the text (inside the cell) to 2
style.IndentLevel = 2;
cell.SetStyle(style);
// Saving the Excel file
workbook.Save(dataDir + "book1.out.xls", SaveFormat.Excel97To2003);

Orientation

Set the orientation (rotation) of the text in a cell with the Style object’s RotationAngle property.

// 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 worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Accessing the "A1" cell from the worksheet
Aspose.Cells.Cell cell = worksheet.Cells["A1"];
// Adding some value to the "A1" cell
cell.PutValue("Visit Aspose!");
// Setting the horizontal alignment of the text in the "A1" cell
Style style = cell.GetStyle();
// Setting the rotation of the text (inside the cell) to 25
style.RotationAngle = 25;
cell.SetStyle(style);
// Saving the Excel file
workbook.Save(dataDir + "book1.out.xls", SaveFormat.Excel97To2003);

Text Control

The following section discusses how to control text by setting text wrapping, shrink to fit and other formatting options.

Wrapping Text

Wrapping text in a cell makes it easier to read: the height of the cell adjusts to fit all the text, instead of cutting it off or spilling over into adjacent cells. Set text wrapping on or off with the Style object’s IsTextWrapped property.

// 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 Workbook Object
Workbook wb = new Workbook();
// Open first Worksheet in the workbook
Worksheet ws = wb.Worksheets[0];
// Get Worksheet Cells Collection
Aspose.Cells.Cells cell = ws.Cells;
// Increase the width of First Column Width
cell.SetColumnWidth(0, 35);
// Increase the height of first row
cell.SetRowHeight(0, 36);
// Add Text to the Firts Cell
cell[0, 0].PutValue("I am using the latest version of Aspose.Cells to test this functionality");
// Make Cell's Text wrap
Style style = cell[0, 0].GetStyle();
style.IsTextWrapped = true;
cell[0, 0].SetStyle(style);
// Save Excel File
wb.Save(dataDir+ "WrappingText.out.xlsx");
Shrinking to Fit

An option to wrapping text in a field is to shrink the text size to fit a cell’s dimensions. This is done by setting the Style object’s IsTextWrapped property to true.

// 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 worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Accessing the "A1" cell from the worksheet
Aspose.Cells.Cell cell = worksheet.Cells["A1"];
// Adding some value to the "A1" cell
cell.PutValue("Visit Aspose!");
// Setting the horizontal alignment of the text in the "A1" cell
Style style = cell.GetStyle();
// Shrinking the text to fit according to the dimensions of the cell
style.ShrinkToFit = true;
cell.SetStyle(style);
// Saving the Excel file
workbook.Save(dataDir + "book1.out.xls", SaveFormat.Excel97To2003);
Merging Cells

Like Microsoft Excel, Aspose.Cells supports merging several cells into one. Aspose.Cells provides two approaches to this task. One way is to call the Cells collection’s Merge method. The Merge method takes the following parameters to merge the cells:

  • First row: the first row from where to start merging.
  • First column: the first column from where to start merging.
  • Number of rows: the number of rows to merge.
  • Number of columns: the number of columns to merge.
// 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);
// Create a Workbook.
Workbook wbk = new Workbook();
// Create a Worksheet and get the first sheet.
Worksheet worksheet = wbk.Worksheets[0];
// Create a Cells object ot fetch all the cells.
Cells cells = worksheet.Cells;
// Merge some Cells (C6:E7) into a single C6 Cell.
cells.Merge(5, 2, 2, 3);
// Input data into C6 Cell.
worksheet.Cells[5, 2].PutValue("This is my value");
// Create a Style object to fetch the Style of C6 Cell.
Style style = worksheet.Cells[5, 2].GetStyle();
// Create a Font object
Font font = style.Font;
// Set the name.
font.Name = "Times New Roman";
// Set the font size.
font.Size = 18;
// Set the font color
font.Color = System.Drawing.Color.Blue;
// Bold the text
font.IsBold = true;
// Make it italic
font.IsItalic = true;
// Set the backgrond color of C6 Cell to Red
style.ForegroundColor = System.Drawing.Color.Red;
style.Pattern = BackgroundType.Solid;
// Apply the Style to C6 Cell.
cells[5, 2].SetStyle(style);
// Save the Workbook.
wbk.Save(dataDir + "mergingcells.out.xls");

The other way is to first call the Cells collection’s CreateRange method to create a range of the cells to be merged. The CreateRange method takes the same set of parameters as that of the Merge method discussed above and returns a Range object. The Range object also provides a Merge method that merges the range specified in the Range object.

Text Direction

It is possible to set the reading order of text in cells. The reading order is the visual order in which characters, words, etc. are displayed. For example, English is a left to right language while Arabic is a right to left language.

The reading order is set with the Style object’s TextDirection property. Aspose.Cells provides pre-defined text direction types in the TextDirectionType enumeration.

Text Direction Types Description
Context The reading order consistent with the language of the first entered character
LeftToRight Left to right reading order
RightToLeft Right to left reading order
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Obtaining the reference of 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("I am using the latest version of Aspose.Cells to test this functionality.");
// Gets style in the "A1" cell
Style style = cell.GetStyle();
// Shrinking the text to fit according to the dimensions of the cell
style.TextDirection = (TextDirectionType.LeftToRight);
cell.SetStyle(style);
// Saving the Excel file
workbook.Save("book1.xlsx");

Advance topics