读取Excel数据到DataTable

///<summary>
    /// 获取指定路径、指定工作簿名称的Excel数据:取第一个sheet的数据
    ///</summary>
    ///<param name="FilePath">文件存储路径</param>
    ///<param name="WorkSheetName">工作簿名称</param>
    ///<returns>如果争取找到了数据会返回一个完整的Table，否则返回异常</returns>
public DataTable GetExcelData(string astrFileName)
    {
        string strSheetName = GetExcelWorkSheets(astrFileName)[0].ToString();
        return GetExcelData(astrFileName, strSheetName);
    }

 

///<summary>
    /// 返回指定文件所包含的工作簿列表;如果有WorkSheet，就返回以工作簿名字命名的ArrayList，否则返回空
    ///</summary>
    ///<param name="strFilePath">要获取的Excel</param>
    ///<returns>如果有WorkSheet，就返回以工作簿名字命名的ArrayList，否则返回空</returns>
public ArrayList GetExcelWorkSheets(string strFilePath)
    {
        ArrayList alTables =new ArrayList();

        OleDbConnection odn =new OleDbConnection(GetExcelConnection(strFilePath));
        odn.Open();

        DataTable dt =new DataTable();

        dt = odn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        if (dt ==null)
        {
            thrownew Exception("无法获取指定Excel的架构。");
        }

        foreach (DataRow dr in dt.Rows)
        {
            string tempName = dr["Table_Name"].ToString();

            int iDolarIndex = tempName.IndexOf('$');

            if (iDolarIndex >0)
            {
                tempName = tempName.Substring(0, iDolarIndex);
            }

            //修正了Excel2003中某些工作薄名称为汉字的表无法正确识别的BUG。
if (tempName[0] =='\'')
            {
                if (tempName[tempName.Length -1] =='\'')
                {
                    tempName = tempName.Substring(1, tempName.Length -2);
                }
                else
                {
                    tempName = tempName.Substring(1, tempName.Length -1);
                }

            }
            if (!alTables.Contains(tempName))
            {
                alTables.Add(tempName);
            }
        }

        odn.Close();

        if (alTables.Count ==0)
        {
            returnnull;
        }

        return alTables;
    }

 

///<summary>
    /// 获取指定路径、指定工作簿名称的Excel数据
    ///</summary>
    ///<param name="FilePath">文件存储路径</param>
    ///<param name="WorkSheetName">工作簿名称</param>
    ///<returns>如果争取找到了数据会返回一个完整的Table，否则返回异常</returns>
public DataTable GetExcelData(string FilePath, string WorkSheetName)
    {
        DataTable dtExcel =new DataTable();
        OleDbConnection con =new OleDbConnection(GetExcelConnection(FilePath));
        OleDbDataAdapter adapter =new OleDbDataAdapter("Select * from ["+ WorkSheetName +"$]", con);

        //读取
        con.Open();
        adapter.FillSchema(dtExcel, SchemaType.Mapped);
        adapter.Fill(dtExcel);
        con.Close();
        dtExcel.TableName = WorkSheetName;

        //返回
return dtExcel;
    }

 

///<summary>
    /// 获取链接字符串
     ///</summary>
    ///<param name="strFilePath"></param>
    ///<returns></returns>
publicstring GetExcelConnection(string strFilePath)
    {
        if (!File.Exists(strFilePath))
        {
            thrownew Exception("指定的Excel文件不存在！");
        }
        return"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ strFilePath +";Extended properties=\"Excel 8.0;Imex=1;HDR=Yes;\"";

         //@"Provider=Microsoft.Jet.OLEDB.4.0;" +
         //@"Data Source=" + strFilePath + ";" +
         //@"Extended Properties=" + Convert.ToChar(34).ToString() +
         //@"Excel 8.0;" + "Imex=1;HDR=Yes;" + Convert.ToChar(34).ToString();        
    }