Data alignment

Example

A simple data alignment example

Output file: console-alignment.xls

[C#]
using System;
using System.Drawing;
using NativeExcel;

namespace Console_Alignment
{
    class Program
    {
        static void Main(string[] args)
        {
            string FileName = "console-alignment.xls";
            CreateWorkbook(FileName);
            OpenWorkbookWithExcel(FileName);
        }

        static void CreateWorkbook(string FileName) {
            //Create a new workbook
            IWorkbook book = NativeExcel.Factory.CreateWorkbook();

            //Add worksheet
            IWorksheet sheet = book.Worksheets.Add();

            //Top header   
            IRange range = sheet.Range[2,3,2,5];
            range.Value = new string[] {"Top","Center","Bottom"};
            range.Font.Bold = true; 

            range = sheet.Range[1,3,1,5];
            range[1,1].Value = "Vertical alignment";
            range.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;

            //Left header
            range = sheet.Range[3,2,5,2];
            range.Value = new string[,] {{"Left"},{"Center"},{"Right"}};
            range.VerticalAlignment = XlVAlign.xlVAlignTop;
            range.Font.Bold = true; 

            range = sheet.Range[3,1,5,1];
            range[1,1].Value = "Horizontal alignment";
            range.Merge();
            range.Orientation = 90;
            range.VerticalAlignment = XlVAlign.xlVAlignCenter;
            
            //Table body 
            range = sheet.Range[3,3,5,5];
            range.Rows.RowHeight = 40; 
            range.Columns.ColumnWidth = 12; 
            range.Value = "Text";

            //Columns
            range.Columns[1].VerticalAlignment = XlVAlign.xlVAlignTop;
            range.Columns[2].VerticalAlignment = XlVAlign.xlVAlignCenter;
            range.Columns[3].VerticalAlignment = XlVAlign.xlVAlignBottom;

            //Rows
            range.Rows[1].HorizontalAlignment = XlHAlign.xlHAlignLeft;
            range.Rows[2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
            range.Rows[3].HorizontalAlignment = XlHAlign.xlHAlignRight;
            
            //Borders
            range = sheet.Range[2,2,5,5]; 
            range.Borders[XlBordersIndex.xlAround].ColorIndex = XlColorIndex.xlColorIndexAutomatic;
            range.Borders[XlBordersIndex.xlInsideAll].ColorIndex = XlColorIndex.xlColorIndexAutomatic;

            //Save workbook
            book.SaveAs(FileName);
        }

        static void OpenWorkbookWithExcel(string FileName){
            try {
                System.Diagnostics.Process.Start(FileName);
            } catch {
                Console.WriteLine(FileName + " created in application folder");
            }
        }
    }
}

[Visual Basic]
imports System
imports System.Drawing
imports NativeExcel
imports Microsoft.VisualBasic

Module Console_Alignment

    Sub Main()
        Dim FileName As String = "console-alignment.xls"
        CreateWorkbook(FileName)
        OpenWorkbookWithExcel(FileName)
    End Sub

    Sub CreateWorkbook(FileName As String)

        'Create workbook
        Dim book As IWorkbook = NativeExcel.Factory.CreateWorkbook()

        'Add sheet
        Dim sheet As IWorksheet = book.Worksheets.Add()

        'Top header   
        Dim range As IRange = sheet.Range(2,3,2,5)
        range.Value =  New String() {"Top","Center","Bottom"}
        range.Font.Bold = True 

        range = sheet.Range(1,3,1,5)
        range(1,1).Value = "Vertical alignment"
        range.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection

        'Left header
        range = sheet.Range(3,2,5,2)
        range.Value = New String(,) {{"Left"},{"Center"},{"Right"}}
        range.VerticalAlignment = XlVAlign.xlVAlignTop
        range.Font.Bold = True

        range = sheet.Range(3,1,5,1)
        range(1,1).Value = "Horizontal alignment"
        range.Merge()
        range.Orientation = 90
        range.VerticalAlignment = XlVAlign.xlVAlignCenter
        
        'Table body 
        range = sheet.Range(3,3,5,5)
        range.Rows.RowHeight = 40
        range.Columns.ColumnWidth = 12
        range.Value = "Text"

        'Columns
        range.Columns(1).VerticalAlignment = XlVAlign.xlVAlignTop
        range.Columns(2).VerticalAlignment = XlVAlign.xlVAlignCenter
        range.Columns(3).VerticalAlignment = XlVAlign.xlVAlignBottom

        'Rows
        range.Rows(1).HorizontalAlignment = XlHAlign.xlHAlignLeft
        range.Rows(2).HorizontalAlignment = XlHAlign.xlHAlignCenter
        range.Rows(3).HorizontalAlignment = XlHAlign.xlHAlignRight
        
        'Borders
        range = sheet.Range(2,2,5,5) 
        range.Borders(XlBordersIndex.xlAround).ColorIndex = XlColorIndex.xlColorIndexAutomatic
        range.Borders(XlBordersIndex.xlInsideAll).ColorIndex = XlColorIndex.xlColorIndexAutomatic

        'Save workbook
        book.SaveAs(FileName)

    End Sub

    Sub OpenWorkbookWithExcel(FileName As String)
        Try
           System.Diagnostics.Process.Start(FileName)
        Catch
           Console.WriteLine(FileName + " created in application folder")
        End Try  
    End Sub

End Module

Data alignment

Example

Output file: console-alignment.xls

See also