Unable to fetch dataset value

Question

0.00/5 (No votes)

See more:

Iam trying to read data from .xlsx using OLEDB. But I couldnt retrieve value from dataset. Iam getting value as "System.Data.DataRow" . But i need to get an value for each row. I have given my coding below.

C#

private void ImportExcel(string strFilePath)
      {
          if (!File.Exists(strFilePath)) ;
          String strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;"
      + "Data Source=" + strFilePath + ";"
      + "Extended Properties='Excel 8.0;HDR=Yes'";
          OleDbConnection connExcel = new OleDbConnection(strExcelConn);
          OleDbCommand cmdExcel = new OleDbCommand();
          try
          {
              cmdExcel.Connection = connExcel;
              //Check if the Sheet Exists
              connExcel.Open();
              DataTable dtExcelSchema;
              //Get the Schema of the WorkBook
              dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
              connExcel.Close();
              //Read Data from Sheet1
              connExcel.Open();
              OleDbDataAdapter da = new OleDbDataAdapter();
              DataSet ds = new DataSet();
              string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
              cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
              da.SelectCommand = cmdExcel;
              da.Fill(ds);
              for (int i = 0; i <= ds.Tables[0].Rows.Count; i++)
              {
                  string name = ds.Tables[0].Rows[i].ToString();
              }
              connExcel.Close();

          }
          catch (Exception ex)
          {
              MessageBox.Show(ex.Message, "ImportExcel");
          }
          finally
          {
              cmdExcel.Dispose();
              connExcel.Dispose();
          }
      }

What I have tried:

C#

private void ImportExcel(string strFilePath)
      {
          if (!File.Exists(strFilePath)) ;
          String strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;"
      + "Data Source=" + strFilePath + ";"
      + "Extended Properties='Excel 8.0;HDR=Yes'";
          OleDbConnection connExcel = new OleDbConnection(strExcelConn);
          OleDbCommand cmdExcel = new OleDbCommand();
          try
          {
              cmdExcel.Connection = connExcel;
              //Check if the Sheet Exists
              connExcel.Open();
              DataTable dtExcelSchema;
              //Get the Schema of the WorkBook
              dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
              connExcel.Close();
              //Read Data from Sheet1
              connExcel.Open();
              OleDbDataAdapter da = new OleDbDataAdapter();
              DataSet ds = new DataSet();
              string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
              cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
              da.SelectCommand = cmdExcel;
              da.Fill(ds);
              for (int i = 0; i <= ds.Tables[0].Rows.Count; i++)
              {
                  string name = ds.Tables[0].Rows[i].ToString();
              }
              connExcel.Close();

          }
          catch (Exception ex)
          {
              MessageBox.Show(ex.Message, "ImportExcel");
          }
          finally
          {
              cmdExcel.Dispose();
              connExcel.Dispose();
          }
      }

Posted 12-Aug-16 2:12am

vinodh muthusamy

Updated 12-Aug-16 2:16am

Add a Solution

Comments

The Praveen Singh 12-Aug-16 8:13am

what error you have get ??

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Karthik_Mahalingam · Answer 1 · 2016-08-12T02:16:00

Solution 2

Provide the Column Index

C#

int columnIndex =1; // the column index of the table which you want to read
string name = ds.Tables[0].Rows[i][columnIndex].ToString();

Or, using Column Name

C#

string columnName = "Column1"; // name of the column in the DataTable
string name = ds.Tables[0].Rows[i][columnName].ToString();

Posted 12-Aug-16 2:16am

Karthik_Mahalingam

Updated 12-Aug-16 2:18am

v2

Comments

vinodh muthusamy 16-Aug-16 2:15am

Thanks for your valuable answer, But i need to fetch value Each row wise, Not Each column. Kindly solve this issue.

Karthik_Mahalingam 16-Aug-16 2:22am

row wise only. just loop it.

vinodh muthusamy 16-Aug-16 2:35am

Karthik,

I have Looped, But iam getting Column Wise values. Here is my code.

int columnindex = 0;

for (int i = 0; i <= ds.Tables[0].Rows.Count; i++)
{
string name = ds.Tables[0].Rows[i][columnindex].ToString();

}

Karthik_Mahalingam 16-Aug-16 2:37am

boss, you will get the name on each row. this is how we do the loop,

ZurdoDev · Answer 2 · 2016-08-12T02:16:00

Solution 1

This is because you are only accessing the row: ds.Tables[0].Rows[i].ToString();

You want to access the column within the row. Here is one way:

C#

ds.Tables[0].Rows[i][columnNameorIndex].ToString();

Posted 12-Aug-16 2:16am

ZurdoDev

Comments

Karthik_Mahalingam 12-Aug-16 8:20am

we have posted at the same time :)

vinodh muthusamy 16-Aug-16 2:15am

Thanks for your valuable answer, But i need to fetch value Each row wise, Not Each column. Kindly solve this issue.

ZurdoDev 16-Aug-16 6:40am

You should be able to read the code and see what it is doing. It is clearly accessing the Rows collection with an index of i. So, this should be very easy for you.