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

    How to read data from an Excel sheet

    how to read data from an Excel sheet, the name of the sheet is dynamic.
  • #466916
    try this code

    this.openFileDialog1.FileName = "*.xls";
    if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
    {
    Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(
    openFileDialog1.FileName, 0, true, 5,
    "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false,
    0, true);
    Excel.Sheets sheets = theWorkbook.Worksheets;
    Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
    for (int i = 1; i <= 10; i++)
    {
    Excel.Range range = worksheet.get_Range("A"+i.ToString(), "J" + i.ToString());
    System.Array myvalues = (System.Array)range.Cells.Value;
    string[] strArray = ConvertToStringArray(myvalues);
    }
    }


    for more detail visit this link

    http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/b6e8a28c-6760-4e86-a1aa-e2ce9ec36380/

  • #466919
    thanks for ur kind reply, but, i want to read using OleDB.

  • #466925
    dear kolanravinder

    for that please visit this link coz that code is long thats why i giving this link

    http://www.codeproject.com/KB/office/excel_using_oledb.aspx

  • #467074
    Step-1: Include the connection string for the EXCEL file containing the filename and Provider settings.


    string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("Filename.xls") + ";" + "Extended Properties=Excel 8.0;";


    Step-2: Create a new connection object and open it for processing.

    OleDbConnection objConn = new OleDbConnection(connectionString);
    objConn.Open();


    Step-3: Create a command object for querying and pass that command with the connection object created.

    String strConString = "SELECT firstColumnName FROM [Sheet1$]";
    //where date = CDate('" + DateTime.Today.ToShortDateString() + "')";
    OleDbCommand objCmdSelect = new OleDbCommand(strConString, objConn);

    Step-4: Here, in this example i am going to use DataSet for reading and holding the data, for which i am creating a Data Adapter Object.

    // Create new OleDbDataAdapter that is used to build a DataSet
    // based on the preceding SQL SELECT statement.
    OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
    // Pass the Select command to the adapter.
    objAdapter1.SelectCommand = objCmdSelect;
    // Create new DataSet to hold information from the worksheet.
    DataSet objDataset1 = new DataSet();


    Step-5: Now, fill the data from the EXCEL file by querying with the command object and store them into the Dataset created.


    // Fill the DataSet with the information from the worksheet.
    objAdapter1.Fill(objDataset1, "ExcelData");


    Step-6: Process the data for displaying with the Dataset, which you can set as datasource for many ASP.NET controls.


    for (int i = 0; i < objDataset1.Tables[0].Rows.Count; i++)
    {
    document.Write("Data: " + objDataset1.Tables[0].Rows[i].ItemArray[0].ToString() + "
    ");
    }


    Step-7: As we are at the end, important thing is to Close the connection.


    // Clean up objects.
    objConn.Close();

    Regards


  • 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.