Click here to Skip to main content
15,890,506 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have collection excel files upload in my asp.net application i want the application read that file and see what is columns in excel file then show the columns data in dropdown list ex:one of my excel files it has Nationality,Country

Nationality column data: Japanese, Chains ..
Country Column data : Japan , China ..

i want to show data in excel file in my application with option for filtering that dat i want to show dynamic showing dropdown list one for nationality with its data and one for Country with its data so the user can filter data in excel file
Posted
Updated 16-Dec-12 20:02pm
v2
Comments
[no name] 17-Dec-12 1:30am    
Not a good question, please elobrate.
Vani Kulkarni 17-Dec-12 1:40am    
Unclear, please explain.

Hello,

Please, follow this article


Excel To DataBase Table[^]


you have to do one more task that after getting the data from excel separate the data in another datatable according to your need.

Thank

Rashed:: Bangladesh
 
Share this answer
 
Comments
Member 8194711 17-Dec-12 6:10am    
then after saving excel file to database there is any way for represent excel columns data in dropdown list dynamically for filtering data...
because this application user upload excel file and after some times user wants to filter this excel file also because excel files columns not the same columns name Ex:first excel file columns will be nationality and country and second will be employee name , employee ID unexpected excel file... if there is no way please tell me what should i do thank u very much
C#
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;

            //check extension of file 

            if (ext.ToLower() == ".xls")
            {


                //creaate connection with excel using OLEDB
                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;

                //You must use the $ after the object you reference in the spreadsheet

                OleDbDataAdapter myCmd = new OleDbDataAdapter("SELECT * FROM [" + oSheet.Name + "$]", strConn);


                myCmd.Fill(myDs);


                string source = @"F:\TEMP\" + strFilename;
                string target = @"F:\TEMP\temp\" + strFilename;
             

                //move file to another folder if exists
                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\"";

                //You must use the $ after the object you reference in the spreadsheet

                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;

                //You must use the $ after the object you reference in the spreadsheet

                OleDbDataAdapter myCmd = new OleDbDataAdapter("SELECT * FROM [" + oSheet.Name + "$]", strConn);
                
                //fill dataset with your excel data

                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



                //string source = @"F:\TEMP\" + strFilename;
                //string target = @"F:\TEMP\temp\" + strFilename;
              

                //if (File.Exists(target))
                //    File.Delete(target);
                //File.Move(source, target);
            }
            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.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900