Use the Excel Export API to Align Cell Content

Sep 19, 2023
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.
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.

CellFormattingActions.cs
CellFormattingActions.vb

csharp

// 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 };
        }
    }
}

' Create a worksheet.
Using sheet As IXlSheet = document.CreateSheet()

    ' Create three successive columns and set their widths.
    For i As Integer = 0 To 2
        Using column As IXlColumn = sheet.CreateColumn()
            column.WidthInPixels = 130
        End Using
    Next i

    ' Create the first row in the worksheet.
    Using row As IXlRow = sheet.CreateRow()
        ' Set the row height.
        row.HeightInPixels = 40
        ' Create the first cell in the row.
        Using cell As IXlCell = row.CreateCell()
            ' Set the cell value.
            cell.Value = "Left and Top"
            ' Specify the horizontal and vertical alignment of the cell content.
            cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Top))
        End Using
        ' Create the second cell in the row.
        Using cell As IXlCell = row.CreateCell()
            ' Set the cell value.
            cell.Value = "Center and Top"
            ' Specify the horizontal and vertical alignment of the cell content.
            cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Top))
        End Using
        ' Create the third cell in the row.
        Using cell As IXlCell = row.CreateCell()
            ' Set the cell value.
            cell.Value = "Right and Top"
            ' Specify the horizontal and vertical alignment of the cell content.
            cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Top))
        End Using
    End Using

    ' Create the second row in the worksheet.
    Using row As IXlRow = sheet.CreateRow()
        ' Set the row height.
        row.HeightInPixels = 40
        ' Create the first cell in the row.
        Using cell As IXlCell = row.CreateCell()
            ' Set the cell value.
            cell.Value = "Left and Center"
            ' Specify the horizontal and vertical alignment of the cell content.
            cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Center))
        End Using
        ' Create the second cell in the row.
        Using cell As IXlCell = row.CreateCell()
            ' Set the cell value.
            cell.Value = "Center and Center"
            ' Specify the horizontal and vertical alignment of the cell content.
            cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Center))
        End Using
        ' Create the third cell in the row.
        Using cell As IXlCell = row.CreateCell()
            ' Set the cell value.
            cell.Value = "Right and Center"
            ' Specify the horizontal and vertical alignment of the cell content.
            cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Center))
        End Using
    End Using

    ' Create the third row in the worksheet.
    Using row As IXlRow = sheet.CreateRow()
        ' Set the row height.
        row.HeightInPixels = 40
        ' Create the first cell in the row.
        Using cell As IXlCell = row.CreateCell()
            ' Set the cell value.
            cell.Value = "Left and Bottom"
            ' Specify the horizontal and vertical alignment of the cell content.
            cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Bottom))
        End Using
        ' Create the second cell in the row.
        Using cell As IXlCell = row.CreateCell()
            ' Set the cell value.
            cell.Value = "Center and Bottom"
            ' Specify the horizontal and vertical alignment of the cell content.
            cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Bottom))
        End Using
        ' Create the third cell in the row.
        Using cell As IXlCell = row.CreateCell()
            ' Set the cell value.
            cell.Value = "Right and Bottom"
            ' Specify the horizontal and vertical alignment of the cell content.
            cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Bottom))
        End Using
    End Using

    sheet.SkipRows(1)

    ' Create the fifth row in the worksheet.
    Using row As IXlRow = sheet.CreateRow()
        ' Create the first cell in the row.
        Using cell As IXlCell = 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() With {.WrapText = True}
        End Using
        ' Create the second cell in the row.
        Using cell As IXlCell = row.CreateCell()
            ' Set the cell value.
            cell.Value = "Indented text"
            ' Set the indentation of the cell content.
            cell.Formatting = New XlCellAlignment() With {.Indent = 2}
        End Using
        ' Create the third cell in the row.
        Using cell As IXlCell = row.CreateCell()
            ' Set the cell value.
            cell.Value = "Rotated text"
            ' Rotate the text within the cell.
            cell.Formatting = New XlCellAlignment() With {.TextRotation = 90}
        End Using
    End Using
End Using

The image below shows the result (the workbook is opened in Microsoft® Excel®).