How can show Excel Sheet Name in dropdownlist using C#

Question

I want to show an excel sheet name in a dropdown list using c#. But using my code, I am able to show excel sheet name but excel sheet name comes with '' and $. Coming Output is like: Sheet1$, Sheet2$

        sheetNameddl.Items.Clear();
        sheetNameddl.DataSource = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        sheetNameddl.DataTextField = "TABLE_NAME";
        sheetNameddl.DataValueField = "TABLE_NAME";
        sheetNameddl.DataBind();
        sheetNameddl.Items.Insert(0, new ListItem("--Select Sheet--", ""));

But the wanted output is like sheet1, sheet2.

Welcome to StackOverflow. So, are you having problems retrieving the different Sheet names? Have you tried something like [this](https://stackoverflow.com/a/1165281/3563910) ? — Gonzo345, Sep 05 '19 at 08:45

score 0 · Accepted Answer · answered Sep 05 '19 at 08:57

GetOleDbSchemaTable returns a DataTable. You can enumerate it and change the names (might need to do before you bind):

    sheetNameddl.Items.Clear();
    DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    foreach(DataRow ro in dt.Rows)
      ro["TABLE_NAME"] = ro["TABLE_NAME"].ToString().TrimEnd('$');
    sheetNameddl.DataSource = dt;
    sheetNameddl.DataTextField = "TABLE_NAME";
    sheetNameddl.DataValueField = "TABLE_NAME";
    sheetNameddl.DataBind();
    sheetNameddl.Items.Insert(0, new ListItem("--Select Sheet--", ""));

Thanks.. this is useful for me. – Annoy Sep 05 '19 at 09:50 — Annoy, Sep 05 '19 at 09:50

How can show Excel Sheet Name in dropdownlist using C#

1 Answers1