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:

Freely Download Spire.XLS for .NET

About these ads

3 thoughts on “How to Set Text Alignment in Excel with C#

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s