Bind Excel Sheet (WorkSheet) Names in DropDownList in ASP.Net Core

Last Reply one month ago By dharmendr

Posted one month ago

Hello All,

I am reading sheet number from excel file and want to bind that sheet in dropdown using asp.net coe.

Like :Sheet1, Sheet2, Sheet3 etc

i bind sheet in list but i want to bind that sheet in dropdown

i had done all the part like browse excel read sheet from above code 

please help in asp.net core

public List<string> ListSheetInExcel(string filePath)
{
    OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder();
    String strExtendedProperties = String.Empty;
    sbConnection.DataSource = filePath;
    if (Path.GetExtension(filePath).Equals(".xls"))//for 97-03 Excel file
    {
        sbConnection.Provider = "Microsoft.Jet.OLEDB.4.0";
        strExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1";//HDR=ColumnHeader,IMEX=InterMixed
    }
    else if (Path.GetExtension(filePath).Equals(".xlsx"))  //for 2007 Excel file
    {
        sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
        strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
    }
    sbConnection.Add("Extended Properties", strExtendedProperties);
    List<string> listSheet = new List<string>();
    using (OleDbConnection conn = new OleDbConnection(sbConnection.ToString()))
    {
        conn.Open();
        DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        foreach (DataRow drSheet in dtSheet.Rows)
        {
            if (drSheet["TABLE_NAME"].ToString().Contains("$"))//checks whether row contains '_xlnm#_FilterDatabase' or sheet name(i.e. sheet name always ends with $ sign)
            {
                listSheet.Add(drSheet["TABLE_NAME"].ToString());                       
            }
        }
    }
    return listSheet;
}

 

<div class="col-md-4 col-xs-12">
    <div class="form-group">
        <label for="">Select Sheet</label>
        <select class="form-control">
            <option>Sheet 01</option>
            <option>Sheet 02</option>
        </select>
    </div>
</div>

 

Posted one month ago

Hi telldurges,

Check with below code.

Controller

public class HomeController : Controller
{
    // GET: /Home/
    public ActionResult Index()
    {
        TempData["Excel"] = ListSheetInExcel(Server.MapPath("~/Files/Sample.xls"));
        return View();
    }

    public List<SelectListItem> ListSheetInExcel(string filePath)
    {
        OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder();
        String strExtendedProperties = String.Empty;
        sbConnection.DataSource = filePath;
        if (Path.GetExtension(filePath).Equals(".xls"))//for 97-03 Excel file
        {
            sbConnection.Provider = "Microsoft.Jet.OLEDB.4.0";
            strExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1";//HDR=ColumnHeader,IMEX=InterMixed
        }
        else if (Path.GetExtension(filePath).Equals(".xlsx"))  //for 2007 Excel file
        {
            sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
            strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
        }
        sbConnection.Add("Extended Properties", strExtendedProperties);
        List<SelectListItem> listSheet = new List<SelectListItem>();
        using (OleDbConnection conn = new OleDbConnection(sbConnection.ToString()))
        {
            conn.Open();
            DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            foreach (DataRow drSheet in dtSheet.Rows)
            {
                if (drSheet["TABLE_NAME"].ToString().Contains("$"))//checks whether row contains '_xlnm#_FilterDatabase' or sheet name(i.e. sheet name always ends with $ sign)
                {
                    listSheet.Add(new SelectListItem
                    {
                        Text = drSheet["TABLE_NAME"].ToString(),
                        Value = drSheet["TABLE_NAME"].ToString()
                    });
                }
            }
        }

        return listSheet;
    }
}

View

<div class="col-md-4 col-xs-12">
    <div class="form-group">
        <label for="">Select Sheet</label>
        <select asp-for="ID" class="form-control" asp-items="@((IEnumerable<SelectListItem>)TempData["Excel"])">  
        </select> 
    </div>
</div>