求一个可以连接数据库倒入导出的execl功能
------最佳解决方案--------------------
把excel当作数据库来操作,就变的简单了。
使用OleDb,让Excel操作与数据库一样简单
namespace GuideInto
{
public class DAL_File
{
public static string FilePath
{
get;
set;
}
/// <summary>
/// 连接Excel
/// </summary>
/// <returns></returns>
public static OleDbConnection Conn()
{
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=True;Data Source=" + FilePath + ";Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(connStr);
return conn;
}
/// <summary>
/// 读取所有工作薄
/// </summary>
/// <returns></returns>
public static DataTable GetAllExeclWorkBook()
{
using (OleDbConnection conn = Conn())
{
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
return dt;
}
}
/// <summary>
/// 根据Sql读取Excel中的数据
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataSet Query(string sql)
{
using (OleDbConnection conn = Conn())
{
conn.Open();
OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);