Click here to Skip to main content
15,746,107 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have below code

public void impSpanJvNSEF(string FilePath, int eximID)
          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;';");
            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);

            cmd = null;
            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

            return (ds);

but in above query my

shows empty data and gives exception No value given for one or more required parameters?
Updated 8-Nov-14 4:16am

1 solution

use OleDbConection For Conect To Excel File

Query String Include : Select * from [Sheetname$] Where Condition

OledbCommand and Oledbdataadabtor For Fill Datatabe
Share this answer
Member 9410081 3-Sep-13 0:56am    
"SELECT * FROM [" + SheetName + "$] WHERE " + ColumnToCheckNotNull + " IS NOT NULL";

i have used above query still not working giving exception No value given for one or more required parameters

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900