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

    Importing Excel file in C#

    Hello Friends,

    I am developing a simple project in c#. I need your help.
    My query is. I have to import data from my Excel file.
    After connecting to excel file. when i enter any letter, it should view related words in my grid table.
    for ex: when i press "A", All the names which starts in "A" should view me in my grid table.

    Thanks,

    Shamil
  • #667268
    "I am developing a simple project in c#. I need your help.
    My query is. I have to import data from my Excel file.
    After connecting to excel file. when i enter any letter, it should view related words in my grid table.
    for ex: when i press "A", All the names which starts in "A" should view me in my grid table.".............??????


    MAKE CLEAR ...DO YOU WANT TO USE EXCEL AS DATABASE OR AFTER CONNECTION ON SAME TIME YOU NEED DATA TO DISPLAY?...NOT ABLE TO GET U?

  • #667270
    to show in grid view. just do one thing. first read all data from excel into data table and then process on datatable. To know how to read excel data check my post in below link.

    hope you will find similar solution.

    http://dotnetsquare.com/resources/44-import-data-from-excel-to-sql-server-using-Asp-Net

    If it helps you then Rate this.

    Best Regards,
    Manoranjan Sahoo
    http://www.dotnetsquare.com
    Blog: http://blog.msahoo.net

  • #667277
    [Response removed by Admin. Read forum policies.]
    Thanks & Regards,
    Gaurav Agrawal

  • #667290
    [Response removed by Admin. Read forum policies.]

  • #667306
    Refer below code sample


    protected void Button1_Click(object sender, EventArgs e)
    {
    LoadGrid();
    }
    void LoadGrid()
    {
    string Exfilepath = @"D:\Sample.xls";
    DataTable dt = new DataTable();
    DataRow dr = null;
    SqlConnection sqlcon = new SqlConnection();
    SqlCommand sqlcmd = new SqlCommand();
    string fetch = null;
    sqlcon = new SqlConnection(@"Server=RAVI\SQLEXPRESS;database=test;uid=ravindran;pwd=srirangam;");

    System.Data.OleDb.OleDbConnection MyConnection = null;
    System.Data.DataSet DtSet = null;
    System.Data.OleDb.OleDbDataAdapter MyCommand = null;
    MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + Exfilepath + "';Extended Properties=Excel 8.0;");

    //If you MS Excel 2007 then use below lin instead of above line
    //MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & Exfilepath & "';Extended Properties=Excel 12.0;");

    if (TextBox1.Text != "")
    {
    MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$] where empname='" + TextBox1.Text + "'", MyConnection);
    }
    else
    {
    MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
    }
    DtSet = new System.Data.DataSet();
    MyCommand.Fill(DtSet, "[Sheet1$]");
    dt = DtSet.Tables[0];
    MyConnection.Close();
    }

    Regards
    N.Ravindran
    Your Hard work never fails

  • #667314
    Plase check the following code to get data from an Excel Sheet and store it in datatable. Then you may use the select method of DataTable to filter rows:


    public OleDbConnection GetExcelCon(string strFilePath)
    {
    OleDbConnection excelcon = new OleDbConnection();
    try
    {
    excelcon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
    "Data Source=" + strFilePath + ";Jet OLEDB:Engine Type=5;" +
    "Extended Properties='Excel 8.0;IMEX=1;'");
    }
    catch (Exception ex)
    {
    Response.Write(ex.Message);
    }
    return excelcon;
    }

    public DataTable GetDataFromExcel(string strFilePath, string SheetName)
    {
    DataTable dtCSV = new DataTable();
    try
    {
    OleDbConnection cnCSV = GetExcelCon(strFilePath);
    cnCSV.Open();
    OleDbCommand cmdSelect = new OleDbCommand(@"SELECT * FROM [" + SheetName + "$]", cnCSV);
    OleDbDataAdapter daCSV = new OleDbDataAdapter();
    daCSV.SelectCommand = cmdSelect;
    daCSV.Fill(dtCSV);
    cnCSV.Close();
    daCSV = null;
    }
    catch (Exception ex)
    {
    Response.Write(ex.Message);
    }
    return dtCSV;
    }


    Thanks & Regards
    Paritosh Mohapatra
    Microsoft MVP (ASP.Net/IIS)
    DotNetSpider MVM


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.