Function to Import Excel Data to dataset in C#

[Javed Ahmed]

I want to import data from excel file (that contains one sheet) to dataset using C#. So far, I have tried oledb method, excel data reader method but both of them don't return expected results. In my oledb method, i get rows till 255th row and after 255th row the rows doesn't get returned. But when i change the content of 256th row, I get all the rows (i.e 900 rows as in sheet). It is really weird as I have checked the excel sheet and there is no issue with the contents of the file. There is no macro, and contents of each row are alike (I checked 256th row thoroughly). And when I try excel data reader method it returns 0 rows. I don't know what is the reason for it. Now, I am in search of another method to import excel data to dataset. Could you please let me know any good function that will work for me?

Both of my methods are as follows,

public DataSet GetDataFromExcel(string filePath)
{
try
{
string strConn;
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text\"";
DataTable dt = new DataTable();
dt = null;
using (OleDbConnection oleDB = new OleDbConnection(strConn))
{
oleDB.Open();
dt = oleDB.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
return null;

ListItemCollection items = new ListItemCollection();
int i = 0;

//if (dt.Rows.Count > 1)
//return null;

for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
{
string excelSheetName;
string lastCharacter = "";

excelSheetName = dt.Rows[rowIndex]["TABLE_NAME"].ToString();
excelSheetName = excelSheetName.Replace("'", "");
lastCharacter = excelSheetName.Substring(excelSheetName.Length - 1, 1);
if (lastCharacter == "$")
{
items.Add(dt.Rows[rowIndex]["TABLE_NAME"].ToString());
}
}
if (items.Count > 1)
return null;

string sName;
string query;

sName = items[0].ToString();
sName = sName.Replace("'", "");
sName = sName.Replace("$", "");

query = "";
query = String.Format("select * from [{0}$]", sName);
OleDbDataAdapter da = new OleDbDataAdapter(query, strConn);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}

}
catch (Exception ex)
{

throw ex;
}
}



public static DataSet ImportExceltoDataset(string file)
{
IExcelDataReader iExcelDataReader = null;

FileStream oStream = File.Open(file, FileMode.Open, FileAccess.Read);

iExcelDataReader = ExcelReaderFactory.CreateBinaryReader(oStream);

iExcelDataReader.IsFirstRowAsColumnNames = true;

DataSet dsUnUpdated = new DataSet();

dsUnUpdated = iExcelDataReader.AsDataSet();

iExcelDataReader.Close();

return dsUnUpdated;
}




Javed Ahmed

Continue reading...