How to Set Text Alignment in Excel with C#

After exporting data to an Excel file, we need to pay attention to formatting because a well formatted file is more appealed for readers and the data will be shown more clearly. Excel formats include font style, number style and alignment. In this post, I will talk something about how to align data in cells with C#.

There are three mainly used types in alignment, horizontal, vertical and orientation. Generally speaking, if we don’t set alignment for cells, the data in cell will be aligned right in horizontal and bottom in vertical automatically. In my example, I will set all styles of the three alignments.

Note: A component, Spire.XLS for .NET is used in this example for realizing this function more easily and quickly. So, I have added its DLL file as reference in my project.

Steps:

1. Create a new workbook and one worksheet.
2. Fill cue words “Text Align” in B1 and format the cell.
3. Set vertical alignment for text from B3 to D3.
4. Set default alignment for text in B5 and set format for the cell.
5. Set horizontal alignment for text from B7 to D7.
6. Set orientation alignment for text from B9 to C9.
7. In order to show the setting effects more clearly, I set format for horizontal, vertical and orientation alignment respectively.
8. Save and launch file.

Coding:

using System;
using System.Drawing;
using Spire.Xls;

namespace Alignment
{
    class Align
    {
        static void Main(string[] args)
        {

            //Create Workbook and Initialize Worksheet
            Workbook workbook = new Workbook();
            workbook.CreateEmptySheets(1);
            Worksheet sheet = workbook.Worksheets[0];

            //Add Cue Word in B1
            sheet.Range[“B1″].Text = “Text Align”;
            sheet.Range[“B1″].Style.Font.FontName = “Calibri”;
            sheet.Range[“B1″].Style.Font.Color = Color.Red;
            sheet.Range[“B1″].Style.Font.Size = 14;
            sheet.Range[“B1″].Style.Font.IsBold = true;

            //Set Horizontal Alignment
            sheet.Range[“B3″].Text = “Top”;
            sheet.Range[“B3″].Style.VerticalAlignment =VerticalAlignType.Top;
            sheet.Range[“C3″].Text = “Center”;
            sheet.Range[“C3″].Style.VerticalAlignment =VerticalAlignType.Center;
            sheet.Range[“D3″].Text = “Bottom”;
            sheet.Range[“D3″].Style.VerticalAlignment =VerticalAlignType.Bottom;

            //Set Default Alignment
            sheet.Range[“B5″].Text = “General”;
            sheet.Range[“B5″].Style.Font.FontName = “Calibri”;
            sheet.Range[“B5″].Style.Font.Size = 12;
            sheet.Range[“B5″].Style.Color = Color.LightSkyBlue;
            sheet.Range[“B5″].Style.HorizontalAlignment =HorizontalAlignType.General;

            //Set Vertical Alignment
            sheet.Range[“B7″].Text = “Left”;
            sheet.Range[“B7″].Style.HorizontalAlignment =HorizontalAlignType.Left;
            sheet.Range[“C7″].Text = “Center”;
            sheet.Range[“C7″].Style.HorizontalAlignment =HorizontalAlignType.Center;
            sheet.Range[“D7″].Text = “Right”;
            sheet.Range[“D7″].Style.HorizontalAlignment =HorizontalAlignType.Right;

            //Set Orientation Alignment
            sheet.Range[“B9″].Text = “Rotation 90 degree”;
            sheet.Range[“B9″].Style.Rotation = 90;
            sheet.Range[“C9″].Text = “Rotation 45 degree”;
            sheet.Range[“C9″].Style.Rotation = 45;

            //Set Vertical Alignment Format
            sheet.Range[“B3:D3″].Style.Font.FontName = “Calibri”;
            sheet.Range[“B3:D3″].Style.Font.Size = 12;
            sheet.Range[“B3:D3″].Style.Color  = Color.LightYellow;
            sheet.Range[“B3:D3″].Borders.LineStyle = LineStyleType.Thin;
            sheet.Range[“B3:D3″].Borders.Color = Color.DarkBlue;
           sheet.Range[“B3:D3″].Borders[BordersLineType.DiagonalDown].LineStyle =LineStyleType.None;
            sheet.Range[“B3:D3″].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;

            //Set Horizontal Alignment Format
            sheet.Range[“B7:D7″].Style.Font.FontName = “Calibri”;
            sheet.Range[“B7:D7″].Style.Font.Size = 12;
            sheet.Range[“B7:D7″].Style.Color = Color.LightYellow;
            sheet.Range[“B7:D7″].Borders.LineStyle = LineStyleType.Thin;
            sheet.Range[“B7:D7″].Borders.Color = Color.DarkBlue;
           sheet.Range[“B7:D7″].Borders[BordersLineType.DiagonalDown].LineStyle =LineStyleType.None;
            sheet.Range[“B7:D7″].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;

            //Set Orientation Alignment Format
            sheet.Range[“B9:C9″].Style.Font.FontName = “Calibri”;
            sheet.Range[“B9:C9″].Style.Font.Size = 13;
            sheet.Range[“B9:C9″].Style.Font.IsBold = true;
            sheet.Range[“B9:C9″].Style.Font.Color = Color.LimeGreen;

            //Set Column Width and Row Height
            sheet.AllocatedRange.AutoFitColumns();
            sheet.Range[“B3:D3″].RowHeight = 20;
            sheet.Range[“B7:D7″].RowHeight = 20;

            //Save and Launch File
            workbook.SaveToFile(“Alignment.xlsx”, ExcelVersion.Version2010);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

After running, we can get the following result: