如何从excel表读取数据？ [英] how to read data from excel sheet?

问题描述

我有以下代码

i have below code

public void impSpanJvNSEF(string FilePath, int eximID)
  {
      try
      {
          DataSet ds = o_Cls_Utility.ReadExcelToDataSet(FilePath, "SHEET1", "A6",false);




  public DataSet ReadExcelToDataSet(string filepath, string SheetName, string ColumnToCheckNotNull, bool renameHeader = true)
        {
            // need to pass relative path after deploying on server
            string path = System.IO.Path.GetFullPath(filepath);

            /* connection string  to work with excel file. HDR=Yes - indicates 
                that the first row contains columnnames, not data. HDR=No - indicates 
                the opposite. "IMEX=1;" tells the driver to always read "intermixed" 
                (numbers, dates, strings etc) data columns as text. 
            Note that this option might affect excel sheet write access negative. */
            OleDbConnection oledbConn;

            oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
            oledbConn.Open();
            OleDbCommand cmd = new OleDbCommand(); ;
            OleDbDataAdapter oleda = new OleDbDataAdapter();
            DataSet ds = new DataSet();

            // selecting distict list of Slno 
            cmd.Connection = oledbConn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [" + SheetName + "$] WHERE " + ColumnToCheckNotNull + " IS NOT NULL";
            oleda = new OleDbDataAdapter(cmd);
            oleda.Fill(ds);

            cmd = null;
            oledbConn.Close();
            oledbConn = null;

            if (renameHeader == true)
            {
                foreach (DataColumn column in ds.Tables[0].Columns)
                {
                    string cName = ds.Tables[0].Rows[0][column.ColumnName].ToString();
                    if (!ds.Tables[0].Columns.Contains(cName) && cName != "")
                    {
                        column.ColumnName = cName;
                    }
                }

                ds.Tables[0].Rows[0].Delete(); //If you don't need that row any more
                ds.AcceptChanges();
            }

            return (ds);
        }

但在上面查询我的

but in above query my

oleda.Fill(ds);

显示空数据并给出异常没有给出一个或多个必需参数的值？

shows empty data and gives exception No value given for one or more required parameters?

推荐答案

WHERE + ColumnToCheckNotNull + IS NOT NULL;
oleda = new OleDbDataAdapter（cmd）;
oleda.Fill（ds）;

cmd = null ;
oledbConn。关闭（）;
oledbConn = null ;

if （ renameHeader == true ）
{
foreach （DataColumn column > ds.Tables [ 0 ]。列）
{
string cName = ds.Tables [ 0 ]。行[ 0 ] [column.ColumnName]的ToString（）;
if （！ds.Tables [ 0 ]。Columns.Contains（cName）& & cName！= ）
{
column.ColumnName = cName ;
}
}

ds.Tables [ 0 ]。行[ 0 ]删除（）; // 如果您不再需要该行
ds.AcceptChanges（）;
}

return （ds）;
}

WHERE " + ColumnToCheckNotNull + " IS NOT NULL"; oleda = new OleDbDataAdapter(cmd); oleda.Fill(ds); cmd = null; oledbConn.Close(); oledbConn = null; if (renameHeader == true) { foreach (DataColumn column in ds.Tables[0].Columns) { string cName = ds.Tables[0].Rows[0][column.ColumnName].ToString(); if (!ds.Tables[0].Columns.Contains(cName) && cName != "") { column.ColumnName = cName; } } ds.Tables[0].Rows[0].Delete(); //If you don't need that row any more ds.AcceptChanges(); } return (ds); }

但在上面查询我的

but in above query my

oleda.Fill(ds);

显示空数据并给出异常没有给出一个或多个必需参数的值？

shows empty data and gives exception No value given for one or more required parameters?

使用OleDbConection来连接到Excel文件



查询字符串包括：从[Sheetname

use OleDbConection For Conect To Excel File

Query String Include : Select * from [Sheetname

中选择* *

其中条件





OledbCommand和Oledbdataadabtor For Fill Datatabe

Where Condition


OledbCommand and Oledbdataadabtor For Fill Datatabe

这篇关于如何从excel表读取数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！