Read Excel Column and bind ASP.Net DropDownList using C# and VB.Net

Hi BugHunter,

Refer below sample.

HTML

CS.aspx

<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnImport" runat="server" Text="Import" OnClick="ImportExcel" />

Default.aspx

<asp:DropDownList runat="server" ID="ddlCountry">
</asp:DropDownList>

Namespaces

C#

using System.IO;
using System.Data;
using ClosedXML.Excel;

VB.Net

Imports ClosedXML.Excel
Imports System.Data
Imports System.IO

Code

C#

CS.aspx.cs

protected void ImportExcel(object sender, EventArgs e)
{
    string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
    FileUpload1.SaveAs(Server.MapPath("~/Files/") + fileName);
    using (XLWorkbook workBook = new XLWorkbook(Server.MapPath("~/Files/") + fileName))
    {
        IXLWorksheet workSheet = workBook.Worksheet(1);
        DataTable dt = new DataTable();
        bool firstRow = true;
        foreach (IXLRow row in workSheet.Rows())
        {
            if (firstRow)
            {
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Columns.Add(cell.Value.ToString());
                    string[] columnNames = dt.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToArray();
                    Session["columnNames"] = columnNames;
                }
                firstRow = false;
            }
            else
            {
                dt.Rows.Add();
                int i = 0;
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                    i++;
                }
            }
        }
    }
    Response.Redirect("Default.aspx");
}

Default.aspx.cs

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        string[] value = Session["columnNames"] as string[];
        foreach (var item in value)
        {
            ddlCountry.Items.Add(item);
        }
        ddlCountry.Items.Insert(0, new ListItem("SELECT", ""));
    }
}

VB.Net

VB.aspx.vb

Protected Sub ImportExcel(ByVal sender As Object, ByVal e As EventArgs)
    Dim fileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
    FileUpload1.SaveAs(Server.MapPath("~/Files/") & fileName)

    Using workBook As XLWorkbook = New XLWorkbook(Server.MapPath("~/Files/") & fileName)
        Dim workSheet As IXLWorksheet = workBook.Worksheet(1)
        Dim dt As DataTable = New DataTable()
        Dim firstRow As Boolean = True
        For Each row As IXLRow In workSheet.Rows()
            If firstRow Then
                For Each cell As IXLCell In row.Cells()
                    dt.Columns.Add(cell.Value.ToString())
                    Dim columnNames As String() = dt.Columns.Cast(Of DataColumn)().Select(Function(x) x.ColumnName).ToArray()
                    Session("columnNames") = columnNames
                Next

                firstRow = False
            Else
                dt.Rows.Add()
                Dim i As Integer = 0

                For Each cell As IXLCell In row.Cells()
                    dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString()
                    i += 1
                Next
            End If
        Next
    End Using

    Response.Redirect("Default.aspx")
End Sub

DefaultVB.aspx.vb

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim value As String() = TryCast(Session("columnNames"), String())
        For Each item In value
            ddlCountry.Items.Add(item)
        Next
        ddlCountry.Items.Insert(0, New ListItem("SELECT", ""))
    End If
End Sub

Screenshot

Excel

Binding excel header to Dropdownlist