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.

To change alignment characteristics of cell content, perform the steps below.

  1. 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.).
  2. 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.
  3. To apply alignment settings to a cell, use one of the following approaches.

    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 the 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 the 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 the 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 the 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 the 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 the 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 the 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 the 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 the 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®).

XLExport_Examples_CellAlignment

See Also