You must Sign In to post a response.
  • Category: Visual Studio

    Import Excel to Gridview

    Dear All,

    i need to import excel sheet to Gridview, and the Gridview datas have to insert into sql server.
    it's happening.
    But my problem is, while i import from excel to gridview, it also added some extra rows in gridview,
    for example, excel contains 3 rows, while it comes to gridview it has 6 rows.

    Need to remove empty rows in gridview ?

    My Code is ,

    Dim cn As System.Data.OleDb.OleDbConnection
    Dim cmd As System.Data.OleDb.OleDbDataAdapter
    Dim DT As DataTable = New DataTable
    cn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & txtPath.Text & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes""")

    cmd = New System.Data.OleDb.OleDbDataAdapter("select * from [Contact$]", cn)
    'Load the values from Data Table to Grid View

    grdContactDetails.DataSource = DT

    Ramkumar G.R
  • #756137

    Nothing issue in your coding side, the issue is in Excel sheet. I think you are copied rows from one sheet to another, at the time of coping the rows might be copied duplicate cells.

    As per your point of view you are saying from 4th row to 6th row you are getting this issue right.?

    compare 6th row with 7th row in excel sheet there is some difference in cells. I mean up to 6th row the cells are enabled from 7th row onwards its disabled that is the reason it show up to 6th row. If you want to remove unwanted spaces rows. Then select those rows in your excel sheet and delete those and then check it once.

    Hope you got my point.

    Give respect to your work, Instead of trying to impress your boss.

    Blog :

  • #756138
    Here is the code to read the data from the excel file and then bind it to the grid view.

    String fileName = string.Format("{0}\\fileNameHere", Directory.GetCurrentDirectory());
    String connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);

    SqlDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [workSheetNameHere$]", connectionString);
    Dataset ds = new DataSet();


    DataTable data = ds.Tables[0];

    Thanks & Regards
    Anil Kumar Pandey
    Microsoft MVP, DNS MVM

  • Sign In to post your comments