C#实现Sql2005与Excel数据交互与转换

实现数据库(SQL2005)导出导入Excel：红色部分根据自己的实际情况修改!

 

protected void Button1_Click(object sender, EventArgs e)
    {
        //导出
        DB_Excel("Public", "Company", "C:\\Users\\justinkwan\\Desktop\\c.xls", "justinkwan", "sa", "sa", "out");
        //导入
        //DB_Excel("Public", "Company", "C:\\Users\\justinkwan\\Desktop\\c.xls", "justinkwan", "sa", "sa", "in");
    }

    /// <summary>
    /// 导入导出
    /// </summary>
    /// <param name="DB">数据库名</param>
    /// <param name="Table">表名</param>
    /// <param name="File">导出/导入的Excel文件路径</param>
    /// <param name="Server">数据库服务器名</param>
    /// <param name="User">数据库服务器用户名</param>
    /// <param name="Pwd">数据库服务器密码</param>
    /// <param name="type">"out":数据库导出到Excel;  "in":Excel导入数据库</param>
    public void DB_Excel(string DB, string Table, string File, string Server, string User, string Pwd, string type)
    {
        string sql = "EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;";
        sql += "Exec xp_cmdshell 'bcp " + DB + ".dbo." + Table + " " + type + " " + File + " -c -q -S\"" + Server + "\" -U\"" + User + "\" -P\"" + Pwd + "\"';";
        sql += "EXEC sp_configure 'show advanced options', 1; RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 0;RECONFIGURE;";

        Executesql(sql, "server=" + Server   + ";database=" + DB  + ";uid=" + User + ";pwd=" + Pwd );