读取excel到datatable

Question

求读取excel到datatable的函数，
说明：在不知道excel格式和sheet名的情况下也适用。
谢谢！

Answer 1

http://blog.csdn.net/jinjazz/archive/2008/05/13/2441635.aspx
http://blog.csdn.net/jinjazz/archive/2007/12/11/1930455.aspx

Answer 2

OpenFileDialog ofd = new OpenFileDialog();
            ofd.Title = "Excel文件";
            ofd.FileName = "";
            ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);//为了获取特定的系统文件夹，可以使用System.Environment类的静态方法GetFolderPath()。该方法接受一个Environment.SpecialFolder枚举，其中可以定义要返回路径的哪个系统目录
            ofd.Filter = "Excel文件(*.xls)|*.xls";
            try
            {                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    string tableName = "";
                    if (arratlist != null)
                    {
                        arratlist.Clear();
                    }
                    string Path = ofd.FileName;
                    string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
                    OleDbConnection conn = new OleDbConnection(strConn);
                    conn.Open();
                    System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                    tableName = schemaTable.Rows[0][2].ToString().Trim();//sheet名称
                    for (int i = 0; i < schemaTable.Rows.Count; i++)
                    {
                        arratlist.Add(schemaTable.Rows[i][2].ToString().TrimStart('\'').Trim('\'', '$'));
                    }
                    arratlist.Sort();
                    string strExcel = "Select   *   From   [" + tableName + "]";
                    OleDbCommand cmd = new OleDbCommand(strExcel, conn);
                   DataTable excelDt = new DataTable();
                    OleDbDataAdapter da = new OleDbDataAdapter(strExcel, conn);
                    da.Fill(excelDt);
                    conn.Close();

Answer 3

 public DataTable TransferDs(string excelFile, string sheetName, string connectionString, string sqlExl)
    {
        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;";
        OleDbConnection conn = new OleDbConnection(strConn);
        DataSet ds = new DataSet();
        try
        {
            //获取全部数据              conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            strExcel = string.Format(sqlExl, sheetName);
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            myCommand.Fill(ds, sheetName);
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            conn.Close();
        }
        return ds.Tables[0];
    }