Read excel spreadsheet into a datatable

Question

I am using the below C# code in my program to read excel 97 - 2003 spreadsheet data into a datatable using oledbconnection and ran into the name does not exist in the current context.

DataTable rs = null;

string path = Path.GetFullPath(filePath);
odConnection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
odConnection.Open();
OleDbCommand cmd = new OleDbCommand(); ;
OleDbDataAdapter oleda = new OleDbDataAdapter();
DataSet ds = new DataSet();
DataTable dt = odConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = string.Empty;
if (dt != null)
{
    sheetName = dt.Rows[0]["Sheet_Name"].ToString();
}

cmd.Connection = odConnection;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
oda = new OleDbDataAdapter(cmd);
oda.Fill(ds, "excelData");
rs = ds.Tables["excelData"];

Answer 1

Here is example how to get all columns and rows from special Sheet from xlsx file. This code takes all data from Sheet2 from xlsx file and fill the DataTable with that values.

Hopefully this will help you.

using System;
using System.Data;
using System.Data.OleDb;

namespace ConsoleApp4
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable rs = new DataTable();

            using (var odConnection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Users\IIG\Desktop\test.xlsx;Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"))
            {
                odConnection.Open();

                using (OleDbCommand cmd = new OleDbCommand())
                {
                    cmd.Connection = odConnection;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "SELECT * FROM [Sheet2$]";
                    using (OleDbDataAdapter oleda = new OleDbDataAdapter(cmd))
                    {
                        oleda.Fill(rs);
                    }
                }
                odConnection.Close();
            }
            foreach(DataRow row in rs.Rows)
            {
                foreach(object item in row.ItemArray)
                {
                    Console.Write(item +"\t");
                }
                Console.WriteLine();
            }
        }
    }
}