Use the Excel Export API to Align Cell Content
- 8 minutes to read
To align data contained within a cell, use the corresponding properties of the XlCellAlignment object.
- XlCellAlignment.HorizontalAlignment, XlCellAlignment.VerticalAlignment - set the horizontal and vertical alignment of the cell content.
- XlCellAlignment.Indent - sets an indent value for text in a cell.
- XlCellAlignment.WrapText - sets whether or not text should be wrapped in a cell.
- XlCellAlignment.ShrinkToFit - sets whether or not text should be shrunk to fit the cell width.
- XlCellAlignment.TextRotation - sets the rotation of the text within a cell.
- XlCellAlignment.ReadingOrder - sets the reading order for the cell content.
To change alignment characteristics of cell content, perform the steps below.
Initialize an instance of the XlCellAlignment class. Do one of the following.
- Use the XlCellAlignment default constructor.
- Call the static XlCellAlignment.FromHV method to specify both horizontal and vertical alignment for a cell. This method returns the XlCellAlignment object, so that you can use its properties to specify additional alignment options (indent, text rotation, etc.).
- Set the required properties of the XlCellAlignment object. For example, to wrap long text in a cell into multiple lines, set the XlCellAlignment.WrapText property to true.
To apply alignment settings to a cell, use one of the following approaches.
- Call the IXlCell.ApplyFormatting method and pass the specified XlCellAlignment object as a parameter.
- Assign the specified XlCellAlignment object to the IXlCell.Formatting property.
Tip
To share alignment settings with multiple cells in a row at once, use the IXlRow.BulkCells method.
To specify alignment characteristics for the entire row or column, use the IXlRow.ApplyFormatting and IXlColumn.ApplyFormatting methods, or IXlRow.Formatting and IXlColumn.Formatting properties, respectively.
// Create a worksheet.
using(IXlSheet sheet = document.CreateSheet())
{
// Create three successive columns and set their widths.
for(int i = 0; i < 3; i++)
{
using(IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 130;
}
}
// Create the first row in the worksheet.
using(IXlRow row = sheet.CreateRow())
{
// Set the row height.
row.HeightInPixels = 40;
// Create the first cell in the row.
using(IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Left and Top";
// Specify horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Top));
}
// Create the second cell in the row.
using(IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Center and Top";
// Specify horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Top));
}
// Create the third cell in the row.
using(IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Right and Top";
// Specify horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Top));
}
}
// Create the second row in the worksheet.
using(IXlRow row = sheet.CreateRow())
{
// Set the row height.
row.HeightInPixels = 40;
// Create the first cell in the row.
using(IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Left and Center";
// Specify horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Center));
}
// Create the second cell in the row.
using(IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Center and Center";
// Specify horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Center));
}
// Create the third cell in the row.
using(IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Right and Center";
// Specify horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Center));
}
}
// Create the third row in the worksheet.
using(IXlRow row = sheet.CreateRow())
{
// Set the row height.
row.HeightInPixels = 40;
// Create the first cell in the row.
using(IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Left and Bottom";
// Specify horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Bottom));
}
// Create the second cell in the row.
using(IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Center and Bottom";
// Specify horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Bottom));
}
// Create the third cell in the row.
using(IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Right and Bottom";
// Specify horizontal and vertical alignment of the cell content.
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Bottom));
}
}
sheet.SkipRows(1);
// Create the fifth row in the worksheet.
using(IXlRow row = sheet.CreateRow())
{
// Create the first cell in the row.
using(IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "The WrapText property is applied to wrap the text within a cell";
// Wrap the text within the cell.
cell.Formatting = new XlCellAlignment() { WrapText = true };
}
// Create the second cell in the row.
using(IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Indented text";
// Set the indentation of the cell content.
cell.Formatting = new XlCellAlignment() { Indent = 2 };
}
// Create the third cell in the row.
using(IXlCell cell = row.CreateCell())
{
// Set the cell value.
cell.Value = "Rotated text";
// Rotate the text within the cell.
cell.Formatting = new XlCellAlignment() { TextRotation = 90 };
}
}
}
The image below shows the result (the workbook is opened in Microsoft® Excel®).
See Also