读取Excel数据到DataTable

读取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)        

{           

throw new 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)       

{          

 return null;       

}       

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>    public string GetExcelConnection(string strFilePath)    {        if (!File.Exists(strFilePath))        {            throw new 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();            }

转载于:https://www.cnblogs.com/taofx/p/4141953.html