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); }
oleda.Fill(ds);
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)