The sample demonstrates how to define named cell references or ranges in excel workbook.

Download MiscDataTable.xls

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

The sample demonstrates how to work with MarkerDesign in Silverlight via Spire.XLS.

Download template xls file.

Download data table file.

Download merged result file.

<Application xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
             xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" 
             x:Class="Report.App">
    <Application.Resources>        
    </Application.Resources>
</Application>

using System;
using System.Windows;
using System.Windows.Browser;
using Spire.License;

namespace Report
{
    public partial class App : Application
    {
        public App()
        {
            this.Startup += this.Application_Startup;
            this.Exit += this.Application_Exit;
            this.UnhandledException += this.Application_UnhandledException;

            InitializeComponent();
        }

        private void Application_Startup(object sender, StartupEventArgs e)
        {
            LicenseProvider.SetLicenseKey("your license key in license.elic.xml");
            this.RootVisual = new MainPage();
        }

        private void Application_Exit(object sender, EventArgs e)
        {

        }

        private void Application_UnhandledException(object sender, ApplicationUnhandledExceptionEventArgs e)
        {
            // If the app is running outside of the debugger then report the exception using
            // the browser's exception mechanism. On IE this will display it a yellow alert 
            // icon in the status bar and Firefox will display a script error.
            if (!System.Diagnostics.Debugger.IsAttached)
            {

                // NOTE: This will allow the application to continue running after an exception has been thrown
                // but not handled. 
                // For production applications this error handling should be replaced with something that will 
                // report the error to the website and stop the application.
                e.Handled = true;
                Deployment.Current.Dispatcher.BeginInvoke(delegate { ReportErrorToDOM(e); });
            }
        }

        private void ReportErrorToDOM(ApplicationUnhandledExceptionEventArgs e)
        {
            try
            {
                string errorMsg = e.ExceptionObject.Message + e.ExceptionObject.StackTrace;
                errorMsg = errorMsg.Replace('"', '\'').Replace("\r\n", @"\n");

                String exp = "throw new Error(\"Unhandled Error in Silverlight Application " + errorMsg + "\");";
                HtmlPage.Window.Eval(exp);
            }
            catch (Exception)
            {
            }
        }
    }
}

 Partial Public Class App
    Inherits Application

    public Sub New()
        InitializeComponent()
    End Sub
    
    Private Sub Application_Startup(ByVal o As Object, ByVal e As StartupEventArgs) _
            Handles Me.Startup
        Me.RootVisual = New MainPage()
    End Sub
    
    Private Sub Application_Exit(ByVal o As Object, ByVal e As EventArgs) Handles Me.Exit

    End Sub
    
    Private Sub Application_UnhandledException(ByVal sender As object, _
            ByVal e As ApplicationUnhandledExceptionEventArgs) Handles Me.UnhandledException

        ' If the app is running outside of the debugger then report the exception using
        ' the browser's exception mechanism. On IE this will display it a yellow alert 
        ' icon in the status bar and Firefox will display a script error.
        If Not System.Diagnostics.Debugger.IsAttached Then

            ' NOTE: This will allow the application to continue running after an exception has been thrown
            ' but not handled. 
            ' For production applications this error handling should be replaced with something that will 
            ' report the error to the website and stop the application.
            e.Handled = True
            Deployment.Current.Dispatcher.BeginInvoke( _
                New Action(Of ApplicationUnhandledExceptionEventArgs)(AddressOf ReportErrorToDOM), e)
        End If
    End Sub

   Private Sub ReportErrorToDOM(ByVal e As ApplicationUnhandledExceptionEventArgs)

        Try
            Dim errorMsg As String = e.ExceptionObject.Message + e.ExceptionObject.StackTrace
            errorMsg = errorMsg.Replace(""""c, "'"c).Replace(ChrW(13) & ChrW(10), "\n")

            System.Windows.Browser.HtmlPage.Window.Eval( _
                "throw new Error(""Unhandled Error in Silverlight Application " + errorMsg + """);")
        Catch
        
        End Try
    End Sub
    
End Class

