Articles in this section
Category / Section

How to get column names of a named range with column headers in C#, VB.NET?

1 min read

This article explains how to retrieve the column names from a named range with column headers using XlsIO.

XlsIO does not have any direct way of retrieving column names from a named range with column headers. But there are few workarounds to achieve this. One way is exporting the named range to data table and retrieving the column names from data table. The alternate one is processing named range.

XlsIO always considers first row of named range as column header. So, the column names can be retrieved by accessing the first row of named range.

Download input file with data

Download complete sample

To know more about exporting data to data table, please refer the documentation.

The following complete code snippet explains how to retrieve column names of a named range with column headers by processing it.

C#

using Syncfusion.XlsIO;
using System.Collections.Generic;
using System.IO;
using System.Reflection;
 
namespace ColumnNames_NamedRange
{
    class Program
    {
        static void Main(string[] args)
        {
            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2013;
 
                //Opening existing workbook with named range
                Assembly assembly = typeof(Program).GetTypeInfo().Assembly;
                Stream inputStream = assembly.GetManifestResourceStream("ColumnNames_NamedRange.Sample.xlsx");
                IWorkbook workbook = application.Workbooks.Open(inputStream);
 
               //Accessing named range
               IName namedRange = workbook.Names["DataValue"];
               List<string> columnName = new List<string>();
 
               int firstrow = namedRange.RefersToRange.Row;
 
               //Since the first row of named range is considered as column header, accessing the first row to get the column name
               for (int i = 1; i <= namedRange.RefersToRange.Columns.Length; i++)
               {
                   columnName.Add(namedRange.RefersToRange[firstrow, i].Text);
               }
 
               //Adding the column names to another worksheet
               IWorksheet worksheet = workbook.Worksheets.Create("Column Names");
 
               for (int i = 1, j = 0; i <= columnName.Count; i++, j++)
               {
                   worksheet[i, 1].Text = columnName[j];
               }
 
               //Saving the workbook as stream
               Stream outputStream = File.Create("Output.xlsx");
                workbook.SaveAs(outputStream);
            }
        }
    }
}

VB

Imports System.IO
Imports System.Reflection
Imports System.Collections.Generic
Imports Syncfusion.XlsIO
 
Namespace ColumnNames_NamedRange
  Class Program
      Private Shared Sub Main(ByVal args() As String)
          Using excelEngine As ExcelEngine = New ExcelEngine
               Dim application As IApplication = excelEngine.Excel
               application.DefaultVersion = ExcelVersion.Excel2013
 
               'Opening existing workbook with named range
               Dim assembly As Assembly = GetType(Program).GetTypeInfo.Assembly
               Dim inputStream As Stream = assembly.GetManifestResourceStream("ColumnNames_NamedRange.Sample.xlsx")
               Dim workbook As IWorkbook = application.Workbooks.Open(inputStream)
 
               'Accessing named range
               Dim namedRange As IName = workbook.Names("DataValue")
               Dim columnName As List(Of String) = New List(Of String)
 
               Dim firstrow As Integer = namedRange.RefersToRange.Row
 
               'Since the first row of named range is considered as column header, accessing the first row to get the column name
               Dim i As Integer = 1
               Do While (i <= namedRange.RefersToRange.Columns.Length)
                     columnName.Add(namedRange.RefersToRange(firstrow, i).Text)
                     i = (i + 1)
               Loop
 
               'Adding the column names to another worksheet
               Dim worksheet As IWorksheet = workbook.Worksheets.Create("Column Names")
               i = 1
               Dim j As Integer = 0
               Do While (i <= columnName.Count)
                   worksheet(i, 1).Text = columnName(j)
                   i = (i + 1)
               Loop
 
               'Saving the workbook as stream
               Dim outputStream As Stream = File.Create("Output.xlsx")
               workbook.SaveAs(outputStream)
          End Using
      End Sub
  End Class
End Namespace

 

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please sign in to leave a comment