无法获取数据集值 [英] Unable to fetch dataset value

问题描述

Iam尝试使用OLEDB从.xlsx读取数据。但我无法从数据集中检索价值。我得到的值是System.Data.DataRow。但我需要为每一行获得一个值。我在下面给出了我的编码。

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.

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:

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();
          }
      }

推荐答案

提供列索引

Provide the Column Index

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

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

这是因为您只访问行：ds.Tables [0] .Rows [i] .ToString（）;



您想要访问行中的列。这是一种方式：

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:

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

这篇关于无法获取数据集值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！