Excel导入导出的最好办法

 用了几个月调用COM组件导入导出Excel十分的郁闷，可重用性太差对客户的要求太苛刻，这些天换了个角度去思考做了个不用调用COM组件导入导出的方法，看后感觉不错大家帮忙顶呀！！！

供大家参考：   

导入

    /// <summary>
    /// 得到导入数据，返回DataSet 。
    /// </summary>
    /// <param name="p">Excel文件路径</param>
    /// <returns></returns>
    private DataSet ExcelToDs(string p)
    {
        DataSet ds = new DataSet();
        string strConn = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + p + ";Extended Properties=ExceL 8.0;";
        OleDbConnection conn = new OleDbConnection(strConn);
        conn.Open();
        string strExcel = "SELECT * FROM [Sheet1$]";
        try
        {
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
            myCommand.Fill(ds);
            conn.Close();
            conn.Dispose();
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message.ToString());
        }
        return ds;
    }

导出

    protected void butExecl_Click(object sender, EventArgs e)
    {
        string path = Server.MapPath(ConfigurationManager.AppSettings["UploadRoot"]) + @"temp/temp" + UserID.ToString() + @".xls";          //存放临时Excel文件的路径。
        ExportExcel(path);

        HttpResponse response = HttpContext.Current.Response;
        response.Clear();

       /*写到客户端并删除服务器端临时文件*/

        response.WriteFile(path);
        string httpHeader = "attachment;filename=test.Xls";
        response.AppendHeader("Content-Disposition", httpHeader);
        response.Flush();
        System.IO.File.Delete(path);//删除临时文件
        response.End();

    }
    /// <summary>
    /// 导出到Excel。
    /// </summary>
    private void ExportExcel(string path)
    {

        string strConn = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + path + ";Extended Properties=ExceL 8.0;";
        OleDbConnection conn = new OleDbConnection(strConn);

        string strExcel = "CREATE TABLE test( 件号 VARCHAR ,责任者 VARCHAR,  文号 VARCHAR ,  题名 VARCHAR ,成文日期 Date,文件页数 Integer,备注 VARCHAR)";
        OleDbCommand myCommand = new OleDbCommand();
        conn.Open();
        myCommand.Connection = conn;
        myCommand.CommandText = strExcel;
        try
        {
            myCommand.ExecuteNonQuery();

            /*把Excel当做数据库往里写数据*/
            myCommand.CommandText = "INSERT INTO test(件号,责任者,文号,题名,成文日期,文件页数,备注) VALUES (@Serial,@Owner,@FileNO,@FileName,@CreateDate,@PageSize,@Remark)";
            OA.Archives.Entities.TList<OA.Archives.Entities.OAArchivesFile> archivesList = printfList;
            for (int i = 0; i < archivesList.Count; i++)
            {
                myCommand.Parameters.Add(new OleDbParameter("@Serial", OleDbType.VarChar));
                myCommand.Parameters["@Serial"].Value = archivesList[i].Serial;
                myCommand.Parameters.Add(new OleDbParameter("@Owner", OleDbType.VarChar));
                myCommand.Parameters["@Owner"].Value = archivesList[i].Owner;
                myCommand.Parameters.Add(new OleDbParameter("@FileNO", OleDbType.VarChar));
                myCommand.Parameters["@FileNO"].Value = archivesList[i].FileNO;
                myCommand.Parameters.Add(new OleDbParameter("@FileName", OleDbType.VarChar));
                myCommand.Parameters["@FileName"].Value = archivesList[i].FileName;
                myCommand.Parameters.Add(new OleDbParameter("@CreateDate", OleDbType.Date));
                myCommand.Parameters["@CreateDate"].Value = archivesList[i].CreateDate.Value.ToShortDateString();
                myCommand.Parameters.Add(new OleDbParameter("@PageSize", OleDbType.Integer));
                myCommand.Parameters["@PageSize"].Value = archivesList[i].PageSize.ToString();
                myCommand.Parameters.Add(new OleDbParameter("@Remark", OleDbType.VarChar));
                myCommand.Parameters["@Remark"].Value = archivesList[i].Remark;
                myCommand.ExecuteNonQuery();
            }
            conn.Close();
        }
        catch (Exception ex)
        {
            conn.Close();
            Response.Write("<script language='javascript'>alert('此表已存在！" + ex.Message + "')</script>");
        }
        Response.Write("<script language='javascript'>alert('导出成功！')</script>");
    }
}