Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET
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 16-Dec-12 20:28pm
Edited 16-Dec-12 21:02pm
v2
Comments
sinhasourabh at 17-Dec-12 1:30am
   
Not a good question, please elobrate.
Vani Kulkarni at 17-Dec-12 1:40am
   
Unclear, please explain.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  
Comments
Member 8194711 at 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
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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.
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 400
1 Jochen Arndt 190
2 Richard MacCutchan 135
3 DamithSL 95
4 Garth J Lancaster 90
0 OriginalGriff 6,045
1 DamithSL 4,601
2 Maciej Los 4,032
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,220


Advertise | Privacy | Mobile
Web04 | 2.8.141220.1 | Last Updated 17 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100