[RESOLVED] How to read and store excel file data in a datatable Object?

I am uploading a excel file using file upload control and storing the files in a database. Now I want to display these uploaded excel files within a gridview and on selecting a particular file it should display within a aspx page. Please guide me how
can I do this?

My aspx page-

    
    
Enter PO Number:
    

        
    
    
    
Upload PO Chart:
    

        
    
    
    

        
        

        
        
    
    
    


    









    
    
Uploaded Files
    
    
    

        
            
                
                    
                        
                    
                
                
                    
                        
                    
                
                
                    
                        
                    
                
                
                    
                                                     Text="Show" onclick="btndwnld_Click" />
                    
                
            
        
    
    
    
    

        
        
    
    
    

Now in CS page I have read some where to approach like this-

protected void ShowBtn_Click(object sender, EventArgs e)
    {
        Button btn = (Button)sender;
        string filename = btn.CommandArgument.ToString();
        string sexcelconnectionstring = "";
        string path = Server.MapPath("~/"+filename);
        //Use this if .xls
        // sexcelconnectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
        sexcelconnectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
        using (OleDbConnection conn = new OleDbConnection(sexcelconnectionstring))
        {
            conn.Open();
            DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string sheetname = dt.Rows[0]["Table_Name"].ToString();
            string query = "SELECT * FROM [" + sheetname + "]";
            OleDbCommand ocmd = new OleDbCommand(query, conn);
            OleDbDataAdapter adp = new OleDbDataAdapter(ocmd);
            DataSet ds = new DataSet();
            adp.Fill(ds);
            GridView2.DataSource = ds.Tables[0];
            GridView2.DataBind();

        }
    }

But in this case I am having a doubt, since I am using SQL Server, So what should be my Provider for connection string in cs code. Then to read the excel file and store it withing DataTable Object dt what should I do to get the schema of the table.
Please guide me.?

Hello,

Let me clear the code what you have written here.

1) You are saving the excel file(s) in your virtual directory , when you are using below line :

FileUpload1.SaveAs(Server.MapPath("Upload/")+fname);

2) You are saving the filename and filepath of your excel file in your database (not the excel file).You are doing this on this following line:

string sql = "insert into tbl_po_file_upld(po_no,po_file_name,po_file_path) values('" + txtpon.Text.ToString() + "','" + fname + "/" + dt + "','~/Upload/" + fname + "')";

3) Now you are searching your virtual directory for that particular excel file , details of the file are comming from your database , so the connection string would be , This you are doing in below line:

 string path = Server.MapPath("~/"+filename);
        //Use this if .xls
        // sexcelconnectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
        sexcelconnectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";

I hope this clears the picture about your codes.