<UserControl x:Class="Report.MainPage"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    mc:Ignorable="d"
    d:DesignHeight="600" d:DesignWidth="500"
             xmlns:dataInput="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data.Input" xmlns:sdk="http://schemas.microsoft.com/winfx/2006/xaml/presentation/sdk"
                  xmlns:data="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data" >

    <Grid x:Name="LayoutRoot" Background="#FF003399" Height="450" Width="500" VerticalAlignment="Top" HorizontalAlignment="Center" Loaded="LayoutRoot_Loaded">
        <Grid.RowDefinitions>
            <RowDefinition Height="40" MaxHeight="40" MinHeight="40" />
            <RowDefinition />
            <RowDefinition Height="30" MaxHeight="30" MinHeight="30" />
        </Grid.RowDefinitions>
        <dataInput:Label HorizontalAlignment="Center" Name="labelTitle" VerticalAlignment="Center" Content="Countries List" Foreground="White" FontWeight="Bold" FontSize="16" Grid.ColumnSpan="2" />
        <data:DataGrid AutoGenerateColumns="True" Grid.Row="1" HorizontalAlignment="Stretch" Name="dataGrid" VerticalAlignment="Stretch" Margin="1" />
        <Button Content="Generate" Grid.Row="2" HorizontalAlignment="Right" Margin="0,0,2,0" Name="buttonGenerate" VerticalAlignment="Center" Width="75" IsEnabled="False" Click="buttonGenerate_Click" />
    </Grid>
</UserControl>

using System;
using System.Collections.Generic;
using System.IO;
using System.Reflection;
using System.Windows;
using System.Windows.Controls;

using Spire.Xls;

namespace Report
{
    public partial class MainPage : UserControl
    {
        public class Country
        {
            public String Name { get; set; }
            public String Capital { get; set; }
            public String Continent { get; set; }
            public double Area { get; set; }
            public long Population { get; set; }
        }

        private SaveFileDialog saveFileDialog = null;
        private List dataSource = null;
        private Workbook template = null;
        public MainPage()
        {
            InitializeComponent();
            this.saveFileDialog = new SaveFileDialog();
            this.saveFileDialog.Filter = "Excel workbooks (*.xls) |*.xls";
        }

        private void LayoutRoot_Loaded(object sender, RoutedEventArgs e)
        {
            Assembly assembly = this.GetType().Assembly;
            foreach (String name in assembly.GetManifestResourceNames())
            {
                if (name.EndsWith(".DatatableSample.xls"))
                {
                    using (Stream stream = assembly.GetManifestResourceStream(name))
                    {
                        Workbook workbook = new Workbook();
                        workbook.LoadFromStream(stream);
                        Worksheet sheet = workbook.Worksheets[0];

                        this.dataSource = new List();
                        foreach (CellRange row in sheet.Rows)
                        {
                            if (row != null && row.Cells != null && row.Cells.Length == 5
                                && !row.Cells[0].IsBlank)
                            {
                                if (row.Cells[0].Row == 1)
                                {
                                    continue;
                                }
                                this.dataSource.Add(new Country()
                                {
                                    Name = row.Cells[0].Value,
                                    Capital = row.Cells[1].Value,
                                    Continent = row.Cells[2].Value,
                                    Area = row.Cells[3].NumberValue,
                                    Population = Convert.ToInt64(row.Cells[4].NumberValue)
                                });
                            }
                            else
                            {
                                break;
                            }
                        }
                        this.dataGrid.ItemsSource = this.dataSource;
                    }
                    this.buttonGenerate.IsEnabled = true;
                }
                else if(name.EndsWith(".MarkerDesignerSample.xls"))
                {
                    using (Stream stream = assembly.GetManifestResourceStream(name))
                    {
                        this.template = new Workbook();
                        this.template.LoadFromStream(stream);
                    }
                }
            }
        }

        private void buttonGenerate_Click(object sender, RoutedEventArgs e)
        {
            Worksheet worksheet = this.template.Worksheets[0];

            this.template.MarkerDesigner.AddParameter("Variable1", 1234.5678);
            this.template.MarkerDesigner.AddArray("Country", dataSource.ToArray());
            this.template.MarkerDesigner.Apply();

            worksheet.AllocatedRange.AutoFitRows();
            worksheet.AllocatedRange.AutoFitColumns();

            bool? result = this.saveFileDialog.ShowDialog();
            if (result.HasValue && result.Value)
            {
                using (Stream stream = this.saveFileDialog.OpenFile())
                {
                    this.template.SaveToStream(stream);
                }
            }
        }
    }
}

