Excel Cell Names

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

namespace Names
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();

            workbook.LoadFromFile(@"..\..\MiscDataTable.xls");
            Worksheet sheet = workbook.Worksheets[0];
            sheet.InsertRow(1, 2);
            sheet.Rows[0].RowHeight = 16;

            //Style
            sheet.Range["A1:B1"].Style.Font.IsBold = true;
            sheet.Range["A3:E3"].Style.KnownColor = ExcelColors.LightOrange;

            //set the color of le event/odd row color
            int index = 4;
            while (sheet.Range[String.Format("A{0}", index)].HasString)
            {
                sheet.Range[String.Format("A{0}:E{0}", index)].Style.KnownColor
                    = index % 2 == 0 ? ExcelColors.PaleBlue : ExcelColors.LightTurquoise;
                index++;
            }

            //define named cell ranges
            sheet.Names.Add("Countries", sheet[String.Format("A4:A{0}", index - 1)]);
            sheet.Names.Add("Cities", sheet[String.Format("B4:B{0}", index - 1)]);
            sheet.Names.Add("Continents", sheet[String.Format("C4:C{0}", index - 1)]);
            sheet.Names.Add("Area", sheet[String.Format("D4:D{0}", index - 1)]);
            sheet.Names.Add("Population", sheet[String.Format("E4:E{0}", index - 1)]);
            sheet.Names.Add("NumberOfCountries", sheet[String.Format("A{0}", index)]);

            //references of names
            sheet.Range["A1"].Value = "Number of Countries:";
            sheet.Range["B1"].Formula = "=NumberOfCountries";
            sheet[String.Format("A{0}", index)].Formula = "=COUNTA(Countries)";
            sheet[String.Format("D{0}", index)].Formula = "=SUM(Area)";
            sheet[String.Format("E{0}", index)].Formula = "=SUM(Population)";

            //style
            sheet.Rows[index - 1].RowHeight = 16;
            String range = String.Format("A{0}:E{0}", index);
            sheet.Range[range].Style.Font.IsBold = true;
            sheet.Range[range].Style.KnownColor = ExcelColors.LightOrange;
            sheet.Range[range].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 0);
            sheet.Range[range].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thick;
            sheet.Range[range].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 0);
            sheet.Range[range].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            sheet.Range[range].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 0);
            sheet.Range[range].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            sheet.Range[range].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 0);
            sheet.Range[range].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
            workbook.SaveToFile("Sample.xls");
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

Imports Spire.Xls
Imports System.Drawing

Module Module1
    Sub Main()
        Dim workbook As New Spire.Xls.Workbook()

        workbook.LoadFromFile("..\..\MiscDataTable.xls")
        Dim sheet As Worksheet = workbook.Worksheets(0)
        sheet.InsertRow(1, 2)
        sheet.Rows(0).RowHeight = 16

        'Style
        sheet.Range("A1:B1").Style.Font.IsBold = True
        sheet.Range("A3:E3").Style.KnownColor = ExcelColors.LightOrange

        Dim index As Integer = 4
        While sheet.Range([String].Format("A{0}", index)).HasString
            sheet.Range([String].Format("A{0}:E{0}", index)).Style.KnownColor _
                = If(index Mod 2 = 0, ExcelColors.PaleBlue, ExcelColors.LightTurquoise)
            index += 1
        End While

        'define named cell ranges
        sheet.Names.Add("Countries", sheet([String].Format("A4:A{0}", index - 1)))
        sheet.Names.Add("Cities", sheet([String].Format("B4:B{0}", index - 1)))
        sheet.Names.Add("Continents", sheet([String].Format("C4:C{0}", index - 1)))
        sheet.Names.Add("Area", sheet([String].Format("D4:D{0}", index - 1)))
        sheet.Names.Add("Population", sheet([String].Format("E4:E{0}", index - 1)))
        sheet.Names.Add("NumberOfCountries", sheet([String].Format("A{0}", index)))

        'references of names
        sheet.Range("A1").Value = "Number of Countries:"
        sheet.Range("B1").Formula = "=NumberOfCountries"
        sheet([String].Format("A{0}", index)).Formula = "=COUNTA(Countries)"
        sheet([String].Format("D{0}", index)).Formula = "=SUM(Area)"
        sheet([String].Format("E{0}", index)).Formula = "=SUM(Population)"

        'style
        sheet.Rows(index - 1).RowHeight = 16
        Dim range As [String] = [String].Format("A{0}:E{0}", index)
        sheet.Range(range).Style.Font.IsBold = True
        sheet.Range(range).Style.KnownColor = ExcelColors.LightOrange
        sheet.Range(range).Style.Borders(BordersLineType.EdgeTop).Color = Color.FromArgb(0, 0, 0)
        sheet.Range(range).Style.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thick
        sheet.Range(range).Style.Borders(BordersLineType.EdgeBottom).Color = Color.FromArgb(0, 0, 0)
        sheet.Range(range).Style.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
        sheet.Range(range).Style.Borders(BordersLineType.EdgeLeft).Color = Color.FromArgb(0, 0, 0)
        sheet.Range(range).Style.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
        sheet.Range(range).Style.Borders(BordersLineType.EdgeRight).Color = Color.FromArgb(0, 0, 0)
        sheet.Range(range).Style.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin

        workbook.SaveToFile("Sample.xls")
        System.Diagnostics.Process.Start(workbook.FileName)
    End Sub

End Module