Shall I read an excel file via OleDb Jet4.0 and save into dataset?

I have to read an excel file and put into a dataset.

Shall I read excel file content via OleDbDataAdapter, and then Fill into a dataset? I tried but faild. It said the application cannot recognize database format when data adapter is doing Fill method.

Code:

String queryAll = "SELECT * FROM [Sheet1开发者_C百科$]";
String xlsPath = Directory.GetCurrentDirectory() + "\\paid.xls";
String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + xlsPath;
try
{
    m_dbDA = new OleDbDataAdapter(queryAll, strConn);
    DataSet dsPaidXls = new DataSet();
    m_dbDA.Fill(dsPaidXls);  //exception here
 }
 catch (System.Exception ex)
 {
    MessageBox.Show(ex.Message);
 }

Does it mean there is no way to directly read an excel data and put into a new dataset? And the only one way is to read excel data cell by cell and insert to a new DataSet with datatable?

Thanks in advance.

========================================
Resolved

========================================

String queryAll = "SELECT * FROM [Sheet1$]";
String xlsPath = Directory.GetCurrentDirectory() + "\\paid.xls";
String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + xlsPath +
                 ";Extended Properties='Excel 8.0;IMEX=1';";
try
{
   m_dbDA = new OleDbDataAdapter(queryAll, strConn);
   DataSet dsPaidXls = new DataSet();
   m_dbDA.Fill(dsPaidXls,"[Sheet1$]");
   dataGridView1.DataSource = dsPaidXls;
   dataGridView1.DataMember = "[Sheet1$]";
 }
 catch (System.Exception ex)
 {
     MessageBox.Show(ex.Message);
 }

OLEDB works quite well once you have the correct connection string and are aware of issues with data types. Jet is for versions prior to 2007 and you need to add extended properties for Excel.

String strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" 
  + xlsPath + "Extended Properties='Excel 12.0 Xml;HDR=YES';";

See: Connection Strings
How To Use ADO with Excel Data from Visual Basic or VBA (contains useful notes)
Various notes

When you read Excel files via OleDB, make sure you have the right version of the provider (one for xls, one for xlsx), and also make sure you have x86 selected as platform.

If you don't, it will compile to x64 on a 64-Bit system, and because OleDb is deprecated, there are no 64-bit OleDb drivers, which means your program will crash on calling OleDb.

Also, the Office 2007 system driver (ACE Data Connectivity Components) must be installed.

See here:
Diagnosing an OLEDB exception when Quering Excel 2010

You could also use ODBC or Excel Package Plus.
Again, you need to take a different libary here if the format is xls and not xlsx.

ExcelLibrary for XLS
http://code.google.com/p/excellibrary/

Excel Package Plus for XLSX
http://epplus.codeplex.com/

You should avoid using OleDb to read Excel files. Many pitfals.

For xls files, this works much better and more reliably.

http://www.codeproject.com/KB/office/ExcelReader.aspx

For xlsx files, use the Office Open XML SDK:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=5124