Excel Data Import / Export in MSSQL using asp.net

I want to import Excel Data in MSSQL using asp.net. Can anyone send me full coding to add excel data and in shows in gridview.

I am using these fields in excel Student Name, College, Bach Year

1 answer

  • answered 2018-11-08 07:56 Nakul

    Please install Interop.Excel from NuGet

    Using Microsoft.Office.Interop.Excel.

    Get records from excel file using below funtion

     public DataTable ReadExcel(string fileName, string fileExt) {  
        string conn = string.Empty;  
        DataTable dtexcel = new DataTable();  
        if (fileExt.CompareTo(".xls") == 0)  
            conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended 
        Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //for below excel 2007  
        else  
            conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended 
        Properties='Excel 12.0;HDR=NO';"; //for above excel 2007  
        using(OleDbConnection con = new OleDbConnection(conn)) {  
            try {  
                OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$]", 
         con); //here we read data from sheet1  
                oleAdpt.Fill(dtexcel); //fill excel data into dataTable  
            } catch {}  
        }  
        return dtexcel;  
        }  
    

    Then on button click use below code

    private void btnChooseFile_Click(object sender, EventArgs e) {  
        string filePath = string.Empty;  
        string fileExt = string.Empty;  
        OpenFileDialog file = new OpenFileDialog(); //open dialog to choose file  
        if (file.ShowDialog() == System.Windows.Forms.DialogResult.OK) //if there is a file choosen by the user  
        {  
            filePath = file.FileName; //get the path of the file  
            fileExt = Path.GetExtension(filePath); //get the file extension  
            if (fileExt.CompareTo(".xls") == 0 || fileExt.CompareTo(".xlsx") == 0) {  
                try {  
                    DataTable dtExcel = new DataTable();  
                    dtExcel = ReadExcel(filePath, fileExt); //read excel file  
                    dataGridView1.Visible = true;  
                    dataGridView1.DataSource = dtExcel;  
                } catch (Exception ex) {  
                    MessageBox.Show(ex.Message.ToString());  
                }  
            } else {  
                MessageBox.Show("Please choose .xls or .xlsx file only.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error); //custom messageBox to show error  
            }  
        }  
    }  
    

    Happy coding!!!!!