Imports System
Imports System.IO
Imports System.Net
Imports System.Reflection
Imports System.Windows
Imports System.Windows.Controls

Imports Spire.Xls

Partial Public Class MainPage
    Inherits UserControl

    Public Class Country
        Public Property Name() As [String]
            Get
                Return m_Name
            End Get
            Set(ByVal value As [String])
                m_Name = Value
            End Set
        End Property
        Private m_Name As [String]
        Public Property Capital() As [String]
            Get
                Return m_Capital
            End Get
            Set(ByVal value As [String])
                m_Capital = Value
            End Set
        End Property
        Private m_Capital As [String]
        Public Property Continent() As [String]
            Get
                Return m_Continent
            End Get
            Set(ByVal value As [String])
                m_Continent = Value
            End Set
        End Property
        Private m_Continent As [String]
        Public Property Area() As Double
            Get
                Return m_Area
            End Get
            Set(ByVal value As Double)
                m_Area = Value
            End Set
        End Property
        Private m_Area As Double
        Public Property Population() As Long
            Get
                Return m_Population
            End Get
            Set(ByVal value As Long)
                m_Population = Value
            End Set
        End Property
        Private m_Population As Long
    End Class

    Private saveFileDialog As SaveFileDialog = Nothing
    Private dataSource As List(Of Country) = Nothing
    Private xlsTemplate As Workbook = Nothing

    Public Sub New()
        InitializeComponent()
        Me.saveFileDialog = New SaveFileDialog()
        Me.saveFileDialog.Filter = "Excel workbooks (*.xls) |*.xls"
    End Sub

    Private Sub LayoutRoot_Loaded(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs)
        Dim assembly As Assembly = Me.[GetType]().Assembly
        For Each name As [String] In assembly.GetManifestResourceNames()
            If name.EndsWith(".DatatableSample.xls") Then
                Using stream As Stream = assembly.GetManifestResourceStream(name)
                    Dim workbook As New Workbook()
                    workbook.LoadFromStream(stream)
                    Dim sheet As Worksheet = workbook.Worksheets(0)

                    Me.dataSource = New List(Of Country)()
                    For Each row As CellRange In sheet.Rows
                        If row IsNot Nothing AndAlso row.Cells IsNot Nothing AndAlso row.Cells.Length = 5 AndAlso Not row.Cells(0).IsBlank Then
                            If row.Cells(0).Row = 1 Then
                                Continue For
                            End If
                            Me.dataSource.Add(New Country() With { _
                             .Name = row.Cells(0).Value, _
                             .Capital = row.Cells(1).Value, _
                             .Continent = row.Cells(2).Value, _
                             .Area = row.Cells(3).NumberValue, _
                             .Population = Convert.ToInt64(row.Cells(4).NumberValue) _
                            })
                        Else
                            Exit For
                        End If
                    Next
                    Me.dataGrid.ItemsSource = Me.dataSource
                End Using
                Me.buttonGenerate.IsEnabled = True
            ElseIf name.EndsWith(".MarkerDesignerSample.xls") Then
                Using stream As Stream = assembly.GetManifestResourceStream(name)
                    Me.xlsTemplate = New Workbook()
                    Me.xlsTemplate.LoadFromStream(stream)
                End Using
            End If
        Next
    End Sub

    Private Sub buttonGenerate_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs)
        Dim worksheet As Worksheet = Me.xlsTemplate.Worksheets(0)

        Me.xlsTemplate.MarkerDesigner.AddParameter("Variable1", 1234.5678)
        Me.xlsTemplate.MarkerDesigner.AddArray("Country", dataSource.ToArray())
        Me.xlsTemplate.MarkerDesigner.Apply()

        worksheet.AllocatedRange.AutoFitRows()
        worksheet.AllocatedRange.AutoFitColumns()

        Dim result As System.Nullable(Of Boolean) = Me.saveFileDialog.ShowDialog()
        If result.HasValue AndAlso result.Value Then
            Using stream As Stream = Me.saveFileDialog.OpenFile()
                Me.xlsTemplate.SaveToStream(stream)
            End Using
        End If
    End Sub
End Class