protected void FillDataSet()
{
try
{
Microsoft.Office.Interop.Excel.ApplicationClass app = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Worksheet oSheet;
string strFilename = FileUpload.PostedFile.FileName;
strFilename = System.IO.Path.GetFileName(strFilename);
string ext = Path.GetExtension(strFilename);
bool hasHeaders = true;
string HDR = hasHeaders ? "Yes" : "No";
string strConn;
if (ext.ToLower() == ".xls")
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fu + " ;Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=1\"";
Microsoft.Office.Interop.Excel.WorkbookClass workBook = (Microsoft.Office.Interop.Excel.WorkbookClass)app.Workbooks.Open(fu, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
app.Visible = false;
OleDbDataAdapter myCmd = new OleDbDataAdapter("SELECT * FROM [" + oSheet.Name + "$]", strConn);
myCmd.Fill(myDs);
string source = @"F:\TEMP\" + strFilename;
string target = @"F:\TEMP\temp\" + strFilename;
if (File.Exists(target))
File.Delete(target);
File.Move(source, target);
}
else if (ext.ToLower() == ".xlsx")
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fu + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=2\"";
Microsoft.Office.Interop.Excel.WorkbookClass workBook = (Microsoft.Office.Interop.Excel.WorkbookClass)app.Workbooks.Open(fu, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
app.Visible = false;
OleDbDataAdapter myCmd = new OleDbDataAdapter("SELECT * FROM [" + oSheet.Name + "$]", strConn);
myCmd.Fill(myDs);
DataTable dtMyExcel = new DataTable();
dtMyExcel = myDs.Tables[0];
#region Make DataTable for Nationality
DataTable dtCopyMyExcel = new DataTable();
dtCopyMyExcel = dtMyExcel.Copy();
DataView view = new DataView(dtCopyMyExcel);
DataTable distinctValues = view.ToTable(true, "Nationality");
DataTable dtNationality = new DataTable();
dtNationality.Columns.Add("NationalityID", typeof(int));
dtNationality.Columns.Add("Nationality", typeof(string));
if (distinctValues.Rows.Count > 0)
{
int slNo = 1;
foreach (DataRow objDr in distinctValues.Rows)
{
DataRow drNewNational = dtNationality.NewRow();
drNewNational["NationalityID"] = slNo.ToString();
drNewNational["Nationality"] = objDr["Nationality"].ToString();
dtNationality.Rows.Add(drNewNational);
slNo++;
}
}
#endregion
#region Make Process for Employee
DataTable dtEmployee= new DataTable();
dtEmployee.Columns.Add("EmployeeId", typeof(int));
dtEmployee.Columns.Add("EmployeeName", typeof(string));
if (dtMyExcel.Rows.Count > 0)
{
foreach (DataRow objDr in dtMyExcel.Rows)
{
DataRow drdtEmployee = dtEmployee.NewRow();
drdtEmployee["EmployeeId"] = objDr["EmployeeId"].ToString();
drdtEmployee["EmployeeName"] = objDr["EmployeeName"].ToString();
dtEmployee.Rows.Add(drdtEmployee);
}
}
#endregion
}
else
{
Response.Write("Check the extension of uploaded file.");
}
}
catch (Exception ex)
{
Response.Write("Error !" + ex.Message);
}
}
Look, I added 2 table in the FillDataSet() Method in ExcelToDatabase.aspx page of given link. Those table are in #region Make DataTable for Nationality and #region Make Process for Employee. Please, find those table.
Next, your work: create datatable as your wish like my given sample. Next. Bind those datatable in your dropdownlist. After select on dropdownlist, get the selected value and search in the main datatable (dtMyExcel). and after getting the searching result re-bind those table and re-bind your dropdownlist.
thank you.
Rashed:: Bangladesh.