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

    How to get data from excel

    Hi Everyone,
    I'm trying to get data from excel sheet and send some part of the same data to a method. I'm getting data from data grid. There is blank space for each set of data. Each set can be passed as a parameter to method.
    kindly suggest me how to achieve this.
    thanks,
    Sreeja
  • #768608
    Hi,

    I'm little bit confused about your requirement in your first point you said that you are trying to get data from excel sheet and immediately you say that you are getting data from data grid.

    Not sure what you are trying to achieve but as per headline I suspect that you want excel sheet data into temporary table like dataset / datatable.

    please use below piece of code, this might be helpful to you

    public static DataTable ReadExcel(string excelPath, string sheetName)
    {
    DataTable dtSheet = new DataTable();
    try
    {
    #region Read data from excel
    objApp.Visible = false;

    //Open the Workbook
    objWrkbook = objApp.Workbooks.Open(excelPath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

    //Get all sheets available under the workbook
    objSheets = (Excel.Sheets)objWrkbook.Worksheets;

    // Get the particular sheet which you want to get the information
    objWrkSheet = (Microsoft.Office.Interop.Excel.Worksheet)objSheets.get_Item(sheetName);

    //Get the column count of the sheet
    int colcnt = objWrkSheet.UsedRange.Columns.Count;

    //Get Row count of the sheet
    int rowcnt = objWrkSheet.UsedRange.Rows.Count;



    DataRow dr;
    Excel.Range rang;

    for (int row = 1; row <= rowcnt; row++)
    {
    dr = dtSheet.NewRow();
    for (int col = 1; col <= colcnt; col++)
    {
    rang = (Excel.Range)objWrkSheet.Cells[row, col];
    if (row == 1)
    {
    // Read Headers from Excel Sheet and store it in DataTable
    dtSheet.Columns.Add(Convert.ToString(rang.Value2));
    }
    else
    {
    // Read rows
    dr[col - 1] = rang.Value2;

    }

    }
    if (row != 1)
    dtSheet.Rows.Add(dr);

    dtSheet.AcceptChanges();
    #endregion

    }
    }
    catch (Exception ex)
    {
    throw;
    }
    finally
    {
    if (objWrkSheet != null)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(objWrkSheet);
    if (objWrkbook != null)
    objWrkbook.Close(false, inputPath, Type.Missing);
    if (objApp != null)
    objApp.Quit();
    }
    return dtSheet;
    }



    if you want to know more details on how to read and write data from excel sheet refer below link.
    http://www.dotnetspider.com/resources/46439-Read-and-Write-Excel-in-C.aspx

    Hope this helps you..

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

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #768611
    Hi Naveen,
    Thanks for your information.
    PFA excel document. my input data is from sheet named "Change Log".I need only two columns of data "Change Description" and "Code" which are starting from second row.
    I'm using below code:
    con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dictFile.FileName + ";Extended Properties='Excel 8.0;HDR=No'");
    con.Open();
    cmd = new OleDbDataAdapter("select * from [change Log$]", con);
    cmd.Fill(dsData);
    dgCodeDesc.DataSource = dsData.Tables[0];
    then I'm getting whole data into data grid.
    when I'm trying to execute below code, getting an exception:
    con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dictFile.FileName + ";Extended Properties='Excel 8.0;HDR=No'");
    con.Open();
    cmd = new OleDbDataAdapter("select Change Descriptio,Code from [change Log$]", con);

    Exception details:
    "An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

    Additional information: Syntax error (missing operator) in query expression 'Change Description'."

    Requirement 1: Need to get only two columns(change description, code) data into grid.
    Requirement 2: After getting data into grid, Need to process it in such a way that the records which are between "The following BU's have been renamed:" and the blank space. this set of records will be the parameter to method.

    please find the attache doc for further reference.

    Thanks,
    Sreeja

    GPD_Data_Dictionary_v131-June-2016.xls

    Delete Attachment

  • #768613
    You can try this code snippet to get data from excel


    protected void Page_Load(object sender, EventArgs e)
    {
    string connString = ConfigurationManager.ConnectionStrings["xls"].ConnectionString;
    OleDbConnection oledbConn = new OleDbConnection(connString);
    try
    {

    oledbConn.Open();

    OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);


    OleDbDataAdapter oleda = new OleDbDataAdapter();

    oleda.SelectCommand = cmd;

    DataSet ds = new DataSet();


    oleda.Fill(ds, "Employees");
    GridView1.DataSource = ds.Tables[0].DefaultView;
    GridView1.DataBind();
    }
    catch
    {
    }
    finally
    {
    oledbConn.Close();
    }

  • #768614
    Hi,

    The problem is while you call the column with spaces you should use single quotes for the same other wise you may get this type of issues only.

    Ex:
    Instead of Change Description you should use 'Change Description'.

    use below sample code for your reference.

    private void Display()
    {
    OleDbConnection oconn = null;
    try
    {
    string FileName = ViewState["FileName"] as string;
    string FilePath = ViewState["FilePath"] as string;

    oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=Excel 8.0");

    //select file name based upon dropdown selecteditem.

    OleDbCommand ocmd = new OleDbCommand("select 'Change Description', Code from [ChangeLog$]", oconn);

    oconn.Open();

    //Read all rows and then store in DataTable

    OleDbDataReader odr = ocmd.ExecuteReader();

    while (odr.Read())
    {
    //do your job here
    }

    }
    catch (DataException ex)
    {
    }
    finally
    {
    if (oconn != null)
    {
    oconn.Close();
    oconn.Dispose();
    }

    }
    }


    Hope this helps you...

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

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #768621
    Check this thread:

    http://www.c-sharpcorner.com/blogs/import-and-export-data-from-excel-to-database


Sign In to post your comments