My application need's to read a certain row on excel document.Anyone can help with C# code for reading excel doc.

I have included a few methods to create the OleDb connection strings for you depending on the version of Excel.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace daniweb
{
  public partial class frmExcel : Form
  {
    public frmExcel()
    {
      InitializeComponent();
    }


    public static string BuildExcelConnectionString(string Filename, bool FirstRowContainsHeaders)
    {
      return string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}';Extended Properties=\"Excel 8.0;HDR={1};\"",
                          Filename.Replace("'", "''"),
                          FirstRowContainsHeaders ? "Yes" : "No");
    }
    public static string BuildExcel2007ConnectionString(string Filename, bool FirstRowContainsHeaders)
    {
      return string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR={1}\";",
                            Filename.Replace("'", "''"),
                            FirstRowContainsHeaders ? "Yes" : "No");
    }
    private static void ReadExcelFile()
    {
      string connStr = BuildExcel2007ConnectionString(@"C:\Data\Spreadsheet.xlsx", true);
      //Note 'row' is the first column in my spreadsheet.
      string query = @"Select * From [Sheet1$] Where Row = 2";
      using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connStr))
      {
        conn.Open();
        using (System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(query, conn))
        {
          using (System.Data.OleDb.OleDbDataReader dr = cmd.ExecuteReader())
          {
            DataTable dt = new DataTable();
            dt.Load(dr);
            //At this point you have your Data loaded in "dt"
            MessageBox.Show(dt.Rows.Count.ToString());
            System.Diagnostics.Debugger.Break();
          }
        }
      }
    }

    private void button1_Click(object sender, EventArgs e)
    {
      ReadExcelFile();
    }
  }
}

The code you gave me i tried it, but it gives me this error - ''C:\Data\GraduatesList.xlsx' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.'

Is the file located at ''C:\Data\GraduatesList.xlsx"? And are you doing this from a form application or a web application?

The Excel file is in a folder together with web application, and i dont want to attach to the project, the thing is just the correct path that can connect it even if i move it to another computer

Use Server.MapPath() to access files in the local filesystem within a web application. There are examples in the help file of how to call it.

This article has been dead for over six months. Start a new discussion instead.